Started modelling GTFS (YRT) data storage

Wow, the General Transit Feed Specification by Google is incredibly complete, with all kinds of options and edge cases that make both storing and using it fairly challenging. The two things left out in the diagram on the right are the relationships between Stops and FareRules (a stop has a zone_id assigned to it that maps to a fare’s destination_id, origin_id or contains_id) and those between Trip and Calendar or Calendar Dates (where the crucial link is service_id, but where CalendarDate can override Calendar linkings, if defined.)

Of course, I’ll evolve the structure as I go along. It’s obviously designed primarily for routing applications like Google Maps, with such fields as color or text_color. So I’ll have to add my own fields too. You can see, for instance, the count and accessible fields in Route from the existing TTC Mobile app. (I’m surprised that there’s no machine-readable way to add such feature codes to GTFS, actually, e.g. Washrooms. You could put a human-readable version in Route’s desc or Stop’s desc, however.)

Also, right now there’s duplication in the dataset such that there’s both a trip_id and a trip relationship — this is primarily for debugging purposes and will eventually be eliminated.

So I still have a bit more modelling to go, and then I have to write some loading code, but within the next month, lets say, I might have a working version of TTC Mobile with GTFS data from YRT. For TTC data, I’ll additionally need to scrape together my own GTFS file, or use myttc.ca’s (clearly marked as such and/or unofficial).

Edit: Still working on this, just noticed a bug in the image I posted, Trip should have many Frequiences, and not the other way around. (The arrows are incorrect)