[postgis-users] Point, Polygon - Does not intersects

[Andreas Forø Tollefsen]

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 <hugues.francois at irstea.fr>:

>  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:* postgis-users-bounces at lists.osgeo.org [mailto:
> postgis-users-bounces at lists.osgeo.org] *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
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20140217/b300f477/attachment.html>