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 does not exist)
on the database.
pgInsert(
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 pgi
print(x, ...)
Returns TRUE
if the insertion was successful,
FALSE
if failed, or a pgi
object if specified.
A connection object to a PostgreSQL database
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.
A Spatial*
or Spatial*DataFrame
, or data.frame
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.
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).
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.
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
).
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.
Whether to add the data frame row names to the database table. Column name will be '.R_rownames'.
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+.
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.)
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.
Whether to return a formatted list of insert parameters
(i.e., a pgi
object; see function details.)
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.
Logical; Whether to write the spatial data as a PostGIS
'GEOGRAPHY' type. By default, FALSE, unless geom = "geog"
.
A list of class pgi
Further arguments not used.
David Bucklin david.bucklin@gmail.com
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"
.
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.
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 pgInsert
;
(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 codedf.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 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.
if (FALSE) {
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,
## where non-matching columns are not inserted
colnames(spdf@data)[4] <- "cu"
pgInsert(conn, name = c("public", "meuse_data"), data.obj = spdf,
partial.match = TRUE)
}
Run the code above in your browser using DataLab