2

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?

Improve this question
6
  • How did you create the spatial index? GIST?
    – Mapperz ♦
    Commented Sep 25, 2020 at 2:23
  • Contours generally span large regions. Spatial indexes do not help much for these scenarios. Do you have the original raster? Is it possible to upload this into PostGIS? You can extract 'height' from the raster along the linestring.
    – Mike T
    Commented Sep 25, 2020 at 2:39
  • @Mapperz create index contour_cb9eba_geom_idx on contour_cb9eba using gist(geom)
    – ZhiLi
    Commented Sep 25, 2020 at 3:09
  • @MikeT My contour lines are vectors, not raster. They are in postgresql.
    – ZhiLi
    Commented Sep 25, 2020 at 3:11
  • lines (yes, vector) are normally determined from a raster source.
    – Mike T
    Commented Sep 25, 2020 at 9:54

1 Answer 1

Reset to default
1

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.

Improve this answer
2
  • 2
    Shamelessly self-promoting, still I find that exact functionality should be more easily available: a simple PL/PgSQL function suite to divide lines by segment length or number of parts, and, if rebuilding PostGIS is sth. you do, a C core function suite with that and more functionality.
    – geozelot
    Commented Sep 25, 2020 at 6:09
  • Thanks for the reply! I did split the contour lines. Though splitting takes about 4 seconds, finding intersections only takes 0.1 seconds now, which is really a big improvement if I need to find intersections several times at the same region.
    – ZhiLi
    Commented Sep 25, 2020 at 8:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.