Postgis distance between two points on a line

Question

I have a route (like LINESTRING) and two cars with a position (like POINT). I need to calculate the distance between two points along the route.

As an additional difficulty, I think I also need to measure the distance from the point to the nearest point on the line if at that moment the car is not on the route.

I use this to find the closest point on the route:

SELECT ST_AsText(ST_ClosestPoint(pt,line)) AS cp_pt_line, ST_AsText(ST_ClosestPoint(line,pt)) As cp_line_pt FROM (SELECT 'POINT(100 100)'::geometry As pt, 'LINESTRING (20 80, 98 190, 110 180, 50 75 )'::geometry As line ) As foo; 

Is this possible in the request?

Answer 1

 SELECT ST_Length(ST_GeographyFromText(ST_AsText(ST_Line_Substring(line,ST_Line_Locate_Point(line,ST_ClosestPoint(line,fpt)),ST_Line_Locate_Point(line,ST_ClosestPoint(line,tpt)))))) As length_m, ST_Distance(ST_ClosestPoint(line,tpt)::geography, tpt::geography) as to_point_to_line_m, ST_Distance(ST_ClosestPoint(line,fpt)::geography, fpt::geography) as from_point_to_line_m, ST_AsText(ST_ClosestPoint(line,tpt)) as to_point_on_line, ST_AsText(ST_ClosestPoint(line,fpt)) as from_point_on_line, ST_AsText(tpt) as to_point, ST_AsText(fpt) as from_point FROM ( SELECT 'SRID=4326;POINT(1)'::geometry As tpt, 'SRID=4326;POINT(2)'::geometry As fpt, ST_Segmentize('SRID=4326;LINESTRING(123)'::geometry, 0.00001) As line ) As foo; 

Distance length_m, distance to_point_on_line and from_point_on_line. :)