This blog presents how GTFS is organized and shares three data diagrams for a better understanding.
1. How GTFS is organized
Personally, GTFS is well-organized and its manual (here) is very readable as well. The following data diagram is borrowed from MARTA Developer Resources, showing clearly the structure of GTFS.
Fig. 1 UML diagram of MARTA’s GTFS
There are two UML (Unified Modeling Language) diagrams posted on the blog Data model diagrams for GTFS which is quite helpful to design a database.
Fig. 2 Data model diagram for GTFS
Fig. 3 UML for GTFS
2. Demonstration Example
In this section, we illustrate how GTFS is organized using Tisséo GTFS.
A public transportation networks consists of N routes or lines. For instance, there are 106 routes in Toulouse, part of them are shown in the following:
mysql> SELECT COUNT(route_id) FROM routes; +-----------------+ | COUNT(route_id) | +-----------------+ | 106 | +-----------------+ 1 row in set (0,00 sec) mysql> SELECT route_id, route_short_name, route_long_name FROM routes LIMIT 10; +-------------------+------------------+------------------------------------------+ | route_id | route_short_name | route_long_name | +-------------------+------------------+------------------------------------------+ | 11821949021891615 | 1 | Grand Rond / Compans - Caffarelli | | 11821949021891616 | 2 | Cours Dillon / Université Paul Sabatier | | 11821949021891617 | 3 | St Cyprien - République / Oncopole | | 11821949021891618 | 8 | Basso Cambo / Cité Scolaire Rive-Gauche | | 11821949021891619 | 10 | Cours Dillon / Malepère | | 11821949021891621 | 12 | Cours Dillon / Basso Cambo | | 11821949021891623 | 14 | Marengo - SNCF / Basso Cambo | | 11821949021891624 | 15 | "Jeanne d'Arc / États-Unis Fondeyre" | | 11821949021891625 | L16 | "Sept Deniers / Gymnase de l'Hers" | | 11821949021891627 | 19 | "Borderouge / Place de l'Indépendance" | +-------------------+------------------+------------------------------------------+ 10 rows in set (0,00 sec)
A route contains several trips. In general, there are several trips of a given route within one day.
mysql> SELECT COUNT(*) FROM trips -> JOIN routes ON routes.route_id=trips.route_id AND routes.route_short_name='34'; +----------+ | COUNT(*) | +----------+ | 400 | +----------+ 1 row in set (0,00 sec) mysql> mysql> SELECT trips.* FROM trips -> JOIN routes ON routes.route_id=trips.route_id AND routes.route_short_name='34' -> LIMIT 5; +------------------+------------------+-------------------+------------------------------------+--------------+------------------+ | trip_id | service_id | route_id | trip_headsign | direction_id | shape_id | +------------------+------------------+-------------------+------------------------------------+--------------+------------------+ | 4503603928279631 | 4503603928276835 | 11821949023083020 | Université Paul Sabatier TOULOUSE | 0 | 4503603928279631 | | 4503603928279632 | 4503603928276835 | 11821949023083020 | Université Paul Sabatier TOULOUSE | 0 | 4503603928279631 | | 4503603928279633 | 4503603928276835 | 11821949023083020 | Université Paul Sabatier TOULOUSE | 0 | 4503603928279631 | | 4503603928279634 | 4503603928276835 | 11821949023083020 | Université Paul Sabatier TOULOUSE | 0 | 4503603928279631 | | 4503603928279635 | 4503603928276835 | 11821949023083020 | Université Paul Sabatier TOULOUSE | 0 | 4503603928279631 | +------------------+------------------+-------------------+------------------------------------+--------------+------------------+ 5 rows in set (0,00 sec)
A trip is composed of several stops. For instance, Bus 34 in Toulouse,
mysql> SELECT stop_times.trip_id, stop_times.stop_id, stop_times.stop_sequence, stops.stop_name, stop_times.arrival_time, stop_times.departure_time -> FROM stop_times -> JOIN stops ON stops.stop_id=stop_times.stop_id AND stop_times.trip_id='4503603928279631'; +------------------+------------------+---------------+----------------------------+--------------+----------------+ | trip_id | stop_id | stop_sequence | stop_name | arrival_time | departure_time | +------------------+------------------+---------------+----------------------------+--------------+----------------+ | 4503603928279631 | 3377699720880799 | 0 | Arènes | 06:01:00 | 06:01:00 | | 4503603928279631 | 3377699720882759 | 1 | Déodat de Séverac | 06:05:00 | 06:05:00 | | 4503603928279631 | 3377699720882896 | 2 | Lycée Déodat de Séverac | 06:07:00 | 06:07:00 | | 4503603928279631 | 3377699720881776 | 3 | Croix de Pierre | 06:07:00 | 06:07:00 | | 4503603928279631 | 3377699721872216 | 4 | Stadium Ouest | 06:08:00 | 06:08:00 | | 4503603928279631 | 3377699721872227 | 5 | Stadium Est | 06:09:00 | 06:09:00 | | 4503603928279631 | 3377699720881977 | 6 | Récollets Daste | 06:10:00 | 06:10:00 | | 4503603928279631 | 3377699720882808 | 7 | Récollets | 06:12:00 | 06:12:00 | | 4503603928279631 | 3377699720880648 | 8 | Saint Agne-SNCF | 06:12:00 | 06:12:00 | | 4503603928279631 | 3377699720882811 | 9 | Av. de Rangueil | 06:13:00 | 06:13:00 | | 4503603928279631 | 3377699720882813 | 10 | Delmas | 06:14:00 | 06:14:00 | | 4503603928279631 | 3377699720882816 | 11 | Jules Julien | 06:14:00 | 06:14:00 | | 4503603928279631 | 3377699720882817 | 12 | Pouset | 06:15:00 | 06:15:00 | | 4503603928279631 | 3377699720882820 | 13 | Caubère | 06:16:00 | 06:16:00 | | 4503603928279631 | 3377699720882824 | 14 | IUT | 06:16:00 | 06:16:00 | | 4503603928279631 | 3377699720880945 | 15 | Pont Ducuing | 06:17:00 | 06:17:00 | | 4503603928279631 | 3377699720881450 | 16 | Université Paul Sabatier | 06:19:00 | 06:19:00 | +------------------+------------------+---------------+----------------------------+--------------+----------------+ 17 rows in set (0,07 sec)赞赏
微信赞赏
支付宝赞赏
Pingback: GTFS Application Notes: Table of ContentsSpark & Shine | Spark & Shine