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

[nicklas.aven at jordogskog.no]

I don't know if I have misunderstood this, but to me it looks like what you are doing is:

create table agglom_areas as
select (st_dump(st_union(the_geom))).geom as split_geom
from     grid_squares;

with cascaded union (geos 3.1 or higher) this should befast as I understand it.

But if I have missunderstood everything, just ignore it :-)

/Nicklas

2009-11-05 Derek Jones  wrote:

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
>> 
>_______________________________________________
>postgis-users mailing list
>postgis-users at postgis.refractions.net
>http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20091105/209037ea/attachment.html>