<html>
<head>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
</head>
<body text="#000000" bgcolor="#FFFFFF">
<div class="moz-cite-prefix">Hi Lauri,<br>
<br>
did you try (untested):<br>
<br>
Select a.id,a.geom from points a, polygons b where not
st_intersects/st_contains(a.geom,b.geom);<br>
<br>
regards<br>
Simon<br>
<br>
Am 12.08.2014 08:52, schrieb Lauri Kajan:<br>
</div>
<blockquote
cite="mid:CAKWoFM+7Xx-7KMRCc0NrTVSz4WBF1pQMY0AYpS1RLuHWFJ=XCA@mail.gmail.com"
type="cite">
<div dir="ltr">Hi all,
<div><br>
</div>
<div>I'm trying to figure out an optimal query to find points in
a table that are not in any polygons.</div>
<div>My points table contains 3,7 million points and 6000
polygons.</div>
<div><br>
</div>
<div>My goal is to find points from certain area that are not
contained by polygons. Here is my current query:</div>
<div>
<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><i> select * from polygons where
st_contains(polygons.geom, points.geom)</i></div>
<div><i> )</i></div>
<div><i> and points.geom && 'BOX(425930 7197112,
429605.074 7200582.906)'::BOX2D;</i></div>
</div>
<div><br>
</div>
<div>I'm getting following notice from PostGIS:</div>
<div><i>NOTICE: gserialized_gist_joinsel: jointype 5 not
supported</i><br>
</div>
<div>What I have understood is that query works ok but the query
plan might not be optimal.</div>
<div><br>
</div>
<div>Is there something to do to improve this?</div>
<div><br>
</div>
<div><br>
</div>
<div>Here is the explain analyze results:</div>
<div>
<div>"Nested Loop Anti Join (cost=18.83..3356.77 rows=809
width=36) (actual time=3.986..438.047 rows=272 loops=1)"</div>
<div>" -> Bitmap Heap Scan on points
(cost=18.68..1540.86 rows=809 width=36) (actual
time=0.724..2.955 rows=1452 loops=1)"</div>
<div>" Recheck Cond: (geom &&
'010300000001000000050000000000000028FF1941000000006E745B410000000028FF19416DE7FBB9D1775B41F0A7C64B94381A416DE7FBB9D1775B41F0A7C64B94381A41000000006E745B410000000028FF1941000000006E745B41'::geometry)"</div>
<div>" -> 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)"</div>
<div>" Index Cond: (geom &&
'010300000001000000050000000000000028FF1941000000006E745B410000000028FF19416DE7FBB9D1775B41F0A7C64B94381A416DE7FBB9D1775B41F0A7C64B94381A41000000006E745B410000000028FF1941000000006E745B41'::geometry)"</div>
<div>" -> 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)"</div>
<div>" Index Cond: (geom && points.geom)"</div>
<div>" Filter: _st_contains(geom, points.geom)"</div>
<div>" Rows Removed by Filter: 0"</div>
<div>"Total runtime: 438.491 ms"</div>
</div>
<div><br>
</div>
<div><br>
</div>
<div>I appreciate all your help!</div>
<div><br>
</div>
<div>-Lauri</div>
<div><br>
</div>
<div><br>
</div>
</div>
<br>
<fieldset class="mimeAttachmentHeader"></fieldset>
<br>
<pre wrap="">_______________________________________________
postgis-users mailing list
<a class="moz-txt-link-abbreviated" href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a>
<a class="moz-txt-link-freetext" href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a></pre>
</blockquote>
<br>
</body>
</html>