The distance between two points on a linestring respecting the linestring course

Question

I have a big dilemma that I can't get over as much as I'm looking for here. I have on a linestring two points marked A and B, I want to calculate the distance between point A and point B as the line does, not the air distance between the two points.

I tried to use ST_Distance () but I understand that it returns the air distance between the two points (A -> B)

Any advice to solve this problem?

Answer 1

Just find the fraction of line length between the closest projections (A' & B') of the Points A & B on the given line:

SELECT ST_Length(ln.geom) * seg_frac AS seg_len   -- can be used with GEOGRAPHY
FROM   <line> AS ln,
       LATERAL ABS(
         ST_LineLocatePoint(ln.geom, <A>) - ST_LineLocatePoint(ln.geom, <B>)
       ) AS seg_frac
;

---

This example is not generic; to apply on triplets of geometries from one or mutliple tables it is necessary to relate them properly, either by matching attributes or a spatial relation (e.g. proximity).

Answer 2

If both A and B are on top of the same linestring you can follow these steps.

Use http://postgis.net/docs/ST_LineLocatePoint.html and locate points A and B on the linestring. The result is a fraction of the length of the linestring

By using the two fractional lengths cut the linestring between A and B with https://postgis.net/docs/ST_LineSubstring.html

Finally get the length of the substring with https://postgis.net/docs/ST_Length.html

PostGIS example:

Source geometries:

LINESTRING ( 174 425, 230 402, 246 369, 242 309, 218 270, 198 192, 216 156, 257 88, 336 63, 395 66, 429 96, 444 170, 431 216, 412 249, 415 293, 448 329, 478 356, 510 388 )

POINT ( 206 234 )

POINT ( 420 268 )

Locate A

SELECT ST_LineLocatePoint(
ST_GeomFromText(
'LINESTRING ( 174 425, 230 402, 246 369, 242 309, 218 270, 198 192, 216 156, 257 88, 336 63, 395 66, 429 96, 444 170, 431 216, 412 249, 415 293, 448 329, 478 356, 510 388 )'),
ST_GeomFromText(
'POINT ( 206 234 )'));


Result: 0.2589818067666891

Locate B

SELECT ST_LineLocatePoint(
ST_GeomFromText(
'LINESTRING ( 174 425, 230 402, 246 369, 242 309, 218 270, 198 192, 216 156, 257 88, 336 63, 395 66, 429 96, 444 170, 431 216, 412 249, 415 293, 448 329, 478 356, 510 388 )'),
ST_GeomFromText(
'POINT ( 420 268 )'));

Result: 0.8290726913727895

Query the lenght of the substring with the fractions

SELECT ST_Length(
ST_LineSubstring(
ST_GeomFromText(
'LINESTRING ( 174 425, 230 402, 246 369, 242 309, 218 270, 198 192, 216 156, 257 88, 336 63, 395 66, 429 96, 444 170, 431 216, 412 249, 415 293, 448 329, 478 356, 510 388 )'),
0.2589818067666891,0.8290726913727895));

Result: 530.4905141151486

Check if the result feels reasonable by making a comparison with the total length of the linestring

SELECT ST_Length(
ST_GeomFromText(
'LINESTRING ( 174 425, 230 402, 246 369, 242 309, 218 270, 198 192, 216 156, 257 88, 336 63, 395 66, 429 96, 444 170, 431 216, 412 249, 415 293, 448 329, 478 356, 510 388 )'),
0.2589818067666891,0.8290726913727895);

SELECT ST_Length(
ST_GeomFromText(
'LINESTRING ( 174 425, 230 402, 246 369, 242 309, 218 270, 198 192, 216 156, 257 88, 336 63, 395 66, 429 96, 444 170, 431 216, 412 249, 415 293, 448 329, 478 356, 510 388 )')
);

Result: 930.536741491116

Makes sense.