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

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

Best regards,

On 02/06/2012 11:33 PM, Vivien Deparday wrote:
> 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]]
> 
> _______________________________________________
> R-sig-Geo mailing list
> R-sig-Geo at r-project.org
> https://stat.ethz.ch/mailman/listinfo/r-sig-geo

-- 
Edzer Pebesma
Institute for Geoinformatics (ifgi), University of Münster
Weseler Straße 253, 48151 Münster, Germany. Phone: +49 251
8333081, Fax: +49 251 8339763  http://ifgi.uni-muenster.de
http://www.52north.org/geostatistics      e.pebesma at wwu.de