Validation automatique des données

[camillemonchicourt]

Un des enjeux évoqués du module Validation est le fait de pouvoir valider automatiquement les données banales. Ou du moins de pouvoir mettre en avant les données exceptionnelles.
Pour faire cela, à voir comment mettre en place des règles dans la BDD, générique et adaptables à chaque contexte.

Un exemple : http://si.cenlr.org/validation_automatique_de_donnee

[gildeluermoz]

Une validation totalement automatique ça se discute.

Je pense par contre que le sujet est un bon candidat à l'intelligence artificielle. Et plus il y aura de donnée plus le réseau de neurones sera pertinent. Ce qui milite pour une API basée sur l'ensemble des données du SINP auxquelles il serait pertinent d'ajouter tout ce qui peut provenir de l'expertise de l'UMS Patrinat.
Du coup, on peut avoir des règles locales mais si on peut consulter un service qui nous retourne un niveau (en %) ou une note (de 0 à 20 ou à 100), on peut utiliser cette réponse pour lever des warnings dès la saisie. A l'utilisateur d'en tenir compte ou pas. On peut aussi demander le poids de chaque paramètre dans la note (dateobs, loc, altitude, observateur, etc...).
Ce service pourrait être utilisé à la saisie ou par les validateurs dans le module de validation.

A ma connaissance, les librairies python sur le sujet permettent de coder ça en qq lignes. Mais je me trompe peut-être.

[camillemonchicourt]

Clairement, il ne s'agit pas de valider les données automatiquement.
Mais plutôt de mettre en avant les données exceptionnelles (jamais vu dans la commune, jamais vu à telle altitude, jamais vu à cette période...) pour orienter la validation.
Faire un truc basé sur le SINP serait idéal en effet, mais on pourrait commencé par quelque chose plus simple, basé sur les données dans la Synthèse.

Et en effet, des alertes dès la saisie, avaient été évoqués/demandés par certains.
Les couleurs et unités géographiques de la V1 étaient d'ailleurs un élément pouvant être utilisé dans ce sens.

[orovellotti]

Est ce que quelqu’un dispose d’un gros jeux de données avce des données annotées comme normale et d’autre comme anormale ?

On doit pouvoir faire un poc si c’est le cas ?

Merci

[camillemonchicourt]

On peut déjà commencer avec des choses simples en se basant sur les données du PNE (1 million de données dans la Synthèse sur le territoire) en identifiant :

• Si le taxon n'a jamais été vu dans la commune
• Si il n'a jamais été vu à cette altitude
• Si il n'a jamais été vu à cette période

Et si une observation rentre dans un des ces 3 cas, alors on attire l'attention sur ces données "exceptionnelles" pour les valider en priorité.

[gildeluermoz]

Olivier
J'ai discuté avec Olivier Gargomini du SPN, eux disposent d'un jeu de données avec validée non-validée.
J'ai aussi discuter de ça avec Olivier Courtin il dit que l'on peut utiliser le réseau de neurone sans les négatifs et uniquement avec les positifs mais évidemment c'est plus pertinent si on a les deux.

[orovellotti]

Si on veut juste "taxon n'a jamais été vu dans la commune" ont n'a pas besoin de réseau de neurone, une simple requette SQL peut faire le job.

Ca reste hyper interessant d'entainer un reseau sur les "validees / non validees" scientifiquement mais en terme de finesse du diagnostic.

[gildeluermoz]

Si je t'exporte les champs cd_nom et Insee sur les 1 million de données que l'on a dans notre GeoNature est-ce que ça le fait ?

[orovellotti]

il faudrait avoir toute l'observation (qui, quand, ou, quoi) et l'annotation valide ou non valide.

[camillemonchicourt]

Oui je baserai ça directement sur les données de la Synthèse où on a les obs avec date, localisation, insee, altitude et meme Validation à terme.

Je ferai deja 3 vues matérialisées basées sur la Synthese calculant pour chaque taxon l'altitude min et max, les communes de présence et les dates min et max.

