Description Usage Arguments Details Value References See Also Examples
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 sp
package).
1 2 | get_postgis_query(conn, statement, geom_name = NA_character_,
hstore_name = NA_character_)
|
conn |
A |
statement |
Character string for a SQL SELECT query. |
geom_name |
Name of the geometry column ( |
hstore_name |
Name of the hstore column ( |
Conversion to spatial data frame objects will fail if there are NULL
values in the geometry column, so these should be filtered out in the provided
query statement.
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.
The %->%
operator for working with hstore columns;
postgis_insert
and 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)
|
Add the following code to your website.
For more information on customizing the embed code, read Embedding Snippets.