[postgis-users] How best to do this?

[Stephen Woodbridge]

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