[R-sig-Geo] passing SQL through readOGR()

[Roger Bivand]

On Mon, 15 Nov 2010, Tom Gottfried wrote:

> Hi Etienne,
>
> so far I only did it with points:
>
> data_frame <- sqlQuery(odbcConnect("yourdatabase"),
> 	"SELECT attr, ST_X(the_geom) AS x, ST_Y(the_geom) AS y FROM yourtable 
> WHERE ...")
> coordinates(data_frame) <- ~x+y

Because "OGR SQL has been reimplemented for GDAL/OGR 1.8.0", see: 
http://www.gdal.org/ogr/ogr_sql.html, and the RFC: 
http://trac.osgeo.org/gdal/wiki/rfc28_sqlfunc, it doesn't seem sensible to 
make changes in readOGR() now for released GDAL/OGR 1.7.3. As rgdal is now 
on R-Forge, I would welcome contributions and other input so that a 
new-generation readOGR() is ready soon after GDAL/OGR 1.8.0 is released, 
perhaps later this year or early next year.

Roger

>
> regards,
> Tom
>
> Am 15.11.2010 17:01, schrieb Etienne Bellemare:
>> Thanks Tom,
>> 
>> But, can I ask how you switch from a data.frame to a spatial feature ? I 
>> get
>> 
>> 'data.frame':    17657 obs. of  3 variables:
>>   $ gid      : int  11533765 11534718 11535010 11535664 11535802 11535953 
>> 11536125 11536126 11536263
>> 11536264 ...
>>   $ hauteurcm: int  1474 1212 1331 1494 1296 1621 1305 1753 1588 1504 ...
>>   $ the_geom : chr "0101000020850B000000000000270D1441000000F0E48C5441"
>> "0101000020850B000000000000330D144100000030E48C5441"
>> 
>> Do you have some code to transform the_geom, or are you using st_astext 
>> (with, I guess, some code) ?
>> 
>> Thanks,
>> Etienne
>> 
>> On Mon, Nov 15, 2010 at 10:44 AM, Tom Gottfried <tom.gottfried at wzw.tum.de
>> <mailto:tom.gottfried at wzw.tum.de>> wrote:
>>
>>     Hi Etienne,
>>
>>     I still do as described in the mail you answered (through RODBC). An 
>> alternative is described
>>     here: https://stat.ethz.ch/pipermail/r-sig-geo/2010-May/008256.html
>>
>>     regards,
>>     Tom
>>
>>     Am 15.11.2010 16:21, schrieb Etienne Bellemare:
>>
>>         Hi Tom,
>>
>>         I'd like to know how you finally implemented your solution. I'm 
>> facing the same problem.
>>
>>         Etienne
>>
>>         On Tue, Oct 13, 2009 at 11:34 AM, Tom Gottfried 
>> <tom.gottfried at wzw.tum.de
>>         <mailto:tom.gottfried at wzw.tum.de>
>>         <mailto:tom.gottfried at wzw.tum.de 
>> <mailto:tom.gottfried at wzw.tum.de>>> wrote:
>>
>>             Hi list,
>>
>>             is there a way to pass any SQL-statement through readOGR (as 
>> with the -sql Option to
>>         ogr2ogr). I
>>             want to import a subset of a large dataset from PostGIS into R. 
>> I know it's possible
>>         with for
>>             example RODBC and then coercing the resulting data.frame to an 
>> sp-class, but I wonder if
>>         I can do it
>>             directly through readOGR().
>>
>>             Thanks!
>>             Tom
>>
>>             _______________________________________________
>>             R-sig-Geo mailing list
>>         R-sig-Geo at stat.math.ethz.ch <mailto:R-sig-Geo at stat.math.ethz.ch>
>>         <mailto:R-sig-Geo at stat.math.ethz.ch 
>> <mailto:R-sig-Geo at stat.math.ethz.ch>>
>>
>>         https://stat.ethz.ch/mailman/listinfo/r-sig-geo
>> 
>
> _______________________________________________
> R-sig-Geo mailing list
> R-sig-Geo at stat.math.ethz.ch
> https://stat.ethz.ch/mailman/listinfo/r-sig-geo
>

-- 
Roger Bivand
Economic Geography Section, Department of Economics, Norwegian School of
Economics and Business Administration, 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