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"