[postgis-users] Combining / agglomerating / clustering numbers ofadjacent polygons

[Paragon Corporation]

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: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Derek
Jones
Sent: Tuesday, November 03, 2009 5:27 PM
To: postgis-users at postgis.refractions.net
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
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users