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

[Rémi Cura]

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.cura at 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.francois at 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-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
>>
>>
>>
>>
>>
>> _______________________________________________
>> 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/f8349bdb/attachment.html>