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

[Andreas Forø Tollefsen]

Hi,

Thanks for you suggestions. I have gist indexes on both tables for columns
geom, and for the centroid column in table g1.

Best,
Andreas


2014-02-17 13:15 GMT+01:00 <fLaNsch at gmx.de>:

>
> Hi,
>
> to speed up, "CREATE INDEX name_gist ON table_name USING gist(geom);"  is
> your friend!
>
> If you go for something like this "st_intersect(st_centroid(a.geom)
> ,.b.geom) " , remember there will be no Index on st_centroid(a.geom) = SLOW!
> Better way, in my opinin, create a second centroid table with an index.
>
> Good Luck
> Simon
>
>
>
>  *Gesendet:* Montag, 17. Februar 2014 um 12:38 Uhr
> *Von:* "Andreas Forø Tollefsen" <andreasft at gmail.com>
> *An:* "PostGIS Users Discussion" <postgis-users at lists.osgeo.org>
> *Betreff:* Re: [postgis-users] Point, Polygon - Does not intersects
>  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
>
>  _______________________________________________ postgis-users mailing
> list postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
>
>
> _______________________________________________
> 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/c22aa86d/attachment.html>