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