Oups, typo : Something like (not tested)
SELECT id FROM points EXCEPT SELECT DISTINCT pt.id FROM points AS pt *LEFT JOIN* polygons AS pol ON (ST_Intersects(pt.geom, pol.geom)=TRUE) 2014-08-12 12:47 GMT+02:00 Rémi Cura <remi.c...@gmail.com>: > Hm, > also, > if you feel more comfortable, > instead of trying to find points that are no in any polygons, you can use > the complementary approach. > > you can find points that are in at least one polygons, then take all the > points that are not in this set. > > The advantage is that it uses the classical query : which point is in at > least one polygon > You could gain something if you have more points in polygon than outside > > Something like (not tested) > > SELECT id > FROM points > EXCEPT > SELECT DISTINCT pt.id > FROM points AS pt > INNER JOIN polygons AS pol ON (ST_Intersects(pt.geom, pol.geom)=TRUE) > > > > 2014-08-12 11:30 GMT+02:00 Rémi Cura <remi.c...@gmail.com>: > > Hey, >> small change if you stick with your original querry (which looks fine). >> You may want to force the planner to apply >> *points.geom && 'BOX(425930 7197112, 429605.074 >> 7200582.906)'::BOX2D=TRUE; * >> before the filtering (to be tested). >> If your polygon are well clustered, you could also simply estimate the >> extent of the polygon table and use it as a prefiltering. >> Use http://postgis.net/docs/ST_Estimated_Extent.html . >> If your polygons have a strong spatial structure (grid, topology, no >> overlapping, etc), you can further accelerate this. >> >> If you really want all the options, here they are explained : >> http://stackoverflow.com/questions/19363481/select-rows-which-are-not-present-in-other-table >> >> Cheers. >> >> >> --forcing the planner to cut with the bbox first >> WITH point_in_bbox AS ( >> SELECT id, geom >> FROM points >> WHERE *points.geom && 'BOX(425930 7197112, 429605.074 >> 7200582.906)'::BOX2D=TRUE* >> ) >> SELECT id, geom >> FROM points_in_bbox >> LEFT JOIN polygons ON ST_Intersects(_.geom, polygons.geom) >> >> >> --minor change to your stuff >> * select* >> *id,* >> *geom* >> * from* >> * points* >> * where not exists(* >> * select 1 from polygons where st_intersects(polygons.geom, >> points.geom)=TRUE* >> * )* >> * and points.geom && 'BOX(425930 7197112, 429605.074 >> 7200582.906)'::BOX2D=TRUE;* >> >> >> >> 2014-08-12 11:03 GMT+02:00 Hugues François <hugues.franc...@irstea.fr>: >> >>> 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 :* postgis-users-boun...@lists.osgeo.org [mailto: >>> postgis-users-boun...@lists.osgeo.org] *De la part de* Lauri Kajan >>> *Envoyé :* mardi 12 août 2014 08:52 >>> *À :* postgis-users@lists.osgeo.org >>> *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 >>> postgis-users@lists.osgeo.org >>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >>> >> >> >
_______________________________________________ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users