passing SQL through readOGR()

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.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

Roger,

I'd be happy to help. If I get it correct, the only missing piece is a WKB
or WKT driver to convert to sp class (and vice-versa)?

Etienne