[postgis-users] ST_Intersection problems
L Bogert-OBrien
dlawbob at ncf.ca
Fri Sep 3 14:41:55 PDT 2010
Well that was a marathon, but I think we have it licked! Thanks to
everyone for their suggestions. It probably would be simpler to use
Andrea's suggestion for using ST_CollectionExtract, but we are not on
PostGIS version 1.5 yet, so here is what I found worked in version 1.4
to get all intersecting polygons no matter what the St_Intersection
returned for a geometry type (thanks to Leo for the information about
what ST_Geometry is):
INSERT INTO y1991.ca_ea_dbf_clp (eauid, pruid, the_geom_4269)
(SELECT inter.eauid, inter.pruid,
ST_MULTI(ST_UNION(inter.inter_geom))
FROM (SELECT ea.eauid, ea.pruid,
(ST_DUMP(ST_INTERSECTION(ea.ea_geom, ca.ca_geom))).geom AS inter_geom
FROM (SELECT eauid, pruid, (ST_DUMP(the_geom_4269)).geom AS ea_geom
FROM y1991.ca_ea_dbf) AS ea
JOIN (SELECT (ST_DUMP(the_geom_4269)).geom AS ca_geom
FROM y1991.ca_dcf) AS ca
ON ST_INTERSECTS(ea.ea_geom, ca.ca_geom)
) AS inter
WHERE ST_GeometryType(inter.inter_geom) IN ('ST_Polygon',
'ST_MultiPolygon', 'ST_GeometryCollection', 'ST_Geometry')
AND NOT ST_ISEMPTY(inter.inter_geom)
GROUP BY inter.eauid, inter.pruid);
Thanks again,
Loretta
----- Original Message -----
From: Paragon Corporation <lr at pcorp.us>
Date: Thursday, September 2, 2010 5:07 pm
Subject: Re: [postgis-users] ST_Intersection problems
> 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
> othersthat 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
> wholeland 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
> havetried 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
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
More information about the postgis-users
mailing list