Distance between points inside LineString

Question

I have a collection of geometries represented by a linestring. Each has at least two coordinates. Is it possible to write a SQL that instead of returning coordinates, return a distance between them.

input: LINESTRING( C1,C2,C3 .. CN ) size N

output: D1,D2, D3 .. DN-1 size N-1

Answer 1

Thank you everyone for your responses. Link provided by JGH was very helpful.

It could be done in a very simple with a subquery.

Query bellow picks one OSM way and return its osm id and the array of distances between its nodes.

select ln.osm_id, (with pt as (select st_dumppoints(ln.way) as p)
select array_agg(st_distance((p1.p).geom, (p2.p).geom) :: int)
from pt p1
       join pt p2 on (p1.p).path [ 1 ] + 1 = (p2.p).path [ 1 ]) as intervals
from planet_osm_line ln
where osm_id = 282180059;

Response looks like this. Default OSM SRID is 3857 that uses meters. So using int values is optimal for performance imho.

osm_id      intervals
282180059   {126,47,41,49,23}

Answer 2

The algorithm might be looking like this one

WITH RECURSIVE generate_points(sec) AS (
SELECT conf.starting_point
FROM conf
UNION ALL
SELECT sec + conf.step
FROM generate_points, conf
WHERE sec + conf.step <= conf.num_segments
),

conf AS (
SELECT SUM(ST_NPoints(geometry)) AS num_points,
SUM(ST_NPoints(geometry)) - 1 AS num_segments,
1 AS starting_point,
1 AS step
FROM lines)

SELECT gs.sec AS sec,
       ROUND(ST_Distance(ST_PointN(l.geometry,sec),
                         ST_PointN(l.geometry,sec + conf.step)),2) AS distance,
       sec AS point_from,
       sec + conf.step AS point_to
FROM generate_points AS gs, lines AS l, conf
WHERE sec + conf.step <= num_points
GROUP BY gs.sec

Tested on QGIS 2.18 and QGIS 3.4 by means of a "Virtual Layer" through Layer > Add Layer > Add/Edit Virtual Layer...

Let's assume there is a line layer called "lines", see image below.

Note: The length in the above attribute table is wrong. It was previously calculated for a different line and was not updated afterwards.

With the above Query, it is possible to return distances between backward and forward points of the line string.

The output will look as following

---

References:

• ST_PointN