Différences

Ci-dessous, les différences entre deux révisions de la page.

--- fonctionnalites:geonature-atlas:integration-biodiv-territoire [2025/07/11 09:21] – [Fonctionnalités secondaires] jpmilcent
+++ fonctionnalites:geonature-atlas:integration-biodiv-territoire [2025/07/11 18:46] (Version actuelle) – [Contraintes Atlas v2 et GN branche feat/sinp] jpmilcent
@@ Ligne 141: / Ligne 141: @@
 ===== Contraintes Atlas v2 et GN branche feat/sinp =====
-  * Nécessite de rajouter les tables suivante à la base GeoNature 2.10 branche ''feat/sinp'' : ''ref_geo.cor_areas''
+  * Nécessite de rajouter les tables suivante à la base GeoNature 2.10 branche ''feat/sinp'' : [[https://github.com/PnX-SI/RefGeo/blob/master/src/ref_geo/migrations/data/ref_geo_cor.sql#L1C1-L23C94|ref_geo.cor_areas]]
+  * Nécessite d'activer les zones géo à prendre en compte (SINP, DEP, COM, PNR) : <code sql>
+UPDATE ref_geo.l_areas SET
+enable = TRUE
+WHERE id_type = ref_geo.get_id_area_type_by_code('PNR');
+</code>
+  * Nécessite de peupler la table ''ref_geo.cor_areas'' : <code sql>
+INSERT INTO ref_geo.cor_areas (
+    id_area_group,
+    id_area
+)
+  SELECT
+    p.id_area AS id_area_goup,
+    c.id_area
+  FROM ref_geo.l_areas AS p, ref_geo.l_areas AS c
+  WHERE p.id_type = ref_geo.get_id_area_type_by_code('SINP')
+    AND p."enable" = TRUE
+    AND c.id_type = ref_geo.get_id_area_type_by_code('DEP')
+    AND c."enable" = TRUE
+  UNION
+  SELECT
+    p.id_area AS id_area_goup,
+    c.id_area
+  FROM ref_geo.l_areas AS p, ref_geo.l_areas AS c
+  WHERE p.id_type = ref_geo.get_id_area_type_by_code('DEP')
+    AND p."enable" = TRUE
+    AND c.id_type = ref_geo.get_id_area_type_by_code('COM')
+    AND c."enable" = TRUE
+    AND starts_with(c.area_code, p.area_code)
+  UNION
+  SELECT
+    p.id_area AS id_area_goup,
+    c.id_area
+  FROM ref_geo.l_areas AS p, ref_geo.l_areas AS c
+  WHERE p.id_type = ref_geo.get_id_area_type_by_code('SINP')
+    AND p."enable" = TRUE
+    AND c.id_type = ref_geo.get_id_area_type_by_code('PNR')
+    AND c."enable" = TRUE ;
+</code>
+  * Ajout de la colonne ''geom_4326'' sur ''ref_geo.l_areas'' et d'un trigger : <code sql>
+ALTER TABLE ref_geo.l_areas ADD COLUMN geom_4326 public.geometry(multipolygon, 4326) NULL;
+CREATE FUNCTION ref_geo.fct_tri_transform_geom()
+  RETURNS trigger AS
+  $BODY$
+    DECLARE
+      local_srid integer;
+      c integer;
+    BEGIN
+      IF (TG_OP = 'INSERT') THEN
+        -- Insert policy: we set geom from geom_4326 if geom is null and geom_4326 is not null, and reciprocally.
+        -- If both geom and geom_4326 have been set (or both are null), we do nothing.
+        IF (NEW.geom IS NULL AND NEW.geom_4326 IS NOT NULL) THEN
+          NEW.geom = ST_Transform(NEW.geom_4326, local_srid);
+          RAISE NOTICE '(I) Updated geom';
+        ELSEIF (NEW.geom IS NOT NULL AND NEW.geom_4326 IS NULL) THEN
+          NEW.geom_4326 = ST_Transform(NEW.geom, 4326);
+          RAISE NOTICE '(I) Updated geom_4326';
+        END IF;
+      ELSEIF (TG_OP = 'UPDATE') THEN
+        -- Update policy: we set geom from geom_4326 if geom_4326 have been updated to non null value,
+        -- unless geom have also been modified to non null value, and reciprocally.
+        -- We also set geom from geom_4326 if geom is modified to null, and geom_4326 is not null (modified or not),
+        -- in order to be consistent when updating one or two columns at the same time.
+        IF (
+          NEW.geom_4326 IS NOT NULL
+          AND
+          (
+            (OLD.geom IS NOT DISTINCT FROM NEW.geom AND OLD.geom_4326 IS DISTINCT FROM NEW.geom_4326)
+            OR
+            (NEW.geom IS NULL AND OLD.geom IS NOT NULL)
+          )
+        ) THEN
+          SELECT INTO local_srid Find_SRID('ref_geo', 'l_areas', 'geom');
+          NEW.geom = ST_Transform(NEW.geom_4326, local_srid);
+          RAISE NOTICE '(U) Updated geom';
+        ELSEIF (
+          NEW.geom IS NOT NULL
+          AND
+          (
+            (OLD.geom_4326 IS NOT DISTINCT FROM NEW.geom_4326 AND OLD.geom IS DISTINCT FROM NEW.geom)
+            OR
+            (NEW.geom_4326 IS NULL AND OLD.geom_4326 IS NOT NULL)
+          )
+        ) THEN
+          NEW.geom_4326 = ST_Transform(NEW.geom, 4326);
+          RAISE NOTICE '(U) Updated geom_4326';
+        END IF;
+      END IF;
+      RETURN NEW;
+    END;
+  $BODY$
+  LANGUAGE plpgsql VOLATILE
+  COST 100;
+</code>
+  * Ajout de la colonne ''description'' sur la table ''ref_geo.l_areas'' : <code sql>
+ALTER TABLE ref_geo.l_areas ADD COLUMN description text NULL;
+</code>