<div dir="ltr"><div><div><div><div><div><div><div><div><div>Hm,<br></div>also,<br></div>if you feel more comfortable, <br></div>instead of trying to find points that are no in any polygons, you can use the complementary approach.<br>
<br></div>you can find points that are in at least one polygons, then take all the points that are not in this set.<br><br></div>The advantage is that it uses the classical query : which point is in at least one polygon<br>
</div><div>You could gain something if you have more points in polygon than outside<br></div><div><br></div>Something like (not tested)<br><br></div><div style="margin-left:40px">SELECT id<br></div><div style="margin-left:40px">
FROM points<br></div>EXCEPT <br></div><div><div style="margin-left:40px">SELECT DISTINCT <a href="http://pt.id">pt.id</a><br>FROM points AS pt<br>INNER JOIN polygons AS pol ON (ST_Intersects(pt.geom, pol.geom)=TRUE)<br></div>
</div></div><div><br></div></div><div class="gmail_extra"><br><br><div class="gmail_quote">2014-08-12 11:30 GMT+02:00 Rémi Cura <span dir="ltr"><<a href="mailto:remi.cura@gmail.com" target="_blank">remi.cura@gmail.com</a>></span>:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr"><div><div><div><div><div>Hey,<br>small change if you stick with your original querry (which looks fine).<br>
</div>You may want to force the planner to apply <i>points.geom && 'BOX(425930 7197112, 429605.074 7200582.906)'::BOX2D=TRUE;<br>
</i></div>before the filtering (to be tested).<br></div><div>If your polygon are well clustered, you could also simply estimate the extent of the polygon table and use it as a prefiltering.<br></div><div>Use <a href="http://postgis.net/docs/ST_Estimated_Extent.html" target="_blank">http://postgis.net/docs/ST_Estimated_Extent.html</a> .<br>
</div><div>If your polygons have a strong spatial structure (grid, topology, no overlapping, etc), you can further accelerate this.<br><br></div><div>If you really want all the options, here they are explained :<a href="http://stackoverflow.com/questions/19363481/select-rows-which-are-not-present-in-other-table" target="_blank">http://stackoverflow.com/questions/19363481/select-rows-which-are-not-present-in-other-table</a><br>
<br></div><div>Cheers.<br></div> <br><br></div><div>--forcing the planner to cut with the bbox first<br></div>WITH point_in_bbox AS (<br><div style="margin-left:40px">SELECT id, geom <br>FROM points<br>WHERE <i>points.geom && 'BOX(425930 7197112, 429605.074 7200582.906)'::BOX2D=TRUE</i></div>
)<br></div>SELECT id, geom <br>FROM points_in_bbox<br><span style="font-size:11pt;font-family:"Calibri","sans-serif";color:rgb(31,73,125)" lang="EN-GB">LEFT JOIN polygons ON ST_Intersects(</span><span style="font-size:11pt;font-family:"Calibri","sans-serif";color:rgb(31,73,125)" lang="EN-GB">_.geom, polygons.geom)</span><br>
<div><div><div><i></i><div><i></i><div><br><br></div><div>--minor change to your stuff<br></div><div><div><div class=""><div><i> select</i></div><div><i> </i><i>id,</i></div><div><i> </i><i>geom</i></div>
<div><i> from</i></div><div><i> points</i></div><div><i> where not exists(</i></div></div><div><i> select <b>1</b> from polygons where <b>st_intersects</b>(polygons.geom, points.geom)=TRUE</i></div><div><i> )</i></div>
<div><i> and points.geom && 'BOX(425930 7197112, 429605.074 7200582.906)'::BOX2D=TRUE;</i></div>
</div><br></div></div></div></div></div></div><div class="gmail_extra"><br><br><div class="gmail_quote">2014-08-12 11:03 GMT+02:00 Hugues François <span dir="ltr"><<a href="mailto:hugues.francois@irstea.fr" target="_blank">hugues.francois@irstea.fr</a>></span>:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div><div class="h5"><div link="blue" vlink="purple" lang="FR"><div><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">Hi,<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d" lang="EN-GB">I think it could be simpler and more efficient to use a null select from a left join and a st_intersects test like this :<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d" lang="EN-GB"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d" lang="EN-GB">SELECT point_id, poly_id, point_geom FROM points_table<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d" lang="EN-GB">LEFT JOIN poly_table ON ST_Intersects(point_geom, poly_geom)<u></u><u></u></span></p><p class="MsoNormal">
<span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d" lang="EN-GB">WHERE poly_id IS NULL<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d" lang="EN-GB"><u></u> <u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d" lang="EN-GB">NOT EXISTS will also perform a left outer join<u></u><u></u></span></p><p class="MsoNormal">
<span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d" lang="EN-GB"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d" lang="EN-GB">I’m not very familiar with box2d type and I generally prefer to use the general geometry type. So To restrict potential candidates, I would add AND ST_Intersects(point_geom, ST_GeomFromText(‘POLYGON((425930 7197112, 429605.074 7197112, 429605.074 7200582.906, 425930 7200582.906, 425930 7197112))’)) and you could also add the SRID information as a GeomFromText parameter.<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d" lang="EN-GB"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d" lang="EN-GB">HTH<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d" lang="EN-GB"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d" lang="EN-GB">Hugues.<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d" lang="EN-GB"><u></u> <u></u></span></p><p class="MsoNormal"><b><span style="font-size:10.0pt;font-family:"Tahoma","sans-serif"">De :</span></b><span style="font-size:10.0pt;font-family:"Tahoma","sans-serif""> <a href="mailto:postgis-users-bounces@lists.osgeo.org" target="_blank">postgis-users-bounces@lists.osgeo.org</a> [mailto:<a href="mailto:postgis-users-bounces@lists.osgeo.org" target="_blank">postgis-users-bounces@lists.osgeo.org</a>] <b>De la part de</b> Lauri Kajan<br>
<b>Envoyé :</b> mardi 12 août 2014 08:52<br><b>À :</b> <a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br><b>Objet :</b> [postgis-users] Points not in Polygons and 'jointype 5 notsupported'<u></u><u></u></span></p>
<div><div><p class="MsoNormal"><u></u> <u></u></p><div><p class="MsoNormal">Hi all,<u></u><u></u></p><div><p class="MsoNormal"><u></u> <u></u></p></div><div><p class="MsoNormal">I'm trying to figure out an optimal query to find points in a table that are not in any polygons.<u></u><u></u></p>
</div><div><p class="MsoNormal">My points table contains 3,7 million points and 6000 polygons.<u></u><u></u></p></div><div><p class="MsoNormal"><u></u> <u></u></p></div><div><p class="MsoNormal">My goal is to find points from certain area that are not contained by polygons. Here is my current query:<u></u><u></u></p>
</div><div><div><p class="MsoNormal"><i> select</i><u></u><u></u></p></div><div><p class="MsoNormal"><i> id,</i><u></u><u></u></p></div><div><p class="MsoNormal"><i> geom</i><u></u><u></u></p></div><div><p class="MsoNormal">
<i> from</i><u></u><u></u></p></div><div><p class="MsoNormal"><i> points</i><u></u><u></u></p></div><div><p class="MsoNormal"><i> where not exists(</i><u></u><u></u></p></div><div><p class="MsoNormal"><i> select * from polygons where st_contains(polygons.geom, points.geom)</i><u></u><u></u></p>
</div><div><p class="MsoNormal"><i> )</i><u></u><u></u></p></div><div><p class="MsoNormal"><i> and points.geom && 'BOX(425930 7197112, 429605.074 7200582.906)'::BOX2D;</i><u></u><u></u></p></div></div><div>
<p class="MsoNormal"><u></u> <u></u></p></div><div><p class="MsoNormal">I'm getting following notice from PostGIS:<u></u><u></u></p></div><div><p class="MsoNormal"><i>NOTICE: gserialized_gist_joinsel: jointype 5 not supported</i><u></u><u></u></p>
</div><div><p class="MsoNormal">What I have understood is that query works ok but the query plan might not be optimal.<u></u><u></u></p></div><div><p class="MsoNormal"><u></u> <u></u></p></div><div><p class="MsoNormal">Is there something to do to improve this?<u></u><u></u></p>
</div><div><p class="MsoNormal"><u></u> <u></u></p></div><div><p class="MsoNormal"><u></u> <u></u></p></div><div><p class="MsoNormal">Here is the explain analyze results:<u></u><u></u></p></div><div><div><p class="MsoNormal">
"Nested Loop Anti Join (cost=18.83..3356.77 rows=809 width=36) (actual time=3.986..438.047 rows=272 loops=1)"<u></u><u></u></p></div><div><p class="MsoNormal">" -> Bitmap Heap Scan on points (cost=18.68..1540.86 rows=809 width=36) (actual time=0.724..2.955 rows=1452 loops=1)"<u></u><u></u></p>
</div><div><p class="MsoNormal">" Recheck Cond: (geom && '010300000001000000050000000000000028FF1941000000006E745B410000000028FF19416DE7FBB9D1775B41F0A7C64B94381A416DE7FBB9D1775B41F0A7C64B94381A41000000006E745B410000000028FF1941000000006E745B41'::geometry)"<u></u><u></u></p>
</div><div><p class="MsoNormal">" -> Bitmap Index Scan on points_geom (cost=0.00..18.48 rows=809 width=0) (actual time=0.699..0.699 rows=1452 loops=1)"<u></u><u></u></p></div><div><p class="MsoNormal">
" Index Cond: (geom && '010300000001000000050000000000000028FF1941000000006E745B410000000028FF19416DE7FBB9D1775B41F0A7C64B94381A416DE7FBB9D1775B41F0A7C64B94381A41000000006E745B410000000028FF1941000000006E745B41'::geometry)"<u></u><u></u></p>
</div><div><p class="MsoNormal">" -> Index Scan using polygons_geom on polygons (cost=0.15..2.23 rows=1 width=4361) (actual time=0.296..0.296 rows=1 loops=1452)"<u></u><u></u></p></div><div><p class="MsoNormal">
" Index Cond: (geom && points.geom)"<u></u><u></u></p></div><div><p class="MsoNormal">" Filter: _st_contains(geom, points.geom)"<u></u><u></u></p></div><div><p class="MsoNormal">
" Rows Removed by Filter: 0"<u></u><u></u></p>
</div><div><p class="MsoNormal">"Total runtime: 438.491 ms"<u></u><u></u></p></div></div><div><p class="MsoNormal"><u></u> <u></u></p></div><div><p class="MsoNormal"><u></u> <u></u></p></div><div><p class="MsoNormal">
I appreciate all your help!<u></u><u></u></p></div><div><p class="MsoNormal"><u></u> <u></u></p></div><div><p class="MsoNormal">-Lauri<u></u><u></u></p></div><div><p class="MsoNormal"><u></u> <u></u></p></div><div><p class="MsoNormal">
<u></u> <u></u></p></div></div></div></div></div></div><br></div></div><div class="">_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto: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><br></div></blockquote></div><br></div>
</blockquote></div><br></div>