2

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): enter image description here

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.

2

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! – Alexander Irribarra Dec 18 '18 at 18:54

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.