Chacun pourrait adapter ces VM pour ne prendre que les donnees validées par exemple.

[gildeluermoz]

Le "qui" n'apporte pas grand chose il me semble pour le moment et c'est rgpd sensible ;). Je ne le mettrais pas.

Voici une requête vite fait qu'on peut mettre en vue matérialisée si besoin. Elle compile toutes les infos dans une seule vue.
Je pense que l'info INSEE n'est pas vraiment pertinente. Les bestioles ne connaissent pas les frontières communales. Par contre si on prend la bbox de l'ensemble des obs du taxon, on est bcp plus pertinent.
Idem, comme c'est un calcul machine, pour travailler sur les dates, j'ai choisi le jour de l'année (de 1 à 366).
Je calcule aussi le nombre d'observations pour pouvoir fixer un seuil de pertinence.
Attention toutes les données avec date imprécises pourrissent le résultat, idem pour les altitudes à 0, idem pour les loc imprécises. Il faudra affiner la requête pour exclure toutes les données imprécises sur le où et le quand.

GeoNature2

WITH loc AS (SELECT DISTINCT cd_nom, count(*) AS nbobs,
min(st_x(the_geom_point)::int) AS xmin,
max(st_x(the_geom_point)::int) AS xmax, 
min(st_y(the_geom_point)::int) AS ymin,
max(st_y(the_geom_point)::int) AS ymax
FROM gn_synthese.synthese
GROUP BY cd_nom),
dat AS (SELECT DISTINCT cd_nom, 
min(TO_CHAR(date_min, 'DDD')::int) AS daymin,
max(TO_CHAR(date_max, 'DDD')::int) AS daymax
FROM gn_synthese.synthese
GROUP BY cd_nom),
alt AS (SELECT DISTINCT cd_nom,
min(altitude_min) AS altitudemin,
max(altitude_max) AS altitudemax
FROM gn_synthese.synthese
GROUP BY cd_nom
)
SELECT loc.cd_nom, nbobs, xmin, xmax, ymin, ymax, daymin, daymax, altitudemin, altitudemax
FROM loc 
JOIN alt ON alt.cd_nom = loc.cd_nom
JOIN dat ON dat.cd_nom = loc.cd_nom
ORDER BY cd_nom;

GeoNature1 (test sur 1 million de données, les perfs sont assez bonnes : 5s dans pgadmin pour 6658 taxons).
Si on fait la requête pour 1 seul taxon, on est à moins de 100 ms (73ms). Et comme on a besoin de la réponse taxon par taxon, on peut envisager d'écrire une fonction avec le cd_nom en paramètre.
Si on analyse les résultats, ça demande un peu de nettoyage des données imprécises mais on est pas mal. J'ai regardé les dates pour les migrateurs ou pas migrateurs et les altitudes, il y a qq abbérations mais c'est pas mal. A creuser.

WITH loc AS (SELECT DISTINCT cd_nom, count(*) AS nbobs,
min(st_x(the_geom_point)::int) AS xmin,
max(st_x(the_geom_point)::int) AS xmax, 
min(st_y(the_geom_point)::int) AS ymin,
max(st_y(the_geom_point)::int) AS ymax
FROM synthese.syntheseff --WHERE cd_nom = 351
GROUP BY cd_nom),
dat AS (SELECT DISTINCT cd_nom, 
min(TO_CHAR(dateobs, 'DDD')::int) AS daymin,
max(TO_CHAR(dateobs, 'DDD')::int) AS daymax
FROM synthese.syntheseff --WHERE cd_nom = 351
GROUP BY cd_nom),
alt AS (SELECT DISTINCT cd_nom,
min(altitude_retenue) AS altitudemin,
max(altitude_retenue) AS altitudemax
FROM synthese.syntheseff --WHERE cd_nom = 351
WHERE altitude_retenue != 0 AND altitude_retenue IS NOT NULL
GROUP BY cd_nom
)
SELECT loc.cd_nom, t. nom_vern, t.nom_complet, nbobs,  daymin, daymax, altitudemin, altitudemax, xmin, xmax, ymin, ymax
FROM loc
LEFT JOIN alt ON alt.cd_nom = loc.cd_nom
LEFT JOIN dat ON dat.cd_nom = loc.cd_nom 
JOIN taxonomie.taxref t ON t.cd_nom = loc.cd_nom
ORDER BY cd_nom

