Thank you Hugues, Your suggestion worked. I still have some problems understanding why my query did not work, but I will give it some thought.
Andreas 2014-02-17 12:09 GMT+01:00 Hugues François <[email protected]>: > Hello, > > Maybe the issue is the number of polygon which not intersect between your > tables : this query will return you all g1 polygon any times it does not > intersect one polygon in your g2 table (I don't know if I'm clear enought, > but a g1 polygon where g2 exists will also be returned since it does not > intersect others g2 polygon) > > I think you could try > > SELECT name, geom INTO gadm2_level1_union_selected > FROM > (SELECT g1.*, g2.gid AS g2_gid FROM gadm2_level1_union g1 > LEFT JOIN gadm2_level2_union g2 ON ST_Intersects(g1.centroid, g2.geom)) > foo > WHERE g2_gid is null > > Hugues. > > > ------------------------------ > *From:* [email protected] [mailto: > [email protected]] *On Behalf Of *Andreas Forø > Tollefsen > *Sent:* Monday, February 17, 2014 11:49 AM > *To:* PostGIS Users Discussion > *Subject:* [postgis-users] Point, Polygon - Does not intersects > > Hi, > > I am running the current setup: > "POSTGIS="2.0.2 r10789" GEOS="3.4.0dev-CAPI-1.8.0" PROJ="Rel. 4.7.1, 23 > September 2009" GDAL="GDAL 1.9.0, released 2011/12/29" LIBXML="2.7.8" (core > procs from "2.0.1 r9979" need upgrade) RASTER (raster procs from "2.0.1 > r9979" need upgrade)" > > I have two tables with geometries. Both include polygons representing > administrative units. One at the first level and one at the second level. > I want a table that includes all the second level polygons where > available, and where not available I want the first level polygons. > > To find the level 1 polygons for filling the gaps where level 2 is N/A, I > thought about finding where the centroid of level one polygons does not > intersects with the polygons for level 2. > > Hence, I tried: > > SELECT g1.name, g1.geom INTO gadm2_level1_union_selected FROM > gadm2_level1_union g1, gadm2_level2_union g2 WHERE > ST_Intersects(g1.centroid, g2.geom)=FALSE; > > I also tried the ST_Disjoint(g1.centroid, g2.geom). > > My problem is that this is running for ages without finishing. This should > be such a simple query, but I have used half of the day running it. If I > take the two geometries into PostGIS and run a spatial query to find the > points from level 1 not intersecting with polygons of level 2, it takes 15 > seconds. > > What could be the issue with my query? > > Thanks! > Andreas > > > _______________________________________________ > postgis-users mailing list > [email protected] > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >
_______________________________________________ postgis-users mailing list [email protected] http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
