Writeogr to PostgreSQL/PostGIS database with R

Question

I'm producing some Spatialpolygondataframes with R that I would like to upload to my PostGIS Database. I've found out that the way to do this is via the writeOGR() function using the PostgreSQL driver.

Sadly, my version of rgdal and GDAL/OGR doesn't support the PostgreSQL function (as seen when listing the available drivers using ogrDrivers()). Is there a way to make this driver available? My sessionInfo() is as follows:

R version 3.2.3 (2015-12-10)
Platform: x86_64-w64-mingw32/x64 (64-bit)
Running under: Windows >= 8 x64 (build 9200)

[...]

other attached packages:
rgdal_1.1-10
gdalUtils_2.0.1.7
RPostgreSQL_0.4-1

Answer 1

Edit :

As of 2021, the preferred solution would be to use the {sf} package, even when using {sp} classes :

library(sf)
library(RPostgres)

con <- dbConnect(Postgres(), user = "PG_USER", password = "PG_PASS", host = "192.0.2.0",dbname = "dbname")
st_write(st_as_sf(my_layer), con, Id(schema = "public", table = "my_layer"))

---

Former answer :

The package PostGIStools can help. See for example the vignette.

Another way could be to transform your Spatial*DataFrame geometry to WKT, insert into PostGIS using the classic RPostgreSQL package and re-create the geom there.

Answer 2

If this is still relevant, at the University of Florida, David Bucklin and I have released a rpostgis package that provides bi-directional transfer between PostGIS and R for vector and raster data. The package does not rely on GDAL (and rgdal), and should be platform independent.

Assuming that a functional connection can be established through RPostgreSQL, here is a minimal reproducible example that demonstrates its use with respect to the original question (upload a SpatialPolygonsDataFrame to PostGIS). Specifically, using the low resolution map of the world from rworldmap:

library(rpostgis)
conn <- dbConnect(drv = "PostgreSQL", host = "localhost", dbname = "rpostgis", user = "postgres", password = "postgres_password")

library("rworldmap")
world <- getMap()
pgInsert(conn, "world", world, new.id = "gid")

Note that the new.id argument will create a new column name for a sequential ID ("gid"). To retrieve the data back into R as a SpatialPolygonsDataFrame, use pgGetGeom:

world.db <- pgGetGeom(conn, "world")