[camillemonchicourt]

OK super.
Bien vu pour la bbox.
Peut être qu'il sera plus simple d'intersecter la geometrie de l'observation avec la bbox (https://postgis.net/docs/ST_Extent.html) mais c'est surement plus lourd à verifier. Mais mieux pour les obs lignes et polygones.
Et pour les taxons il faudra peut etre plutot verifier sur les cd_ref si une espece a été notée sous différents synonymes.

[gildeluermoz]

Oui bonne idée. J'affine ça et je teste sur les données de la V1.
Olivier, est-ce que tu veux que je vous envoie le tableau de résultat pour tous les taxons du PNE ?

[camillemonchicourt]

A terme il faudra intégrer ça dans le schéma Synthese et l'intégrer à l'API pour pouvoir l'utiliser dans différents modules.
Dans le module Validation, cela permettra de mettre en avant les données non validées qui ont un caractère exceptionnelles.
Dans des modules de saisie comme OccTax, cela permettrait de donner des alertes au moment de la saisie, du genre : "Ce taxon n'a jamais été noté à cette altitude".
Si ce service est basé sur une vue, cela permettra à chacun de l'adapter (en le basant que sur les données validées, sur toutes les données, uniquement sur les données de sa structures sans les données partenaires etc etc...)

[gildeluermoz]

Voici une function pour la V1 qui permet de travailler taxon par taxon avec de très bonnes perfs.

CREATE OR REPLACE FUNCTION synthese.fct_calculate_min_max_for_taxon(mycdnom integer)
  RETURNS TABLE(cd_ref int, nbobs bigint,  daymin int, daymax int, altitudemin int, altitudemax int, bbox4326 geometry) AS
$BODY$
  BEGIN
    --USAGE (getting all fields): SELECT * FROM synthese.fct_calculate_min_max_for_taxon(351);
    --USAGE (getting one or more field) : SELECT cd_ref, bbox4326 FROM synthese.fct_calculate_min_max_for_taxon(351)
    --See field names in TABLE declaration above
	RETURN QUERY WITH 
	loc AS (SELECT 
			taxonomie.find_cdref(cd_nom) AS cdref,
			count(*) AS nb_obs,
			ST_Transform(ST_SetSRID(box2d(st_extent(the_geom_local))::geometry,2154), 4326) AS bbox_4326
		FROM synthese.syntheseff
		WHERE cd_nom = mycdnom AND supprime = false
		GROUP BY cdref),
	dat AS (SELECT 
			taxonomie.find_cdref(cd_nom) AS cdref, 
			min(TO_CHAR(dateobs, 'DDD')::int) AS day_min,
			max(TO_CHAR(dateobs, 'DDD')::int) AS day_max
		FROM synthese.syntheseff
		WHERE cd_nom = mycdnom AND supprime = false
		GROUP BY cd_nom),
	alt AS (SELECT
			taxonomie.find_cdref(cd_nom) AS cdref,
			COALESCE(min(altitude_retenue), -1) AS altitude_min,
			COALESCE(max(altitude_retenue), -1) AS altitude_max
		FROM synthese.syntheseff
		WHERE cd_nom = mycdnom AND supprime = false
		AND altitude_retenue != 0 AND altitude_retenue IS NOT NULL
		GROUP BY cd_nom
	)
	SELECT loc.cdref, nb_obs,  day_min, day_max, altitude_min, altitude_max, bbox_4326
	FROM loc
	LEFT JOIN alt ON alt.cdref = loc.cdref
	LEFT JOIN dat ON dat.cdref = loc.cdref 
	JOIN taxonomie.taxref t ON t.cd_nom = loc.cdref
	ORDER BY cdref;

	END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

