get_postgis_query: Send SELECT query and parse geometry, hstore columns
In postGIStools: Tools for Interacting with 'PostgreSQL' / 'PostGIS' Databases

Description Usage Arguments Details Value References See Also Examples

View source: R/get_postgis_query.R

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

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.

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