2024-03-28

Reprenons le problème d'hier, j'ai un voyage 'trip' qui est un peu une anomalie dans les données Mairie de Montreuil -> Porte de Montreuil, ça se produit uniquement 2 fois, sur un même service IDFM:100001 un vendredi.

Je n'arrive pas à le filtrer simplement, alors je me dis qu'en me basant sur la durée du trajet entre deux stations, je pourrais écarter les durées trop longues (> 5min), ça m'embête pour la ré-utilisabilité du code.

J'avais noté de former la donnée sous cette forme :

{
  "adjacents": [
    {
      "from_stop_id": "stop_id",
      "to_stop_id": "adjacent_stop_id",
      "time": 123,
      "path": [
        {
          "lat": 48.123,
          "lon": 2.123
        }
      ]
    }
  ]
}

J'hésite avec directement la liste des adjacents depuis une station, sous cette forme :

{
  "adjacents": [
    {
      "from_stop_id": "stop_id",
      "adjacents": [
        {
          "to_stop_id": "adjacent_stop_id",
          "time": 123,
          "path": [
            {
              "lat": 48.123,
              "lon": 2.123
            }
          ]
        }
      ]
    }
  ]
}

Ce qui permet de faire un find au lieu d'un filter, mais bon combien il y a de données ?

postgres@localhost:idfm> WITH stops_and_adjacents AS (
     SELECT DISTINCT
         st.stop_id AS from_stop_id,
         adjacent_stops.stop_id AS to_stop_id,
         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
         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
 ), "rqt" as (
 SELECT
     from_stop_id,
     to_stop_id,
     MIN(duration) AS min_duration
 FROM
     stops_and_adjacents
 GROUP BY
     from_stop_id,
     to_stop_id
 ORDER BY min_duration DESC) select count(*) from rqt;
+-------+
| count |
|-------|
| 1490  |
+-------+
SELECT 1

On est sur 1490 adjacents, ça reste raisonnable, je vais partir sur la première forme, ça se modifie facilement au pire.

Il me reste du coup la deuxième partie à faire : adjacents, car c'est 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.

select from_stop_id, fs.stop_name, to_stop_id, ts.stop_name, pathways.traversal_time, fr.route_short_name, tr.route_short_name
from pathways
inner join stops fs on fs.stop_id = pathways.from_stop_id
inner join stops ts on ts.stop_id = pathways.to_stop_id
inner join stop_times fst on fst.stop_id = pathways.from_stop_id
inner join stop_times tst on tst.stop_id = pathways.to_stop_id
inner join trips ft on ft.trip_id = fst.trip_id
inner join trips tt on tt.trip_id = tst.trip_id
inner join routes fr on fr.route_id = ft.route_id
inner join routes tr on tr.route_id = tt.route_id
where fr.route_type = 1 and tr.route_type = 1;

Oups, ça ne remonte rien. Ah oui, ce ne sont pas les pathways qui m'intéressent, mais les transfers, seulement, je ne les ai pas importés.

Je modifie la migration, et j'ajoute ce qu'il faut pour importer les données.

select from_stop_id, to_stop_id, transfers.min_transfer_time, fr.route_short_name, tr.route_short_name
from transfers
inner join stop_times fst on fst.stop_id = transfers.from_stop_id
inner join stop_times tst on tst.stop_id = transfers.to_stop_id
inner join trips ft on ft.trip_id = fst.trip_id
inner join trips tt on tt.trip_id = tst.trip_id
inner join routes fr on fr.route_id = ft.route_id
inner join routes tr on tr.route_id = tt.route_id
where fr.route_type = 1 and tr.route_type = 1;

Bon la requête ne passe pas, c'est le moment d'ajouter des index, la plus grosse jointure est sûr stop_times, je vais ajouter un index sur stop_id dans la table transfers et stop_times

Cela ne change pas grand-chose, bon en réalité je n'ai pas besoin de tous les stop_times, c'est un ridicule. Ce qu'il faut faire c'est de récupérer les stop_id qui sont sur les routes qui m'intéressent.

with stop_ids as (select
    distinct stop_id, route_short_name
    from stop_times
    inner join trips on stop_times.trip_id = trips.trip_id
    inner join routes on trips.route_id = routes.route_id
    where routes.route_type = 1)
select distinct from_stop_id, fst.stop_name, fs.route_short_name, to_stop_id, tst.stop_name, ts.route_short_name, transfers.min_transfer_time
from transfers
inner join stop_ids fs on fs.stop_id = transfers.from_stop_id
inner join stop_ids ts on ts.stop_id = transfers.to_stop_id
inner join stops fst on fst.stop_id = transfers.from_stop_id
inner join stops tst on tst.stop_id = transfers.to_stop_id

Et voilà, j'ai le résultat en 0.4s. En ajoutant les noms des stations, je retrouve bien le fameux Saint-Augustin -> Saint-Lazare qui est une correspondance possible, ça fait plaisir.

Ah, mais il y a aussi les stations proches, du genre Stalingrad -> Jaurès.

Bon, faisons le code pour récupérer les données. Ici, on a des secondes de l'autre côté des duration en interval, va falloir harmoniser le tout.

Puis, on peut peut-être tout faire en un seul coup

with stop_ids as (select
    distinct stop_id, route_short_name
    from stop_times
    inner join trips on stop_times.trip_id = trips.trip_id
    inner join routes on trips.route_id = routes.route_id
    where routes.route_type = 1
),
adjacent_stops_in_transfers as (
    select distinct from_stop_id, fst.stop_name, fs.route_short_name, to_stop_id, tst.stop_name, ts.route_short_name, transfers.min_transfer_time as duration
    from transfers
    inner join stop_ids fs on fs.stop_id = transfers.from_stop_id
    inner join stop_ids ts on ts.stop_id = transfers.to_stop_id
    inner join stops fst on fst.stop_id = transfers.from_stop_id
    inner join stops tst on tst.stop_id = transfers.to_stop_id
),
ajacent_stops_by_routes as (
    select distinct
        st.stop_id as from_stop_id,
        adjacent_stops.stop_id as to_stop_id,
        case
            when st.arrival_time::interval <= adjacent_stops.arrival_time::interval
                then extract(epoch from (adjacent_stops.arrival_time::interval - st.arrival_time::interval))::int
            else extract(epoch from (st.arrival_time::interval - adjacent_stops.arrival_time::interval))::int
        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 stop_ids as fs on st.stop_id = fs.stop_id
    inner join stop_ids as ts on adjacent_stops.stop_id = ts.stop_id
    where abs(st.stop_sequence - adjacent_stops.stop_sequence) = 1
),
adjacent_stops as (
    select
        from_stop_id,
        to_stop_id,
        duration
    from ajacent_stops_by_routes
    union
    select
        from_stop_id,
        to_stop_id,
        duration
    from adjacent_stops_in_transfers
)
select
    from_stop_id,
    to_stop_id,
    MIN(duration) AS min_duration
from
    adjacent_stops
group by
    from_stop_id,
    to_stop_id

Je vais m'amuser à l'écrire sous le format knex, je vais demander à Mistral en local.

J'ai obtenu quelque chose de pas trop mal, il a voulu simplifier l'union avec le group by de fin, je l'ai retiré. Ça aide bien.

Il me manque d'ajouter les paths dans les stations adjacentes, mais ça ne sera pas dans cette couche comme les paths ne sont pas en base de données.