Getting stops by route from GTFS data

[Jeff Lopes]

Using the MBTA realtime API it's pretty easy to get the list of stops for a given route in both directions. 

Using this example, I get one set of stop sequences for each direction_id 0 and direction_id 1: 

http://realtime.mbta.com/developer/api/v2/stopsbyroute?api_key=wX9NwuHnZU2ToO7GmGR9uw&route=47&format=json

Is there a way to parse the GTFS data to get the same result? I've tried the following to get the route directions: 

SELECT trips.route_id, routes.route_short_name, routes.route_long_name, routes.route_type, trips.direction_id, trips.trip_headsign from trips 

INNER JOIN routes on routes.route_id = trips.route_id 

INNER JOIN stop_times on stop_times.trip_id = trips.trip_id 

INNER JOIN stops on stops.stop_id = stop_times.stop_id 

WHERE routes.route_id = '47'

GROUP BY trips.route_id, trips.direction_id, trips.trip_headsign

But I get multiple variations for direction_id 1. For example: 

route_id,route_short_name,route_long_name,route_type,direction_id,trip_headsign

47,47,,3,0,Central Sq Via Longwood & Boston Medical Ctr

47,47,,3,1,Boston Medical Center Via Dudley

47,47,,3,1,Broadway Via Boston Medical Center

I'm basically just trying to create a UI that shows a list of routes, followed by a list of directions for that route, followed by a list of stops for that route / direction. 

Thanks,

Jeff

[Developer at MBTA]

It's actually a hard problem; we tried a number of different strategies and didn't find a perfect one, but what we ended up using was something like this:

select stop_id, max(stop_order) as route_stop_order

from stop_times

where route_id = '47'

and direction_id = 1

group by stop_id

Then, sort by route_stop_order. You'll get some ties which are broken arbitrarily. 

Sincerely,

developer@mbta

[Developer at MBTA]

Here's an edited version with corrected field names & locations:

select stop_times.stop_id, max(stop_times.stop_sequence) as route_stop_order

from stop_times

inner join trips on stop_times.trip_id = trips.trip_id

where trips.route_id = '47'

and trips.direction_id = 1

group by stop_times.stop_id

-developer@mbta

[Jeff Lopes]

Thanks. That's really helpful. How do you decide which trip_headsign to associate with it?