rpostgis (version 1.0.0)

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

Description

These are internal rpostgis functions that take an R sp object (Spatial* or Spatial*DataFrame; 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 = "geom", create.table = NULL, force.match = NULL, conn = NULL, new.id = NULL, alter.names = TRUE, partial.match = FALSE)
pgInsertize(data.obj, create.table = NULL, force.match = NULL, conn = NULL, new.id = NULL, alter.names = TRUE, partial.match = 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 pgInsert().) 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").

alter.names

Logical, whether to make database column names DB-compliant (remove special characters). Defualt 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.

Value

pgi A list containing four character strings: (1) in.table, the table name which will be created or inserted into, if specifed 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.

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.

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)
# pgInsert(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)
# 
# pgInsert(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)
# pgInsert(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)
# 
# pgInsert(conn, data.obj = values)
# ## End(Not run)