Inserts data into a PostgreSQL table. — pgWriteGeom • rpostgis

This function takes a take an R sf, a SpatVector or sp object (Spatial* or Spatial*DataFrame); or a regular data.frame, and performs the database insert (and table creation, when the table does not exist) on the database.

Usage

pgWriteGeom(
  conn,
  name,
  data.obj,
  geom = "geom",
  df.mode = FALSE,
  partial.match = FALSE,
  overwrite = FALSE,
  new.id = NULL,
  row.names = FALSE,
  upsert.using = NULL,
  alter.names = FALSE,
  encoding = NULL,
  return.pgi = FALSE,
  df.geom = NULL,
  geog = FALSE
)

# S3 method for class 'pgi'
print(x, ...)

Arguments

conn: A connection object to a PostgreSQL database
name: A character string specifying a PostgreSQL schema and table name (e.g., name = c("schema","table")). If not already existing, the table will be created. If the table already exists, the function will check if all R data frame columns match database columns, and if so, do the insert. If not, the insert will be aborted. The argument partial.match allows for inserts with only partial matches of data frame and database column names, and overwrite allows for overwriting the existing database table.
data.obj: A sf,SpatVector, sp-class, or data.frame
geom: character string. For Spatial* datasets, the name of geometry/(geography) column in the database table. (existing or to be created; defaults to "geom"). The special name "geog" will automatically set geog to TRUE.
df.mode: Logical; Whether to write the (Spatial) data frame in data frame mode (preserving data frame column attributes and row.names). A new table must be created with this mode (or overwrite set to TRUE), and the row.names, alter.names, and new.id arguments will be ignored (see dbWriteDataFrame for more information).
partial.match: Logical; allow insert on partial column matches between data frame and database table. If TRUE, columns in R data frame will be compared with the existing database table name. Columns in the data frame that exactly match the database table will be inserted into the database table.
overwrite: Logical; if true, a new table (name) will overwrite the existing table (name) in the database. Note: overwriting a view must be done manually (e.g., with dbDrop).
new.id: Character, name of a new sequential integer ID column to be added to the table for insert (for spatial objects without data frames, this column is created even if left NULL and defaults to the name "gid"). If partial.match = TRUE and the column does not exist in the database table, it will be discarded.
row.names: Whether to add the data frame row names to the database table. Column name will be '.R_rownames'.
upsert.using: Character, name of the column(s) in the database table or constraint name used to identify already-existing rows in the table, which will be updated rather than inserted. The column(s) must have a unique constraint already created in the database table (e.g., a primary key). Requires PostgreSQL 9.5+.
alter.names: Logical, whether to make database column names DB-compliant (remove special characters/capitalization). Default is FALSE. (This must be set to FALSE to match with non-standard names in an existing database table.)
encoding: Character vector of length 2, containing the from/to encodings for the data (as in the function iconv. For example, if the dataset contain certain latin characters (e.g., accent marks), and the database is in UTF-8, use encoding = c("latin1", "UTF-8"). Left NULL, no conversion will be done.
return.pgi: Whether to return a formatted list of insert parameters (i.e., a pgi object; see function details.)
df.geom: Character vector, name of a character column in an R data.frame storing PostGIS geometries, this argument can be used to insert a geometry stored as character type in a data.frame (do not use with Spatial* data types). If only the column name is used (e.g., df.geom = "geom"), the column type will be a generic (GEOMETRY); use a two-length character vector (e.g., df.geom = c("geom", "(POINT,4326)") to also specify a specific PostGIS geometry type and SRID for the column. Only recommended for for new tables/overwrites, since this method will change the existing column type.
geog: Logical; Whether to write the spatial data as a PostGIS 'GEOGRAPHY' type. By default, FALSE, unless geom = "geog".
x: A list of class pgi
...: Further arguments not used.

Value

Returns TRUE if the insertion was successful, FALSE if failed, or a pgi object if specified.

Details

If new.id is specified, a new sequential integer field is added to the data frame for insert. For spatial-only objects (no data frame), a new ID column is created by default with name "gid".

This function will use sf::st_as_text() for geography types, and sf::st_as_binary() for geometry types.

In the event of function or database error, the database uses ROLLBACK to revert to the previous state.

If the user specifies return.pgi = TRUE, and data preparation is successful, the function will return a pgi object (see next paragraph), regardless of whether the insert was successful or not. This object can be useful for debugging, or re-used as the data.obj in pgWriteGeom; (e.g., when data preparation is slow, and the exact same data needs to be inserted into tables in two separate tables or databases). If return.pgi = FALSE (default), the function will return TRUE for successful insert and FALSE for failed inserts.

Use this function with df.mode = TRUE to save data frames from spatial-class objects to the database in "data frame mode". Along with normal dbwriteDataFrame operation, the proj4string of the spatial data will also be saved, and re-attached to the data when using pgGetGeom to import the data. Note that other attributes of spatial objects are not saved (e.g., coords.nrs, which is used to specify the column index of x/y columns in *POINT and SpatialPoints*).

pgi objects are a list containing four character strings: (1) in.table, the table name which will be created or inserted into (2) db.new.table, the SQL statement to create the new table, (3) db.cols.insert, a character string of the database column names to insert into, and (4) insert.data, a character string of the data to insert.

Author

David Bucklin david.bucklin@gmail.com and Adrián Cidre González adrian.cidre@gmail.com

Examples

if (FALSE) { # \dontrun{
library(sf)
pts <- st_sf(a = 1:2, geom = st_sfc(st_point(0:1), st_point(1:2)), crs = 4326)

## Insert data in new database table
pgWriteGeom(conn, name = c("public", "my_pts"), data.obj = pts)

## The same command will insert into already created table (if all R
## columns match)
pgWriteGeom(conn, name = c("public", "my_pts"), data.obj = pts)

## If not all database columns match, need to use partial.match = TRUE,
## where non-matching columns are not inserted
names(pts)[1] <- "b"
pgWriteGeom(conn, name = c("public", "my_pts"), data.obj = pts,
    partial.match = TRUE)
} # }