[postgis-users] Points not in Polygons and 'jointype 5 not supported'

[Simon Appelt]

Hi Lauri,

did you try (untested):

Select a.id,a.geom from points a, polygons b where not 
st_intersects/st_contains(a.geom,b.geom);

regards
Simon

Am 12.08.2014 08:52, schrieb Lauri Kajan:
> Hi all,
>
> I'm trying to figure out an optimal query to find points in a table 
> that are not in any polygons.
> My points table contains 3,7 million points and 6000 polygons.
>
> My goal is to find points from certain area that are not contained by 
> polygons. Here is my current query:
> /  select/
> ///id,/
> ///geom/
> /  from/
> /   points/
> /  where not exists(/
> /    select * from polygons where st_contains(polygons.geom, points.geom)/
> /  )/
> /  and points.geom && 'BOX(425930 7197112, 429605.074 
> 7200582.906)'::BOX2D;/
>
> I'm getting following notice from PostGIS:
> /NOTICE:  gserialized_gist_joinsel: jointype 5 not supported/
> What I have understood is that query works ok but the query plan might 
> not be optimal.
>
> Is there something to do to improve this?
>
>
> Here is the explain analyze results:
> "Nested Loop Anti Join  (cost=18.83..3356.77 rows=809 width=36) 
> (actual time=3.986..438.047 rows=272 loops=1)"
> "  ->  Bitmap Heap Scan on points  (cost=18.68..1540.86 rows=809 
> width=36) (actual time=0.724..2.955 rows=1452 loops=1)"
> "        Recheck Cond: (geom && 
> '010300000001000000050000000000000028FF1941000000006E745B410000000028FF19416DE7FBB9D1775B41F0A7C64B94381A416DE7FBB9D1775B41F0A7C64B94381A41000000006E745B410000000028FF1941000000006E745B41'::geometry)"
> "        ->  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)"
> "              Index Cond: (geom && 
> '010300000001000000050000000000000028FF1941000000006E745B410000000028FF19416DE7FBB9D1775B41F0A7C64B94381A416DE7FBB9D1775B41F0A7C64B94381A41000000006E745B410000000028FF1941000000006E745B41'::geometry)"
> "  ->  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)"
> "        Index Cond: (geom && points.geom)"
> "        Filter: _st_contains(geom, points.geom)"
> "        Rows Removed by Filter: 0"
> "Total runtime: 438.491 ms"
>
>
> I appreciate all your help!
>
> -Lauri
>
>
>
>
> _______________________________________________
> 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/20140812/9ac4c2af/attachment.html>