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

[Paragon Corporation]

Derek,

FWIW -- this is a bit faster and shorter to write than your last step.  You
probably won't notice the difference until you start dealing with huge
geometry collections.

--REVISED LAST STEP
create table
   agglom_areas
as
   SELECT (ST_Dump(the_geom)).geom As split_geom
	FROM temp_f;
    


---YOUR LAST STEP--

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


Leo
-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Derek
Jones
Sent: Wednesday, November 04, 2009 10:26 PM
Cc: PostGIS Users Discussion
Subject: [postgis-users] SOLVED: Combining / agglomerating / clustering
numbers of adjacent polygons

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
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users