[postgis-users] computing unions of intersecting polygons

Martin Davis mbdavis at refractions.net
Tue Jan 13 09:44:26 PST 2009

The way I'd look at doing this is to compute a relation which contains 
pairs (poly_id1, poly_id2) for all polygons which intersect.  Then 
compute the transitive closure of this relation to find all connected 
sets of polygons.  (Until the recursive CTE support emerges in 
PostgresQL 8.4, you'll probably need to do this procedurally in 
pgplsql).  Label each group with the smallest id in that group.  Then 
you can use an aggregate query over the transitive closure table to 
compute unions and counts.

Reid Priedhorsky wrote:
> Dear all,
> I have a table which contains about 10,000 fairly simple polygons (~20 
> vertices, no interior rings). Some of these polygons intersect each 
> other.
> I would like the union polygon of each group of intersecting polygons, 
> and ideally how many polygons were unioned together in each group.
> What's the best way to do this?
> I have a plan which seems to be promising, something along the lines of:
>    insert into bar select st_union(geometry) from foo;
>    select (st_dump(geometry)).geom from bar;
> ... but it is quite slow, it requires an intermediate table, and it 
> doesn't tell me how many polygons were unioned in each group.
> I'm running version 1.3.3.
> Any suggestions?
> Thanks,
> Reid
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users

Martin Davis
Senior Technical Architect
Refractions Research, Inc.
(250) 383-3022

More information about the postgis-users mailing list