La fonction retourne une table avec une seule ligne (= un record) qu'on peut utiliser dans le FROM ou le JOIN d'une requête.

SELECT st_intersects(mm.bbox4326, le_geom_de_mon_obs) as in_know_bbox,
CASE WHEN l_atitude_de_lobs > altitudemax OR  l_atitude_de_lobs < altitudemin THEN false ELSE true END AS in_know_altitude,
CASE WHEN le_jour_de_lobs > daymax OR le_jour_de_lobs < daymin THEN false ELSE true END AS in_know_period
FROM synthese.fct_calculate_min_max_for_taxon(le_cd_nom_du_taxon) mm;

Si ça vous convient, je l'adapte pour la V2.

[gildeluermoz]

PnX-SI/GeoNature@4d89bfa

[gildeluermoz]

Si tu commites avant de sauvegarder les dernières modifs, forcement ...
PnX-SI/GeoNature@a127a55

[DonovanMaillard]

Salut salut!

Top de bosser sur les données de la base, niveau robustesse et contexte local on est pas mal! (surtout pour les phénologies, qui changent selon les territoires).

Par contre sur la bbox... ça fait beaucoup baisser la performance de se baser plutôt sur une distance? Dans le style 'attention, l'espèce n'a jamais été vue dans un rayon de 50km". Je prends le cas d'une espèce typique des vallées, à l'échelle nationale, où montpellier se retrouve dans la bbox entre alpes et pyrénées ;) Et sur cette distance, définir un seuil en fonction du groupe2 inpn?

Sur le fait de fonctionner sur une vue pour permettre à chacun de l'adapter, forcement c'est top.

Taxhub pourrait pas intervenir également pour préciser des variables à utiliser dans les calculs? La c'est pour chipoter mais j'aime bien, par exemple pour les papillons on met un seuil de 50km par defaut (groupe2), mais pour telle espèce très localisée je peux choisir de mettre un seuil à 3km? Ou pour un reptile puisqu'une tortue marine aura pas la capacité de déplacement de la couleuvre d'esculape.

C'est pas des souhaits c'est surtout pour envisager les meilleures possibilités ;)

[camillemonchicourt]

En effet la bbox n'est pas idéale mais c'est un début.
L'enveloppe des points serait déjà plus précis (https://postgis.net/docs/ST_ConcaveHull.html).

Sinon, comme tu évoques, on pourrait faire des buffers autour des points et les fusionner. Ça éviterait de prendre des vallées quand il s'agit d'une espèce d'altitude. Mais sur ce point, le contrôle d'altitude permet de contrôler cette aspect.

Merci pour les pistes intéressantes. Même si pour le moment, on ne va pas trop compliqué avec des distances par espèce gérées dans TaxHub.

[mathieubossaert]

Bonjour à tous,

belle discussion qui s'amorce. Pour ma part,
@gildeluermoz le "qui" permet d'intégrer comme critère l'expertise de l'observateur (a-t-il déjà une obs validée pour l'espèce)
Ici on utilise des listes de valeurs pour les critères (semaines, communes, plages d'altitude...) et la distance min à la donnée validée la plus proche comme proposé par @DonovanMaillard pour travailler de proche en proche. Ce dernier critère nécessite de comparer la nouvelle données aux obs validée du taxon avec st_dwithin.

Stocker et faire évoluer le résultat de ces critères pour chaque espèce dans une table est économe. On ne calcule les critères pour le taxon que si une nouvelle donnée est validée.

Ajouter à cela une possibilité de "tchat" entre validateurs et observateur serait top pour faciliter les discussions et dynamiser encourager les observateurs.

Super projet !

[DonovanMaillard]

Petit ajout sur les dates : ne pas se limiter je pense à un cas simple d'une fourchette "date min-date max", mais prévoir quelque chose basé sur la densité des observations à telle date (en restant simple). Pour couvrir les cas de plusieurs générations annuelles, ou les migrations avec un passage printanier et automnale etc.

Question encore plus "pénilble" ou complexe .. quid des stades? Saisir un juvénile à une période où il n'y a normalement que des adultes? Ca se complexifie vite et je me doute qu'on pourra pas tout gérer de manière automatique mais juste pour savoir ce que vous aviez en tête sur ce point ;)

