rpostgis (version 1.0.0)

pgInsert: Inserts data into a PostgreSQL table.

Description

This function takes a take an R sp object (Spatial* or Spatial*DataFrame), or a regular data frame, and performs the database insert (and table creation, when the table doesn't exist) on the database.

Usage

pgInsert(conn, name, data.obj, geom = "geom", partial.match = FALSE, overwrite = FALSE, new.id = NULL, alter.names = TRUE, encoding = NULL, return.pgi = FALSE)
"print"(x, ...)

Arguments

conn

A connection object to a PostgreSQL database

name

Character, schema and table of the PostgreSQL table to insert into. 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 Spatial* or Spatial*DataFrame, or data frame

geom

character string. For Spatial* datasets, the name of geometry column in the database table. (existing or to be created; defaults to "geom").

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.

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 databse table, it will be discarded.

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.)

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.)

x

A list of class pgi

...

Further arguments not used.

Value

Returns TRUE if the insertion was successful, or a pgi object if specified, or in the case of database error.

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 is created by default with name "gid".

If the R package wkb is installed, this function will use writeWKB for certain datasets (non-Multi types, non-Linestring), which is faster for large datasets. In all other cases the rgeos function writeWKT is used.

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

On database errors, or if the user specifies return.pgi = TRUE, the function will return a pgi object (see next paragraph). This object can be re-used as the data.obj in pgInsert; (e.g., when inserting the exact same data into tables in two separate tables or databases). If return.pgi = FALSE (default), the function will return TRUE, indicating successful insert.

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.

Examples

## Not run: 
# library(sp)
# data(meuse)
# coords <- SpatialPoints(meuse[, c("x", "y")])
# spdf <- SpatialPointsDataFrame(coords, meuse)
# 
# ## Insert data in new database table
# pgInsert(conn, name = c("public", "meuse_data"), data.obj = spdf)
# 
# ## The same command will insert into already created table (if all R
# ## columns match)
# pgInsert(conn, name = c("public", "meuse_data"), data.obj = spdf)
# 
# ## If not all database columns match, need to use partial.match = TRUE
# colnames(spdf@data)[4] <- "cu"
# pgInsert(conn, name = c("public", "meuse_data"), data.obj = spdf,
#     partial.match = TRUE)
# ## End(Not run)