Using R, I would like to import shapefiles that are in a directory on a geometric table I have on PostgreSQL/PostGIS.

What is the instruction that allows me to do it?

EDIT: here is my code to generate contour lines from the PostgreSQL data, the result is a shapefiles I would save .shp on my table PostGIS already create before:

dbname = "postgis"
user = "postgres"
password = "***"
#password = ""

drv <- dbDriver("PostgreSQL")
con <- dbConnect(drv, dbname="BDDMeteo", user=user, password="***",host='localhost', port='5432')
estonia_air_temperature_2=dbGetQuery(con,"select lat,lon,temperature from observation, station, date where id_s=nom_ville AND id_d=id_date")

estonia_air_temperature_2_test <- estonia_air_temperature_2  # duplicate air temp. data file
estonia_air_temperature_2_test$x <- estonia_air_temperature_2_test$lon  # define x & y as longitude and latitude
estonia_air_temperature_2_test$y <- estonia_air_temperature_2_test$lat
coordinates(estonia_air_temperature_2_test) = ~x + y

x.range <- as.numeric(c(-8.5, 9.5))  # min/max longitude of the interpolation area
y.range <- as.numeric(c(26.97, 37))  # min/max latitude of the interpolation area
grd <- expand.grid(x = seq(from = x.range[1], to = x.range[2], by = 0.1), y = seq(from = y.range[1], 
                                                                                  to = y.range[2], by = 0.1))  # expand points to grid
coordinates(grd) <- ~x + y
gridded(grd) <- TRUE
plot(grd, cex = 1.5, col = "grey")
points(estonia_air_temperature_2_test, pch = 1, col = "red", cex = 0.4)

idw <- idw(formula = temperature ~ 1, locations = estonia_air_temperature_2_test, 
           newdata = grd)  # apply idw model for the data
residual_grid = raster(idw, "var1.pred")
x <- rasterToContour(residual_grid)
plot(x, add=TRUE)
writeSpatialShape(x, "/interpolate/contour/contourline")


> ogrDrivers()
             name write
1      AeronavFAA FALSE
2          ARCGEN FALSE
3          AVCBin FALSE
4          AVCE00 FALSE
5             BNA  TRUE
6             CSV  TRUE
7             DGN  TRUE
8             DXF  TRUE
9          EDIGEO FALSE
10 ESRI Shapefile  TRUE
11     Geoconcept  TRUE
12        GeoJSON  TRUE
13       Geomedia FALSE
14         GeoRSS  TRUE
15            GML  TRUE
16            GMT  TRUE
17       GPSBabel  TRUE
18  GPSTrackMaker  TRUE
19            GPX  TRUE
20            HTF FALSE
21         Idrisi FALSE
22            KML  TRUE
23   MapInfo File  TRUE
24         Memory  TRUE
25   MSSQLSpatial  TRUE
26           ODBC  TRUE
27            ODS  TRUE
28        OpenAir FALSE
29    OpenFileGDB FALSE
30         PCIDSK  TRUE
31            PDF  TRUE
32            PDS FALSE
33         PGDump  TRUE
34           PGeo FALSE
35            REC FALSE
36            S57  TRUE
37           SDTS FALSE
39           SEGY FALSE
40            SUA FALSE
41            SVG FALSE
42            SXF FALSE
43          TIGER  TRUE
44        UK .NTF FALSE
45            VRT FALSE
46           Walk FALSE
47           WAsP  TRUE
48           XLSX  TRUE
49         XPlane FALSE
  • what does'R' means in your question...? – GIS Data Butcher Aug 20 '14 at 17:42
  • 1
    R programming language – zina_GIS Aug 20 '14 at 17:49
  • Do you have any working code you could add? – papadoo Aug 20 '14 at 18:13
  • I edited my post – zina_GIS Aug 20 '14 at 18:51
  • Can you clarify which part you're having trouble with? – wildintellect Aug 20 '14 at 18:58

You should use rgdal, specifically writeOGR (which is for vector data) to write the table to Postgis. Note that you must have a spatialdataframe as designated by the sp package (autoloaded with rgdal), I suspect your results of rasterToContour are the correct format.

