GTFS trips. Temporally. I will refine later.
1.
Does all trips with the same route_id contains the same first stop and the last stop
mysql> source /home/qiankun/git/tisseo_toulouse/blog_queries.sql Query OK, 0 rows affected (0,00 sec) +-------------------+------------------+-----------+---------------------+----------------------+----------------------------+--------------------+---------------------+----------------------------+ | route_id | route_short_name | nrofTrips | first_stop_sequence | first_parent_station | first_stop_name | last_stop_sequence | last_parent_station | last_stop_name | +-------------------+------------------+-----------+---------------------+----------------------+----------------------------+--------------------+---------------------+----------------------------+ | 11821949021891615 | 1 | 151 | 0 | 1970324837184671 | Compans-Caffarelli | 14 | 1970324837184947 | Grand Rond | | 11821949021891615 | 1 | 151 | 0 | 1970324837184947 | Grand Rond | 11 | 1970324837184671 | Compans-Caffarelli | | 11821949021891616 | 2 | 211 | 0 | 1970324837184995 | Cours Dillon | 23 | 1970324837185012 | Université Paul Sabatier | | 11821949021891616 | 2 | 208 | 0 | 1970324837185012 | Université Paul Sabatier | 22 | 1970324837184995 | Cours Dillon | | 11821949021891617 | 3 | 163 | 0 | 1970324837184772 | St Cyprien - République | 29 | 1970324839361574 | Oncopole | | 11821949021891617 | 3 | 168 | 0 | 1970324839361574 | Oncopole | 28 | 1970324837184772 | St Cyprien - République | | 11821949021891618 | 8 | 197 | 0 | 1970324837184808 | Basso Cambo | 13 | 1970324837184900 | Cité Scolaire Rive Gauche | | 11821949021891618 | 8 | 196 | 0 | 1970324837184900 | Cité Scolaire Rive Gauche | 13 | 1970324837184808 | Basso Cambo | | 11821949021891619 | 10 | 217 | 0 | 1970324837184995 | Cours Dillon | 23 | 1970324837186852 | Malepère | | 11821949021891619 | 10 | 218 | 0 | 1970324837186852 | Malepère | 27 | 1970324837184995 | Cours Dillon | +-------------------+------------------+-----------+---------------------+----------------------+----------------------------+--------------------+---------------------+----------------------------+ 10 rows in set (49,31 sec)
The above sql sentences are as follows: (There must exist a more efficient implementation.)
-- New table:
-- route_id, route_short_name, nrofTrips,
-- first_stop_sequence=0, first_parent_station, first_stop_name,
-- last_stop_sequence=MAX, last_parent_station, last_stop_name
SELECT routes.route_id, routes.route_short_name, COUNT(*) AS nrofTrips, T1.first_stop_sequence, T1.first_parent_station, T1.first_stop_name, T2.last_stop_sequence, T2.last_parent_station, T2.last_stop_name
FROM
-- T1: trip_id, first_stop_sequence, first_parent_station (of the first stop), first_stop_name (of first_parent_station)
(SELECT st_first1.trip_id, st_first2.first_stop_sequence, stops.parent_station AS first_parent_station, stops.stop_name AS first_stop_name
FROM stop_times st_first1
INNER JOIN
-- filter out the first stop
-- st_first2: trip_id, first_stop_sequence=0
(SELECT stop_times.trip_id, MIN(CAST(stop_times.stop_sequence AS UNSIGNED)) AS first_stop_sequence
FROM stop_times
GROUP BY stop_times.trip_id
) st_first2
ON st_first1.trip_id=st_first2.trip_id AND st_first1.stop_sequence=st_first2.first_stop_sequence
JOIN stops ON stops.stop_id=st_first1.stop_id -- get stop_name
) T1
LEFT JOIN -- combine T1 and T2
-- T2: trip_id, last_stop_sequence=MAX, last_parent_station of last_stop_id, last_stop_name (of last_parent_station)
(SELECT st_last1.trip_id, st_last2.last_stop_sequence, stops.parent_station AS last_parent_station, stops.stop_name AS last_stop_name
FROM stop_times st_last1
INNER JOIN
-- filter out the last stop
-- st_last2: trip_id, last_stop_sequence=MAX
(SELECT stop_times.trip_id, MAX(CAST(stop_times.stop_sequence AS UNSIGNED)) AS last_stop_sequence
FROM stop_times
GROUP BY stop_times.trip_id
) st_last2
ON st_last1.trip_id=st_last2.trip_id AND st_last1.stop_sequence=st_last2.last_stop_sequence
JOIN stops ON stops.stop_id=st_last1.stop_id -- get stop_name
) T2
ON T1.trip_id=T2.trip_id
JOIN trips ON trips.trip_id=T1.trip_id
JOIN routes ON routes.route_id=trips.route_id
GROUP BY routes.route_id, T1.first_parent_station, T2.last_parent_station -- to count the number of trips
ORDER BY routes.route_id;
2. An example
https://www.tisseo.fr/sites/default/files/Tisseo_ligne_43web.pdf
It depends the service type:
Take bus 43 as an example,
+-------------------+------------------+-----------+---------------------+----------------------+-------------------------------+--------------------+---------------------+-------------------------------+ | route_id | route_short_name | nrofTrips | first_stop_sequence | first_parent_station | first_stop_name | last_stop_sequence | last_parent_station | last_stop_name | +-------------------+------------------+-----------+---------------------+----------------------+-------------------------------+--------------------+---------------------+-------------------------------+ | 11821949021891895 | 43 | 17 | 0 | 1970324837184932 | "Jeanne d'Arc" | 34 | 1970324837184935 | "St-Jean Clinique de L'Union" | | 11821949021891895 | 43 | 18 | 0 | 1970324837184935 | "St-Jean Clinique de L'Union" | 36 | 1970324837184932 | "Jeanne d'Arc" | | 11821949021891895 | 43 | 1 | 0 | 1970324837184935 | "St-Jean Clinique de L'Union" | 21 | 1970324837186135 | Lycée Raymond Naves | | 11821949021891895 | 43 | 28 | 0 | 1970324837184935 | "St-Jean Clinique de L'Union" | 23 | 1970324837190453 | Argoulets | | 11821949021891895 | 43 | 2 | 0 | 1970324837186135 | Lycée Raymond Naves | 22 | 1970324837184935 | "St-Jean Clinique de L'Union" | | 11821949021891895 | 43 | 33 | 0 | 1970324837190453 | Argoulets | 24 | 1970324837184935 | "St-Jean Clinique de L'Union" | | 11821949021891895 | 43 | 3 | 0 | 1970324837190453 | Argoulets | 33 | 1970324837394073 | Collège St-Jean | | 11821949021891895 | 43 | 10 | 0 | 1970324837190453 | Argoulets | 33 | 1970324840530366 | St-Jean F. Mitterrand | | 11821949021891895 | 43 | 3 | 0 | 1970324837394073 | Collège St-Jean | 31 | 1970324837190453 | Argoulets | | 11821949021891895 | 43 | 10 | 0 | 1970324840530366 | St-Jean F. Mitterrand | 32 | 1970324837190453 | Argoulets | +-------------------+------------------+-----------+---------------------+----------------------+-------------------------------+--------------------+---------------------+-------------------------------+ 10 rows in set (36,00 sec)
The above sql sentences are as follows: (There must exist a more efficient implementation.)
But even in the same service type:
https://www.tisseo.fr/sites/default/files/Tisseo_ligne_69web.pdf
JOIN calendar
ON calendar.service_id=trips.service_id
AND calendar.monday=1 AND calendar.tuesday=1
AND calendar.wednesday=1 AND calendar.thursday=1 -- AND calendar.friday=1
AND calendar.saturday=0 AND calendar.sunday=0
+-------------------+------------------+-----------+---------------------+----------------------+---------------------+--------------------+---------------------+-------------------------+
| route_id | route_short_name | nrofTrips | first_stop_sequence | first_parent_station | first_stop_name | last_stop_sequence | last_parent_station | last_stop_name |
+-------------------+------------------+-----------+---------------------+----------------------+---------------------+--------------------+---------------------+-------------------------+
| 11821949021891664 | 69 | 2 | 0 | 1970324837184984 | Gratentour Gravette | 35 | 1970324837186162 | Lycée Toulouse-Lautrec |
| 11821949021891664 | 69 | 8 | 0 | 1970324837184984 | Gratentour Gravette | 34 | 1970324838319080 | La Vache |
| 11821949021891664 | 69 | 2 | 0 | 1970324837184987 | Bruguières | 30 | 1970324837186162 | Lycée Toulouse-Lautrec |
| 11821949021891664 | 69 | 10 | 0 | 1970324837184987 | Bruguières | 29 | 1970324838319080 | La Vache |
| 11821949021891664 | 69 | 8 | 0 | 1970324838319080 | La Vache | 34 | 1970324837184984 | Gratentour Gravette |
| 11821949021891664 | 69 | 10 | 0 | 1970324838319080 | La Vache | 30 | 1970324837184987 | Bruguières |
+-------------------+------------------+-----------+---------------------+----------------------+---------------------+--------------------+---------------------+-------------------------+
6 rows in set (37,58 sec)
赞赏
微信赞赏
支付宝赞赏