CAN SOMEONE PLEASE HELP?

[Dan]

I am just getting started with using GTFS as a developer and need some help clearing something up. How would I list the buses that stop at a specific stop? Example: SPRINGFIELD BL - 93 AV - Q1 I looked through the files and found a link between the stops.txt , stop_times.txt, and trips.txt that would accomplish this, but how could would I implement it into a MYSQL database? I want to get something similar to google maps when you click on a bus stop, a list of buses that stop there show up.

Thank you,

Dan

[John Paul N.]

SELECT DISTINCT route_id FROM (SELECT trip_id FROM stopTimes WHERE stop_id='[ID of stop you want]') AS tripsAtStop JOIN trips ON tripsAtStop.trip_id=trips.trip_id;

This should list the routes serving the stop. You may also want the direction_id or trip_headsign as well; just add those columns to the result set in your query.

Hope this helps,
--JP

[John Paul N.]

The GTFS file is called stop_times.txt, so stopTimes in the query should be replaced with stop_times.

[Dan]

Thank you so much John,

This does just what I wanted! Only downside is that it takes 338 seconds to run this query, but that is probably just my problem running on localhost. I hope this problem does not precist when my site goes live. 

Thank you,

Dan

On Sunday, November 11, 2012 2:07:40 AM UTC-5, John Paul N. wrote:

[Michael Justice]

Does your database have indexes? 338s seems long even on development boxes. 

MJ

[Dan]

My bad, I thought I indexed already- but I didn't. I just ran the query again and it took 0.1546 seconds, A LOT better than before. 

Thank you for the help,

Dan

[Michael B. Justice]

Agreed, a definite improvement ;)  Even on an extremely powerful server I've been amazed at the difference a few indexes can make.

MJ