I have a linestring, which represents a bus route, and a sequence of points in order, representing the coordinates of each stop of that route. It kinda looks like this (pardon my poor drawing skills):
Basically, I want to get the total distance from the start of the route to each stop. I know this can be done by doing something like this:
SELECT stops.order,ST_LineLocatePoint(routes.line,stops.point)*ST_LineLength(routes.line) FROM routes,stops WHERE routes.route_id = stops.route_id
But the problem is that in the LineString, those two segments that are really close in the drawing (those that contain 5 and 8) actually overlap, so with that solution, stop 5 may be wrongly located between 8 and 9, or stop 8 between 5 and 6. But as I have the order of the stops, I suppose there must be a way to get the distances without getting those errors. Maybe it cannot be done with a PostGIS function or query.
I have also thought about using ST_Line_Substring to "cut" the linestring, as I advance through each stop in order, but I still have the problem when 5 is located after 8.