2024-03-27
Cette migration vers l'usage de GTFS est plutôt intéressante. Cela me permet de résoudre plusieurs choses :
- Avoir un mode par temps de trajet
- Avoir un affichage des stations plus précises (l'unique point par station)
- Avoir toutes les correspondances possibles (ex: Saint-Lazare - St-Augustin)
- Être en mesure d'ajouter le RER, le bus, le tramway, etc.
- Être en mesure de pouvoir faire d'autres villes avec quelques changements
Import des données
Hier, je me suis arrêté à un moment où je streamais les données parsées pour les enregistrer en base de données. Seulement, à la fin du script, je n'avais pas tout en base. Regardons de plus près.
❯ wc -l ~/Downloads/IDFM-gtfs/trips.txt
440474 /Users/vincenthardouin/Downloads/IDFM-gtfs/trips.txt
Il y a 440 474 lignes dans le fichier
trips.txt, moins 1 ligne de header et j'ai 433 lignes dans ma base de données.
❯ pgcli postgresql://postgres@localhost:5432/idfm
Server: PostgreSQL 14.10
Version: 4.0.1
Home: http://pgcli.com
postgres@localhost:idfm> select count(*) from trips;
+-------+
| count |
|-------|
| 433 |
+-------+
SELECT 1
Time: 0.014s
Une légère différence …
Voici mon implémentation de stream :
await new Promise((resolve, reject) => {
stream.on('data', async (chunk) => {
await saveFunction([chunk]);
});
stream.on('end', () => {
resolve();
});
stream.on('error', (error) => {
reject(error);
});
});
Je suppose que l'async callback ne fonctionne pas, ce qui parait assez logique. Je transforme donc le code sous cette forme :
for await (const chunk of stream) {
await saveFunction([chunk]);
}
Je relance le script, c'est long, mais ça fonctionne bien. Là, on commit 1 à 1 chaque ligne. Je me dis que je peux envoyer des batchs :
for await (const data of stream) {
chunks.push(data);
if (chunks.length > 10000) {
await saveFunction(chunks);
chunks = [];
}
}
Je relance le script, c'est plus rapide. On commit par batch de 1000 lignes. 10 secondes pour 413 000 lignes, testons par 10 000. J'augmente donc à 10 000 la taille du batch de cette fonction est aussi dans le batchInsert de knex, j'ai les mêmes résultats. Je reste à 1 000.
Oups, durant l'import, j'ai l'erreur :
hint: 'Check free disk space.',. Je ne dois pas allouer assez de place pour
docker.
Mais la base est si grosse que ça ? Je regarde :
postgres@localhost:idfm> SELECT pg_size_pretty( pg_database_size('idfm') );
+----------------+
| pg_size_pretty |
|----------------|
| 1912 MB |
+----------------+
SELECT 1
Time: 0.033s
Ah 2 Go quand même. Bon, naïvement, je lance un
docker system prune. Je récupère 3Go.
Je relance. En 4 minutes 55, j'ai tout en base. Je ne sais pas quoi en penser niveau performance, mais ce n'est déjà pas
si mal.
Le map sur les 1000 éléments à chaque insertion ne doit pas aider, mais ce n'est pas le sujet.
Je vais pouvoir passer aux requêtes sql pour récupérer les données.
Export des données
Hier, j'ai fait un semblant de json qui me plairait bien :
{
"routes": [
{
"route_id": "route_id",
"route_name": "route_name",
"route_color": "route_color"
}
],
"stops": [
{
"name": "stop_name",
"id": "stop_id",
"stop_unique_id": "stop_unique_id",
"route_id": "route_id"
}
],
"adjacents": [
{
"from_stop_id": "stop_id",
"to_stop_id": "adjacent_stop_id",
"time": 123,
"path": [
{
"lat": 48.123,
"lon": 2.123
}
]
}
],
"stops_unique": [
{
"id": "stop_unique_id",
"name": "stop_name",
"lat": 48.123,
"lon": 2.123
}
]
}
Routes
Je commence par les routes, dans mon cas, je ne veux que les lignes de métros.
En basant sur le document GTFS de IDFM, je peux voir que le
route_type est à 1
pour les métros. Faisons du TDD, après ce moment sans tests, pour l'import.
Stops
Ensuite, les stops, ça va être plus sympa. Pour les stops, il faut faire la jointure avec
stop_times, puis
trips, et
les liés aux
routes.
En faisant cette requête :
select distinct(stop_name)
from stops
inner join stop_times on stops.stop_id = stop_times.stop_id
inner join trips on trips.trip_id = stop_times.trip_id
inner join routes on routes.route_id = trips.route_id
where routes.route_type = 1;
J'ai la réponse en 2s, et 0.6s au deuxième appel (avec du cache). C'est clairement OK, merci PG <3.
Adjacents
Pour les adjacents, il y a plusieurs calculs à faire.
Adjacents sur une même ligne
Il faut d'abord récupérer les adjacents sur une même ligne, pour ça il faut se baser sur les
stop_times grâce
au
stop_sequence.
Si la station a un
stop_sequence de 5, alors ses adjacents sont les stations 4 et 6.
J'obtiens quelque chose avec cette requête :
SELECT DISTINCT s.stop_name AS station_name,
st.stop_sequence AS current_sequence,
adjacent_stops.stop_sequence AS adjacent_sequence,
adjacent_stops_station.stop_name AS adjacent_station_name,
r.route_short_name AS line_number
FROM stop_times AS st
INNER JOIN stop_times AS adjacent_stops ON st.trip_id = adjacent_stops.trip_id
INNER JOIN stops AS s ON st.stop_id = s.stop_id
INNER JOIN stops AS adjacent_stops_station ON adjacent_stops.stop_id = adjacent_stops_station.stop_id
INNER JOIN trips AS t ON st.trip_id = t.trip_id
INNER JOIN routes AS r ON t.route_id = r.route_id
WHERE ABS(st.stop_sequence - adjacent_stops.stop_sequence) = 1
AND r.route_type = 1;
Elle met 5s, c'est plutôt correct sur 9 millions de lignes et pour un one-shot.
Ce qui m'étonne avec cette requête, c'est que par exemple si on prends Alésia, on a :
+-----------------------------------------+------------------+-------------------+-------------------------------------+-------------+
| station_name | current_sequence | adjacent_sequence | adjacent_station_name | line_number |
|-----------------------------------------+------------------+-------------------+-------------------------------------+-------------|
| Alésia | 1 | 0 | Porte d'Orléans | 4 |
| Alésia | 1 | 2 | Mouton-Duvernet | 4 |
| Alésia | 4 | 3 | Porte d'Orléans | 4 |
| Alésia | 4 | 5 | Mouton-Duvernet | 4 |
| Alésia | 19 | 18 | Mouton-Duvernet | 4 |
| Alésia | 19 | 20 | Porte d'Orléans | 4 |
| Alésia | 24 | 23 | Mouton-Duvernet | 4 |
| Alésia | 24 | 25 | Porte d'Orléans | 4 |
On peut voir les aller-retour par exemple dans un sens, Alésia et la deuxième station (current_sequence = 1) et a donc comme stations adjacentes : Porte d'Orléans et Mouton-Duvernet. Dans l'autre sens, Alésia, c'est la 19e station avec les mêmes stations adjacentes. Mais, c'est aussi la 4e station et la 19e, comme si la ligne était raccourcie à un moment donné ou rallongée d'ailleurs.
Si j'affiche le
trip_headsign, je pourrais en savoir plus
+-----------------------------------------+------------------+-------------------+-------------------------------------+-------------+-------------------------------------------------+
| station_name | current_sequence | adjacent_sequence | adjacent_station_name | line_number | trip_name |
|-----------------------------------------+------------------+-------------------+-------------------------------------+-------------+-------------------------------------------------|
| Alésia | 1 | 0 | Porte d'Orléans | 4 | Porte de Clignancourt |
| Alésia | 1 | 2 | Mouton-Duvernet | 4 | Porte de Clignancourt |
| Alésia | 4 | 3 | Porte d'Orléans | 4 | Porte de Clignancourt |
| Alésia | 4 | 5 | Mouton-Duvernet | 4 | Porte de Clignancourt |
| Alésia | 19 | 18 | Mouton-Duvernet | 4 | Bagneux - Lucie Aubrac |
| Alésia | 19 | 20 | Porte d'Orléans | 4 | Bagneux - Lucie Aubrac |
| Alésia | 24 | 23 | Mouton-Duvernet | 4 | Bagneux - Lucie Aubrac |
| Alésia | 24 | 25 | Porte d'Orléans | 4 | Bagneux - Lucie Aubrac |
On est clairement dans ce cas où la ligne est rallongée.
Bon dans tous les cas ça ne change pas les stations adjacentes, mais c'est intéressant à savoir.
Pour la version sans doublon :
SELECT DISTINCT s.stop_name AS station_name,
adjacent_stops_station.stop_name AS adjacent_station_name
FROM stop_times AS st
INNER JOIN stop_times AS adjacent_stops ON st.trip_id = adjacent_stops.trip_id
INNER JOIN stops AS s ON st.stop_id = s.stop_id
INNER JOIN stops AS adjacent_stops_station ON adjacent_stops.stop_id = adjacent_stops_station.stop_id
INNER JOIN trips AS t ON st.trip_id = t.trip_id
INNER JOIN routes AS r ON t.route_id = r.route_id
WHERE ABS(st.stop_sequence - adjacent_stops.stop_sequence) = 1
AND r.route_type = 1;
J'obtiens quelque chose comme ça :
+-----------------------------------------+-----------------------------------------+
| station_name | adjacent_station_name |
|-----------------------------------------+-----------------------------------------|
| Abbesses | Lamarck - Caulaincourt |
| Abbesses | Pigalle |
| Aimé Césaire | Front Populaire |
| Aimé Césaire | Mairie d'Aubervilliers |
| Alexandre Dumas | Avron |
| Alexandre Dumas | Philippe Auguste |
| Alma - Marceau | Franklin D. Roosevelt |
| Alma - Marceau | Iéna |
| Alésia | Mouton-Duvernet |
| Alésia | Porte d'Orléans |
Je n'ai pas besoin du nom des stations, mais uniquement des ids, je peux donc supprimer 2 jointures.
SELECT DISTINCT st.stop_id AS stop_id,
adjacent_stops.stop_id AS adjacent_stop_id
FROM stop_times AS st
INNER JOIN stop_times AS adjacent_stops ON st.trip_id = adjacent_stops.trip_id
INNER JOIN trips AS t ON st.trip_id = t.trip_id
INNER JOIN routes AS r ON t.route_id = r.route_id
WHERE ABS(st.stop_sequence - adjacent_stops.stop_sequence) = 1
AND r.route_type = 1
Par contre, je n'ai pas du tout pris en compte le temps de trajet entre les stations, je vais devoir le faire.
J'obtiens :
SELECT DISTINCT
st.stop_id AS stop_id,
adjacent_stops.stop_id AS adjacent_stop_id,
st.arrival_time::interval - adjacent_stops.arrival_time::interval AS duration,
t.trip_headsign AS trip_headsign,
t.direction_id AS direction_id,
r.route_short_name AS route_name
FROM stop_times AS st
INNER JOIN stop_times AS adjacent_stops ON st.trip_id = adjacent_stops.trip_id
INNER JOIN trips AS t ON st.trip_id = t.trip_id
INNER JOIN routes AS r ON t.route_id = r.route_id
WHERE ABS(st.stop_sequence - adjacent_stops.stop_sequence) = 1
AND r.route_type = 1;
Dont voici le résultat :
+------------+------------------+------------------+-------------------------------------------------+--------------+------------+
| stop_id | adjacent_stop_id | duration | trip_headsign | direction_id | route_name |
|------------+------------------+------------------+-------------------------------------------------+--------------+------------|
| IDFM:21784 | IDFM:463056 | 0:02:00 | Créteil-Pointe du Lac | 1 | 8 |
| IDFM:21784 | IDFM:463056 | 0:03:00 | Créteil-Pointe du Lac | 1 | 8 |
| IDFM:21902 | IDFM:463250 | -1 day, 23:58:00 | Gallieni (Parc de Bagnolet) | 0 | 3 |
| IDFM:21902 | IDFM:463250 | -1 day, 23:59:00 | Gallieni (Parc de Bagnolet) | 0 | 3 |
| IDFM:21902 | IDFM:463316 | 0:01:00 | Gallieni (Parc de Bagnolet) | 0 | 3 |
| IDFM:21902 | IDFM:463316 | 0:02:00 | Gallieni (Parc de Bagnolet) | 0 | 3 |
On peut constater plusieurs soucis, la durée en négatif et surtout pas la durée pour un même trajet.
En faisant un case, je résous le problème de la durée négative :
SELECT DISTINCT
st.stop_id AS stop_id,
adjacent_stops.stop_id AS adjacent_stop_id,
CASE
WHEN st.arrival_time::interval <= adjacent_stops.arrival_time::interval
THEN adjacent_stops.arrival_time::interval - st.arrival_time::interval
ELSE st.arrival_time::interval - adjacent_stops.arrival_time::interval
END AS duration,
t.trip_headsign AS trip_headsign,
t.direction_id AS direction_id,
r.route_short_name AS route_name
FROM stop_times AS st
INNER JOIN stop_times AS adjacent_stops ON st.trip_id = adjacent_stops.trip_id
INNER JOIN trips AS t ON st.trip_id = t.trip_id
INNER JOINroutes AS r ON t.route_id = r.route_id
WHERE ABS(st.stop_sequence - adjacent_stops.stop_sequence) = 1
AND r.route_type = 1;
Pour ma culture j'ai voulu voir les arrêts les plus longs:
WITH stops_and_adjacents AS (
SELECT DISTINCT
st.stop_id AS from_stop_id,
s.stop_name AS from_stop_name,
adjacent_stops.stop_id AS to_stop_id,
adjacent_stops_station.stop_name AS to_stop_name,
CASE
WHEN st.arrival_time::interval <= adjacent_stops.arrival_time::interval
THEN (adjacent_stops.arrival_time::interval - st.arrival_time::interval)::TEXT
ELSE (st.arrival_time::interval - adjacent_stops.arrival_time::interval)::TEXT
END AS duration
FROM
stop_times AS st
INNER JOIN
stop_times AS adjacent_stops ON st.trip_id = adjacent_stops.trip_id
INNER JOIN
trips AS t ON st.trip_id = t.trip_id
INNER JOIN stops AS s ON st.stop_id = s.stop_id
INNER JOIN stops AS adjacent_stops_station ON adjacent_stops.stop_id = adjacent_stops_station.stop_id
INNER JOIN
routes AS r ON t.route_id = r.route_id
WHERE
ABS(st.stop_sequence - adjacent_stops.stop_sequence) = 1
AND r.route_type = 1
)
SELECT
from_stop_id,
from_stop_name,
to_stop_id,
to_stop_name,
MIN(duration) AS min_duration
FROM
stops_and_adjacents
GROUP BY
from_stop_id,
from_stop_name,
to_stop_id,
to_stop_name
ORDER BY min_duration DESC;
Et voici les résultats :
+--------------+-----------------------------------------+-------------+-----------------------------------------+--------------+
| from_stop_id | from_stop_name | to_stop_id | to_stop_name | min_duration |
|--------------+-----------------------------------------+-------------+-----------------------------------------+--------------|
| IDFM:463221 | Mairie de Montreuil | IDFM:21913 | Porte de Montreuil | 00:06:00 |
| IDFM:21913 | Porte de Montreuil | IDFM:463221 | Mairie de Montreuil | 00:06:00 |
| IDFM:463131 | Bobigny Pablo Picasso | IDFM:22014 | Bobigny-Pantin - Raymond Queneau | 00:04:00 |
| IDFM:22014 | Bobigny-Pantin - Raymond Queneau | IDFM:463131 | Bobigny Pablo Picasso | 00:04:00 |
| IDFM:463002 | Bobigny-Pantin - Raymond Queneau | IDFM:22015 | Bobigny Pablo Picasso | 00:03:00 |
| IDFM:463159 | Châtelet | IDFM:21958 | Gare de Lyon | 00:03:00 |
| IDFM:21959 | Châtelet | IDFM:463046 | Gare de Lyon | 00:03:00 |
| IDFM:22015 | Bobigny Pablo Picasso | IDFM:463002 | Bobigny-Pantin - Raymond Queneau | 00:03:00 |
| IDFM:21958 | Gare de Lyon | IDFM:463159 | Châtelet | 00:03:00 |
| IDFM:463046 | Gare de Lyon | IDFM:21959 | Châtelet | 00:03:00 |
Ce qui est intéressant, c'est que le chemin : Mairie de Montreuil -> Porte de Montreuil, ne devrait pas exister, car il y a des stations entre les deux.
Oups, je me rends compte que ce qui peut m'aider c'est dans la table
stop_times, il y a un
pickup_type
et
drop_off_type mais je n'ai
pas fait la colonne. Je vais devoir la rajouter.
D'ailleurs, j'en ai un peu marre de regarder le csv dans TextEdit, j'installe csvlens, c'est plutôt pas mal.
En regardant les données après avoir ajouté les colonnes, je me rends qu'il y a pas mal où on ne peut pas monter ou descendre :
postgres@localhost:idfm> select count(*), count(*) filter(where drop_off_type = 1), count(*) filter(where pickup_type =1) from stop_times;
+---------+--------+--------+
| count | count | count |
|---------+--------+--------|
| 9761752 | 442360 | 441737 |
+---------+--------+--------+
SELECT 1
Time: 0.958s
En regardant du coup notre cas :
postgres@localhost:idfm> SELECT DISTINCT
st.stop_id AS from_stop_id,
s.stop_name AS from_stop_name,
adjacent_stops.stop_id AS to_stop_id,
adjacent_stops_station.stop_name AS to_stop_name,
CASE
WHEN st.arrival_time::interval <= adjacent_stops.arrival_time::interval
THEN (adjacent_stops.arrival_time::interval - st.arrival_time::interval)::TEXT
ELSE (st.arrival_time::interval - adjacent_stops.arrival_time::interval)::TEXT
END AS duration,
st.pickup_type, st.drop_off_type, adjacent_stops.pickup_type, adjacent_stops.drop_off_type
FROM
stop_times AS st
INNER JOIN
stop_times AS adjacent_stops ON st.trip_id = adjacent_stops.trip_id
INNER JOIN
trips AS t ON st.trip_id = t.trip_id
INNER JOIN stops AS s ON st.stop_id = s.stop_id
INNER JOIN stops AS adjacent_stops_station ON adjacent_stops.stop_id = adjacent_stops_station.stop_id
INNER JOIN
routes AS r ON t.route_id = r.route_id
WHERE
ABS(st.stop_sequence - adjacent_stops.stop_sequence) = 1
AND r.route_type = 1 AND st.stop_id = 'IDFM:463221'
+--------------+---------------------+-------------+--------------------+----------+-------------+---------------+-------------+---------------+
| from_stop_id | from_stop_name | to_stop_id | to_stop_name | duration | pickup_type | drop_off_type | pickup_type | drop_off_type |
|--------------+---------------------+-------------+--------------------+----------+-------------+---------------+-------------+---------------|
| IDFM:463221 | Mairie de Montreuil | IDFM:21913 | Porte de Montreuil | 00:06:00 | 0 | 1 | 1 | 0 |
| IDFM:463221 | Mairie de Montreuil | IDFM:463176 | Croix de Chavaux | 00:01:00 | 0 | 1 | 0 | 0 |
| IDFM:463221 | Mairie de Montreuil | IDFM:463176 | Croix de Chavaux | 00:02:00 | 0 | 1 | 0 | 0 |
+--------------+---------------------+-------------+--------------------+----------+-------------+---------------+-------------+---------------+
Donc ce qu'on peut voir c'est qu'à Mairie de Montreuil, c'est un arrêt où on peut que monter, mais on ne peut pas descendre (le train doit faire une boucle ou autres). Et à son arrivée à Porte de Montreuil, on ne peut que descendre.
On est vraiment dans un cas à la marge, je pense. Est-ce que je ne pourrais pas avoir tous les bouts de lignes pour éviter ce genre de cas ?
Adjacents sur une correspondance
Exemple : Saint-Lazare : Je peux changer de la ligne 13 à la 12, 14, …
Il faut donc récupérer les
pathways et les
stops pour avoir les correspondances.