Versement des données Flora Data au SINP

ATTENTION (2020-04-01) - la situation peut avoir changé depuis que ces informations ont été écrites

Procédure

Envoyer par mail le fichier au format standard, qui peut contenir les différents jeux de données. Dans ce cas, les jeux de données sont identifiés par un champ qui les distingue, dans notre cas ce sera le programme (programme="sauvages" => jeu de données "Sauvages de ma rue"). En pratique, il faut tenir compte du fait que les programmes ont souvent des champs étendus, qui doivent donc être exportés à part.

Contact :
Nouveau contact donné en 2024 : Noëlie Maurel (noelie.maurel@mnhn.fr).

L'intégration des données peut s'accompagner de la publication d'un article parmi les actualités de l'INPN + idem dans le NL de Tela.

Données à exporter

Exporter l'ensemble des données :

standard (grade 3 ou 4)
dans BDTFX
en excluant l'année en cours

Infos particulières à transmettre :

le nom/prénom de l'auteur ou son pseudo le cas échéant, mais ni son email ni son user_id
lorsque le floutage a été demandé par l'utilisateur lui-même lors de la saisie, on transmet les données floutées. Lors que le floutage est appliqué a posteriori par Tela (données sensibles par exemple), on transmet les données précises
le cd_nom
les champs étendus des programmes le cas échéant

La correspondance avec les noms de champs du standard SINP n'a pas été fait, mais un fichier d'explication des noms de colonnes (plusieurs onglets) a été joint à l'export, et peut être complété si besoin lors des prochains versements.

Gestion des métadonnées

Pour l'instant c'est le compte INPN perso de Laura / Thomas / qui permet d'accéder à l'application métadonnées. Pour ouvrir un compte pour Tela, pour consulter, modifier ces fiches ou en créer d'autres, demander au contact (il faut avoir un compte INPN et lui transmettre le pseudo ou l'adresse mail liée).

Fiches "Cadre d'acquisition"

A chaque jeu de données (donc à chaque programme) correspond une fiche de métadonnées. Les fiches suivantes ont été créées :

Données d'observation flore du réseau Tela Botanica : c'est le "métacadre" qui englobe tous les autres. Aucun jeu de données ne peut lui être attribué
Données d'observation flore du réseau Tela Botanica (hors programmes) : toutes les données de Tela une fois exclues les données des programmes ci-dessous meta SINP
Données d'observation flore issues du programme Sauvages de ma rue (Tela Botanica) meta SINP
Données d'observation flore issues du programme Observatoire des Messicoles (Tela Botanica) meta SINP
Inventaire participatif des Arbres Têtards de l'Isère (Gentiana, Tela Botanica) meta SINP
Inventaire participatif des Arbres Remarquables de l'Isère (FRAPNA, Tela Botanica) meta SINP
Données d'observation flore issues du programme Missions Flore (Tela Botanica) meta SINP
Données d'observation saisies pour le programme Lichens Go (Tela Botanica) meta SINP
Données d'observation flore issues du programme sTREEts (Tela Botanica) meta SINP
Données d'observation saisies via le projet Pl@ntNet (Tela Botanica) : données dont la source de saisie est "PlantNet" meta SINP

Fiches "Jeu de données"

Au sein de chaque cadre d'acquisition, on peut ajouter / gérer les jeux de données associés. Une série de métadonnées doit être saisie pour chaque jeu de données, lors de chaque versement.

Historique des versements

Versement 2024

En 2024, seules les données de 2022 et 2023 ont été reversées pour tous les programmes à l'exception de celles de Streets et LichensGo qui n'avaient pas été encore reversées. Pour ceux-ci, toutes les données saisies depuis la création de ses programmes ont été prises en compte.

Versement 2021

/!\ ATTENTION /!\ : voir paragraphe "Données non exportées" ci-dessous.

Versement 01/04/2020

Objet

Toutes les données depuis le début de Tela jusqu'en 2019 (inclus).

Données non exportées

Il a été décidé avec Christel de laisser les données de PlantNetdans cet export. Or, l'export a été fait alors que la synchro PlantNetétait coupée depuis le 19/04/2019]].

Lors du prochain versement, il sera donc nécessaire d'exporter aussi les données dont la source est "PlantNet" créées à partir de cette date.

D'autre part, les obs des programmes tb_streets et tb_lichensgo ont été exclus de l'export (trop peu de données / pas le référentiel adapté), il faudra donc inclure les données plus anciennes que 2020 lors du prochain export.

A savoir

Le champ "programme" des obs dont la source est "PlantNet" a été mise manuellement à la valeur "PlantNet" - sauf pour 12 obs dont la valeur était déjà "sauvages" et dont le champ "source" est visiblement erroné.

Requêtes

Tout sauf les programmes

