# [postgis-users] How best to do this?

Stephen Woodbridge woodbri at swoodbridge.com
Thu Feb 1 19:34:38 PST 2007

```Hi all,

Sorry the subject line is not more useful, but let me explain.

1) I have a set of polgons A and another set of polygon B
2) I need to a new set of polygons C that are the union of
A intersect B and A difference B

So basically B partially covers A and I want to split polygons in a into
the covered piece(s) and that that is not covered.

On the surface this is very straight forward, but it isn't ...

insert into newtable (....)
select ..., intersection(A.the_geom, B.the_geom) as the_geom
from A, B
where A.the_geom && B.the_geom and
intersects(A.the_geom, B.the_geom);

This works, but I get a bunch of GEOMETRYCOLLECTION objects in the
results and out of 1022 collection I have 2780 additional POLYGONs.

So I could do something like:

insert into newtable (....)
select * from
(select ..., (dump(the_geom)).geom as the_geom from newtable
where geometrytype(the_geom)='GEOMETRYCOLLECTION' ) as foo
where geometrytype(the_geom)='POLYGON';

delete from newtable where geometrytype(the_geom)='GEOMETRYCOLLECTION';

So now comes the tricky part how do you do the difference? I was
thinking I could take A difference newtable but because I have multiple
little pieces instead of a single piece, I think I am getting:
(A - Part1), (A - Part2), (A - Part3) instead of
(A - Part1 - Part2 - Part3)

So I think I need to union the polygon pieces from each row that is
dumped above. Is that as simple as tossing part of the dump into a
geomunion()? How would that look?

-Steve W

```