postGIStools (version 0.2.4)

get_postgis_query: Send SELECT query and parse geometry, hstore columns

Description

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).

Usage

get_postgis_query(conn, statement, geom_name = NA_character_,
  hstore_name = NA_character_)

Arguments

conn

A PostgreSQLConnection-class object, such as the output of dbConnect.

statement

Character string for a SQL SELECT query.

geom_name

Name of the geometry column (NA if none).

hstore_name

Name of the hstore column (NA if none).

Value

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.

Details

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.

References

The code for importing geom fields is based on a blog post by Lee Hachadoorian: Load PostGIS geometries in R without rgdal.

See Also

The %->% operator for working with hstore columns; postgis_insert and postgis_update for writing to a PostgreSQL connection.

Examples

# 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"
# }
# NOT RUN {
# }