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

[Rémi Cura]

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.cura at 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.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/5567ba39/attachment.html>