# LineLocate a sequence of points, preserving order

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.

This is not trivial; in general, a road network needs any form of linear reference/topology to be able to measure or route on and find relations between nodes/edges. The Linear Referencing functions are immensely useful, but any 'closest point' derivations are limited to raw geometric proximity, and thus are ambiguous for cases like yours.

Now, since you seem to have one contiguous Linestring representing the road, you could try and hack your way around these problems with an ST_OffsetCurve:

``````WITH
offset AS (
SELECT route_id,
ST_Length(line) AS len,
ST_OffsetCurve(line, <offset>, 'join=mitre') AS geom
FROM  routes
)

SELECT a.order,
ST_LineLocatePoint(b.geom, a.point) * b.len AS dist
FROM   stops AS a
JOIN   offset AS b USING (route_id);
``````

Here, a positive `<offset>` value will offset the resulting geometry to the left of the line (in line direction), and a negative value to the right; if we assume your Linestrings direction in the picture follows the bus stop order, you'd want to offset to the right. Then, however, the offset line direction will be reversed; you'd want to undo that, e.g. with

``````... ST_Reverse(ST_OffsetCurve(line, <offset>, 'join=mitre')) AS geom, ...
``````

to get the `dist` values with respect to the bus stop order.

The `<offset>` value is interpreted as units of the data's CRS; since the function doesn't allow for the `GEOGRAPHY` type, you will either need to pass in degrees, for a geographic CRS (e.g. EPSG:4236), or transform your data in a suitable projection, with e.g. meter as unit. In any case, you'd need only a tiny offset, e.g. `0.000001` in degrees.

Note that this similarly applies to `ST_Length`; if you pass in a `GEOMETRY` in a geographic CRS, you'd get back the length in degrees, which are quite meaningless. If you are not working in a projected CRS, consider using the `GEOGRAPHY` type (only possible from EPSG:4326) to get (very precise) measurements in meter, e.g.

``````... ST_Length(line::GEOGRAPHY) AS len, ...
``````

Be aware that this is a workaround with limited robustness and tailored to your presented case!

• Clear answer, and using offset works like a charm. Thank you very much! Dec 18, 2018 at 18:54