2

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:

library(ggplot2)
library(gstat)
library(sp)
library(maptools)
library(rgdal)
library(zoo)
library(xts)
library(RPostgreSQL)
library(spacetime)
library(raster)
library(foreign)
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

plot(estonia_air_temperature_2_test)
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")
plot(residual_grid)
x <- rasterToContour(residual_grid)
class(x)
plot(residual_grid)
plot(x, add=TRUE)
writeSpatialShape(x, "/interpolate/contour/contourline")

Edit:

> 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
38       SEGUKOOA 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
6
  • what does'R' means in your question...? Aug 20, 2014 at 17:42
  • 1
    R programming language
    – zina_GIS
    Aug 20, 2014 at 17:49
  • Do you have any working code you could add?
    – papadoo
    Aug 20, 2014 at 18:13
  • I edited my post
    – zina_GIS
    Aug 20, 2014 at 18:51
  • Can you clarify which part you're having trouble with? Aug 20, 2014 at 18:58

4 Answers 4

3

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")
6
  • Error in writeOGR(x, "PG:dbname='BDDMeteo' user=user password=password,host='localhost', port='5432' ",: No such driver: PostgreSQL help?
    – zina_GIS
    Aug 22, 2014 at 18:30
  • Use ogrDrivers() to determine which drivers you have available to you. rdocumentation.org/packages/rgdal/functions/readOGR.html Aug 22, 2014 at 20:04
  • no it's not available in the ogrDrivers() list ,how to ad it?
    – zina_GIS
    Aug 23, 2014 at 10:38
  • Depends on operating system and GDAL/OGR install method. Aug 26, 2014 at 0:44
  • Thanks for the example of writeOGR() function call , what argument is "newtablename" supposed to be? Feb 24, 2016 at 14:32
1

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:

http://www.kyngchaos.com/software/frameworks#gdal

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.

1
  • 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). Aug 28, 2014 at 6:35
1

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:

library(rpostgis)
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).

3
  • 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. Dec 27, 2017 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. Dec 29, 2017 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. Dec 29, 2017 at 13:31
0

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:
http://cran.r-project.org/web/packages/RPostgreSQL/index.html

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...

2
  • I edited my post
    – zina_GIS
    Aug 20, 2014 at 18:52
  • This won't work in this case because RPostgresql can't handle spatial field of postgis. Aug 20, 2014 at 18:57

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