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

[Derek Jones]

Dear Leo

Hey thanks!

Since these and other steps can run for several tens of minutes or even 
hours because of the data densities, I didn't notice because I left them 
each cooking and did something else instead, but I appreciate any 
improvements here because I may well be running the same kind of query 
elsewhere also.

Occam's Razor and all that ;-)

Kind regards

Derek.

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