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

[L Bogert-OBrien]

Hi,

Just another update for this.  The scenario described first with 3 rows
for each census tract in the new table was for when there was a lake or
bay.  I have also found that there can be many rows in the new table
when an island is surrounded by many polygons in the water file.

Thanks again for any help you can give.

Loretta

----- Original Message -----
From: L Bogert-O'Brien <dlawbob at ncf.ca>
Date: Wednesday, August 25, 2010 12:39 pm
Subject: [postgis-users] Problem trying to clip multipolygons in one
file from multipolygons in another.

> 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
> 
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>