TransWikia.com

PostGIS slow ST_Intersection

Geographic Information Systems Asked by ZhiLi on July 22, 2021

I’m trying get get intersections between one straight line and some contour lines. Using the SQL below:

DROP TABLE IF EXISTS v_line;
SELECT st_geomfromtext('linestring(103.492217095662 30.9300260311043,103.494817217263 30.9200436767033)',4326) as geom INTO v_line;
SELECT a.gid,a.height, ST_AsText(ST_Intersection(b.geom, a.geom)) 
FROM contour_cb9eba as a,v_line as b 
WHERE ST_Intersects(b.geom,a.geom);

But the query takes tens of seconds to finish. I have tuned the postgresql, doesn’t help. I have create index on contour table, doesn’t help. the Explain is below:

Nested Loop  (cost=0.14..665.47 rows=37 width=44)
  ->  Seq Scan on v_line b  (cost=0.00..23.60 rows=1360 width=32)
  ->  Index Scan using contour_cb9eba_geom_idx on contour_cb9eba a  (cost=0.14..0.41 rows=1 width=746174)
        Index Cond: (b.geom && geom)
        Filter: _st_intersects(b.geom, geom)

the explain analyze is below:

Nested Loop  (cost=0.14..665.47 rows=37 width=44) (actual time=245.343..4629.091 rows=23 loops=1)
  ->  Seq Scan on v_line b  (cost=0.00..23.60 rows=1360 width=32) (actual time=0.011..0.013 rows=1 loops=1)
  ->  Index Scan using contour_cb9eba_geom_idx on contour_cb9eba a  (cost=0.14..0.41 rows=1 width=746174) (actual time=114.466..803.730 rows=23 loops=1)
        Index Cond: (b.geom && geom)
        Filter: _st_intersects(b.geom, geom)
        Rows Removed by Filter: 40
Planning time: 0.235 ms
Execution time: 4629.272 ms

The contour table has only 82 rows but with many vertices up to almost 80000.
Is there any way to make ST_Intersection faster?

One Answer

Can you can split the contour lines into smaller lines, carrying the same attribute information? Then the spatial index will be more effective.

This is the same idea as using ST_Subdivide for polygons. It might be nice if there was an analogous function for lines, but it's fairly easy to write SQL to do this.

Correct answer by dr_jts on July 22, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP