[R-sig-Geo] PostGIS merge and subset selection

[Barry Rowlingson]

On Tue, Feb 7, 2012 at 6:45 AM, Edzer Pebesma
<edzer.pebesma at uni-muenster.de> wrote:
> Vivien, the following might give you some pointers:
>
> library(spacetime)
> vignette("stpg")
> edit(vignette("stpg"))
>
> to run the postGIS thing, you have to uncomment all the eval=FALSE sections.
>

 Not sure if Edzer's vignette is doing this, but another way might be
to use a VIEW on the database to do your joining and selecting. You
can use the R PostgreSQL database driver to do this. You'll also have
to add the view to the geometry_columns table to make it 'spatial'.
Then you can get it via readOGR.

I can dig out more details later, but basically you run some SQL like:

 CREATE VIEW fred AS SELECT * FROM polygons,attributes WHERE
polygons.id = attributes.id AND attributes.name='fred';

SQL strings like that can be constructed in R and sent to the database
via the usual R database tools.

 If you create database indexes on the id variables then this will be
speedy, although note it is done each time you query the view - ie the
results of the view aren't stored on the database.

Barry