[R-sig-Geo] Pointers for querying postGIS and bringing spatial geometries into R

[Edzer Pebesma]

Lyndon,

as a side note, readOGR with the postGIS driver does work nicely, as
shown e.g. here:

http://wiki.intamap.org/index.php/PostGIS

(where no username / passwd was set); more recent (but deeper hidden) I
reported attempts with username / passwd here:
http://cran.r-project.org/web/packages/spacetime/vignettes/stpg.pdf (in
this example, readOGR was not used because only the attribute tables
resulting from spatial and/or temporal selections in postGIS were retrieved)

On 10/10/2012 02:01 PM, Lyndon Estes wrote:
> Hi to everyone,
> 
> Thanks to all of you for the advice on this, which has solved my problem
> quite nicely now.  Based on your suggestions, I figured out the following:
> 
> ogr2ogr was broken for me because I had not completely removed a prior
> custom install of gdal that I had set up for hdf4 support, which required
> libjpeg. Once removed, I can use ogr2ogr fine from the linux shell.
> 
> Since I want to select a particular record from the postgis database, and
> want to keep the object in R without writing it to disk for the time being
> (which would rule out a system call to ogr2ogr as an option), I am using
> the RpostgreSQL option together with rgeos::readWKT.  I am basing this on
> the idea that it is not possible to use sql statements in readOGR, and thus
> can't select an individual geometry from a table (although I see there was
> some talk about this in 2010:
> http://r-sig-geo.2731867.n2.nabble.com/passing-SQL-through-readOGR-td3816889.html).
> 
> 
> library(RPostgreSQL)
> # CRS
> drv <- dbDriver("PostgreSQL")
> con <- dbConnect(drv, dbname = "mydb", user = "me", password = "mypw")
> sql <- "SELECT ST_AsEWKT(geom) from sa1kgrid where id=9"
> kmlGeom <- dbGetQuery(con, sql)
> kmlGeom
> 1 SRID=97490;MULTIPOLYGON(((570779.01553729
> -2401338.07340772,571779.01553729 -2401338.07340772,571779.01553729
> -2402338.07340772,570779.01553729 -2402338.07340772,570779.01553729
> -2401338.07340772)))
> # Comes back as data.frame with SRID, which needs to be stripped out for
> readWKT to work
> geomStr <- unlist(strsplit(kmlGeom[1, ], ";"))
> geomPoly <- readWKT(geomStr[-grep("SRID", geomStr)])
> 
> That seems to work nicely, so thanks for the solution!
> 
> Best, Lyndon
> 
> 
> 
> 
> 
> On Tue, Oct 9, 2012 at 5:21 PM, Edzer Pebesma <edzer.pebesma at uni-muenster.de
>> wrote:
> 
>>
>>
>> On 10/09/2012 12:04 PM, Barry Rowlingson wrote:
>>> There must be an R WKT reader somewhere...
>>
>> rgeos::readWKT
>> --
>> Edzer Pebesma
>> Institute for Geoinformatics (ifgi), University of Münster
>> Weseler Straße 253, 48151 Münster, Germany. Phone: +49 251
>> 8333081, Fax: +49 251 8339763  http://ifgi.uni-muenster.de
>> http://www.52north.org/geostatistics      e.pebesma at wwu.de
>>
>> _______________________________________________
>> R-sig-Geo mailing list
>> R-sig-Geo at r-project.org
>> https://stat.ethz.ch/mailman/listinfo/r-sig-geo
>>
> 

-- 
Edzer Pebesma
Institute for Geoinformatics (ifgi), University of Münster
Weseler Straße 253, 48151 Münster, Germany. Phone: +49 251
8333081, Fax: +49 251 8339763  http://ifgi.uni-muenster.de
http://www.52north.org/geostatistics      e.pebesma at wwu.de