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

Retrieve geometries from a PostGIS table/view/query, and convert it to an R sf object.

Usage

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

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 sf 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. Setting other.cols=FALSE will return a Spatial-only object without attributes (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

sf, SpatVector or sp object; or numeric. If a spatial object is provided, its bounding box will be used to select geometries to import. Alternatively, a numeric vector (c([top], [bottom], [right], [left])) indicating the projection-specific limits with which to subset the spatial data. If not value is provided, the default boundary = NULL will not apply any boundary subset.

query

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

returnclass

'sf' by default; 'terra' for SpatVector; or 'sp' for sp objects.

Value

sf, SpatVector or sp object

Details

The features of the table to retrieve must have the same geometry type. 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.

Author

David Bucklin david.bucklin@gmail.com

Mathieu Basille mathieu@basille.org

Adrián Cidre González adrian.cidre@gmail.com

Examples

if (FALSE) { # \dontrun{
## Retrieve a sf with all data from table
## 'schema.tablename', with geometry in the column 'geom'
pgGetGeom(conn, c("schema", "tablename"))
## Return a sf 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);")
} # }