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)