reading PostGIS table into sp data frame

The GDAL was installed from www.gisinternals.com/sdk; I installed the 
32 bits version, though my Windows version is 64: the table on 
gisinternals.com stated that there was a build problem with the 
64-bits version.

rgdal was installed from CRAN, as a binary, version 0.7-20, build 
2.15.1. R version is 2.15.2 (2012-10-26) -- "Trick or Treat", platform
x86_64-w64-mingw32/x64 (64-bit).

The output from ogrDrivers() is pasted below.

If I understand you correctly, and since I am using the standard CRAN 
Windows binary package, I might be out of luck?

Cheers,

Edward

Output from 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        OpenAir FALSE
28         PCIDSK  TRUE
29            PDS FALSE
30         PGDump  TRUE
31           PGeo FALSE
32            REC FALSE
33            S57  TRUE
34           SDTS FALSE
35       SEGUKOOA FALSE
36           SEGY FALSE
37            SUA FALSE
38            SVG FALSE
39          TIGER  TRUE
40        UK .NTF FALSE
41            VFK FALSE
42            VRT FALSE
43         XPlane FALSE

-----Original Message-----
From: Roger Bivand [mailto:Roger.Bivand at nhh.no]
Sent: 20 December 2012 20:58
To: Edward Vanden Berghe
Cc: r-sig-geo at r-project.org
Subject: Re: [R-sig-Geo] reading PostGIS table into sp data frame

On Thu, 20 Dec 2012, Edward Vanden Berghe wrote:

Thanks!

Unfortunately I have no luck trying to get readOGR to work directly 
with the PostGIS database. I tried different variations:

Did you check whether your rgdal/GDAL supports the PostGIS driver? Did you look at the output of ogrDrivers()? If you did not install rgdal as a source package built against a GDAL that has the PostGIS driver on your platform (maybe OSGeo4W??), but are using the standard CRAN Windows binary package, then it is simply the case that the driver is not supported. On OSX using the Kyngchaos frameworks, or on Linux, this would be simple, but it isn't simple on Windows. You need to establish how rgdal was installed and report that.

Hope this clarifies,

Roger

sp.object <- readOGR(dsn="PG:dbname=...
sp.object <- readOGR(dsn="my_dsn" ... [where my_dsn is a Windows 
system dsn] sp.object <- readOGR(dsn="PG:my_dsn" ...

all result in the same error message:

	Error in ogrInfo(dsn = dsn, layer = layer, input_field_name_encoding = input_field_name_encoding) :
	  Cannot open file

Another variation,

sp.object <- readOGR(dsn="ODBC:my_dsn" ...

Results in

	Error in ogrInfo(dsn = dsn, layer = layer, input_field_name_encoding = input_field_name_encoding) :
	  Multiple # dimensions:

though all he geometries are 2d, and there is a constraint enforcing 
this in the database.

It seems rgdal is properly installed: I can use readOGR to read shape 
files (and this, actually, resolves my problem, as I can use this as 
a work-around); also, ogrDrivers() returns a list of 43 drivers (but 
PG, PostgreSQL or PostGIS are not among these; ESRI Shapefile and ODBC are).

I have no problems connecting to the PostgreSQL database using either 
DBI/RPostgreSQL, or RODBC (the latter with the same Windows system 
DSN that I used for the tests above). I am not familiar with GDAL, 
don't know how to try and connect to the PostgreSQL database from 
GDAL directly, rather than through rgdal.

Again, any insight in what might go wrong would be most appreciated.

Cheers,

Edward

Message: 4
Date: Wed, 19 Dec 2012 14:58:52 +0100
From: Raffaele Morelli <raffaele.morelli at gmail.com>
To: r-sig-geo at r-project.org
Subject: Re: [R-sig-Geo] reading PostGIS table into sp data frame
Message-ID:
	<CAD4guxOt5fSUPbK4VbbPuZf-oTBrcY-UvNPcguvUYt4qS0+_kQ at mail.gmail.com>
Content-Type: text/plain

2012/12/19 Edward Vanden Berghe <evberghe at gmail.com>

I wanted to create a global map with squares in lat-lon. I have 
PostGIS tables to define these squares  but I havent been able to 
figure out an efficient way of reading those tables into R. The code I am using now is:

       crs <- CRS("+proj=longlat +ellps=WGS84")
       s <- paste("select id, st_astext(geom) as geom from 
geo.cs10d";",
sep="")
       r <- dbGetQuery(con, s)
       p <- readWKT(r$geom[1],id=r$id[1],p4s=crs)
       for(i in 2:length(r$id)){
              p <- rbind(p, readWKT(r$geom[i], id=r$id[i], p4s=crs))
       }

where geo.cs10d is the table with squares, id the primary key of the 
table, and geom the binary geometry field.

The code above works fine for the larger squares, such as 10 
degrees, of which I only need 648 to cover the globe. For finer 
resolutions, the above takes just too long  I assume because the 
rbind function rewrites the whole sp object each time it executes. 
Ive seen other R scripts that initiate an empty data frame of the 
correct length to go round similar problems with the rbind function; 
I havent been able to find an equivalent for spatial polygons. How 
can I initiate an empty data frame with the right structure, and the right length?

A preferable solution would be if there would be a single function 
to load a complete PostGIS table, rather than having to load the 
polygons one by one in a loop. Is there such a function?

Im using PostgreSQL 8.4, PostGIS 1.5, R 2.15.2, platform 
x86_64-w64-mingw32; IDE is StatET 3.0.1 plugin for Eclipse 3.7.2.

Any help would be much appreciated.

Edward

Use postgis st_transfom to convert your table in epsg:4326, I would 
suggest to use a view for that.
Then use readOGR (in package rgdal) to read the table/view (geom and
attributes) in a sp object with :

sp.object <- readOGR("PG:dbname=your_db host=your_host user=username 
password=xxx", "geo.cs10d ")

regards
-r

--
Roger Bivand
Department of Economics, NHH Norwegian School of Economics, Helleveien 30, N-5045 Bergen, Norway.
voice: +47 55 95 93 55; fax +47 55 95 95 43
e-mail: Roger.Bivand at nhh.no