PostGIS merge and subset selection

Dear all,

I have been reading around the mailing list but I have not been able to 
find a somewhat easy solution to my issue.
I have a PostgreSQL / PostGIS database that I am accessing with R. The 
database is organized in a way that there is one large table that has 
all the geometry records (two columns, one with centroid, one with 
polygons) and then I have several tables that have attributes related to 
these geometries. The link between the tables is made with a unique ID.
The second constraint is that when I am doing calculation in R, I want 
to retrieve only a subset of the lines. And in the end, I want to make 
some choropleth maps. So I suppose that I want to create a 
SpatialPolygonsDataFrame (and SpatialPointsDataFrame) that contains my 
subset of geometries and attribute values and then map that.

- I have tried to use rgdal and readOGR. I used readOGR to retrieve all 
the geometries (I found several references in the mailing about 
implementing -sql for readOGR [1] but I don't think it has been 
implemented yet? I would have to use system(ogr2ogr ... ) ?)  and then I 
tried to do the merge with the example here [2]
But I get the following error: Error: length(Sr at polygons) == nrow(data) 
is not TRUE
I suppose it is due to the fact that my attribute table has a subset of 
the lines whereas the geometry table retrieved with OGR has all the values?
A solution could be to use ogr2ogr but it is becoming quite cumbersome.

- Given my constraints, it may be easier to use RODBC. So I managed to 
retrieve my geometries through RODBC. Then I am stuck as I don' t know 
how to convert my two geometry fields (polygons and points) into spatial 
object. There are detected as Factors by default. I have seen a similar 
questions in the archive but the advice was to use readOGR. Would there 
be a way to create spatial objects from the RODBC query?

For points I can follow [1]:
SELECT attr, ST_X(the_geom) AS x, ST_Y(the_geom) AS y FROM yourtable 
WHERE ...")
coordinates(data_frame) <- ~x+y

But how could I do that with polygons?

Thank you,

Vivien

[1] 
http://r-sig-geo.2731867.n2.nabble.com/passing-SQL-through-readOGR-td3816889.html 

[2] 
http://r-sig-geo.2731867.n2.nabble.com/merging-data-with-SpatialPolygonsDataFrame-td2764348.html


	[[alternative HTML version deleted]]