<div dir="ltr"><div>Hi,</div><div> </div><div>Thanks for you suggestions. I have gist indexes on both tables for columns geom, and for the centroid column in table g1.</div><div> </div><div>Best,</div><div>Andreas</div></div>
<div class="gmail_extra"><br><br><div class="gmail_quote">2014-02-17 13:15 GMT+01:00 <span dir="ltr"><<a href="mailto:fLaNsch@gmx.de" target="_blank">fLaNsch@gmx.de</a>></span>:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div><div style="font-family:Verdana;font-size:12px"><div>
<div>
<div>Hi,</div>
<div> </div>
<div>to speed up, "CREATE INDEX name_gist ON table_name USING gist(geom);" is your friend!</div>
<div> </div>
<div>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!</div>
<div>Better way, in my opinin, create a second centroid table with an index.</div>
<div> </div>
<div>Good Luck</div>
<div>Simon </div>
<div> </div>
<div> </div>
<div> </div>
<div style="margin:10px 5px 5px 10px;padding:10px 0px 10px 10px;border-left-color:rgb(195,217,229);border-left-width:2px;border-left-style:solid" name="quote">
<div style="margin:0px 0px 10px"><b>Gesendet:</b> Montag, 17. Februar 2014 um 12:38 Uhr<br>
<b>Von:</b> "Andreas Forų Tollefsen" <<a href="mailto:andreasft@gmail.com" target="_blank">andreasft@gmail.com</a>><br>
<b>An:</b> "PostGIS Users Discussion" <<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a>><br>
<b>Betreff:</b> Re: [postgis-users] Point, Polygon - Does not intersects</div><div><div class="h5">
<div name="quoted-content">
<div>Thank you Hugues,
<div> </div>
<div>Your suggestion worked. I still have some problems understanding why my query did not work, but I will give it some thought.</div>
<div> </div>
<div>Andreas</div>
<div> </div>
</div>
<div class="gmail_extra">
<div class="gmail_quote">2014-02-17 12:09 GMT+01:00 Hugues Franēois <span><<a href="http://hugues.francois@irstea.fr" target="_blank">hugues.francois@irstea.fr</a>></span>:
<blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;padding-left:1ex;border-left-color:rgb(204,204,204);border-left-width:1px;border-left-style:solid">
<div>
<div align="left"><font color="#0000ff" face="Arial"><span>Hello,</span></font></div>
<div align="left"> </div>
<div align="left"><font color="#0000ff" face="Arial"><span>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)</span></font></div>
<div align="left"> </div>
<div align="left"><font color="#0000ff" face="Arial"><span>I think you could try </span></font></div>
<div align="left"> </div>
<div align="left"><font color="#0000ff"><span><font color="#000000">SELECT </font><font color="#000000">name, geom INTO gadm2_level1_union_selected</font></span></font></div>
<div align="left"><font color="#0000ff"><span><font color="#000000">FROM </font></span></font></div>
<div align="left"><font color="#0000ff"><span><font color="#000000">(SELECT g1.*, g2.gid AS g2_gid FROM gadm2_level1_union g1</font></span></font></div>
<div align="left"><span>LEFT JOIN gadm2_level2_union g2 ON ST_Intersects(g1.centroid, g2.geom)) foo</span></div>
<div align="left"><font color="#0000ff"><span><font color="#000000">WHERE g2_gid is null</font></span></font></div>
<div align="left"> </div>
<div align="left"><font color="#0000ff" face="Arial"><span>Hugues.</span></font></div>
<div align="left"> </div>
<div align="left">
<hr><font face="Tahoma"><b>From:</b> <a href="http://postgis-users-bounces@lists.osgeo.org" target="_blank">postgis-users-bounces@lists.osgeo.org</a> [mailto:<a href="http://postgis-users-bounces@lists.osgeo.org" target="_blank">postgis-users-bounces@lists.osgeo.org</a>] <b>On Behalf Of </b>Andreas Forų Tollefsen<br>
<b>Sent:</b> Monday, February 17, 2014 11:49 AM<br>
<b>To:</b> PostGIS Users Discussion<br>
<b>Subject:</b> [postgis-users] Point, Polygon - Does not intersects</font><br>
</div>
<div>
<div>
<div> </div>
<div>Hi,
<div> </div>
<div>I am running the current setup:</div>
<div> "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)"</div>
<div> </div>
<div>I have two tables with geometries. Both include polygons representing administrative units. One at the first level and one at the second level.</div>
<div>I want a table that includes all the second level polygons where available, and where not available I want the first level polygons.</div>
<div> </div>
<div>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.</div>
<div> </div>
<div>Hence, I tried:</div>
<div>
<div> </div>
<div>SELECT <a href="http://g1.name" target="_blank">g1.name</a>, g1.geom INTO gadm2_level1_union_selected FROM gadm2_level1_union g1, gadm2_level2_union g2 WHERE ST_Intersects(g1.centroid, g2.geom)=FALSE;</div>
<div> </div>
<div>I also tried the ST_Disjoint(g1.centroid, g2.geom).</div>
<div> </div>
<div>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.</div>
<div> </div>
<div>What could be the issue with my query?</div>
<div> </div>
<div>Thanks!</div>
<div>Andreas</div>
<div> </div>
</div>
</div>
</div>
</div>
</div>
<br>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="http://postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a></blockquote>
</div>
</div>
_______________________________________________ postgis-users mailing list <a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a> <a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a></div>
</div></div></div>
</div>
</div>
<div> </div>
<div> </div></div></div>
<br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br></blockquote></div><br></div>