rpostgis now has a new home: https://mablab.org/rpostgis/
Documentation prepared using
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 size (
blocks = NULL) will retain previous functionality, though the minimum block size (rows or columns) was increased to 100 (previously 80).
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 (
band argument changed to
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
RasterBrick-class objects. When raster blocks have imperfect alignment, PostGIS function
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 as in
pgWriteRast: now attempts to write a new SRID to
spatial_ref_sys, if one cannot be resolved from the raster’s proj4string.
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 table).
pgWriteRast: fixed bug affecting imported raster’s metadata regarding the upper left pixel location (returned by PostGIS function
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 zone).
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
pgWriteRast to upload R Raster* datasets to PostGIS database tables.
dbReadDataFrame: Write/read in data frame mode to/from database table (see below).
pgInsert can write in data frame mode when
df.mode = TRUE (default is
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
pgInsert, 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 frame object.
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+.
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
TRUE if successful.
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.
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