search

PnX-SI / GeoNature

Application de saisie et de synthèse des observations faune et flore
GNU General Public License v3.0
104 stars 102 forks source link

Export synthese personnalisé #2955

Closed TheoLechemia closed 6 months ago

TheoLechemia commented 8 months ago

Actuellement les exports de la synthèse sont basés sur une vue v_synthese_for_web_app. Certains ont modifié cette vue, mais cela rend difficile les mises à jour du modèle pour les développeurs : on ne peut pas trop toucher à cette vue sans faire sauter des répercutions que certains ce serait surcouchés. Pour palier à ça on pourrait proposer un export de base pour tout le monde, qui suivrait l'évolution du standard, et des exports personnalisés basés sur un paramètre qui listerait des vues d'export personnalisées, générant un bouton d'export dans la modale d'export de la synthèse.

camillemonchicourt commented 8 months ago

Oui cela serait beaucoup mieux.

camillemonchicourt commented 6 months ago

Fait dans la 2.14.1 avec l'ajout d'un paramètre EXPORT_OBSERVATIONS_CUSTOM_VIEWS (https://github.com/PnX-SI/GeoNature/blob/2.14.1/config/default_config.toml.example#L331).

image

TheoLechemia commented 4 months ago

On en a un peu discuté aux journée GN, je vous partage notre vue d'export customisée avec les statuts de protections et listes rouges. Elle rajoute 4 colonnes (protections nationales, protection européenne, protection mondiale et listes rouges) un sont concatené les différents statuts La vue d'export est elle même basée sur une vue materialisé préalablement calculée pour ne pas ralentir les performances, ainsi que sur une vue pour décodé les CD_SIG (à adapter sur son territoire)

Vue materialisée : 


CREATE MATERIALIZED VIEW taxonomie.v_custom_status_aggregation
TABLESPACE pg_default
AS WITH protection_nat AS (
         SELECT string_agg(concat(vbs_1.cd_type_statut, ': ', vbs_1.code_statut, ' (', vbs_1.zonage, ') '), '- '::text) AS agg,
            vbs_1.cd_nom
           FROM taxonomie.v_bdc_status_cd_sig_decoded vbs_1
          WHERE vbs_1.cd_type_statut::text = ANY (ARRAY['REGLII'::text, 'REGLUTTE'::text, 'REGL'::text, 'PD'::text, 'PR'::text, 'PN'::text, 'REGLSO'::text])
          GROUP BY vbs_1.cd_nom
        ), protection_eur AS (
         SELECT string_agg(concat(vbs_1.cd_type_statut, ': ', vbs_1.code_statut::text, ' (', vbs_1.zonage, ') '), ' - '::text) AS agg,
            vbs_1.cd_nom
           FROM taxonomie.v_bdc_status_cd_sig_decoded vbs_1
          WHERE vbs_1.cd_type_statut::text = ANY (ARRAY['DH'::text, 'DO'::text])
          GROUP BY vbs_1.cd_nom
        ), protection_mond AS (
         SELECT string_agg(concat(vbs_1.cd_type_statut, ' : ', vbs_1.code_statut, ' (', vbs_1.zonage, ')'), ' - '::text) AS agg,
            vbs_1.cd_nom
           FROM taxonomie.v_bdc_status_cd_sig_decoded vbs_1
          WHERE vbs_1.cd_type_statut::text = ANY (ARRAY['BERN'::text, 'BONN'::text, 'OSPAR'::text])
          GROUP BY vbs_1.cd_nom
        ), listes_rouges AS (
         SELECT string_agg(concat(vbs_1.cd_type_statut, ' : ', vbs_1.code_statut, ' (', vbs_1.zonage, ')'), ' - '::text) AS agg,
            vbs_1.cd_nom
           FROM taxonomie.v_bdc_status_cd_sig_decoded vbs_1
          WHERE (vbs_1.cd_type_statut::text = ANY (ARRAY['LRM'::text, 'LRN'::text, 'LRR'::text, 'LRE'::text])) AND (vbs_1.code_statut::text <> ALL (ARRAY['NA'::text, 'DD'::text]))
          GROUP BY vbs_1.cd_nom
        )
 SELECT DISTINCT vbs.cd_nom,
    p.agg AS protections_nat,
    d.agg AS protections_eur,
    m.agg AS protections_mond,
    r.agg AS listes_rouge
   FROM taxonomie.taxref vbs
     LEFT JOIN protection_nat p ON p.cd_nom = vbs.cd_nom
     LEFT JOIN protection_eur d ON d.cd_nom = vbs.cd_nom
     LEFT JOIN protection_mond m ON m.cd_nom = vbs.cd_nom
     LEFT JOIN listes_rouges r ON r.cd_nom = vbs.cd_nom
WITH DATA;

vue de décodage : 


CREATE OR REPLACE VIEW taxonomie.v_bdc_status_cd_sig_decoded
AS SELECT s.cd_nom,
    s.cd_ref,
    s.rq_statut,
    v.code_statut,
    v.label_statut,
    t.cd_type_statut,
    ty.thematique,
    ty.lb_type_statut,
    ty.regroupement_type,
    t.cd_st_text,
    t.cd_sig,
        CASE
            WHEN t.cd_sig::text = 'INSEER84'::text THEN 'Auvergne Rhône Alpes'::character varying
            WHEN t.cd_sig::text = 'INSEER93'::text THEN 'Provence-Alpes-Côte d’Azur'::character varying
            WHEN t.cd_sig::text = 'INSEER82'::text THEN 'Rhône Alpes'::character varying
            WHEN t.cd_sig::text = 'TERFXFR'::text THEN 'Territoire Français'::character varying
            WHEN t.cd_sig::text = 'ETATFRA'::text THEN 'Territoire Français'::character varying
            WHEN t.cd_sig::text = 'INSEED38'::text THEN 'Isère'::character varying
            WHEN t.cd_sig::text = 'INSEED05'::text THEN 'Hautes-Alpes'::character varying
            ELSE t.cd_sig
        END AS zonage,
    t.cd_doc,
    t.niveau_admin,
    t.cd_iso3166_1,
    t.cd_iso3166_2,
    t.full_citation,
    t.doc_url,
    ty.type_value
   FROM taxonomie.bdc_statut_taxons s
     JOIN taxonomie.bdc_statut_cor_text_values c ON s.id_value_text = c.id_value_text
     JOIN taxonomie.bdc_statut_text t ON t.id_text = c.id_text
     JOIN taxonomie.bdc_statut_values v ON v.id_value = c.id_value
     JOIN taxonomie.bdc_statut_type ty ON ty.cd_type_statut::text = t.cd_type_statut::text
  WHERE t.enable = true;CREATE OR REPLACE VIEW taxonomie.v_bdc_status_cd_sig_decoded
AS SELECT s.cd_nom,
    s.cd_ref,
    s.rq_statut,
    v.code_statut,
    v.label_statut,
    t.cd_type_statut,
    ty.thematique,
    ty.lb_type_statut,
    ty.regroupement_type,
    t.cd_st_text,
    t.cd_sig,
        CASE
            WHEN t.cd_sig::text = 'INSEER84'::text THEN 'Auvergne Rhône Alpes'::character varying
            WHEN t.cd_sig::text = 'INSEER93'::text THEN 'Provence-Alpes-Côte d’Azur'::character varying
            WHEN t.cd_sig::text = 'INSEER82'::text THEN 'Rhône Alpes'::character varying
            WHEN t.cd_sig::text = 'TERFXFR'::text THEN 'Territoire Français'::character varying
            WHEN t.cd_sig::text = 'ETATFRA'::text THEN 'Territoire Français'::character varying
            WHEN t.cd_sig::text = 'INSEED38'::text THEN 'Isère'::character varying
            WHEN t.cd_sig::text = 'INSEED05'::text THEN 'Hautes-Alpes'::character varying
            ELSE t.cd_sig
        END AS zonage,
    t.cd_doc,
    t.niveau_admin,
    t.cd_iso3166_1,
    t.cd_iso3166_2,
    t.full_citation,
    t.doc_url,
    ty.type_value
   FROM taxonomie.bdc_statut_taxons s
     JOIN taxonomie.bdc_statut_cor_text_values c ON s.id_value_text = c.id_value_text
     JOIN taxonomie.bdc_statut_text t ON t.id_text = c.id_text
     JOIN taxonomie.bdc_statut_values v ON v.id_value = c.id_value
     JOIN taxonomie.bdc_statut_type ty ON ty.cd_type_statut::text = t.cd_type_statut::text
  WHERE t.enable = true;

et la vue d'export elle même :

-- gn_synthese.v_synthese_for_export_custom_status source


CREATE OR REPLACE VIEW gn_synthese.v_synthese_for_export_custom_status
AS SELECT s.id_synthese,
    s.date_min::date AS date_debut,
    s.date_max::date AS date_fin,
    t.cd_nom,
    t.cd_ref,
    t.nom_valide,
    t.nom_vern AS nom_vernaculaire,
    s.nom_cite,
    t.regne,
    t.group2_inpn,
    t.classe,
    t.ordre,
    t.famille,
    t.id_rang AS rang_taxo,
    status.protections_nat,
    status.protections_eur,
    status.protections_mond,
    status.listes_rouge,
    cta.valeur_attribut AS patrimonial,
    s.count_min AS nombre_min,
    s.count_max AS nombre_max,
    s.altitude_min AS alti_min,
    s.altitude_max AS alti_max,
    s.observers AS observateurs,
    sa.communes,
    st_astext(s.the_geom_4326) AS geometrie_wkt_4326,
    st_x(s.the_geom_point) AS x_centroid_4326,
    st_y(s.the_geom_point) AS y_centroid_4326,
    st_asgeojson(s.the_geom_4326) AS geojson_4326,
    st_asgeojson(s.the_geom_local) AS geojson_local,
    s.comment_context AS comment_releve,
    s.comment_description AS comment_occurrence,
    d.dataset_name AS jdd_nom,
    af.acquisition_framework_name AS ca_nom,
    s."precision" AS precision_geographique,
    n1.label_default AS nature_objet_geo,
    n3.label_default AS technique_observation,
    n10.label_default AS stade_vie,
    n11.label_default AS sexe,
    s.unique_id_sinp AS uuid_perm_sinp,
    s.unique_id_sinp_grp AS uuid_perm_grp_sinp
   FROM gn_synthese.synthese s
     JOIN taxonomie.taxref t ON t.cd_nom = s.cd_nom
     LEFT JOIN taxonomie.cor_taxon_attribut cta ON cta.cd_ref = t.cd_nom AND cta.id_attribut = 1
     JOIN gn_meta.t_datasets d ON d.id_dataset = s.id_dataset
     JOIN gn_meta.t_acquisition_frameworks af ON d.id_acquisition_framework = af.id_acquisition_framework
     LEFT JOIN LATERAL ( SELECT cas.id_synthese,
            string_agg(DISTINCT concat(a_1.area_name, ' (', a_1.area_code, ')'), ', '::text) AS communes
           FROM gn_synthese.cor_area_synthese cas
             JOIN ref_geo.l_areas a_1 ON cas.id_area = a_1.id_area
             JOIN ref_geo.bib_areas_types ta ON ta.id_type = a_1.id_type AND ta.type_code::text = 'COM'::text
          WHERE cas.id_synthese = s.id_synthese
          GROUP BY cas.id_synthese) sa ON true
     LEFT JOIN ref_nomenclatures.t_nomenclatures n1 ON s.id_nomenclature_geo_object_nature = n1.id_nomenclature
     LEFT JOIN ref_nomenclatures.t_nomenclatures n3 ON s.id_nomenclature_obs_technique = n3.id_nomenclature
     LEFT JOIN ref_nomenclatures.t_nomenclatures n10 ON s.id_nomenclature_life_stage = n10.id_nomenclature
     LEFT JOIN ref_nomenclatures.t_nomenclatures n11 ON s.id_nomenclature_sex = n11.id_nomenclature
     JOIN taxonomie.v_custom_status_aggregation status ON status.cd_nom = s.cd_nom;
pierre56 commented 4 months ago

Contrib - Export obs certaine/probable avec anonymisation et floutage

Vue export dédiée aux collègues ayant le droit de voir toutes les obs précises, ce qui leur permet d'extraire les données et les transmettre à d'autres organismes sans avoir a faire de nettoyage avant envoi.

obs certaine/probable avec anonymisation des noms et suppression des champs commentaires. Floutage de la géométrie en fonction du statut de sensibilité

Vue

CREATE OR REPLACE VIEW gn_synthese.v_synthese_for_export_floutage
AS SELECT s.id_synthese,
    s.date_min::date AS date_debut,
    s.date_max::date AS date_fin,
--    s.date_min::time without time zone AS heure_debut, -- non complété
--    s.date_max::time without time zone AS heure_fin, -- non complété
    t.cd_nom,
    t.cd_ref,
    t.nom_valide,
    t.nom_vern AS nom_vernaculaire,
    s.nom_cite,
    t.regne,
    t.group1_inpn,
    t.group2_inpn,
    t.group3_inpn,
    t.classe,
    t.ordre,
    t.famille,
    t.id_rang AS rang_taxo,
    s.count_min AS nombre_min,
    s.count_max AS nombre_max,
    s.altitude_min AS alti_min,
    s.altitude_max AS alti_max,
--    s.depth_min AS prof_min, -- non complété
--    s.depth_max AS prof_max, -- non complété
--    s.observers AS observateurs, -- anonymisation
    s.id_digitiser,
--    s.determiner AS determinateur, -- anonymisation
    sa.communes,
    coalesce (st_astext(floutage.geom),st_astext(s.the_geom_4326)) AS geometrie_wkt_4326,
--    st_x(s.the_geom_point) AS x_centroid_4326, 
--    st_y(s.the_geom_point) AS y_centroid_4326,
    coalesce (st_asgeojson(s.the_geom_4326) , st_asgeojson(floutage.geom)) AS geojson_4326,
    coalesce (st_asgeojson(s.the_geom_4326), st_asgeojson(floutage.geom)) AS geojson_local,
    s.place_name AS nom_lieu,
--    s.comment_context AS comment_releve, -- anonymisation
--    s.comment_description AS comment_occurrence, -- anonymisation
--    s.validator AS validateur, -- anonymisation
    n21.label_default AS niveau_validation,
    s.meta_validation_date AS date_validation,
--    s.validation_comment AS comment_validation, -- anonymisation
    s.digital_proof AS preuve_numerique_url,
    s.non_digital_proof AS preuve_non_numerique,
    d.dataset_name AS jdd_nom,
    d.unique_dataset_id AS jdd_uuid,
--    d.id_dataset AS jdd_id, -- inutile
    af.acquisition_framework_name AS ca_nom,
    af.unique_acquisition_framework_id AS ca_uuid,
--    d.id_acquisition_framework AS ca_id, -- inutile
    s.cd_hab AS cd_habref,
    hab.lb_code AS cd_habitat,
    hab.lb_hab_fr AS nom_habitat,
    s."precision" AS precision_geographique,
    n1.label_default AS nature_objet_geo,
    n2.label_default AS type_regroupement,
    s.grp_method AS methode_regroupement,
    n3.label_default AS technique_observation,
    n5.label_default AS biologique_statut,
    n6.label_default AS etat_biologique,
    n22.label_default AS biogeographique_statut,
    n7.label_default AS naturalite,
    n8.label_default AS preuve_existante,
    n9.label_default AS niveau_precision_diffusion,
    n10.label_default AS stade_vie,
    n11.label_default AS sexe,
    n12.label_default AS objet_denombrement,
    n13.label_default AS type_denombrement,
    n14.label_default AS niveau_sensibilite,
    n15.label_default AS statut_observation,
--    n16.label_default AS floutage_dee, -- données floutées dans le UNION
    n17.label_default AS statut_source,
    n18.label_default AS type_info_geo,
    n19.label_default AS methode_determination,
    n20.label_default AS comportement,
--    s.reference_biblio, -- anonymisation car url faune
--    s.entity_source_pk_value AS id_origine, --inutile
    s.unique_id_sinp AS uuid_perm_sinp,
    s.unique_id_sinp_grp AS uuid_perm_grp_sinp,
    s.meta_create_date AS date_creation,
--    s.meta_update_date AS date_modification, -- redondant
--    s.additional_data AS champs_additionnels, -- anonymisation
    COALESCE(s.meta_update_date, s.meta_create_date) AS derniere_action
   FROM gn_synthese.synthese s
     JOIN taxonomie.taxref t ON t.cd_nom = s.cd_nom
     JOIN gn_meta.t_datasets d ON d.id_dataset = s.id_dataset
     JOIN gn_meta.t_acquisition_frameworks af ON d.id_acquisition_framework = af.id_acquisition_framework
     LEFT JOIN ( SELECT cas.id_synthese,
            string_agg(DISTINCT a_1.area_name::text, ', '::text) AS communes
           FROM gn_synthese.cor_area_synthese cas
             LEFT JOIN ref_geo.l_areas a_1 ON cas.id_area = a_1.id_area
             JOIN ref_geo.bib_areas_types ta ON ta.id_type = a_1.id_type AND ta.type_code::text = 'COM'::text
          GROUP BY cas.id_synthese) sa ON sa.id_synthese = s.id_synthese
     LEFT JOIN ref_nomenclatures.t_nomenclatures n1 ON s.id_nomenclature_geo_object_nature = n1.id_nomenclature
     LEFT JOIN ref_nomenclatures.t_nomenclatures n2 ON s.id_nomenclature_grp_typ = n2.id_nomenclature
     LEFT JOIN ref_nomenclatures.t_nomenclatures n3 ON s.id_nomenclature_obs_technique = n3.id_nomenclature
     LEFT JOIN ref_nomenclatures.t_nomenclatures n5 ON s.id_nomenclature_bio_status = n5.id_nomenclature
     LEFT JOIN ref_nomenclatures.t_nomenclatures n6 ON s.id_nomenclature_bio_condition = n6.id_nomenclature
     LEFT JOIN ref_nomenclatures.t_nomenclatures n7 ON s.id_nomenclature_naturalness = n7.id_nomenclature
     LEFT JOIN ref_nomenclatures.t_nomenclatures n8 ON s.id_nomenclature_exist_proof = n8.id_nomenclature
     LEFT JOIN ref_nomenclatures.t_nomenclatures n9 ON s.id_nomenclature_diffusion_level = n9.id_nomenclature
     LEFT JOIN ref_nomenclatures.t_nomenclatures n10 ON s.id_nomenclature_life_stage = n10.id_nomenclature
     LEFT JOIN ref_nomenclatures.t_nomenclatures n11 ON s.id_nomenclature_sex = n11.id_nomenclature
     LEFT JOIN ref_nomenclatures.t_nomenclatures n12 ON s.id_nomenclature_obj_count = n12.id_nomenclature
     LEFT JOIN ref_nomenclatures.t_nomenclatures n13 ON s.id_nomenclature_type_count = n13.id_nomenclature
     LEFT JOIN ref_nomenclatures.t_nomenclatures n14 ON s.id_nomenclature_sensitivity = n14.id_nomenclature
     LEFT JOIN ref_nomenclatures.t_nomenclatures n15 ON s.id_nomenclature_observation_status = n15.id_nomenclature
     LEFT JOIN ref_nomenclatures.t_nomenclatures n16 ON s.id_nomenclature_blurring = n16.id_nomenclature
     LEFT JOIN ref_nomenclatures.t_nomenclatures n17 ON s.id_nomenclature_source_status = n17.id_nomenclature
     LEFT JOIN ref_nomenclatures.t_nomenclatures n18 ON s.id_nomenclature_info_geo_type = n18.id_nomenclature
     LEFT JOIN ref_nomenclatures.t_nomenclatures n19 ON s.id_nomenclature_determination_method = n19.id_nomenclature
     LEFT JOIN ref_nomenclatures.t_nomenclatures n20 ON s.id_nomenclature_behaviour = n20.id_nomenclature
     LEFT JOIN ref_nomenclatures.t_nomenclatures n21 ON s.id_nomenclature_valid_status = n21.id_nomenclature
     LEFT JOIN ref_nomenclatures.t_nomenclatures n22 ON s.id_nomenclature_biogeo_status = n22.id_nomenclature
     LEFT JOIN ref_habitats.habref hab ON hab.cd_hab = s.cd_hab
     -- Jointure Calcul geom floutée
    LEFT JOIN LATERAL (
        SELECT
            COALESCE(ST_Transform(a.geom,4326), s.the_geom_4326) AS geom
        FROM
            gn_synthese.cor_area_synthese cas
        JOIN ref_geo.l_areas a ON a.id_area = cas.id_area
        JOIN ref_geo.bib_areas_types bat ON bat.id_type = a.id_type
        JOIN gn_sensitivity.cor_sensitivity_area_type sat ON bat.id_type = sat.id_area_type
        WHERE
            cas.id_synthese = s.id_synthese
            AND s.id_nomenclature_sensitivity = sat.id_nomenclature_sensitivity
        LIMIT 1
    ) floutage ON TRUE
    WHERE  -- règles floutage
     -- validation certaine et probable
     s.id_nomenclature_valid_status in (ref_nomenclatures.get_id_nomenclature('STATUT_VALID', '1'), ref_nomenclatures.get_id_nomenclature('STATUT_VALID', '2') )
     and 
     -- non diffusion mm floutée
     s.id_nomenclature_diffusion_level != ref_nomenclatures.get_id_nomenclature('NIV_PRECIS', '4') -- Aucune diffusion
     and 
     s.id_nomenclature_sensitivity != ref_nomenclatures.get_id_nomenclature('SENSIBILITE', '4') --Aucune diffusion
     ;

GRANT SELECT ON TABLE gn_synthese.v_synthese_for_export_floutage TO geonatadmin;

Config

[SYNTHESE]
...
# Export custom dans synthese
EXPORT_OBSERVATIONS_CUSTOM_VIEWS = [
{
label = "Export obs certaine/probable avec anonymisation et floutage",
view_name = "gn_synthese.v_synthese_for_export_floutage",
},
]

ce qui donne image

TheoLechemia commented 4 months ago

Salut, merci pour le partage ! Juste pour info, le floutage (y compris des exports) a été développé dans la 2.14

camillemonchicourt commented 4 months ago

OK merci pour ce partage intéressant et complémentaire. A noter par contre qu'il n'est pas nécessaire de flouter les données dans la vue d'export, car GeoNature se charge de flouter au nom les données en fonction des permissions de l'utilisateur qui fait l'export - https://docs.geonature.fr/admin-manual.html#module-synthese

En tout cas, ces partages de requête, ainsi que les échanges lors des rencontres GeoNature rappellent qu'il serait intéressant de remettre en route l'espace de partage de ressources techniques qu'on avait initié ici : https://github.com/PnX-SI/Ressources-techniques/tree/master/GeoNature

pierre56 commented 4 months ago

Merci pour les retours, Je sais que le floutage est mis en place et ça marche bien pour nos bénévoles👍

Mais on est justement dans le cas où la personne a des droits élevés et qu'il peut tout exporter. Il veut pouvoir exporter les données floutées et anonymisées mais les voir précises sur geonature et pouvoir les exporter précises au besoin.

Ça évite qu'il fasse le taff de suppression des colonnes (potentiellement mal) qui sont pas RGPD compatibles et le floutage est fait (selon la convention de données passée) Avant envoi a un autre organisme, ça doit faire gagner 5 min aux collègues par export 😄

Ok je vais plus ajouter dans ressources-technique mais j'ai un doute où ranger ce que j'ai pu dev au fil du temps 😓

camillemonchicourt commented 4 months ago

OK je vois, super intéressant.