[postgis-users] ST_Intersection problems

[Paragon Corporation]

ST_Geometry is returned if an intersection is empty or a geometry
collection.

Leo 

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of L
Bogert-O'Brien
Sent: Thursday, September 02, 2010 12:58 PM
To: postgis-users at postgis.refractions.net
Subject: Re: [postgis-users] ST_Intersection problems

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/0275
>69.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



_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users