5

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

6

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.

  • 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
1

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
0
["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

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.