# Distance between points inside LineString

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

• Am I correctly understand that `D1` is a distance between points `C2` and `C1`? – Taras Oct 8 at 19:40
• correct, D1, D2, D3 and so on is a distance – Stan Sokolov Oct 8 at 20:11
• If you use zero in the coordinate array, both lists can have the N element be the last value. – Vince Oct 9 at 0:20
• Have a look at this PostGIS solution to get the inner segments – JGH Oct 9 at 0:45

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}
``````
• Beware of distances in 3857. Unless the location is near the equator, this projection highly distorts distances (by 40% where I live). – JGH Oct 9 at 11:30
• A good point. For continental USA before getting distance: st_transform((ln.way).geom,2877) and multiple result distance by 0.3048 to convert ft into meters. – Stan Sokolov Oct 9 at 16:58

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: