Provides an interface between R and 'PostGIS'-enabled 'PostgreSQL' databases to transparently transfer spatial data. Both vector (points, lines, polygons) and raster data are supported in read and write modes. Also provides convenience functions to execute common procedures in 'PostgreSQL/PostGIS'.
rpostgis provides an interface between R and
PostgreSQL databases to transparently transfer spatial data. Both
vector (points, lines, polygons) and raster data are supported in read
and write modes. Also provides convenience functions to execute common
You can install the latest released version from CRAN:
You can then use
update.packages() to update to the latest CRAN version.
For the latest (possibly unstable) development version, use:
install_github("mablab/rpostgis", ref = "dev")
rpostgis relies on a working connection provided by the
RPostgreSQL package to a PostgreSQL database, e.g.:
conn <- RPostgreSQL::dbConnect("PostgreSQL", host = "localhost", dbname = "<DB_NAME>", user = "<USER>", password = "<PASSWORD>")
Once the connection is established, the first step is to check if the
PostGIS already installed (and install it if it's not
If the function returns
TRUE, the database is ready and functional.
You can check the geometries and rasters present in the database with:
pgListGeom(conn, geog = TRUE) pgListRast(conn)
To terminate the session, close and clear the connection with:
Full documentation with the complete list of functions of the package
can be found on
fixed bug affecting
pgWriteRast, affecting rasters with irregular x/y resolutions.
fixed bug affecting
pgInsert, where single-part polygons with holes were
counted as MultiPolygons, causing the geometry column to be defined as MultiPolygon.
This did not affect import into the database, but caused an error when single-part
polygons (without holes) were attempted to be inserted into the existing table.
rpostgis now has a new home: https://mablab.org/rpostgis/
Documentation prepared using
dbReadDataFrame, where datetimes were assigned the PostgreSQL time zone instead of the system timezone. This resulted in incorrect time assignments when the PostgreSQL time zone setting did not match the system's time zone setting. Tables in PostgreSQL written by
dbWriteDataFramewere not affected and have the correct time assigned.
dbIndex: support added for keys and indexes
referencing multiple columns.
blocks argument to specify exact number of
desired blocks for the new raster in PostGIS table. Default block
blocks = NULL) will retain previous functionality, though
the minimum block size (rows or columns) was increased to 100
pgWriteRast: fixed alignment issue affecting high-precision (generally decimal-degree unit) based raster writing, where PostGIS was not able to add a same alignment constraint to all tiles in the newly-created raster.
pgListRastto list all raster columns in a PostGIS database
pgGetRast: re-written for faster reading of rasters from the
database. Multi-band raster import is now supported (
bands, and can be given a vector of integers, or TRUE
to return all bands). Default is still to import the first band (1)
of the raster. Multi-band rasters imported as
objects. When raster blocks have imperfect alignment, PostGIS
ST_SnapToGrid is applied to register the raster according
to the upper left pixel of the full raster dataset.
pgWriteRast: now support sp-class
SpatialPixels* type objects. The class of the raster written in
pgWriteRast is saved in a database column, and re-applied to the
raster during import with
"Data frame mode" for Spatial* objects: now saves proj4string of
Spatial* objects in data frame mode writing with
pgInsert. It is
then re-imported using
pgGetGeom, if the saved proj4string and the
database proj4string (defined by the column's SRID) are
equivalent. Otherwise, the database proj4string is used.
pgWriteRast: original R proj4string is saved in the
raster database table, and re-imported, if the saved proj4string and
the database proj4string (defined by the column's SRID) are
equivalent. Otherwise, the database proj4string is used.
pgGetGeom: now includes
boundary parameter, to spatially subset
GEOMETRY/GEOGRPAHY objects to return (same usage as in
pgGetBoundary: now includes
clauses parameter, with same usage
pgWriteRast: now attempts to write a new SRID to
spatial_ref_sys, if one cannot be resolved from the raster's
All query-constructor functions (e.g.
dbDrop) now return nothing
(previously returned TRUE) if
exec = FALSE.
other.cols = FALSE now not necessary when reading
only a geometry column (e.g., in query mode or from a one-column
pgWriteRast: fixed bug affecting imported raster's metadata
regarding the upper left pixel location (returned by PostGIS
pgWriteRast was erroneously applying the lower left pixel location
for this value. This did not affect the actual data of the raster,
or their usage within PostGIS, however it could have affected export
of this raster to external files, or viewing in a GIS.
pgWriteRast: fixed bug where constraints were not written for
tables given without a schema name in
pgGetGeom: fixed bug for one-column
dbReadDataFrame: infolocs columns of type
POSIXlt now re-import
time zone attribute correctly (previously just used database time
GEOGRAPHYtypes added, in
Spatial*object can be uploaded to PostGIS as a geography using
geog = TRUE.
pgWriteRast: fixed bug causing failed uploads when
multiple PostGIS SRIDs found with
pgSRID for a spatial object, now
uses the first one returned.
pgGetGeom: fixed bug when loading non-spatial columns with names
'x' or 'y' for point geometries.
digitsargument default lowered to 5 (same as
raster::rasterFromXYZ). Resolution of the raster is now determined using PostGIS functions and directly applied to the R raster. This allows (in some cases) for faster recognition of uneven cell sizes, which result in an error.
query argument allows
pgGetGeom to specify a
full SQL query which returns a GEOMETRY instead of an existing
table/view. If desired, the query can be saved in the database as a
new view using the
pgInsert: now can insert geometries stored as character in
pgGetGeom, drop with
pgGetGeom: fixed bug causing failed imports for views due to usage
RPostgreSQL::dbListFields (changed to
fixed bug for data frame mode writing (e.g.,
pgInsert(...,df.mode = TRUE) and
dbWriteDataFrame) where factor names included a comma.
pgWriteRast to upload R Raster* datasets to PostGIS database
dbReadDataFrame: Write/read in data frame mode
to/from database table (see below).
pgInsert can write in data frame mode when
df.mode = TRUE
pgInsert now defaults to 'alter.names = FALSE'.
pgGetGeom will try to read Spatial*DataFrames in data frame mode
(will not affect import for non-data frame mode tables).
pgGetRast now has a
band argument to select which band in the
database raster to import.
pgGetGeom: Fix bug affecting one-column selections with
other.colsargument for line and polygon imports.
ABOUT DATA FRAME MODE
Writing in data frame mode is only for new database tables (or for
overwriting an existing one). It will save all column names as they
appear in R, along with column data types and attributes. This is
done by adding metadata to a lookup table in the table's schema named
'.R_df_defs' (will be created if not present). It also adds two field
with fixed names to the database table: '.R_rownames' (storing the
row.names of the data frame), and '.db_pkid', which is a new integer
primary key. Existing columns in the
data.frame matching these names
will be automatically changed.
Spatial*DataFrames writing should continue to use
which can write in data frame mode when
df.mode = TRUE. For more
flexible writing of
data.frames to the
database (including all inserts into existing database tables), use
df.mode = FALSE (default).
pgGetGeom: Fix bug which was causing errors when using 'ORDER BY ...' statements in the
dbBuildTableQuery (internal): Builds CREATE TABLE query for a data
dbExistsTable (internal): Check if a PostgreSQL table exists.
dbConnCheck (internal): Check if a supported PostgreSQL connection.
dbVersion(internal): PostgreSQL version checking.
pgInsertnow provides 'upsert' functionality (INSERT ON CONFLICT UPDATE) with the 'upsert.using' argument. Requires PostgreSQL version 9.5+.
pgPostGISnow returns TRUE for non-standard installs.
pgGetGeomnow handles all regular PostGIS Geometry data table imports.
Big package cleaning.
Changed all non-PostGIS related functions to
db- instead of
All functions not returning an actual object now returns
Change 'str' to 'query'
messages instead of
cat to print SQL code.
pgPostGIS: Check and create PostGIS extension.
pgSchema: Now returns TRUE if the schema exists (whether it was already available or was just created).
pgis2rpackage, incorporating support for loading geometry/raster objects stored in PostgreSQL databases in R.
pgGetRast: Load raster from PostGIS database.
pgGetLines: Load a PostGIS linestring geometry from a PostgreSQL
table/view into R.
pgGetPolys: Load a PostGIS polygon geometry from a PostgreSQL
table/view into R.
pgGetPts: Retrieve point geometries.
pgColumn: Add or remove a column (replaces
pgMakeStp: Add a POINT or LINESTRING geometry field.
display = TRUEto print the executed or non-executed query.
Non executed examples added for each function.
display = TRUE by defaults for all functions.
pgAsDate: Converts to timestamp.
pgDrop: Drop table/view/schema.
pgSchema: Create schema.
pgComment: Allows comments on schemas.
Change the package name to
db- function to
pg- function, in order to avoid
confusion with standard
dbAddKey: Add key.
dbComment: Comment table/view.
dbDropColumn: Removing a Column.
dbIndex: CREATE INDEX.