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

Rémi Cura remi.cura at gmail.com
Tue Aug 12 02:30:38 PDT 2014


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/16ac612e/attachment.html>


More information about the postgis-users mailing list