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 '19 at 19:40
  • correct, D1, D2, D3 and so on is a distance Oct 8 '19 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 '19 at 0:20
  • Have a look at this PostGIS solution to get the inner segments
    – JGH
    Oct 9 '19 at 0:45

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}
  • 1
    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 '19 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. Oct 9 '19 at 16:58

The algorithm might be looking like this one

WITH RECURSIVE generate_points(sec) AS (
SELECT conf.starting_point
FROM conf
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,
                         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.

input 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



Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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