`r lifecycle::badge("deprecated")`
This function has been deprecated in favour of [pgWriteGeom()] and will be removed in a future release.
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.
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 st_as_text
for geography types, and
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 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 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 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.
Usage
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
)
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 argumentpartial.match
allows for inserts with only partial matches of data frame and database column names, andoverwrite
allows for overwriting the existing database table.- data.obj
A
Spatial*
orSpatial*DataFrame
, ordata.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 setgeog
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
, andnew.id
arguments will be ignored (seedbWriteDataFrame
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 tablename
. 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., withdbDrop
).- 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"
). Ifpartial.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 toFALSE
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, useencoding = c("latin1", "UTF-8")
. LeftNULL
, 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"
.
Author
David Bucklin david.bucklin@gmail.com
Examples
if (FALSE) { # \dontrun{
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)
} # }