Derek, Which version of PostgreSQL are you using?
Interestingly we are demonstrating some of these kinds of exercises in our upcoming book "PostGIS in Action" book well actually we are in the middle of writing those chapters now -- and cleaning up some of our examples - so they aren't quite ready to disseminate without a lot of explanation. But all these tricks require the new PostgreSQL 8.4 window function and/or recursive query (common table expression) functionality. Sound like the recursive query might be closer to what you want (where you keep on accumulating based on some condition and then start a new bucket). There are also several techniques for creating on the fly grids in PostGIS which would help out here. Hope that helps, Regina -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Derek Jones Sent: Tuesday, November 03, 2009 5:27 PM To: [email protected] Subject: [postgis-users] Combining / agglomerating / clustering numbers ofadjacent polygons Dear list, I have been using postgres/postgis for some years, and written a fair amount of spatial SQL and pgsql and R and Perl based queries. However, I've hit a brick wall in my thinking (November time change ;-) perhaps? ) and wondered if someone could suggest a method to help out. I have a spatial data set that varies in density across a region. I have constructed a grid (did it in qgis) and overlayed on the region, and then derived the density of items per grid area. Where the density is >= 3 per area, I retain the grid square in a new table. Then I want to combine the grid squars to form a larger region and (then incidentally to this question) find the centroid of that region where the grid areas touch. Singletons are rejected, and, if, say, I had 10000 original grid squares, I might end up with 1000 clusterings of squares unioned together of arbitrary eventual shapes. Touching here - adjacency - could mean colinear or diagonal with 2 minimum. The maximum # of adjacent grid squares could run to 1000s per unioned area. But, I'm having a hard time to think through how to write the query. There are no columns in common across the grid squares, otherwise it'd be an easy st_union where col_xyz = 'PQR' kind of select, but all I have is the spatial adjacency. I've looked through the archives for the past couple of days, and perhaps I'm just using the wrong search criteria but I haven't seen anything that helps. I can see how to do this programmatically by agglomerating clusters and deleting from the remaining set as I go (and if I did that I would probably export the data and do it externally because it's quite a large data set), but I was really wanting to write a query for it and let it chug. Any thoughts? Kind regards Derek Jones. _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
