<div dir="ltr">How many vertices are in the polygon geometry?<div><br></div><div>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)</div><div><br></div><div><a href="https://postgis.net/docs/manual-3.3/ST_SimplifyPolygonHull.html">https://postgis.net/docs/manual-3.3/ST_SimplifyPolygonHull.html</a><br></div><div><br></div><div>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.</div><div><br></div><div>WITH hulls AS (SELECT </div><div><div> ST_SimplifyPolygonHull(po.geom, 0.01, true) AS outerHull, </div><div><div> ST_SimplifyPolygonHull(po.geom, 0.01, false) AS innerHull</div><div> geom</div><div> FROM test_polygon po)</div><div>SELECT count(*) </div><div> FROM gisdata_30 g JOIN hulls </div><div> ON ST_Covers(hulls.outerHull, g.geom)</div><div> AND ( ST_ContainsProperly(hulls.innerHull, g.geom) </div><div> OR ST_ContainsProperly(po.geom,g.geom));</div><div><br></div></div></div><div>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).</div><div><br></div><div>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).</div><div><br></div><div><br></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Fri, Aug 11, 2023 at 7:22 AM Light <<a href="mailto:wangdapeng20191008@gmail.com">wangdapeng20191008@gmail.com</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr"> Hi, everyone,<br>I have 2 issues and need to improve their speed.<br>There are 3 tables in total, they use the ST_ContainsProperly function and ST_Intersects.<br><br>1 table name gisdata containing 3 billion rows of data, fields gid and geom and others.<br>There is also 1 test_line table, gid and geom, with 1 row of data.<br>There is also 1 test_polygon table, gid and geom, with 1 row of data.<br>Created SPGIST (geom).<br><br>--ST_ContainsProperly Execution Time: 143759.746 ms<br>select count(*) from gisdata_30 g join test_polygon po on ST_ContainsProperly(po.geom,g.geom)='t';<br> Aggregate (cost=85375554.05..85375554.06 rows=1 width=8) (actual time=143758.524..143758.526 rows=1 loops=1)<br> -> Nested Loop (cost=84983.55..85368054.05 rows=3000000 width=0) (actual time=940.048..143633.927 rows=1148761 loops=1)<br> -> Seq Scan on bs c (cost=0.00..1.01 rows=1 width=32) (actual time=0.002..0.004 rows=1 loops=1)<br> -> Bitmap Heap Scan on gisdata_30 sl (cost=84983.55..85365053.04 rows=300000 width=157) (actual time=940.042..143458.484 rows=<br>1148761 loops=1)<br> Filter: st_containsproperly(c.geom, geom)<br> Rows Removed by Filter: 564339<br> Heap Blocks: exact=1107489<br> -> Bitmap Index Scan on gisdata_30_geom (cost=0.00..84908.55 rows=3000000 width=0) (actual time=551.154..551.154 rows=17<br>13100 loops=1)<br> Index Cond: (geom @ c.geom)<br> Planning Time: 32.232 ms<br> Execution Time: 143759.746 ms<br> <br>--ST_Intersects 1445741.458 ms<br>select count(*) from gisdata_30 g join test_line li on ST_Intersects(li.geom,g.geom)='t';<br> Aggregate (cost=8720103.56..8720103.57 rows=1 width=8) (actual time=1445741.385..1445741.386 rows=1 loops=1)<br> -> Nested Loop (cost=0.56..8712603.56 rows=3000000 width=0) (actual time=526.359..1445730.137 rows=76332 loops=1)<br> -> 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)<br> -> Index Scan using gisdata_30_geom on gisdata_30 sl (cost=0.56..8709602.55 rows=300000 width=157) (actual time=526.000..14457<br>15.941 rows=76332 loops=1)<br> Index Cond: (geom && li.geom)<br> Filter: st_intersects(li.geom, geom)<br> Rows Removed by Filter: 7907303<br> Planning Time: 0.783 ms<br> Execution Time: 1445741.458 ms<br> <br>Is there any way to make them faster?<br><br>Wangdapeng.<div></div><div><br></div></div>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a><br>
</blockquote></div>