Load a PostGIS geometry from a PostgreSQL table/view/query into R.
Source:R/pgGetGeom.R
pgGetGeom.Rd
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 thesf
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. Settingother.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
orsp
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 defaultboundary = 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' forsp
objects.
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);")
} # }