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

Description Usage Arguments Details Value Author(s) Examples

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

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

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 column. (Default = "geom")

gid

Name of the column in name holding the IDs. Should be unique if additional columns of unique data are being appended. 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.

query

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

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.

Value

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

Author(s)

David Bucklin dbucklin@ufl.edu

Mathieu Basille basille@ufl.edu

Examples

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
## Not run: 
## 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 field = '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);")

## End(Not run)


Search within the rpostgis 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.