Re: [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.gottfr...@wzw.tum.de
<mailto:tom.gottfr...@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.gottfr...@wzw.tum.de
        <mailto:tom.gottfr...@wzw.tum.de>
        <mailto:tom.gottfr...@wzw.tum.de 
<mailto:tom.gottfr...@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@stat.math.ethz.ch <mailto:R-sig-Geo@stat.math.ethz.ch>
        <mailto:R-sig-Geo@stat.math.ethz.ch 
<mailto:R-sig-Geo@stat.math.ethz.ch>>

        https://stat.ethz.ch/mailman/listinfo/r-sig-geo


_______________________________________________
R-sig-Geo mailing list
R-sig-Geo@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.biv...@nhh.no

_______________________________________________
R-sig-Geo mailing list
R-sig-Geo@stat.math.ethz.ch
https://stat.ethz.ch/mailman/listinfo/r-sig-geo