GTFS Application Notes: trips

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)

发表评论

电子邮件地址不会被公开。 必填项已用*标注