rpostgis (version 1.4.3)

pgGetGeom: Load a PostGIS geometry from a PostgreSQL table/view/query into R.

Description

Retrieve point, linestring, or polygon geometries from a PostGIS table/view/query, and convert it to an R sp object (Spatial* or Spatial*DataFrame).

Usage

pgGetGeom(
  conn,
  name,
  geom = "geom",
  gid = NULL,
  other.cols = TRUE,
  clauses = NULL,
  boundary = NULL,
  query = NULL
)

Value

sp-class (SpatialPoints*, SpatialMultiPoints*, SpatialLines*, or SpatialPolygons*)

Arguments

conn

A connection object to a PostgreSQL database

name

A character string specifying a PostgreSQL schema and table/view name holding the geometry (e.g., name = c("schema","table"))

geom

The name of the geometry/(geography) column. (Default = "geom")

gid

Name of the column in name holding the IDs. Should be unique for each record to return. gid=NULL (default) automatically creates a new unique ID for each row in the sp object.

other.cols

Names of specific columns in the table to retrieve, in a character vector (e.g. other.cols=c("col1","col2").) The default (other.cols = TRUE) is to attach all columns in a Spatial*DataFrame. Setting other.cols=FALSE will return a Spatial-only object (no data frame).

clauses

character, additional SQL to append to modify select query from table. Must begin with an SQL clause (e.g., "WHERE ...", "ORDER BY ...", "LIMIT ..."); see below for examples.

boundary

sp object or numeric. A Spatial* object, whose bounding box will be used to select geometries to import. Alternatively, four numbers (e.g. c([top], [bottom], [right], [left])) indicating the projection-specific limits with which to subset spatial data. boundary = NULL (default) will not subset by spatial extent. Note this is not a true 'clip'- all features intersecting the bounding box with be returned unmodified.

query

character, a full SQL query including a geometry column. For use with query mode only (see details).

Author

David Bucklin david.bucklin@gmail.com

Mathieu Basille basille@ufl.edu

Details

The query mode version of pgGetGeom allows the user to enter a complete SQL query (query) that returns a Geometry column, and save the query as a new view (name) if desired. If (name) is not specified, a temporary view with name ".rpostgis_TEMPview" is used only within the function execution. In this mode, the other arguments can be used normally to modify the Spatial* object returned from the query.

Definitions for tables written in "data frame mode" are automatically applied using this function, including proj4strings of the Spatial*-class object. Note that if the proj4string of the original dataset is not found to be equivalent to the database proj4string (using pgSRID), it will not be applied.

Examples

Run this code
if (FALSE) {
## Retrieve a Spatial*DataFrame with all data from table
## 'schema.tablename', with geometry in the column 'geom'
pgGetGeom(conn, c("schema", "tablename"))
## Return a Spatial*DataFrame with columns c1 & c2 as data
pgGetGeom(conn, c("schema", "tablename"), other.cols = c("c1","c2"))
## Return a Spatial*-only (no data frame), 
## retaining id from table as rownames
pgGetGeom(conn, c("schema", "tablename"), gid = "table_id",
  other.cols = FALSE)
## Return a Spatial*-only (no data frame), 
## retaining id from table as rownames and with a subset of the data
pgGetGeom(conn, c("schema", "roads"), geom = "roadgeom", gid = "road_ID",
    other.cols = FALSE, clauses  = "WHERE road_type = 'highway'")
## Query mode
pgGetGeom(conn, query = "SELECT r.gid as id, ST_Buffer(r.geom, 100) as geom 
                           FROM
                             schema.roads r,
                             schema.adm_boundaries b
                           WHERE 
                             ST_Intersects(r.geom, b.geom);")
}

Run the code above in your browser using DataCamp Workspace