Send SELECT query and parse geometry, hstore columns
This function is an extension of
dbGetQuery that is useful
in cases where selected columns include a PostgreSQL hstore, which is parsed
as a list-column, and/or a PostGIS geometry, in which case the output is a
spatial data frame (from the
Character string for a SQL SELECT query.
Name of the geometry column (
Name of the hstore column (
Conversion to spatial data frame objects will fail if there are
values in the geometry column, so these should be filtered out in the provided
Either a data frame (if
geom_name = NA) or a
Spatial[Points/Lines/Polygons]DataFrame containing the query result. If a
hstore column is present, it appears as a list-column in the data frame,
i.e. each cell is a named list of key-value pairs.
The code for importing geom fields is based on a blog post by Lee Hachadoorian: Load PostGIS geometries in R without rgdal.
%->% operator for working with hstore columns;
postgis_update for writing
to a PostgreSQL connection.
1 2 3 4 5 6 7 8 9 10 11 12
## Not run: library(RPostgreSQL) con <- dbConnect(PostgreSQL(), dbname = "my_db") # If geom column holds points, returns a SpatialPointsDataFrame cities <- get_postgis_query(con, "SELECT name, geom, datalist FROM city", geom_name = "geom", hstore_name = "datalist") # Get the populations (part of datalist hstore) as a vector pop <- cities@data$datalist %->% "population" ## End(Not run)