[camillemonchicourt]

Ce qu'on en tête?
Commencer simple et enrichir ensuite si les choses sont faisables.

Merci pour ces pistes d'évolution.

[camillemonchicourt]

Pour les dates c bien multiannee car c pas date min et date max mais jour min et jour max.

Mais là notre problématique avant d'aller plus loin c'est comment gérer les données pourries sans date precise qui peuplent 80% des BDD naturalistes. Idem pour les autres champs comme les géométries imprécises etc...

[DonovanMaillard]

I serait pas souhaitable de mettre des seuils?

Une date ou on a que l'année, on la rentre pas dans le processus. On date ou on a 2 semaines (données de piégeage ou autre) on prend la période, ou la médiane?

Pour géographie, pareil, on a le département ou plus de 100km2 ou je ne sais quoi, la donnée n'est pas prise en compte. On a une commune, on prend toute la commune. Donc le tampon se fait autour de la commune... ?

[camillemonchicourt]

Oui on réfléchit comment ne pas prendre en compte les données imprécises qui pourraient perturber les calculs.

[DejasDejas]

Bonjour à tous,

Je trouve l'idée mise en avant par ce forum intéressante, à savoir de valider automatiquement une observation en vérifiant que celle-ci ne soit pas absurde ou que celle-ci suit une logique cohérente en comparaison avec d'autres observations de cette même espèce.

Afin de garder une plage de validité juste il faudrait avoir une marge la plus précise possible et faire attention au observation qui serait ni fausse ni juste, c'est à dire qu'une observation qui est enregistrée avec une mauvaise altitude par exemple (erreur de l'observateur et donc observation fausse) mais qui est validée comme étant juste (car l'altitude enregistrer est cohérente, elle peut être par exemple à la limite haute de la marge de validation) va tout de même contribuer à la mise à jour de la nouvelle marge de validité de cette espèce et donc si ce phénomène ce reproduit plusieurs fois la marge de validité risque de se décaler de plus en plus jusqu'à détecter de bonnes observations comme étant fausses.
Mais peut être que ce cas particulier est trop rare pour causer ce phénomène je ne sais pas.
Il pourrait être interessant de calculer la validité d'une observation avec un pourcentage de certitude qui pourra servir comme poids à cette observation au moment de la mise à jour de la nouvelle marge de validité, ainsi les observations moins probables influeront moins et vice versa.

Les réseaux de neurones (ou une regresion) ici pourraient être intéressants si vous souhaitez prendre en compte des relations plus complexes et plus nombreuse entre les différentes observations, mais pour ce début les requêtes SQL semblent une solutions efficaces et simple.

Concernant le problèmes des données imprécises, je ne comprend pas bien quel est le problème ?
Pour les observations à venir, le problème des données imprécises peut être résolue en obligeant l'observateur à respecter un format précis lorsqu'il rentre une observation.

[camillemonchicourt]

Merci pour ce retour.
Pour le moment, il ne s'agit pas de valider automatiquement des données mais plutôt de donner des infos sur les données existantes et d'identifier, mettre en avant des données exceptionnelles.

En effet, pour les données imprécises, les données à venir ne posent pas vraiment de problème, puisque la saisie est contrôlée et avec pas mal de choses calculées automatiquement pour éviter les erreurs.
La question se pose principalement sur les données historiques, ou sur les données bibliographiques peu précises qui seraient saisies.

On verra comment les mettre de côté dans les calculs. Et c'est aussi pour ça que l'on se base sur une vue. Cela laisse la liberté à chacun d'ajuster les données sur lesquels se base ces calculs, en fonction de son contexte et de son historique de données.

[DejasDejas]

Merci pour votre réponse, je m'interresse à cette discution car en dehors du faite que je trouve cela intéressant, je suis stagiaire en deep learning chez Natural Solutions.

