[postgis-users] ST_Intersection problems

[L Bogert-O'Brien]

Hi again,

My main question here is what is a geometry type of ST_GEOMETRY and 
why is it returned from an ST_INTERSECTION of some multipolygons, but 
not others that look very similar?  How can I manage these 
ST_GEOMETRY types so that I can add them to my output table as 
multipolygons just like the rest?

A recap of the problem:
I am trying to clip a file with thousands of adjacent multipolygons 
(ca_ea_dbf) with one big multipolygon (ca_dcf) which represents the 
whole land mass with holes for lakes. I was getting ERROR:  new row 
for relation "ca_ea_dbf_clp" violates check constraint 
"enforce_geotype_the_geom_4269" because of the ST_GEOMETRY types.

A big thanks to Regina for getting me closer to what I need by 
suggesting an alternate check for the geometrytype, however, I still 
need something returned for every multipolygon in ca_ea_dbf, so I 
added a LEFT OUTER JOIN and COALESCE to my query as follows:
INSERT INTO y1991.ca_ea_dbf_clp (eauid, pruid, the_geom_4269)
(SELECT ea.eauid, ea.pruid,
         COALESCE(ST_MULTI(ST_INTERSECTION(ea.the_geom_4269, 
ca.the_geom_4269)),
                  ea.the_geom_4269)
   FROM y1991.ca_ea_dbf ea LEFT OUTER 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))
     LIKE '%Polygon%'
);

I now get all the entries in the ca_ea_dbf file returned, but the 
ones which were of type ST_GEOMETRY are not clipped of course, since 
they don't match LIKE '%Polygon%'.  Can anyone tell me what this 
geometry type is and how to manage it when I get it in the the 
results of the intersection?  I have tried ST_DUMP on the 
multipolygons before the intersection and then using ST_UNION and 
ST_MULTI to gather them back into multipolygons after the 
intersections, but I still get the geometry constraint error.

Thanks,

Loretta


At 04:25 PM 8/27/2010, L Bogert-O'Brien wrote:
>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