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.
赞赏微信赞赏
支付宝赞赏