Effectivement concernant les données historiques et/ou bibliographiques si l'information est manquante ou imcomplète cela est problématique, comme vous le dite vaut mieux les ignorer.

[camillemonchicourt]

OK super, si il y a matière à aller plus loin, ou faire des tests, on est partant !

[DonovanMaillard]

Pour le cas des données a la marge, il y a moyen de faire des choses relativement simples niveau stats. Ça doit pouvoir se traduire pour le calculer dans les bases, de manière à utiliser une gamme qui englobe 95% des données. Les 5% sur un jeu conséquent c'est des données un peu originales. La aussi chacun ajusté son seuil...

[camillemonchicourt]

Pour le moment le trigger tri_refresh_vm_min_max_for_taxons a été désactivé (PnX-SI/GeoNature@1cc72c1) car il avait une erreur de syntaxe et n'est pas encore utilisé.

[DonovanMaillard]

Bonjour, comme discuté avec Camille.

Je commence pour ma part à travailler sur la validation semi-automatique des données dans le cadre de la mise en place du pôle sinp invertébrés pour la région/dreal AURA.
En parallèle de la méthodo scientifique, je travaille donc à voir comment le mettre en oeuvre coté BDD, avec des projets un peu plus clairs me concernant.

Le processus sera exclusivement en SQL pour ma part et a pour but d'être générique et utilisable/personnalisable le plus facilement possible par d'autres. Le fonctionnement global :

• Pour chaque cd_ref, une fonction calcule des règles sur la base des données valides déjà présentes dans la base. Ces règles sont inscrites dans deux tables (voir mcd en dessous), interrogeables après par tout autre module.
• Cette fonction de calcule des règles est lancée par un cron, une fois par semaine/mois/an au bon vouloir de l'utilisateur

Pour faire simple :

• Pour chaque cd_ref, on sélectionne les données valides disponibles, on leur applique un buffer (paramétrable), et cela génère une géométrie (polygon/multipolygon) de "présence validée".

• Pour chaque zonage et espèce, on calcule le nombre d'observations par pas de temps dans une seconde table (pour le moment je pense semaine de l'année ou décade, ou alors des dates mais en éliminant les x dates les plus précoces/tardives) pour définir les périodes valides.

Dans son fonctionnement par défaut on donc peut imaginer que pour chaque espèce, la fonction calcule un unique zonage de type multipolygone, avec ses effectifs validés par pas de temps. Après discussion avec notre groupe de travail,on laisse tomber la notion de stade, qui n'est pas applicable à tous les taxons et qui éclate et complexifie trop les règles. On part donc du principe que si j'ai 23 données d'une espèce validée en janvier, quelque soit son stade, ca veut dire qu'on est en mesure de produire des données valides sur cette période de l'année. Ca évite aussi de gérer les stades non renseignés etc qui nous parviendront au pôle dans notre contexte agglomérateur.

Cependant le MCD suivant (et la fonction peut-être dans un second temps) permettront de faire des choses un peu plus poussées (sans partir trop loin), en calculant plusieurs géométries pour une espèce, ayant chacune leurs périodes valides. On peut par exemple intégrer un id_type_area en paramètre, de manière à calculer des règles différentes pour chaque aire de ce type (région, zone biogéographique, parc national ou autre...) ce qui laisse pas mal de souplesse à l'utilisateur pour adapter les règles à ses besoins

Coté BDD
On aurait donc une table pour configurer les paramètres : un seuil (minimum tant de données pour calculer des règles de validation auto), un buffer (ma zone valide comprend les obs + un tampons de x mètres), le nombre minimum de données par période pour la considérer fiable (si j'ai plus de 5 données d'une espècepour cette période, c'est ok), et un id_type_area pour calculer des règles différentes selon les contextes. Cette table alimente la fonction de calcul des règles. (voir s'il faut intégrer le groupe 2 inpn pour appliquer des seuils différents selon le groupe inpn dans un second temps...).