SELECT guid, id_observation, pseudo_utilisateur, nom_sel, nom_sel_nn, nom_ret, nom_ret_nn, cd_nom, nom_referentiel, certitude, pays, ce_zone_geo, zone_geo, dept, lieudit, station, milieu, latitude, longitude, altitude, geodatum, localisation_precision, localisation_floutage, date_observation, date_creation, date_modification, date_transmission, programme, commentaire, abondance, phenologie, spontaneite, observateur, observateur_structure, type_donnees, biblio, determinateur, `source`, images FROM `cel_export_total`
WHERE donnees_standard = 1 AND nom_referentiel = "bdtfx" AND YEAR(date_creation) < 2022 and pays = "fr"
AND `source` NOT like "plantnet" 
AND (programme NOT IN ("arbres-remarquables", "arbres-tetards", "messicoles", "missions-flore", "sauvages", "tb_streets", "tb_lichensgo") OR programme IS NULL or programme in ("opcf", "moocbotanique2020", "anjoubota", "hiver2021", "printemps", "gel2021"))

Seulement PlantNet

SELECT guid, id_observation, pseudo_utilisateur, nom_sel, nom_sel_nn, nom_ret, nom_ret_nn, cd_nom, nom_referentiel, certitude, pays, ce_zone_geo, zone_geo, dept, lieudit, station, milieu, latitude, longitude, altitude, geodatum, localisation_precision, localisation_floutage, date_observation, date_creation, date_modification, date_transmission, programme, commentaire, abondance, phenologie, spontaneite, observateur, observateur_structure, type_donnees, biblio, determinateur, `source`, images FROM `cel_export_total` 
WHERE donnees_standard = 1 AND nom_referentiel = "bdtfx" AND YEAR(date_creation) < 2022 
AND (programme NOT IN ("arbres-remarquables", "arbres-tetards", "messicoles", "missions-flore", "sauvages", "tb_streets", "tb_lichensgo") OR programme IS NULL) 
AND `source` like "plantnet"

Seulement un programme

SELECT guid, id_observation, pseudo_utilisateur, nom_sel, nom_sel_nn, nom_ret, nom_ret_nn, cd_nom, nom_referentiel, certitude, pays, ce_zone_geo, zone_geo, dept, lieudit, station, milieu, latitude, longitude, altitude, geodatum, localisation_precision, localisation_floutage, date_observation, date_creation, date_modification, date_transmission, programme, commentaire, abondance, phenologie, spontaneite, observateur, observateur_structure, type_donnees, biblio, determinateur, `source`, images FROM `cel_export_total`
WHERE donnees_standard = 1 AND nom_referentiel = "bdtfx" AND YEAR(date_creation) < 2022
AND programme = "arbres-remarquables"
AND `source` NOT like "plantnet"

Export des champs étendus (par programme)
Sauvages

SELECT occurrence_id, project, GROUP_CONCAT(concat('"',field_id, " : ", value,'"')) as '"coteRue","adresse","latitudeDebutRue","latitudeFinRue","longitudeDebutRue","longitudeFinRue"' 
FROM extended_field_occurrence efo join extended_field ef on extended_field_id = ef.id 
WHERE project in ("sauvages") 
group by occurrence_id

Arbres remarquables

SELECT occurrence_id, project, GROUP_CONCAT(concat('"',field_id, " : ", value,'"')) as '"arbreRemarquableClasseCirconference","arbreRemarquableCirconference","arbreRemarquableHauteur","arbreRemarquableHauteurPrecision","arbreRemarquableAge","arbreRemarquableSante","arbreRemarquablePresenceCavite","arbreRemarquableSituation","arbreRemarquableEsthetique","arbreRemarquableHistorique","arbreRemarquableRemarquable","arbreRemarquableRemarquabilite"' 
FROM extended_field_occurrence efo join extended_field ef on extended_field_id = ef.id 
WHERE project in ("arbres-remarquables") 
group by occurrence_id

Arbres têtards

SELECT occurrence_id, project, GROUP_CONCAT(concat('"',field_id, " : ", value,'"')) as '"arbreTetardFormation","arbreTetardPresenceCavite","arbreTetardClasseCirconferenceA1m","arbreTetardHauteurTete","arbreTetardPresenceSp","arbreTetardTailleType","arbreTetardEntretienCoupe","arbreTetardEtatSanitaire"' 
FROM extended_field_occurrence efo join extended_field ef on extended_field_id = ef.id 
WHERE project in ("arbres-tetards") 
group by occurrence_id

Messicoles

SELECT occurrence_id, project, GROUP_CONCAT(concat('"',field_id, " : ", value,'"')) as '"abondance","typeDeCulture","typeDeCultureDetails","zoneDuChamp"' 
FROM extended_field_occurrence efo join extended_field ef on extended_field_id = ef.id
WHERE project in ("messicoles") 
group by occurrence_id

