CODEWITHSUNDEEP
mysqlgtfs
Masterofawesome
Masterofawesome

Reputation: 69

How do I find the routes associated with a stop using GTFS?

So I'm building a transit application that provides me a with a stop_id on a database. How do I find the buses that services that stop? Ex: buses 10 and 23 passes through stop_id# 1234 I tried the query below but it only provides me with one bus every time

select distinct r.route_short_name
from routes r, trips t, stop_times st
where r.route_id = t.route_id
and t.trip_id = st.trip_id
and st.stop_id =

I checked my gtfs files to find that stop_id# 1234 has two different buses servicing it. I also tried it without the DISTINCT and it just lists the same bus repeatedly. Any comments/help/ideas are appreciated.

Upvotes: 1

Views: 2071

Answers (1)

user473305
user473305

Reputation:

You've got the right idea, but you ought to be joining tables together instead. Try this:

SELECT DISTINCT r.route_short_name
    FROM stop_times st
    INNER JOIN trips t ON t.trip_id = st.trip_id
    INNER JOIN routes r ON r.route_id = t.route_id
    WHERE st.stop_id = <stop_id>;

For good performance, make sure you have stop_times indexed to allow fast lookups of trips by stop ID:

CREATE INDEX stop_times_stop_id_trip_id_index ON stop_times(stop_id, trip_id);

If you haven't defined route_id and trip_id as the primary key for their respective tables, you'll want to create indexes for those as well:

CREATE INDEX routes_route_id_index ON routes(route_id);
CREATE INDEX trips_trip_id ON trips(trip_id);

Upvotes: 1

Related Questions