<html>
<head>
</head>
<body>I don't know if I have misunderstood this, but to me it looks like what you are doing is:<br />
<br />
create table agglom_areas as<br />
select (st_dump(st_union(the_geom))).geom as split_geom<br />
from grid_squares;<br />
<br />
with cascaded union (geos 3.1 or higher) this should befast as I understand it.<br />
<br />
But if I have missunderstood everything, just ignore it :-)<br />
<br />
/Nicklas<br />
<br />
2009-11-05 Derek Jones wrote:<br />
<br />
Dear Leo<br />
><br />
>Hey thanks!<br />
><br />
>Since these and other steps can run for several tens of minutes or even <br />
>hours because of the data densities, I didn't notice because I left them <br />
>each cooking and did something else instead, but I appreciate any <br />
>improvements here because I may well be running the same kind of query <br />
>elsewhere also.<br />
><br />
>Occam's Razor and all that ;-)<br />
><br />
>Kind regards<br />
><br />
>Derek.<br />
><br />
>Paragon Corporation wrote:<br />
>> <br />
>> Derek,<br />
>> <br />
>> FWIW -- this is a bit faster and shorter to write than your last step. You<br />
>> probably won't notice the difference until you start dealing with huge<br />
>> geometry collections.<br />
>> <br />
>> --REVISED LAST STEP<br />
>> create table<br />
>> agglom_areas<br />
>> as<br />
>> SELECT (ST_Dump(the_geom)).geom As split_geom<br />
>> FROM temp_f;<br />
>> <br />
>> <br />
>> <br />
>> ---YOUR LAST STEP--<br />
>> <br />
>> create table<br />
>> agglom_areas<br />
>> as<br />
>> select<br />
>> st_GeometryN<br />
>> (<br />
>> the_geom,<br />
>> generate_series(1,st_NumGeometries(the_geom))<br />
>> ) as split_geom<br />
>> from<br />
>> temp_f;<br />
>> <br />
>> <br />
>> Leo<br />
>> -----Original Message-----<br />
>> From: postgis-users-bounces@postgis.refractions.net<br />
>> [mailto:postgis-users-bounces@postgis.refractions.net] On Behalf Of Derek<br />
>> Jones<br />
>> Sent: Wednesday, November 04, 2009 10:26 PM<br />
>> Cc: PostGIS Users Discussion<br />
>> Subject: [postgis-users] SOLVED: Combining / agglomerating / clustering<br />
>> numbers of adjacent polygons<br />
>> <br />
>> Dear all,<br />
>> <br />
>> I solved the problem with just a handful of lines of SQL in the end... :-P<br />
>> <br />
>> Kinda roundabout thinking but it worked. Here's the solution:<br />
>> <br />
>> grid_squares contains my reduced grid set from the complete grid overlay<br />
>> from a previous select - it's the density table. I also previously removed<br />
>> singletons from it with a check for any grid entries that had 0 touching<br />
>> entities.<br />
>> <br />
>> # Break out the multipolygon grids into polygons<br />
>> <br />
>> create table<br />
>> temp_e<br />
>> as<br />
>> select<br />
>> st_GeometryN<br />
>> (<br />
>> the_geom,<br />
>> generate_series(1,st_NumGeometries(the_geom))<br />
>> ) as split_geom<br />
>> from<br />
>> grid_squares;<br />
>> <br />
>> # Break out the polygons into linestrings and agglomerate into areas #<br />
>> Becomes one big area as a multipolygon so ...<br />
>> <br />
>> create table<br />
>> temp_f<br />
>> as<br />
>> select<br />
>> st_buildarea<br />
>> (<br />
>> st_collect<br />
>> (<br />
>> st_ExteriorRing(split_geom)<br />
>> )<br />
>> ) as the_geom<br />
>> from<br />
>> temp_e;<br />
>> <br />
>> # ... afterwards, break out into polygons again # each of which is the<br />
>> clustered set of # polygons comprising the outer boundary agglomeration # of<br />
>> the original grid areas.<br />
>> <br />
>> create table<br />
>> agglom_areas<br />
>> as<br />
>> select<br />
>> st_GeometryN<br />
>> (<br />
>> the_geom,<br />
>> generate_series(1,st_NumGeometries(the_geom))<br />
>> ) as split_geom<br />
>> from<br />
>> temp_f;<br />
>> <br />
>> <br />
>> <br />
>> I can probably with a little work reduce the steps but I don't have a need<br />
>> to at this point.<br />
>> <br />
>> This does exactly what I wanted - hope it's useful perhaps to others in the<br />
>> same jam. Appreciate the help given in any event.<br />
>> <br />
>> Now I can use the generated areas to get me some centroids and then both<br />
>> intersect and then cluster the "other" data set I have which is 10s of 1000s<br />
>> of points and different to this one but related to it.<br />
>> <br />
>> Thank you again. Always useful to bounce these kinds of things around...<br />
>> <br />
>> Kind regards<br />
>> <br />
>> Derek Jones<br />
>> _______________________________________________<br />
>> postgis-users mailing list<br />
>> postgis-users@postgis.refractions.net<br />
>> http://postgis.refractions.net/mailman/listinfo/postgis-users<br />
>> <br />
>> <br />
>> <br />
>> _______________________________________________<br />
>> postgis-users mailing list<br />
>> postgis-users@postgis.refractions.net<br />
>> http://postgis.refractions.net/mailman/listinfo/postgis-users<br />
>> <br />
>_______________________________________________<br />
>postgis-users mailing list<br />
>postgis-users@postgis.refractions.net<br />
>http://postgis.refractions.net/mailman/listinfo/postgis-users<br />
><br />
>
</body>
</html>