Changement de code pour certaines stations ESU ?

Certaines stations semblent avoir changé de code_station à partir du millésime 2023.

La requête suivante permet d'en identifier une partie :

SELECT *
FROM qualite_cours_d_eau.hubeau_qualite_rivieres_station_pc
WHERE (code_station LIKE 'BS%') OR (code_station LIKE 'BX%') OR (code_station LIKE'CB%')
ORDER BY code_station;

Résultat : code_station_like_bs_bx_cb.csv

assigned to @ronan.vignard

added Bug Données labels

changed title from Changement de code pour certaines stations ? to Changement de code pour certaines stations ESU ?

changed the description

Cas de figure 1 : station ANCENIS ILE DELAGE

• Ancien code : code SISE-EAUX "historique" 044000001
• Nouveau code : code station Hub'eau BS000001
• Présente dans les tables suivantes :
  • captages.n_captage_p_r52 : ancien code
  • nitrates.nitrate_prelevement_v0_15 : ancien code pour 2007-2023
  • qualite_cours_d_eau.hubeau_qualite_rivieres_station_pc (nouveau code)
  • stations.station_esu (ancien code)

Cas de figure 2 : station MONPLAISIR (LA LOIRE)

• Ancien code : code SISE-EAUX "historique" 049000795
• Nouveau code : code station Hub'eau BX000795
• Présente dans les tables suivantes :
  • captages.n_captage_p_r52 : ancien code
  • nitrates.nitrate_prelevement_v0_15 (doublons à vérifier pour 2023) :
    • ancien code : 2007-2023
    • nouveau code : 2023
  • qualite_cours_d_eau.hubeau_qualite_rivieres_station_pc (nouveau code)
  • zz_archives.station_esu_202410071646 (ancien et nouveau codes)

Absente de la table suivante :

• stations.station_esu

Requêtes SQL utilisées

SELECT *
FROM captages.n_captage_p_r52
WHERE nom_captage LIKE '%MONPLAISIR%';

SELECT *
FROM nitrates.nitrate_prelevement_v0_15
WHERE code_station = '049000795';

SELECT *
FROM qualite_cours_d_eau.hubeau_qualite_rivieres_station_pc
WHERE libelle_station LIKE '%MONPLAISIR%';

SELECT *
FROM stations.station_esu
WHERE libelle_station LIKE '%MONPLAISIR%';

added En cours label

@Franck.Gaspard @Philippe.BIRCHEN J'ai identifié l'origine du cas de figure n°2 : la sélection des stations à l'intérieur des SAGE de la région aboutissait à exclure les stations hors SAGE. Le problème est corrigé dans le pakage data.captages. Je vais publier la version corrigée en base.

On passe de 3641 stations à 3675.

Restera à corriger le problème des code_station différents.

@Franck.Gaspard @Philippe.BIRCHEN

La table stations.station_esu a été actualisée en base : 3675 enregistrements

Concernant la problématique des codes SISE-EAUX remplacés par des codes commençant par BS, BX ou CB, il est possible soit de :

• créer une table de passage et remplacer les codes BS, BX, CB par les codes SISE-EAUX dans la table nitrates.nitrate_prelevement_v0_15
• supprimer les prélèvements avec des code_station commençant par BS, BX ou CB qui semblent être des doublons 2023 (à confirmer avec quelques tests)

Qu'en pensez-vous ?

A partir des tables nitrates_P90 de la v0_15 et v0_14 qui servent à faire les cartes de Seqeau pour les ESU, j'ai comparé pour toutes les années sans regarder si la station était ESU ou ESO :

• les code_station qu'on trouve dans la v0_15 mais qu'on ne trouve pas dans la v0_14

• les code_station qu'on trouvait dans la v0_14 mais qu'on ne trouve plus dans la v0_15

nitrates_comparaison_station_v0_14_et_v0_15.pdf

Pour les code_station qu'on trouve dans la v0_15 mais qu'on ne trouve pas dans la v0_14 : on passe de 3091 stations à 3675 avec la MAJ du référentiel station_esu donc ça ne me surprend pas.

Pour les code_station qu'on trouvait dans la v0_14 mais qu'on ne trouve plus dans la v0_15 : je vais vérifier les stations listées.

Tests divers concernant les stations avec un nouveau code

Les préfixes semblent correspondre au même département :

• BS : 044
• BX : 049
• CB : 053

Les nouveaux codes ne concernent que le millésime 2023.

Comparaison des prélèvements entre stations avec anciens et nouveaux codes

