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

[Brian Modra]

2009/11/4 Derek Jones <scunacc at yahoo.com>:
> 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.

Sounds interesting. Are you combining the squares so that you get a
roughly "rasterised" shape that represents the denser areas of your
spatial data?

I'd write a plpgsql function to do it. But rather than use postgis to
find the adjacency (in this case because its a), rather use a naming
convention for the squares that makes it simple to combine with
adjacency based on maybe just an integer x,y coordinate system, where
the top left square is 0,0 then next one to its right is 1,0 etc.Then
use some algorithms from raster graphics to combine the "pixels" into
"polygons".

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



-- 
Brian Modra   Land line: +27 23 5411 462
Mobile: +27 79 69 77 082
5 Jan Louw Str, Prince Albert, 6930
Postal: P.O. Box 2, Prince Albert 6930
South Africa
http://www.zwartberg.com/