I perform spatial analyses using a mixture desktop gis, postgis, and R. With the introduction of the 'sf' package's use of WKT list-columns to represent geometry this makes 'sf' objects equivalent to spatial db tables in postgis. Currently, though, dbplyr, using the rpostgres package backend, does not recognize geom columns when querying a database. Is there a plan at rstudio to allow for better integration of sf and dbplyr to allow for the recognition of geom columns or this something that requires an external issue for the group working on DBI?
Hi @jamgreen , if the filtering and other transformations are based on the non-geo fields, then after you pair down your data set to what you need, you should be able to collect() and then use the sf package's st_as_sf() function to mutate() the collected field into something that can be plotted. Does this satisfy what you need to do?
I shall try this out. I have to wrap the st_as_sf() within the mutate?
Thank you. And if I do have operations on geo fields?
Thank you.
In the ensuing months I have come back to this issue. I attempted to use the st_as_sf solution but it consistently returned errors because the geom column once called from the database does not come in as a list column but as a simple character field and st_as_sf cannot read it.
Error in mutate_impl(.data, dots) :
Evaluation error: no applicable method for 'st_as_sf' applied to an object of class "character".
This is due to RPostgreSQL inability to read the geom column. This remains an issue for me as a spatial analyst and while I understand rstudio does not maintain RPostgreSQL is there anything possible on your end for adding better geom support in dbplyr?
Is it WKT geometry? Try sf::st_as_sfc on the column itself. Then you can use st_as_sf on the data frame as a whole, and you can control which column gets used, what its name is and so on. This is a little fraught since this text-only geometry won't come with a CRS, so there's a little more required to pass that metadata into sf itself. That's why neither of sf or DBI will ever be able to make this completely seamless, there's too many options - but the tools are there and are atomic enough to be able to piece things together.
Also, a spatial DB will be able to cast geometries to many forms, so there's lots of ways this can be handled if one particular way doesn't work. I don't see enough information in your posts though to be able to provide good answers. Can you at least give a table summary, a sample of the geometry column, etc.
1 Like
I'm still a bit lost as to how to use dbplyr with geometry columns. I tried using st_as_sfc(structure... as suggested by Hadley but I could not get this to work with dbplyr lazy evaluation. So I ended up collecting the entire query result dbplyr connection and then converting the WKB text using st_as_sfc(structure....
Is this the intended way to do it?
Thanks
code bellow:
stations <- tbl(con, "stations")
points <- stations %>%
select(coords) %>%
collect()
points <- st_as_sfc(structure(points$coords, class = 'WKB'), EWKB = TRUE)
The sf::st_read supports reading from spatial postgres database; it will load the data locally (equivalent to a collect() call - so no linking, & you can not offload the heavy lifting to a database backend, at present) but you can do something like
shape <- sf::st_read(con, query = 'select something, something_else, geometry
from a_postgres_table
where a_condition_is_met;')
and you will end up with a data.frame of class sf, ready for further processing.