2

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
4

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.

  • I've tried the first method and it works like a charm using the postgis_insert() command (in case you want to includ this in your answer). If you will allow me a followup question: Do you know how do address a schema with the postgis_insert() command? – Ratnanil May 27 '16 at 19:22
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")
  • 3
    Thank you for joining and answering. But please don't post duplicate answers in multiple threads: either vary your posts in meaningful ways or else select one thread for your answer. – whuber Dec 22 '17 at 17:06
  • 2
    Who is the "we" you refer to? Please disclose your affiliation as required in What kind of behavior is expected of users? – Midavalo Dec 22 '17 at 20:25
  • 2
    Fair points, I apologize for cross-posting and keeping my answer minimal. As some of the initial questions were fairly outdated, my proposition may not be relevant any more for the OP… hence the brevity. I'd be happy to provide more details if this still makes sense. – Mathieu Basille Dec 23 '17 at 20:25
  • 2
    As for who's we, that's me (Mathieu Basille) and David Bucklin, both authors of the R package rpostgis. I also understand that it looks like shameless advertising, but I really meant to be useful: the package (which is open-source with exactly no benefits generated from it) was developed to provide exactly what the (various) OP originally asked for, namely to provide bi-directional transfer between PostGIS and R for vector and raster data. – Mathieu Basille Dec 23 '17 at 20:30
  • 1
    I don't understand the negative critic and downvotes. It seems your package solves exactly the issue I had in my original question. The issue isn't relevant to me currently, but I'll definitely will get back to this solution as soon as it does. – Ratnanil Dec 26 '17 at 8:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.