#R code assumes library(rgdal)
writeOGR(x, "PG:dbname='BDDMeteo' user=user password="zina",host='localhost', port='5432' ", layer_options = "geometry_name=geom", 
"newtablename", "PostgreSQL")
| improve this answer | |
  • Error in writeOGR(x, "PG:dbname='BDDMeteo' user=user password=password,host='localhost', port='5432' ",: No such driver: PostgreSQL help? – zina_GIS Aug 22 '14 at 18:30
  • Use ogrDrivers() to determine which drivers you have available to you. rdocumentation.org/packages/rgdal/functions/readOGR.html – wildintellect Aug 22 '14 at 20:04
  • no it's not available in the ogrDrivers() list ,how to ad it? – zina_GIS Aug 23 '14 at 10:38
  • Depends on operating system and GDAL/OGR install method. – wildintellect Aug 26 '14 at 0:44
  • Thanks for the example of writeOGR() function call , what argument is "newtablename" supposed to be? – Paul Rougieux Feb 24 '16 at 14:32

As many have already said, the availability of drivers in rgdal in R for both raster and vector data always depends on the underlying GDAL installation. If that has the driver, then it will work on readGDAL/writeGDAL or readOGR/writeOGR. If it does not have it, you need to upgrade your own GDAL and reinstall rgdal.

Most questions about drivers come from users of the CRAN Windows or OSX binary rgdal packages. These are built static against GDAL, so any other GDAL on the platform you are using are not seen or used. They are built static to ensure that general use with these binaries is trouble-free, but they cannot be built with all drivers, especially with drivers with extensive external dependencies which cannot be statically linked.

It is unfortunately typical that most questions are asked without information about the platform and loaded package versions (shown by output of sessionInfo()) and about the version of GDAL to which rgdal is linked, shown in the package startup messages displayed when the package loads. In this case we don't know either.

This means that users of drivers not supported by these CRAN binaries must install GDAL first, then install rgdal from source linking dynamically to the local GDAL (with the exception of Kyngchaos rgdal binaries on OSX:


Once gdalinfo/ogrinfo report the presence of the desired drivers from the command line, and possibly after re-installation of rgdal and checking that the correct GDAL shared objects are found both by ./configure on installing rgdal and on loading the package, the drivers will be available in rgdal in R.

| improve this answer | |
  • Hi Roger, welcome to GIS stack exchange. I made some small edits to your post, to remove the section which wasn't directly related to the question, and also your signature (which is accessible via the hyperlink on your username). – Stephen Lead Aug 28 '14 at 6:35

You might try a system like rpostgresql which allows you to simply connect to your postgresql / postgis database rather than exporting the tables out of your database.

Another link:

Database interface and PostgreSQL driver for R This package provides a Database Interface (DBI) compliant driver for R to access PostgreSQL database systems. In order to build and install this package from source, PostgreSQL itself must be present your system to provide PostgreSQL functionality via its libraries and header files. These files are provided as postgresql-devel package under some Linux distributions. On Microsoft Windows system the attached libpq library source will be used. A wiki and issue tracking system for the package are available at Google Code at https://code.google.com/p/rpostgresql/

I attended a presentation from one of the GIS folks at the National Renewable Energy Lab and they're using a lot of R and PostgreSQL, and he mentioned there are tools such as these that allow you to point your R scripts at existing PostgreSQL tables and vice versa...

| improve this answer | |
  • I edited my post – zina_GIS Aug 20 '14 at 18:52
  • This won't work in this case because RPostgresql can't handle spatial field of postgis. – wildintellect Aug 20 '14 at 18:57

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.

Given that you already have a functional connection con established through RPostgreSQL, you can upload any Spatial* objects from R to PostGIS using the function pgInsert, for instance:

pgInsert(con, "estonia_air_temperature_2_test", estonia_air_temperature_2_test, 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:

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

If you don't want the table in the public schema (which would be a good idea anyway), you will need to declare it using c("schema", "table") instead.

Corresponding functions for raster are also available (pgWriteRast/pgGetRast).

| improve this answer | |
  • This answer is almost exactly equal another of yours (this one), because the question is almost the same too, correct? Because you are new here, one thing you might not know yet is that instead of answering twice, one question should be marked as duplicate from the other, that way all answers are placed in just one canonical Q/A. See more on the help center page. – Andre Silva Dec 27 '17 at 21:13
  • It is essentially a duplicate, yes. I assumed that, given the amount of answer already provided, that it would make little sense to mark it as duplicate now… But I see you just did it. What would be the right approach now? There is some information provided in each one of my answers that precisely get to one point, so merging would require some adjustment. – Mathieu Basille Dec 29 '17 at 6:48
  • The other Q could be the dupe, but I found it more objective so I chose this one (there is room to discuss which one). The feature of marking questions as duplicates is a big advantage of SE, so there is no need to answer the same questions again and again, and all the useful content can remain in just one place (in theory). The most important is that you got it. – Andre Silva Dec 29 '17 at 13:31

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