Problem statement: Given a bus route (defined as a linestring ln.the_geom), and a set of bus stop locations (pt1.the_geom,pt2.the_geom,...ptn.the_geom), divide the bus route into segments between bus stops. I am using two postgis functions


The above works well if the bus route has no overlapping sections. However, if there is overlapping sections, the ST_line_locate_point will snap a point to the other end of the linestring. As an example: Bus travels from A to B, make a loop, then travels from B to A. "A to B" and "B to A" are parallel routes with all points overlapped. In case of bus stops P1 and P2, P2 is mapped into point P2' instead (on the opposite direction). The segment is extracted wrongly in this case (where extra segments are included).

How to solve problems like this? The overlapping bus routes can happen at any section of the route... EXAMPLE

  • Is there a way for you to identify the termini of your bus routes and split the closed linestring into a line in either direction? Then you will not have overlapping segments anymore. For example, you could find the furthest point on the line from the start, and separate your line into two directions using that point.
    – raphael
    Oct 3, 2014 at 20:11
  • 3
    Old question but, looks like you need get start and enpoint of sections, join with the points dataset and remove any section with a initial point ordering bigger than the end point. Does this makes any sense?
    – Jorge Sanz
    Jun 5, 2016 at 9:00
  • 1
    Will you please provide the geometry WKT for the given bus route?
    – kttii
    Sep 22, 2016 at 13:14

1 Answer 1


Assuming that the bus route overlaps itself no more than 2 times at any given point, then this could be solved by reversing the line for the second line locate point, locating the closest point nearest to the the end of the line. You will need to subtract this from 1 to use in the ST_LineSubstring query. E.g.

  ST_LineLocatePoint(ln.the_geom, pt1.the_geom),
  1 - ST_LineLocatePoint(ST_Reverse(ln.the_geom), pt2.the_geom))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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