[postgis-users] Combining / agglomerating / clustering numbers of adjacent polygons

[Derek Jones]

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.