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

[Hugues François]

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-bounces at lists.osgeo.org [mailto:postgis-users-bounces at lists.osgeo.org] De la part de Lauri Kajan
Envoyé : mardi 12 août 2014 08:52
À : postgis-users at 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

 

 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20140812/2cfdcaec/attachment.html>