[GIS] Getting distance between two points over linestring using PostGIS

distancelinestringpostgis

I have a linestring:
LINESTRING(-1.3326397 50.9174932,-1.3319842 50.9166939)

and I select two points that lay on that linestring.

How do I get the distance between these two points (in meters)?

Best Answer

There are several linear referencing functions that can be used to reference subsets of LineStrings, which can be converted to a geography and get the length of the geodesic with ST_Length.

For example, get the distance along line that are near points pta and ptb:

SELECT
    ST_Length(ST_LineSubstring(
        line,
        ST_LineLocatePoint(line, pta),
        ST_LineLocatePoint(line, ptb))::geography)
FROM (
  SELECT
    'SRID=4326;LINESTRING(-1.3326397 50.9174932,-1.3319842 50.9166939)'::geometry line,
    'SRID=4326;POINT(-1.33262 50.91741)'::geometry pta,
    'SRID=4326;POINT(-1.33202 50.91679)'::geometry ptb
) data;

Returning a distance of 83.315 m (blue line; red line is original).

enter image description here

Note in older versions of PostGIS, the functions were called ST_Line_Locate_Point and ST_Line_Substring, so you many need to adjust the example to work.

In the event of "ERROR: 2nd arg must be smaller then 3rd arg", the query can be made more robust to find the lowest and highest values for A and B with the least and greatest SQL functions, e.g.:

SELECT
    ST_Length(ST_LineSubstring(
        line,
        least(ST_LineLocatePoint(line, pta), ST_LineLocatePoint(line, ptb)),
        greatest(ST_LineLocatePoint(line, pta), ST_LineLocatePoint(line, ptb)))::geography)
FROM (
  SELECT
    'SRID=4326;LINESTRING(-1.3326397 50.9174932,-1.3319842 50.9166939)'::geometry line,
    'SRID=4326;POINT(-1.33262 50.91741)'::geometry pta,
    'SRID=4326;POINT(-1.33202 50.91679)'::geometry ptb
) data;

which will now work if pta and ptb are swapped.

Related Question