I am using sf::st_write() to load data into a PostGIS database. In order to do this, I first had to convert the geometry column of my dataframe from a character to a sfc vector.

df$geom <- st_as_sfc(df$geom)

Then, the entire df was converted into a sf object.

df <- st_as_sf(df)

Once I have an sf object, I then write my data to a PostGIS database.

# db connection object
con <- dbConnect(drv = RPostgres::Postgres()
    ,dbname = db
    ,host = host
    ,port = port
    ,password = pw
    ,user = user

# write sf dataframe to db
st_write(dsn = con
    ,obj = df
    ,table = 'table_name'
    ,query = 'INSERT INTO table_name ON CONFLICT DO NOTHING;'
    ,layer_options = 'OVERWRITE=true'
    ,drop_table = FALSE
    ,try_drop = FALSE
    ,debug = TRUE
    ,append = TRUE


The above code works fine, but problems arise once I use the st_read() function to pull data from my database.

df1 <- st_read(dsn = con
    ,geom_column = 'geom' # name of my geometry column
    ,table = 'table_name'
    ,query = 'SELECT * FROM table_name;'

Error in st_sf(x, ..., agr = agr, sf_column_name = sf_column_name) : 
  no simple features geometry column present

I tried calling dbReadTable() instead, but ran into related issues. I can read in the data, but I am unable to convert from a pq_geometry class to sfc.

df1 <- dbReadTable(conn = con
    ,name = 'table_name'

df1$geom <- st_as_sfc(df1$geom)

Error in UseMethod("st_as_sfc") : 
  no applicable method for 'st_as_sfc' applied to an object of class "pq_geometry"

Try use st_read. I was trying to do the same thing and st_read works for me.

|improve this answer|||||
  • I was able to get this working with st_read() after setting EWKB = TRUE. The following code ended up working for me: st_read(dsn = con, geom_column = "geom", EWKB = TRUE, query = "SELECT * FROM table_name;") – philiporlando Sep 7 '18 at 2:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.