get_postgis_query: Send SELECT query and parse geometry, hstore columns

Description Usage Arguments Details Value References See Also Examples

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

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

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.

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.

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

 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)


Search within the postGIStools package
Search all R packages, documentation and source code

Questions? Problems? Suggestions? or email at ian@mutexlabs.com.

Please suggest features or report bugs with the GitHub issue tracker.

All documentation is copyright its authors; we didn't write any of that.