GTFS Data Confusion

[Youssef El-Hasbani]

I'm setting up a database based on the GTFS data provided by OC Transpo. I have all my data loaded in and I've started creating different queries.

I'm working on query to get all the stops (in order) for a route on a specific day. The problem is finding the right service_id to use because the data is too ambiguous when it comes to knowing what service_id to use on a specific date.

Does anybody have any idea what the different service_id in the calendar part of the GTFS data mean? how it changes? which one should I use to look up specific days?

[Cyrus]

Use table joins

[Youssef El-Hasbani]

That's not exactly what I'm asking. 

My problem is that calendar.txt file, contains service_id's that have overlapping start and end dates and there's no way to know which service_id to query one for a specific day since it might be in multiple service_ids.

On Tuesday, 20 March 2018 20:50:45 UTC-4, Cyrus wrote:

Use table joins

[Cyrus]

1. Lookup what service_ids are in the date ranges of March 22nd 2018 in gtfs_calendar and have Thursday as 1.

2. Now, look at gtfs_calendar_dates and lookup March 22nd 2018 as date.

2a. If exception_type  = 1, add that service_id into the list.

2b. if exception_type = 2, remove that service_id into the list.

There are multiple service_ids for some days, due to Canadian Tire Service or the 300 series service or the SNOWBUS shuttle.