jpm-cbna
commented
1 year ago Si l'on sélectionne seulement le statut "NT" pour la liste nationale, la requête dure plus de 5mn et l'interface échoue à afficher les données. La requête:
WITH national_red_list AS
(SELECT taxonomie.bdc_statut_taxons.cd_ref AS cd_ref, taxonomie.bdc_statut_cor_text_area.id_area AS id_area
FROM taxonomie.bdc_statut_taxons JOIN taxonomie.bdc_statut_cor_text_values ON taxonomie.bdc_statut_cor_text_values.id_value_text = taxonomie.bdc_statut_taxons.id_value_text JOIN taxonomie.bdc_statut_text ON taxonomie.bdc_statut_text.id_text = taxonomie.bdc_statut_cor_text_values.id_text JOIN taxonomie.bdc_statut_values ON taxonomie.bdc_statut_values.id_value = taxonomie.bdc_statut_cor_text_values.id_value JOIN taxonomie.bdc_statut_cor_text_area ON taxonomie.bdc_statut_cor_text_area.id_text = taxonomie.bdc_statut_text.id_text
WHERE taxonomie.bdc_statut_values.code_statut IN ('NT') AND taxonomie.bdc_statut_text.cd_type_statut = 'LRN' AND taxonomie.bdc_statut_text.enable = true),
"OBSERVATIONS" AS
(SELECT DISTINCT ON (gn_synthese.v_synthese_for_web_app.id_synthese, gn_synthese.v_synthese_for_web_app.date_min) gn_synthese.v_synthese_for_web_app.id_synthese AS id_synthese, gn_synthese.v_synthese_for_web_app.id_nomenclature_diffusion_level AS id_nomenclature_diffusion_level, gn_synthese.v_synthese_for_web_app.id_nomenclature_sensitivity AS id_nomenclature_sensitivity, gn_synthese.v_synthese_for_web_app.st_asgeojson AS geojson
FROM gn_synthese.v_synthese_for_web_app JOIN gn_synthese.cor_area_synthese ON gn_synthese.v_synthese_for_web_app.id_synthese = gn_synthese.cor_area_synthese.id_synthese JOIN taxonomie.taxref ON gn_synthese.v_synthese_for_web_app.cd_nom = taxonomie.taxref.cd_nom JOIN national_red_list ON national_red_list.cd_ref = taxonomie.taxref.cd_ref AND national_red_list.id_area = gn_synthese.cor_area_synthese.id_area
WHERE gn_synthese.v_synthese_for_web_app.the_geom_4326 IS NOT NULL ORDER BY gn_synthese.v_synthese_for_web_app.date_min DESC
LIMIT 100000),
"PRIVATE_OBSERVATION" AS
(SELECT DISTINCT ON ("OBSERVATIONS".id_synthese) 5000 AS priority, "OBSERVATIONS".id_synthese AS id_synthese, ref_geo.l_areas.geojson_4326 AS geojson
FROM gn_synthese.cor_area_synthese JOIN ref_geo.l_areas ON ref_geo.l_areas.id_area = gn_synthese.cor_area_synthese.id_area JOIN "OBSERVATIONS" ON "OBSERVATIONS".id_synthese = gn_synthese.cor_area_synthese.id_synthese
WHERE ref_geo.l_areas.id_type = 28 AND "OBSERVATIONS".id_nomenclature_diffusion_level IN (136, 137, 138, 139)),
"PRIVATE_OBSERVATION_PERM" AS
(SELECT "PRIVATE_OBSERVATION".id_synthese AS id_synthese
FROM "PRIVATE_OBSERVATION" JOIN gn_synthese.synthese ON gn_synthese.synthese.id_synthese = "PRIVATE_OBSERVATION".id_synthese JOIN gn_synthese.cor_area_synthese ON gn_synthese.cor_area_synthese.id_synthese = gn_synthese.synthese.id_synthese JOIN taxonomie.taxref ON taxonomie.taxref.cd_nom = gn_synthese.synthese.cd_nom
WHERE gn_synthese.synthese.id_nomenclature_diffusion_level IN (136, 137, 138, 139) AND gn_synthese.cor_area_synthese.id_area IN (661072) AND taxonomie.taxref.cd_ref IN (SELECT cd_ref
FROM taxonomie.find_all_taxons_children(ARRAY[187079]))),
"SENSITIVE_OBSERVATION" AS
(SELECT DISTINCT ON ("OBSERVATIONS".id_synthese) 10000 AS priority, "OBSERVATIONS".id_synthese AS id_synthese, ref_geo.l_areas.geojson_4326 AS geojson
FROM gn_synthese.cor_area_synthese JOIN ref_geo.l_areas ON ref_geo.l_areas.id_area = gn_synthese.cor_area_synthese.id_area JOIN "OBSERVATIONS" ON "OBSERVATIONS".id_synthese = gn_synthese.cor_area_synthese.id_synthese
WHERE ref_geo.l_areas.id_type = 27 AND "OBSERVATIONS".id_nomenclature_sensitivity IN (66, 67, 68)),
"OBS_ALL" AS
(SELECT "PRIVATE_OBSERVATION".priority AS priority, "PRIVATE_OBSERVATION".id_synthese AS id_synthese, "PRIVATE_OBSERVATION".geojson AS geojson
FROM "PRIVATE_OBSERVATION"
WHERE NOT (EXISTS (SELECT 'X' AS anon_1
FROM "PRIVATE_OBSERVATION_PERM"
WHERE "PRIVATE_OBSERVATION_PERM".id_synthese = "PRIVATE_OBSERVATION".id_synthese)) UNION SELECT "SENSITIVE_OBSERVATION".priority AS priority, "SENSITIVE_OBSERVATION".id_synthese AS id_synthese, "SENSITIVE_OBSERVATION".geojson AS geojson
FROM "SENSITIVE_OBSERVATION" UNION SELECT 1 AS priority, "OBSERVATIONS".id_synthese AS id_synthese, "OBSERVATIONS".geojson AS geojson
FROM "OBSERVATIONS"),
"BLURRED_OBSERVATIONS" AS
(SELECT DISTINCT ON ("OBS_ALL".id_synthese) "OBS_ALL".id_synthese AS id_synthese, "OBS_ALL".priority AS priority, "OBS_ALL".geojson AS geojson, json_build_object('id', gn_synthese.v_synthese_for_web_app.id_synthese, 'date_min', gn_synthese.v_synthese_for_web_app.date_min, 'lb_nom', gn_synthese.v_synthese_for_web_app.lb_nom, 'cd_nom', gn_synthese.v_synthese_for_web_app.cd_nom, 'observers', gn_synthese.v_synthese_for_web_app.observers, 'dataset_name', gn_synthese.v_synthese_for_web_app.dataset_name, 'url_source', gn_synthese.v_synthese_for_web_app.url_source, 'unique_id_sinp', gn_synthese.v_synthese_for_web_app.unique_id_sinp, 'nom_vern_or_lb_nom', coalesce(nullif(gn_synthese.v_synthese_for_web_app.nom_vern, ''), gn_synthese.v_synthese_for_web_app.lb_nom), 'count_min_max', CASE WHEN (gn_synthese.v_synthese_for_web_app.count_min != gn_synthese.v_synthese_for_web_app.count_max) THEN concat(gn_synthese.v_synthese_for_web_app.count_min, ' - ', gn_synthese.v_synthese_for_web_app.count_max) WHEN (gn_synthese.v_synthese_for_web_app.count_min IS NOT NULL) THEN concat(gn_synthese.v_synthese_for_web_app.count_min) ELSE '' END) AS obs_as_json
FROM "OBS_ALL" JOIN gn_synthese.v_synthese_for_web_app ON gn_synthese.v_synthese_for_web_app.id_synthese = "OBS_ALL".id_synthese ORDER BY "OBS_ALL".id_synthese, "OBS_ALL".priority DESC)
SELECT "BLURRED_OBSERVATIONS".geojson, json_build_object('observations', json_agg("BLURRED_OBSERVATIONS".obs_as_json)) AS json_build_object_1
FROM "BLURRED_OBSERVATIONS" GROUP BY "BLURRED_OBSERVATIONS".geojson
L'utilisateur n°211 (id silene expert) a un souci avec le filtre protection nationale. Aucune espèce ne ressortent quand il cherche le statut NT (sur aucune liste rouge, quelque soit le territoire). Il a bien un accès pour toute la région PACA. De mon côté, ça fonctionne.
Et les espèces marines protégées (Cymodocea, Posidonia) ne ressortent pas avec le filtre Protection. De mon côté, il y a juste Posidonia qui ressort mais pas Cymodocea.
Il a besoin de ces filtres pour sa saison de terrain.