pgInsertizeGeom: Format R data objects for insert into a PostgreSQL table.

In rpostgis: R Interface to a 'PostGIS' Database

Format R data objects for insert into a PostgreSQL table.

Description

These are internal rpostgis functions that take an R sp or sf object (Spatial*, Spatial*DataFrame or sf; for pgInsertizeGeom), or data frame (for pgInsertize) and return a pgi list object, which can be used in the function pgInsert to insert rows of the object into the database table. (Note that these functions do not do any modification of the database, it only prepares the data for insert.) The function pgInsert is a wrapper around these functions, so pgInsertize* should only be used in situations where data preparation and insert need to be separated.

Usage

pgInsertizeGeom(
  data.obj,
  geom = "geometry",
  create.table = NULL,
  force.match = NULL,
  conn = NULL,
  new.id = NULL,
  row.names = FALSE,
  alter.names = FALSE,
  partial.match = FALSE,
  df.mode = FALSE,
  geog = FALSE
)

pgInsertize(
  data.obj,
  create.table = NULL,
  force.match = NULL,
  conn = NULL,
  new.id = NULL,
  row.names = FALSE,
  alter.names = FALSE,
  partial.match = FALSE,
  df.mode = FALSE
)

Arguments

data.obj	A Spatial* or Spatial*DataFrame, or data frame for pgInsertize.
geom	character string, the name of geometry column in the database table. (existing or to be created; defaults to 'geom').
create.table	character, schema and table of the PostgreSQL table to create (actual table creation will be done in later in pgWriteGeom().) Column names will be converted to PostgreSQL-compliant names. Default is NULL (no new table created).
force.match	character, schema and table of the PostgreSQL table to compare columns of data frame with. If specified with partial.match = TRUE only columns in the data frame that exactly match the database table will be kept, and reordered to match the database table. If NULL, all columns will be kept in the same order given in the data frame.
conn	A database connection (if a table is given in for "force.match" parameter)
new.id	character, name of a new sequential integer ID column to be added to the table. (for spatial objects without data frames, this column is created even if left NULL and defaults to the name "gid").
row.names	Whether to add the data frame row names to the database table. Column name will be '.R_rownames'.
alter.names	Logical, whether to make database column names DB-compliant (remove special characters). Default is TRUE. (This should to be set to FALSE to match to non-standard names in an existing database table using the force.match setting.)
partial.match	Logical; if force.match is set and true, columns in R data frame will be compared with an the existing database table name. Only columns in the data frame that exactly match the database table will be inserted into the database table.
df.mode	Logical; Whether to write data 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.
geog	Logical; Whether to write the spatial data as a PostGIS 'GEOGRAPHY' type.

Details

The entire data frame is prepared by default, unless force.match specifies a database table (along with a database connection conn), in which case the R column names are compared to the force.match column names, and only exact matches are formatted to be inserted.

A new database table can also be prepared to be created using the create.table argument. If new.id is specified, a new sequential integer field is added to the data frame. For Spatial*-only objects (no data frame), a new.id is created by default with name gid. For pgInsertizeGeom, if the R package wkb is installed, this function uses writeWKB to translate the geometries for some spatial types (faster with large datasets), otherwise the rgeos function writeWKT is used.

Value

pgi A list containing four character strings: (1) in.table, the table name which will be created or inserted into, if specified by either create.table or force.match (else NULL) (2) db.new.table, the SQL statement to create the new table, if specified in create.table (else NULL), (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. See examples for usage within the pgInsert function.

Author(s)

David Bucklin [e-mail]

Examples

## Not run: 
library(sp)
data(meuse)
coords <- SpatialPoints(meuse[, c("x", "y")])
spdf <- SpatialPointsDataFrame(coords, meuse)

## Format data for insert
pgi.new <- pgInsertizeGeom(spdf, geom = "point_geom", create.table = c("schema",
    "table"), new.id = "pt_gid")
print(pgi.new)

## Insert data in database table (note that an error will be given if
## all insert columns do not have exactly matching database table
## columns)
pgWriteGeom(conn = conn, data.obj = pgi.new)

## Inserting into existing table
pgi.existing <- pgInsertizeGeom(spdf, geom = "point_geom", force.match = c("schema",
    "table"), conn = conn)
## A warning message is given, since the "dist.m" column is not found
## in the database table (it was changed to "dist_m" in pgi.new to
## make name DB-compliant). All other columns are prepared for insert.
print(pgi.existing)

pgWriteGeom(conn = conn, data.obj = pgi.existing)

## End(Not run)
## Not run: 
## Format regular (non-spatial) data frame for insert using
## pgInsertize connect to database
data <- data.frame(a = c(1, 2, 3), b = c(4, NA, 6), c = c(7,
    "text", 9))

## Format non-spatial data frame for insert
values <- pgInsertize(data.obj = data)

## Insert data in database table (note that an error will be given if
## all insert columns do not match exactly to database table columns)
pgWriteGeom(conn, data.obj = values, name = c("schema", "table"))

## Run with forced matching of database table column names
values <- pgInsertize(data.obj = data, force.match = c("schema",
    "table"), conn = conn)

pgWriteGeom(conn, data.obj = values)

## End(Not run)

rpostgis documentation built on Nov. 20, 2023, 5:07 p.m.