Différences

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

--- fonctionnalites:geonature-atlas:integration-biodiv-territoire [2025/07/11 15:49] – [Contraintes Atlas v2 et GN branche feat/sinp] jpmilcent
+++ fonctionnalites:geonature-atlas:integration-biodiv-territoire [2025/07/11 18:46] (Version actuelle) – [Contraintes Atlas v2 et GN branche feat/sinp] jpmilcent
@@ Ligne 184: / Ligne 184: @@
     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>