[postgis-users] Optimization of ST_ContainsProperly and ST_Intersects functions

[Martin Davis]

How many vertices are in the polygon geometry?

If the polygon has a large number of vertices, it might be an option to use
the ST_SimplifyPolygonHull function (available in PostGIS 3.3)

https://postgis.net/docs/manual-3.3/ST_SimplifyPolygonHull.html

This function can be used to compute a much smaller inner hull of the
query polygon, which can then be used as an initial test to quickly
determine if points are contained in the original polygon.  It might even
be advantageous to compute an outer hull as well, to filter out points
which are *not* contained in the original.

WITH hulls AS (SELECT
   ST_SimplifyPolygonHull(po.geom, 0.01, true) AS outerHull,
   ST_SimplifyPolygonHull(po.geom, 0.01, false) AS innerHull
  geom
  FROM test_polygon po)
SELECT count(*)
  FROM  gisdata_30 g JOIN hulls
   ON ST_Covers(hulls.outerHull, g.geom)
        AND (  ST_ContainsProperly(hulls.innerHull, g.geom)
             OR ST_ContainsProperly(po.geom,g.geom));

It will be VERY interesting to hear if this approach improves the
performance of the query (and if not, to see the query plan to understand
why this doesn't work).

For the linestring intersection case things are trickier.  In theory it's
possible to compute a concave hull of the linestring to act as a fast
filter, but currently PostGIS doesn't have a function that will compute an
accurate concave hull of a linear geometry. (The ST_ConcaveHull function
only works on the input vertices, and thus may not respect the input lines).



On Fri, Aug 11, 2023 at 7:22 AM Light <wangdapeng20191008 at gmail.com> wrote:

>   Hi, everyone,
> I have 2 issues and need to improve their speed.
> There are 3 tables in total, they use the ST_ContainsProperly function and
> ST_Intersects.
>
> 1 table name gisdata containing 3 billion rows of data, fields gid and
> geom and others.
> There is also 1 test_line table, gid and geom, with 1 row of data.
> There is also 1 test_polygon table, gid and geom, with 1 row of data.
> Created SPGIST (geom).
>
> --ST_ContainsProperly Execution Time: 143759.746 ms
> select count(*) from  gisdata_30 g join test_polygon po on
> ST_ContainsProperly(po.geom,g.geom)='t';
>  Aggregate  (cost=85375554.05..85375554.06 rows=1 width=8) (actual
> time=143758.524..143758.526 rows=1 loops=1)
>    ->  Nested Loop  (cost=84983.55..85368054.05 rows=3000000 width=0)
> (actual time=940.048..143633.927 rows=1148761 loops=1)
>          ->  Seq Scan on bs c  (cost=0.00..1.01 rows=1 width=32) (actual
> time=0.002..0.004 rows=1 loops=1)
>          ->  Bitmap Heap Scan on gisdata_30 sl
>  (cost=84983.55..85365053.04 rows=300000 width=157) (actual
> time=940.042..143458.484 rows=
> 1148761 loops=1)
>                Filter: st_containsproperly(c.geom, geom)
>                Rows Removed by Filter: 564339
>                Heap Blocks: exact=1107489
>                ->  Bitmap Index Scan on gisdata_30_geom
>  (cost=0.00..84908.55 rows=3000000 width=0) (actual time=551.154..551.154
> rows=17
> 13100 loops=1)
>                      Index Cond: (geom @ c.geom)
>  Planning Time: 32.232 ms
>  Execution Time: 143759.746 ms
>
> --ST_Intersects  1445741.458 ms
> select count(*) from  gisdata_30 g join test_line li on
> ST_Intersects(li.geom,g.geom)='t';
>  Aggregate  (cost=8720103.56..8720103.57 rows=1 width=8) (actual
> time=1445741.385..1445741.386 rows=1 loops=1)
>    ->  Nested Loop  (cost=0.56..8712603.56 rows=3000000 width=0) (actual
> time=526.359..1445730.137 rows=76332 loops=1)
>          ->  Seq Scan on bs_line li  (cost=0.00..1.01 rows=1 width=32)
> (actual time=0.354..0.357 rows=1 loops=1)
>          ->  Index Scan using gisdata_30_geom on gisdata_30 sl
>  (cost=0.56..8709602.55 rows=300000 width=157) (actual time=526.000..14457
> 15.941 rows=76332 loops=1)
>                Index Cond: (geom && li.geom)
>                Filter: st_intersects(li.geom, geom)
>                Rows Removed by Filter: 7907303
>  Planning Time: 0.783 ms
>  Execution Time: 1445741.458 ms
>
> Is there any way to make them faster?
>
> Wangdapeng.
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20230811/799a796d/attachment.htm>