[postgis-users] Problem trying to clip multipolygons in one file from multipolygons in another.

[L Bogert-O'Brien]

Hi,

I am trying to clip the water polygons (y2006.ca_wat_cbf_clp wat) 
from the census tract polygons (y2006.ca_ct_cbf ct) and store the 
clipped census tracts in another table (y2006.on_ct_cbf_clp).  The 
following is the latest query I tried:

INSERT INTO y2006.on_ct_cbf_clp (ctuid, cmauid, pruid, the_geom_4269)
(SELECT ct.ctuid, ct.cmauid, ct.pruid,
         COALESCE(ST_MULTI(cleangeometry(
                           ST_DIFFERENCE(ct.the_geom_4269, 
wat.the_geom_4269))),
                  ct.the_geom_4269)
   FROM y2006.ca_ct_cbf ct LEFT OUTER JOIN y2006.ca_wat_cbf_clp wat
     ON ct.the_geom_4269 && wat.the_geom_4269
  WHERE ct.pruid = '35'
  ORDER BY ct.ctuid);

When I left the COALESCE out, I did not get any geometry field for 
the census tracts that did not have bounding boxes intersecting with 
bounding boxes of water.  The ctuid, ct.cmauid and ct.pruid fields 
were transferred, but the geometry field was empty.  Now, however, I 
get 1 to 3 rows for each census tract in my new table.  When there 
are three rows, one is the original geometry, another is the water 
portion and the third is the land portion (i.e. the only part I want 
to be in left in the new file).  When there are two rows, this 
appears to be for areas where bounding boxes intersect, but there are 
no actual water pieces intersecting the census tract.  Thus the 
original geometry is entered on two lines, whereas I only want it 
once.  When there is one row for a census tract, it is just what I 
want, the original geometry for the areas that have no water parts 
intersecting them.

Can anyone suggest a fix?

Note that I also got the following warnings output when running this query:

NOTICE:  LWGEOM_gist_joinsel called with incorrect join type
NOTICE:  The input type <NULL> is not supported

..[snip]... about 200 more of these <NULL> warnings

NOTICE:  The input type <NULL> is not supported
NOTICE:  The input type <NULL> is not supported
NOTICE:  The input type <NULL> is not supported
NOTICE:  The input type <NULL> is not supported
NOTICE:  The input type <NULL> is not supported
NOTICE:  The input type <NULL> is not supported
NOTICE:  The input type <NULL> is not supported
Query returned successfully: 3781 rows affected, 64619 ms execution time.

Thanks for your help.

Loretta