On aurait une table avec les cd_ref et leurs geom+altitudes min/max réelles jugés valides
Puis une table avec des périodes valides pour chaque géom définie précédemment.

Tout ça serait alors exploitable par le dashboard, ou par un module de saisie par exemple, pour alerter sur les données qui ne rentreraient pas dans ces cas connus et validés.

Enfin je prévois une table méta pour stocker dans mon cas les dates de mises à jour des règles de validation automatique, par la fonction.

(une seconde fonction permettrait biensur d'attribuer le statut 'probable - automatique' aux données qui rentrent dans les clous, soit en trigger au moment de l'insertion dans la synthèse, soit via une fonction déclenchée par un cron. Tout ce qui ne rentre pas dans les règles reste en attente de validation (peut être paramétrable?) en vue d'une validation manuelle )

(mcd à venir ;))

[mathieubossaert]

Bonjour à tous,

pour le calcul de l'aire de répartition, les fonctions ST_ClusterDBSCAN et ST_ClusterKMeans utilisées ci-dessous sont intéressantes car paramétrables et déjà existantes : http://si.cenlr.org/12-04-2018/postgis-listes-rouges-aire-d-occupation

[gildeluermoz]

Sans rentrer dans le détail technique des critères d'analyse et des calculs, je serais favorable à un dev aboutissant à une API publique prenant en entrée le cd_ref, la dateobs, le geom, l'altitude, etc... et renvoyant une note de fiabilité générale accompagnée d'info concernant le ou les critères analysés.
En effet, plus la base réalisant ces traitements aura une assise large, plus elle sera pertinente. A minima un SINP régional, idéalement l'INPN. En effet, sans cette assise large, chacun aura une bonne raison d'orienter ses calculs à sa façon et plus il sera difficile de faire quelque chose de générique.

A noter qu'Elsa Guilley travaille actuellement chez nous à débroussailler le sujet (touffu !)

[camillemonchicourt]

Une étude des solutions existantes de validation automatique dans plusieurs outils (INPN, CEN LR, SILENE, Tela Botanica, Picardie Nature, SINP La Réunion) est disponible page 21 du rapport de stage https://geonature.fr/documents/2019-09-rapport-stage-Elsa-Guilley-Dashboard-Validation.pdf
Des premiers éléments d'implémentation dans GeoNature sont disponibles page 44.

[ElsaGuilley]

Voici les scripts SQL, fortement inspirés du travail réalisé par Mathieu au CEN LR :

Création de la table des profils types :

CREATE TABLE gn_synthese.references_validation AS 
  SELECT cd_ref, 
         count(s.id_synthese) as nb_observations_total,
         array_agg(DISTINCT id_area) AS liste_communes, 
         array_agg(DISTINCT extract(week FROM COALESCE(date_min, date_max))) AS liste_semaines,
         array_agg(DISTINCT (altitude_min::integer/100) ORDER BY (altitude_min::integer/100) ASC) AS liste_altitudes,
         MIN(count_min) AS borne_inf_eff,
         MAX(count_max) AS borne_sup_eff,       
         array_agg(DISTINCT observers) AS liste_observateurs
    FROM gn_synthese.synthese s
      JOIN gn_synthese.cor_area_synthese cor ON s.id_synthese=cor.id_synthese
      JOIN taxonomie.taxref t ON s.cd_nom=t.cd_nom
  WHERE cor.id_area IN (SELECT id_area FROM ref_geo.l_areas WHERE id_type = ref_geo.get_id_area_type('COM'))
  GROUP BY cd_ref
ALTER TABLE gn_synthese.references_validation ADD CONSTRAINT reference_validation_pkey PRIMARY KEY(cd_ref)

Il manque la condition dans le WHERE qui sélectionne seulement les données validées.

Création fonction validation :

CREATE OR REPLACE FUNCTION gn_synthese.validation_auto()
  RETURNS trigger AS
