[postgis-users] ST_Intersection problems

[L Bogert-O'Brien]

After I could not make ST_Difference with water bodies work, (as I 
documented in a previous thread 
http://postgis.refractions.net/pipermail/postgis-users/2010-August/027569.html 
), I thought I would try ST_Intersection with a single multipolygon 
of the whole land area that I wanted to keep.  So, what I have is a 
lot of multipolygons in one file that are adjacent to each other, but 
some of them extend out into water bodies (ca_ea_dbf), and another 
file with a single multipolygon with all the land I want to keep, 
i.e. it has a smaller outside footprint as well as holes within it 
for lakes (ca_dcf).

When I did the following:

INSERT INTO y1991.ca_ea_dbf_clp (eauid, pruid, the_geom_4269)
(SELECT ea.eauid, ea.pruid,
         ST_MULTI(ST_INTERSECTION(ea.the_geom_4269, ca.the_geom_4269))
   FROM y1991.ca_ea_dbf ea JOIN y1991.ca_dcf ca
   ON ST_INTERSECTS(ea.the_geom_4269, ca.the_geom_4269);

I got the following error:
ERROR:  new row for relation "ca_ea_dbf_clp" violates check 
constraint "enforce_geotype_the_geom_4269"

So, then I ran the following to check the type of geometries:
SELECT 
distinct(st_geometrytype(ST_MULTI(ST_INTERSECTION(ea.the_geom_4269, 
ca.the_geom_4269))))
   FROM y1991.ca_ea_dbf ea JOIN y1991.ca_dcf ca
   ON ea.pruid = '35'
   AND ST_INTERSECTS(ea.the_geom_4269, ca.the_geom_4269);

and found that there were both st_multipolygon and st_geometry 
types.  The geometry field, the_geom_4269, was entered as being a multipolygon.

So, then I used the following, thinking that maybe there were only a 
few rows that were not multipolygons:
INSERT INTO y1991.ca_ea_dbf_clp (eauid, pruid, the_geom_4269)
(SELECT ea.eauid, ea.pruid,
         ST_MULTI(ST_INTERSECTION(ea.the_geom_4269, ca.the_geom_4269))
   FROM y1991.ca_ea_dbf ea JOIN y1991.ca_dcf ca
   ON ST_INTERSECTS(ea.the_geom_4269, ca.the_geom_4269)
   AND ST_GEOMETRYTYPE(ST_INTERSECTION(ea.the_geom_4269, ca.the_geom_4269))
     = ST_GEOMETRYTYPE(ea.the_geom_4269));

It returned successfully, but there were only 282 of the original 
45996 multipolygons in the ca_ea_dbf returned, even though all the 
missing ones would intersect with the one multipolygon in the 
clipping file.  You can see pictures of the files at:
http://web.ncf.ca/do841/GIS/index.html

Thanks in advance for any tips you can give me on this 
ST_Intersection problem (or the previous ST_Difference one).  I am at 
a loss for what to try next.  Could it be that the files are too big 
and complex for postgis to handle-- i.e. too many multipolygons in 
each file?  Or am I just doing something wrong?  I am fairly new with 
postgis functions, but I have searched the archives to see if there 
have been similar problems before, and other than the ones I have 
tried above, I do not find any that I can manipulate to my particular needs.

Regards,

Loretta