1

How to extract stops AND their stop_sequence knowing a route_id from GTFS data using MySQL.

I want this because I'm trying to draw the routes using Leaflet which requires to give the stops coordinates in the right order.

I've only found the stop_sequence information in the stop_times.txt file, but it's only correct for one trip on this route.

This answer only tells which are the stops that are associated with a certain route, but not in the good order

Community
  • 1
  • 1
Ghilas BELHADJ
  • 13,412
  • 10
  • 59
  • 99
  • Ghilas, did what I wrote answer your question? –  Apr 22 '15 at 09:13
  • What you want is a shortest common sequence of the different trip variations of stops commonly known as "route variants" however to put the coordinates on a map you will require a little tweeking you cannot just chain it together, diversions and such will not make sense. Another question on stack from 2013 talks about topologolical sorting but that wont work.. Not good order and becomes extremely slow the more boundaries you put in to get the order you want. – Superfy Jun 13 '15 at 10:29

1 Answers1

0

I think you've arrived at your own answer here: Stops are ordered in sequence only along a specific trip, of which a route normally has many. This is meant to accommodate routes that have multiple branches or that change their path at certain times, such as a route that makes a diversion through an industrial park during rush hour.

What you'll need to do is first identify a trip that is typical of the route you intend to plot, and note its trip ID. To get a list of all the trips along a specific route, run a query like

SELECT id, headsign, short_name, direction_id
    FROM trips
    WHERE route_id = <route_id>;

Once you've selected a trip, getting the list of the stops it visits, in order, is straightforward:

SELECT code, name, lat, lon, arrival_time, departure_time
    FROM stops
    INNER JOIN stop_times ON stop_times.stop_id = stops.id
    WHERE trip_id = <trip_id>
    ORDER BY stop_sequence ASC;

(I've added a few extra fields here for clarity; it sounds like all you really need are the lat and lon fields included in the results.)

So how do you identify a "typical" trip for the route you want to plot? Often the headsign information for a trip indicates its branch or destination. If you need to be more specific—identifying trips that run between certain hours on certain days, for instance—the information in the calendars and calendar_dates tables can help you narrow these down.

  • The problem with your answer is that It does not guarantee me that all the stops of that route will be present because I have to choose between trips ( manually ). I'm trying to create a Directed Graph to solve this, I'll post the answer when done. – Ghilas BELHADJ Apr 22 '15 at 11:12
  • You may also find that every single connection /coordinates between two individual stops will be the same and is a viable way to reduce the data you have to store in a SQL database. I.e. al busses will only travel along a sequence of coordinates between two unique stops. – Superfy Jun 13 '15 at 10:17