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