Setup

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
    )

Issue

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"
up vote 0 down vote accepted

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

  • 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;") – spacedSparking Sep 7 at 2:08

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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