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

[Derek Jones]

Dear all,

I solved the problem with just a handful of lines of SQL in the end... :-P

Kinda roundabout thinking but it worked. Here's the solution:

grid_squares contains my reduced grid set from the complete grid overlay 
from a previous select - it's the density table. I also previously 
removed singletons from it with a check for any grid entries that had 0 
touching entities.

# Break out the multipolygon grids into polygons

create table
   temp_e
as
   select
     st_GeometryN
     (
       the_geom,
       generate_series(1,st_NumGeometries(the_geom))
     ) as split_geom
   from
     grid_squares;

# Break out the polygons into linestrings and agglomerate into areas
# Becomes one big area as a multipolygon so ...

create table
   temp_f
as
   select
     st_buildarea
     (
       st_collect
       (
         st_ExteriorRing(split_geom)
       )
     ) as the_geom
   from
     temp_e;

# ... afterwards, break out into polygons again
# each of which is the clustered set of
# polygons comprising the outer boundary agglomeration
# of the original grid areas.

create table
   agglom_areas
as
   select
     st_GeometryN
     (
       the_geom,
       generate_series(1,st_NumGeometries(the_geom))
     ) as split_geom
   from
     temp_f;



I can probably with a little work reduce the steps but I don't have a 
need to at this point.

This does exactly what I wanted - hope it's useful perhaps to others in 
the same jam. Appreciate the help given in any event.

Now I can use the generated areas to get me some centroids and then both 
intersect and then cluster the "other" data set I have which is 10s of 
1000s of points and different to this one but related to it.

Thank you again. Always useful to bounce these kinds of things around...

Kind regards

Derek Jones