Si on compare les codes anciens et nouveaux pour les 24 stations identifiées dans la table nitrates.nitrate_prelevement_v0_16 (équivalent version 0.15 avec l'ajout du champs nature_eau :

• pour 22 stations le nombre de prélèvement est identique avec les deux codes (exemple : BS000001 et 044000001)
• dans ce cas :
  • avec le code SISE-EAUX : nature_eau = ESU
  • avec le nouveau code : nature_eau = ESU -> vérifier la procédure d'ajout de nature_eau
• pour les stations suivantes :
  • CB000091 : 5 prélèvements ESO / 53000091 : 33 prélèvements ESU
  • CB000164 : 12 prélèvements ESO / 053000164 : 0 prélèvements
• vérifier de façon approfondie le lien avec la table nitrates.nitrate_analyse_v0_15 : il semble y avoir un problème de date entre prélèvement et analyse pour les stations avec un nouveau code

Exemples de requêtes utilisées :

SELECT * FROM nitrates.nitrate_prelevement_v0_16
WHERE code_station = 'CB000904'
ORDER BY date_prelevement, heure_prelevement;

SELECT * FROM nitrates.nitrate_prelevement_v0_16
WHERE code_station = '053000904'
AND date_prelevement::text LIKE '2023%'
ORDER BY date_prelevement, heure_prelevement;

Vérification de la présence des stations avec un nouveau code dans les référentiels

Présence :

• dans stations.station_eso : non
• dans qualite_nappes_eau_souterraine.hubeau_qualite_nappes_stations : non ->alors nature_eau = ESO dans la table nitrates.nitrate_prelevement_v0_16 ce qui indique un problème à ce niveau
• dans qualite_cours_d_eau.hubeau_qualite_rivieres_station_pc : oui

Exemples de requêtes utilisées :

SELECT * FROM qualite_cours_d_eau.hubeau_qualite_rivieres_station_pc
WHERE code_station IN ('BS000001', 'BS000087', 'BS000089', 'BS000090', 'BS000449', 'BX000024', 'BX000260', 'BX000266', 'BX000402', 'BX000461', 'BX000519', 'BX000776', 'BX000795', 'BX000796', 'BX001273', 'CB000038', 'CB000091', 'CB000106', 'CB000164', 'CB000389', 'CB000390', 'CB000488', 'CB000496', 'CB000904');

Vérification de la cohérence prélèvement / analyse entre ancien et nouveau code

Tests effectués :

SELECT * FROM nitrates.nitrate_analyse_v0_15
WHERE code_prelevement = '29275';

-> avec un prélèvement code SISE-EAUX, les dates correspondent

SELECT * FROM nitrates.nitrate_analyse_v0_15
WHERE code_prelevement = '66982'; 

-> avec un prélèvement nouveau code, les dates ne correspondent pas (2023/2016)

Il y a donc potentiellement un problème d'intégration de ces données.

À faire

• générer une version 0.17 avec la chaîne de traitement Pentaho pour comparaison
• revoir l'intégration des données d'analyse et la jointure avec les prélèvements
• potentiellement, traiter le ticket #6 (moved) et conserver une table unique entre prélèvement et analyse pour limiter les risques d'erreur

Publication en base d'une nouvelle version 0.18 corrigeant :

• le bug sur la nature_eau pour les prélèvements ESU Naïades (dorénavant la valeur ESU est bien attribuée)
• potentiellement le problème de jointure entre prélèvements et analyses observé pour les stations ESU avec un nouveau code

Le nombre d'analyses diffère de la version précédente : à investiguer.

Tables chargées en base :

• nitrates.nitrate_prelevement_v0_18
• nitrates.nitrate_analyse_v0_18

Comparatifs de la jointure entre prélèvements et analyses entre les différentes versions

Version de référence : 0.17 (Pentaho)

Tables :

• nitrates.nitrate_analyse_v0_17 : 82275
• nitrates.nitrate_prelevement_v0_17 : 82343

Requête SQL :

SELECT nitrate_analyse_v0_17.*
FROM nitrates.nitrate_analyse_v0_17 
WHERE nitrate_analyse_v0_17.code_prelevement IN (SELECT code_prelevement FROM nitrates.nitrate_prelevement_v0_17);

Bilan : 82693 enregistrements dans la jointure

Version 0.15 (R)

Tables :

• nitrates.nitrate_analyse_v0_15 : 82375
• nitrates.nitrate_prelevement_v0_15 : 82343

Requête SQL :

SELECT nitrate_analyse_v0_15.*
FROM nitrates.nitrate_analyse_v0_15 
WHERE nitrate_analyse_v0_15.code_prelevement IN (SELECT code_prelevement FROM nitrates.nitrate_prelevement_v0_15);

Bilan : 82375 enregistrements dans la jointure

Version 0.19 (R)

Tables :

• nitrates.nitrate_analyse_v0_19 : 82407
• nitrates.nitrate_prelevement_v0_19 : 82375

Requête SQL :

SELECT nitrate_analyse_v0_19.*
FROM nitrates.nitrate_analyse_v0_19 
WHERE nitrate_analyse_v0_19.code_prelevement IN (SELECT code_prelevement FROM nitrates.nitrate_prelevement_v0_19);

Bilan : 65837 enregistrements dans la jointure

Bilan global

• La version 0.17 renvoie le meilleur résultat en terme de jointure.
• Un nouveau bug est apparu entre les version 0.15 et 0.19 qui fait perdre environ un tiers des résultats de la jointure.

Préconisations

• Utiliser la version 0.17 pour la mise à jour des données en production
• Investiguer le problème lié à la version 0.19
• À terme basculer une table unique nitrates.nitrate_analyse_prelevement, la séparation initialement décidée n'ayant plus lieu d'être

closed

moved to dreal-pdl/csd/eau-milieux-aquatiques/data.captages#29 (closed)