[ETL] Corriger détermination des segments + recalculer tous les segments et excursions depuis avril

[marthevienne]

Suite à #283 et #238, il faut revoir la tâche create_update_segments_excursions et refaire tourner sur toute la table vessel_positions.

@ejamet73 corrige @marthevienne testera en local puis tournera en prod après review de @marthevienne, @rv2931 / @njouanin

TODO à compléter :

• [ ] empêcher la création de segments négatifs dûs au spoofing
• [ ] ... ?

[SebM42]

je ne comprends pas bien pourquoi il faut tout recalculer depuis le début

on peut juste ajouter une sécurité pour ne pas traiter les segments avec durée négatives dans l'ETL pour les futurs calculs, et juste supprimer de la base actuelle les segments avec durée négative et éditer les segments avant/apres

[marthevienne]

C'est à cause du fait que j'ai supprimé les relations segments/zones.

[marthevienne]

Les segments avant/après vont voir leur géométrie modifié, il va de toute façon falloir recalculer les durées.

[SebM42]

Les segments avant/après vont voir leur géométrie modifié, il va de toute façon falloir recalculer les durées.

J'avais l'impression que ca concernait seulement quelques segments, d'où ma suggestion de faire un fix sur ces segments (et ceux avant/apres) plutot que de devoir recréer plusieurs millions de segments

et les relations segments/zones peuvent être recalculés toutes seules sans avoir à refaire tous les segments/excursions

[marthevienne]

Il y a un autre souci que j'ai capté là, c'est qu'il y a potentiellement beaucoup plus de cas de spoofing que ceux que l'on voit :

• on traite les positions en batch pour un navire sauf qu'on trie les vessel_positions par date au sein du batch donc certains segments qui seraient négatifs ne le sont pas
• on n'a finalement pas de critère sur les vitesses aberrantes à ma connaissance

[marthevienne]

Pour le 19, on part sur une restauration de la table rel_segment_zone, @rv2931 est sur le coup. Je vais fouiller un peu plus les données de spire_ais_data, je pense qu'on n'est pas au bout de nos surprises sur les données AIS malgré le fait que Spire effectue déjà un bon nettoyage pour éviter le spoofing.

[marthevienne]

• on traite les positions en batch pour un navire sauf qu'on trie les vessel_positions par date au sein du batch donc certains segments qui seraient négatifs ne le sont pas Pour me rendre compte de l'ampleur du problème de spoofing, j'ai fait tourner la requête suivante : SELECT COUNT(*) FROM (SELECT *, position_timestamp - LAG(position_timestamp) OVER (ORDER by vessel_mmsi, created_at asc) AS difference_previous_position, vessel_mmsi = LAG(vessel_mmsi) OVER (ORDER by vessel_mmsi, created_at asc) AS same_mmsi FROM spire_ais_data sad WHERE same_mmsi IS TRUE AND difference_previous_position < '00:00:00';

On a 27 000 lignes sur 23 M dans spire_ais_data qui sont problématiques. C'est beaucoup plus que les 180 segments négatifs qu'on a identifié.

Cependant, il est fort probable qu'un grand nombre de ces lignes ne soient pas prises en compte grâce à la condition d'unicité des positions. Test à venir !