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
df$geom <- st_as_sfc(df$geom)
Then, the entire
df was converted into a
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
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"