This blog presents how public transportation schedules in GTFS.
1. How it works
Each trip (identify as trip_id) is associated with a service_id.
service_id identifies the trip is available on which days from start_date to end_date.
At calendar_dates, exception_type: (it might add or remove some special date to the service_id)
The exception_type indicates whether service is available on the date specified in the date field.
- A value of 1 indicates that service has been added for the specified date.
- A value of 2 indicates that service has been removed for the specified date.
2. Service types
There are 4 service types in Toulouse public transportation schedules: Weekday, Saturday, Sunday and Holidays, Weekday School Holidays.
mysql> -- service_id,monday,tuesday,wednesday,thursday,friday,saturday,sunday,start_date,end_date
mysql> SELECT COUNT(*) AS nrof_service_id, monday,tuesday,wednesday,thursday,friday,saturday,sunday
-> FROM calendar
-> GROUP BY monday,tuesday,wednesday,thursday,friday,saturday,sunday
-> ORDER BY nrof_service_id DESC;
+-----------------+--------+---------+-----------+----------+--------+----------+--------+
| nrof_service_id | monday | tuesday | wednesday | thursday | friday | saturday | sunday |
+-----------------+--------+---------+-----------+----------+--------+----------+--------+
| 38 | 1 | 1 | 1 | 1 | 1 | 0 | 0 |
| 31 | 0 | 0 | 0 | 0 | 0 | 1 | 0 |
| 19 | 0 | 0 | 0 | 0 | 0 | 0 | 1 |
| 7 | 0 | 0 | 0 | 0 | 1 | 0 | 0 |
| 6 | 1 | 1 | 0 | 1 | 1 | 0 | 0 |
| 6 | 0 | 0 | 0 | 0 | 0 | 1 | 1 |
| 5 | 1 | 1 | 1 | 1 | 0 | 0 | 0 |
| 3 | 0 | 0 | 1 | 0 | 0 | 0 | 0 |
| 3 | 1 | 1 | 1 | 1 | 1 | 1 | 0 |
| 2 | 0 | 0 | 0 | 1 | 0 | 0 | 1 |
| 2 | 0 | 0 | 0 | 1 | 0 | 0 | 0 |
| 2 | 1 | 0 | 0 | 0 | 0 | 0 | 0 |
| 2 | 1 | 0 | 1 | 1 | 1 | 0 | 0 |
| 1 | 0 | 1 | 1 | 1 | 1 | 0 | 0 |
| 1 | 1 | 0 | 0 | 1 | 1 | 0 | 0 |
| 1 | 1 | 1 | 0 | 1 | 0 | 0 | 0 |
| 1 | 1 | 1 | 1 | 0 | 1 | 0 | 0 |
| 1 | 0 | 0 | 0 | 1 | 1 | 0 | 1 |
| 1 | 1 | 1 | 1 | 1 | 1 | 0 | 1 |
| 1 | 1 | 1 | 0 | 0 | 0 | 0 | 0 |
| 1 | 0 | 0 | 0 | 0 | 1 | 1 | 0 |
| 1 | 0 | 1 | 0 | 0 | 1 | 1 | 0 |
| 1 | 0 | 0 | 1 | 1 | 0 | 0 | 0 |
+-----------------+--------+---------+-----------+----------+--------+----------+--------+
23 rows in set (0,00 sec)
3. start_date and end_date
mysql> SELECT MIN(start_date), MAX(start_date), MIN(end_date), MAX(end_date)
-> FROM calendar;
+-----------------+-----------------+---------------+---------------+
| MIN(start_date) | MAX(start_date) | MIN(end_date) | MAX(end_date) |
+-----------------+-----------------+---------------+---------------+
| 20131029 | 20150725 | 20150206 | 20151231 |
+-----------------+-----------------+---------------+---------------+
1 row in set (0,00 sec)
3. Application notes
Filter out all trips for a given date.
赞赏
微信赞赏
支付宝赞赏