$BODY$
BEGIN
UPDATE gn_synthese.synthese SET score_validation = gn_synthese.calcul_score(id_synthese) WHERE id_synthese = NEW.id_synthese; 
RETURN NULL;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION gn_synthese.validation_auto()
  OWNER TO dba;

Création fonction de calcul du score :

CREATE OR REPLACE FUNCTION gn_synthese.calcul_score(integer)
  RETURNS text AS
$BODY$	
DECLARE
   var_id_synthese alias FOR $1;
   var_score text;
BEGIN
  WITH 
  /* L'observation qui nous intéresse, et les colonnes utiles à son examen */
  observation AS (
    SELECT s.id_synthese, cd_ref, id_area, date_min, date_max,
           altitude_min, altitude_max, count_min, count_max, observers
      FROM gn_synthese.synthese s
        JOIN gn_synthese.cor_area_synthese cor ON s.id_synthese=cor.id_synthese
        JOIN taxonomie.taxref t ON s.cd_nom=t.cd_nom
    WHERE cor.id_area IN (SELECT id_area FROM ref_geo.l_areas WHERE id_type = ref_geo.get_id_area_type('COM')) AND s.id_synthese = var_id_synthese),
  /* Les valeurs de référence pour le taxon concerné */
  reference AS (
    SELECT cd_ref, nb_observations_total, liste_communes, liste_semaines,
           liste_altitudes, borne_inf_eff, borne_sup_eff, liste_observateurs
      FROM gn_synthese.references_validation
      JOIN observation USING(cd_ref)),
  /* La confrontation des valeurs saisies aux valeurs de référence */
  bilan AS (  
    SELECT id_synthese, 
           CASE WHEN nb_observations_total < X THEN 1 ELSE 0 END AS nb_observations, 
           CASE WHEN id_area = ANY(liste_communes) THEN 1 ELSE 0 END AS commune,
           CASE WHEN EXTRACT(week FROM COALESCE(date_min, date_max)) = ANY(liste_semaines) THEN 1 ELSE 0 END AS semaine,
           CASE WHEN (altitude_min/100)::integer = ANY(liste_altitudes) AND altitude_min IS NOT NULL THEN 1 ELSE 0 END AS altitude,            
           CASE WHEN count_min > borne_inf_eff AND count_max < borne_sup_eff THEN 1 ELSE 0 END AS effectif,
           CASE WHEN observers = ANY(liste_observateurs) THEN 1 ELSE 0 END AS observateur
      FROM observation JOIN reference USING(cd_ref))
  /* Mise en forme du résultat retourné */
  SELECT CONCAT('Nombre observations: ', nb_observations,
                ', Commune: ', commune,
                ', Semaine: ', semaine,
                ', Altitude: ', altitude,
                ', Effectif: ', effectif,
                ', Observateur: ', observateur) into var_score
    FROM bilan;
RETURN var_score;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE;

Il manque le trigger qui se déclenche à chaque insertion/modification de donnée et qui lance la fonction de validation. Il est présent dans mon mémoire.

[DonovanMaillard]

Merci @camillemonchicourt , Elsa.

Est-ce que c'est implémenté en l'état dans la prochaine release? Est-ce que c'est activable/désactivable, ou est-ce qu'il faut désactiver le trigger simplement, si on utilise une autre méthode?

[camillemonchicourt]

Non ce n'est pas du tout implémenté actuellement.
C'est un travail exploratoire, à compléter, tester, affiner.

[DonovanMaillard]

Ca roule.

Pour certains, GN a tout intérêt à avoir une validation automatique dont les règles sont calculées 'en live' au fil des saisies.

Dans notre cadre agglomérateur, ce n'est pas souhaitable que les règles de validation changent de facon "incontrolée". Il faut donc que les développements permettent un calcul des règles à un pas de temps configurable.

[DonovanMaillard]

Finalement pris dans un cadre plus large que la seule validation, les profils d'espèces ont été créés de manière générique, ils seront rafraichis par un cron avec une fréquence qui sera donc configurable par chacun.

Une fonction, basée sur le travail d'Elsa, permettra de faire une validation automatique à partir de ces profils.