PostGIS merge and subset selection

On Tue, Feb 7, 2012 at 6:45 AM, Edzer Pebesma

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