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

[Vivien Deparday]

Thank you for your suggestions. I ended up using PostgreSQL Views as I 
still feel more comfortable with SQL. I had only one issue is that I had 
two geometry columns (polygon and centroids) in my view and because of 
that I was getting "Layer not found" with readOGR. Is the only 
workaround to create two views, one with each geometry?
What are the plans on implementing a -sql argument for readOGR?

Thank you,

Vivien

On 02/07/2012 03:37 AM, Barry Rowlingson wrote:
> 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
>
> _______________________________________________
> R-sig-Geo mailing list
> R-sig-Geo at r-project.org
> https://stat.ethz.ch/mailman/listinfo/r-sig-geo