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
[email protected]
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
