Hi,
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 : SELECT point_id, poly_id, point_geom FROM points_table LEFT JOIN poly_table ON ST_Intersects(point_geom, poly_geom) WHERE poly_id IS NULL NOT EXISTS will also perform a left outer join 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. HTH Hugues. De : [email protected] [mailto:[email protected]] De la part de Lauri Kajan Envoyé : mardi 12 août 2014 08:52 À : [email protected] Objet : [postgis-users] Points not in Polygons and 'jointype 5 notsupported' 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
