# Getting distance between two points over linestring using PostGIS?

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)?

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). 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.

• Thank you for the information. Basically getting the distance between the first and last point is not a problem for me, but the issue is, how to get a distance between two points ON THAT LINE, but not the beginning and the end of the line. For example one point lays on the line, but 15 meters from the beginning and second lays somewhere else on the line and I want to get a distance between them. – user263416 Feb 26 '16 at 9:32
• @user263416 answer updated. For future questions, be sure to specify all the requirements in the question. – Mike T Feb 29 '16 at 4:19
• This is just perfect, thanks! I am sure it will help some others as well. – user263416 Mar 2 '16 at 7:43
• Hi, I am having an issue with the error "ERROR: 2nd arg must be smaller then 3rd arg" What to do in such cases? Does simply replacing the order of a and b coordinates provide a solution? Thanks! – user263416 May 17 '16 at 22:59
• @user263416 good question. I've updated the answer above to consider this case and make it more robust. As suggested, use the `greatest` and `least` SQL functions to swap `a` and `b` for you. – Mike T May 18 '16 at 3:22

Try use

ST_Distance(geometry g1, geometry g2) and ST_Transform(geometry g1, integer srid)

for example

``````SELECT ST_Distance(
ST_Transform(ST_SetSRID( ST_GeomFromText('POINT(-1.3326397 50.9174932)',4326),4326 ), srid),
ST_Transform(ST_SetSRID( ST_GeomFromText('POINT(,-1.3319842 50.9166939)',4326),4326 ), srid)
);
``````

For srid enter a projection in meters.

• The issue is, how to get a distance between two points ON THAT LINE, but not the beginning and the end of the line. For example one point lays on the line, but 15 meters from the beginning and second lays somewhere else on the line. – user263416 Feb 26 '16 at 9:31
``````["SELECT
ST_Distance(line, pta) AS dst_ptb,
ST_Distance(line, ptb) AS dst_pta,
ST_Length(ST_LineSubstring(
line,
least(ST_Line_Locate_Point(line, pta), ST_Line_Locate_Point(line, ptb)),
greatest(ST_Line_Locate_Point(line, pta), ST_Line_Locate_Point(line, ptb)))::geography)  AS dst_line
FROM (
SELECT
'SRID=4326;LINESTRING(1.457834243774414 43.597960902821576,1.462029218673706 43.59636807591895)'::geometry line,
'SRID=4326;POINT(1.457994 43.598124)'::geometry ptb,
'SRID=4326;POINT(1.461628 43.596128)'::geometry pta
) data"
])
``````

1 - Distance line to pta

2 - Distance line to ptb

3 - Substring line beetwen the 2 locatepoint

A to B = dst_pta + dst_ptb + dst_line