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

[Tom Gottfried]

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

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