Missions flore

SELECT occurrence_id, project, GROUP_CONCAT(concat('"',field_id, " : ", value,'"')) as '"stationExposition","stationPente","abondance","individusNombre","phenologie","releveType","determinationSource"' 
FROM extended_field_occurrence efo join extended_field ef on extended_field_id = ef.id 
WHERE project in ("missions-flore") 
group by occurrence_id

Post-traitement

Avec cette méthode, l'ajout des champs étendus au fichier d'export nécessite un post-traitement :

Homogénéiser les colonnes : les colonnes ne seront pas exportées de manière homogène suivant les infos saisies ou non pour chaque données. Il faut donc homogénéiser les colonnes pour que chacune ne corresponde qu'à un champ
Nettoyer les valeurs : enlever les intitulé des champs accompagnant les valeurs (nécessaires à l'étape précédente) avec un Rechercher/remplacer
Rattacher les champs étendus aux obs correspondantes : utiliser la fonction RECHERCHEV qui permet de joindre les deux tableurs sur le champ "occurrence_id" (si besoin voir ce tutoriel). Ne pas oublier de virer les colonnes et les lignes inutiles (occurrence_id en doublon, champs étendus n'ayant pas de correspondance dans le fichier d'obs).

NB : cette méthode est MEGA reloue et longue, il faut trouver une moyen plus adapté d'exporter les champs étendus, afin d'éviter au moins les 2 premières étapes du post-traitement. A regarder : http://www.informatix.fr/tutoriels/base-de-donnees/mysql-une-table-pivot-dynamique-178

Il est inutle de faire tout ça : il suffit d'utiliser les requêtes ci-dessous pour avoir toutes les données en une seule fois. Vérifiez que les paramètres soient corrects : dates, référentiel.

- missions-flore

SELECT
    guid,
    id_observation,
    pseudo_utilisateur,
    nom_sel,
    nom_sel_nn,
    nom_ret,
    nom_ret_nn,
    cd_nom,
    nom_referentiel,
    certitude,
    pays,
    ce_zone_geo,
    zone_geo,
    dept,
    lieudit,
    station,
    milieu,
    latitude,
    longitude,
    altitude,
    geodatum,
    localisation_precision,
    localisation_floutage,
    date_observation,
    date_creation,
    date_modification,
    date_transmission,
    programme,
    commentaire,
    abondance,
    phenologie,
    spontaneite,
    observateur,
    observateur_structure,
    type_donnees,
    biblio,
    determinateur,
    `source`,
    images,
    GROUP_CONCAT(
        CONCAT('"', field_id, " : ",
    VALUE
        , '"')
    ) AS '"stationExposition","stationPente","abondance","individusNombre","phenologie","releveType","determinationSource"'
FROM
    cel_export_total
LEFT JOIN extended_field_occurrence efo ON
    id_observation = occurrence_id
LEFT JOIN extended_field ef ON
    efo.extended_field_id = ef.id
WHERE
    programme = "missions-flore" AND donnees_standard = 1 AND nom_referentiel = "bdtfx" AND YEAR(date_creation) IN(2022, 2023)
GROUP BY
    id_observation;

- arbres tétards :

SELECT
    guid,
    id_observation,
    pseudo_utilisateur,
    nom_sel,
    nom_sel_nn,
    nom_ret,
    nom_ret_nn,
    cd_nom,
    nom_referentiel,
    certitude,
    pays,
    ce_zone_geo,
    zone_geo,
    dept,
    lieudit,
    station,
    milieu,
    latitude,
    longitude,
    altitude,
    geodatum,
    localisation_precision,
    localisation_floutage,
    date_observation,
    date_creation,
    date_modification,
    date_transmission,
    programme,
    commentaire,
    abondance,
    phenologie,
    spontaneite,
    observateur,
    observateur_structure,
    type_donnees,
    biblio,
    determinateur,
    `source`,
    images,
    GROUP_CONCAT( CONCAT('"', field_id, " : ", VALUE , '"') ) AS '"arbreTetardFormation","arbreTetardPresenceCavite","arbreTetardClasseCirconferenceA1m","arbreTetardHauteurTete","arbreTetardPresenceSp","arbreTetardTailleType","arbreTetardEntretienCoupe","arbreTetardEtatSanitaire"'
FROM
    cel_export_total
LEFT JOIN extended_field_occurrence efo ON
    id_observation = occurrence_id
LEFT JOIN extended_field ef ON
    efo.extended_field_id = ef.id
WHERE
    programme = "arbres-tetards" AND donnees_standard = 1 AND nom_referentiel = "bdtfx" AND YEAR(date_creation) IN(2022, 2023)
GROUP BY
    id_observation;

- arbres remarquables :

SELECT
    guid,
    id_observation,
    pseudo_utilisateur,
    nom_sel,
    nom_sel_nn,
    nom_ret,
    nom_ret_nn,
    cd_nom,
    nom_referentiel,
    certitude,
    pays,
    ce_zone_geo,
    zone_geo,
    dept,
    lieudit,
    station,
    milieu,
    latitude,
    longitude,
    altitude,
    geodatum,
    localisation_precision,
    localisation_floutage,
    date_observation,
    date_creation,
    date_modification,
    date_transmission,
    programme,
    commentaire,
    abondance,
    phenologie,
    spontaneite,
    observateur,
    observateur_structure,
    type_donnees,
    biblio,
    determinateur,
    `source`,
    images,
    
GROUP_CONCAT( CONCAT('"', field_id, " : ", VALUE , '"') ) AS '"arbreRemarquableClasseCirconference","arbreRemarquableCirconference","arbreRemarquableHauteur","arbreRemarquableHauteurPrecision","arbreRemarquableAge","arbreRemarquableSante","arbreRemarquablePresenceCavite","arbreRemarquableSituation","arbreRemarquableEsthetique","arbreRemarquableHistorique","arbreRemarquableRemarquable","arbreRemarquableRemarquabilite"'
FROM
    cel_export_total
LEFT JOIN extended_field_occurrence efo ON
    id_observation = occurrence_id
LEFT JOIN extended_field ef ON
    efo.extended_field_id = ef.id
WHERE
    programme = "arbres-remarquables" AND donnees_standard = 1 AND nom_referentiel = "bdtfx" AND YEAR(date_creation) IN(2022, 2023)
GROUP BY
    id_observation;

- sauvages de ma rue :

SELECT
    guid,
    id_observation,
    pseudo_utilisateur,
    nom_sel,
    nom_sel_nn,
    nom_ret,
    nom_ret_nn,
    cd_nom,
    nom_referentiel,
    certitude,
    pays,
    ce_zone_geo,
    zone_geo,
    dept,
    lieudit,
    station,
    milieu,
    latitude,
    longitude,
    altitude,
    geodatum,
    localisation_precision,
    localisation_floutage,
    date_observation,
    date_creation,
    date_modification,
    date_transmission,
    programme,
    commentaire,
    abondance,
    phenologie,
    spontaneite,
    observateur,
    observateur_structure,
    type_donnees,
    biblio,
    determinateur,
    `source`,
    images,
    GROUP_CONCAT( CONCAT('"', field_id, " : ", VALUE , '"') ) AS '"coteRue","adresse","latitudeDebutRue","latitudeFinRue","longitudeDebutRue","longitudeFinRue"'
FROM
    cel_export_total
LEFT JOIN extended_field_occurrence efo ON
    id_observation = occurrence_id
LEFT JOIN extended_field ef ON
    efo.extended_field_id = ef.id
WHERE
    programme = "sauvages" AND donnees_standard = 1 AND nom_referentiel = "bdtfx" AND YEAR(date_creation) IN(2022, 2023)
GROUP BY
    id_observation;

- messicoles :

SELECT
    guid,
    id_observation,
    pseudo_utilisateur,
    nom_sel,
    nom_sel_nn,
    nom_ret,
    nom_ret_nn,
    cd_nom,
    nom_referentiel,
    certitude,
    pays,
    ce_zone_geo,
    zone_geo,
    dept,
    lieudit,
    station,
    milieu,
    latitude,
    longitude,
    altitude,
    geodatum,
    localisation_precision,
    localisation_floutage,
    date_observation,
    date_creation,
    date_modification,
    date_transmission,
    programme,
    commentaire,
    abondance,
    phenologie,
    spontaneite,
    observateur,
    observateur_structure,
    type_donnees,
    biblio,
    determinateur,
    `source`,
    images,
    GROUP_CONCAT(
        CONCAT('"', field_id, " : ",
    VALUE
        , '"')
    ) AS '"abondance","typeDeCulture","typeDeCultureDetails","zoneDuChamp"'
FROM
    cel_export_total
LEFT JOIN extended_field_occurrence efo ON
    id_observation = occurrence_id
LEFT JOIN extended_field ef ON
    efo.extended_field_id = ef.id
WHERE
    programme = "messicoles" AND donnees_standard = 1 AND nom_referentiel = "bdtfx" AND YEAR(date_creation) IN(2022, 2023)
GROUP BY
    id_observation;

Thomas a développé un programme pour créer automatiquement les fichiers aux normes de l'INPN. Il est disponible sur ce dépôt git : https://github.com/telabotanica/create-export-files

Déjà membre

Nouveau membre