Le portail francophone de la géomatique

idjepacreutz · Thu 14 December 2023 13:11

Bonjour,

J'essaie de créer un trigger dans notre base de données PostgreSQL/PostGIS afin de découper les géométries linéaires d'une couche segment sur les polygones d'une couche communes à chaque mise à jour du champ geom (dont sur les opérations INSERT et UPDATE), et de mettre à jour des champs qui contiennent les codes INSEE des différents niveaux de collectivité (tous déjà stockés commune par commune).

Le fonctionnement voulu est celui-ci :

- INSERT : la nouvelle entité doit être découpée sur les limites de la couche communes, et chaque ligne résultant de cette opération doit avoir les attributs du polygone dans lequel elle est contenue
- UPDATE : l'entité préexistant doit continuer d'exister (conservation de l'id et de la date de création de l'entité), mais avec sa nouvelle géométrie (découpée sur les communes), et de nouveaux attributs si nécessaire. Les nouvelles entités issues du découpage doivent être insérées dans la même table.

J'ai du mal à obtenir le bon retour depuis la fonction PL/pgSQL, mon code ci-dessous met bien à jour l'entité existante (attributs et géométrie), mais les entités qui sont supposées être insérées ne le sont pas. J'ai essayé de nombreuses combinaisons de RETURN différentes, sans succès. Quelqu'un aurait un conseil ?

Code:

CREATE OR REPLACE FUNCTION split_segments_on_communes_and_update_collectivities_codes() 
RETURNS TRIGGER AS
$BODY$
DECLARE
rec RECORD;
BEGIN
    FOR rec IN
        SELECT c.insee_com AS insee_com,
               '0' || c.insee_dep AS insee_dep,
               c.insee_reg AS insee_reg,
               c.code_epci AS code_epci,
               (ST_Dump(ST_Intersection(NEW.geom, c.geom))).geom AS geom,
               (ST_Dump(ST_Intersection(NEW.geom, c.geom))).path[0] AS "path",
               NEW.id_statut,
               NEW.propriete,
               NEW.author,
               NEW.create_date
          FROM donnees_externes.commune c
          WHERE ST_Intersects(NEW.geom, c.geom)
    LOOP
        IF rec."path" = 1 OR rec."path" IS NULL THEN
            NEW.geom = rec.geom;
            NEW.code_reg = rec.insee_reg;
            NEW.code_dep = rec.insee_dep;
            NEW.code_epci = rec.code_epci;
            NEW.code_commune = rec.insee_com;
        ELSE
            INSERT INTO reference.segment (geom,
                                           id_statut,
                                           propriete,
                                           code_epci,
                                           code_dep,
                                           code_reg,
                                           author,
                                           create_date,
                                           code_commune)
            VALUES (
                   rec.geom,
                   NEW.id_statut,
                   NEW.propriete,
                   rec.code_epci,
                   rec.insee_dep,
                   rec.insee_reg,
                   NEW.author,
                   NEW.create_date,
                   rec.insee_com);
        END IF;
    END LOOP;
RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;

CREATE OR REPLACE TRIGGER split_segments_on_communes_and_update_collectivities_codes
 BEFORE INSERT
    OR UPDATE OF geom
    ON reference.segment
 FOR EACH ROW
 WHEN (pg_trigger_depth() < 1)
 EXECUTE PROCEDURE split_segments_on_communes_and_update_collectivities_codes();

idjepacreutz · Tue 19 December 2023 17:33

Le problème était dans l'utilisation de `path` dans cette ligne : `(ST_Dump(ST_Intersection(NEW.geom, c.geom))).path[0] AS "path"`.

Voilà la bonne manière de faire :

Code:

WITH parts AS (
            SELECT c.insee_com AS insee_com,
                   '0' || c.insee_dep AS insee_dep,
                   c.insee_reg AS insee_reg,
                   c.code_epci AS code_epci,
                   (ST_Dump(ST_Intersection(NEW.geom, c.geom))).geom AS geom,
                   NEW.id_statut,
                   NEW.propriete,
                   NEW.author,
                   NEW.create_date
              FROM donnees_externes.commune c
             WHERE ST_Intersects(NEW.geom, c.geom)
        )
        SELECT *,
               ROW_NUMBER() OVER () AS "path"
          FROM parts

Et la fonction complète :

Code:

CREATE OR REPLACE FUNCTION reference.split_segments_on_communes_and_update_collectivities_codes()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
DECLARE
rec RECORD;
newgeom geometry;
BEGIN
    FOR rec IN
        WITH parts AS (
            SELECT c.insee_com AS insee_com,
                   '0' || c.insee_dep,
                   c.insee_reg AS insee_reg,
                   c.code_epci AS code_epci,
                   (ST_Dump(ST_Intersection(NEW.geom, c.geom))).geom AS geom
              FROM donnees_externes.commune c
             WHERE ST_Intersects(NEW.geom, c.geom)
        )
        SELECT *,
               ROW_NUMBER() OVER () AS "path"
          FROM parts
    LOOP 
        IF ST_GeometryType(rec.geom) != 'ST_Point' THEN
           IF rec."path" = 1 THEN
              NEW.geom := rec.geom;
              NEW.code_reg := rec.insee_reg;
              NEW.code_dep := rec.insee_dep;
              NEW.code_epci := rec.code_epci;
              NEW.code_commune := rec.insee_com;
           ELSE
              INSERT INTO reference.segment (geom,
                                             id_statut,
                                             propriete,
                                             code_epci,
                                             code_dep,
                                             code_reg,
                                             author,
                                             create_date,
                                             code_commune)
              VALUES (
                     rec.geom,
                     NEW.id_statut,
                     NEW.propriete,
                     rec.code_epci,
                     rec.insee_dep,
                     rec.insee_reg,
                     NEW.author,
                     NEW.create_date,
                     rec.insee_com);
           END IF;
        END IF;
    END LOOP;
RETURN NEW;
END;
$function$
;

Dernière modification par idjepacreutz (Tue 19 December 2023 17:33)

Nicolas Ribot · Tue 19 December 2023 19:32

Bonsoir,

Attention, en ne faisant pas de "order by" dans la CTE "parts", la valeur de path peut etre différente suivant les appels à cette fonction.

Nicolas

Communauté	Ressources	L'emploi	Le marché	L'association	Fils RSS
Forum Blogs Wiki Planet Sites amis	Agenda Biblio Fiches AMO-CNIG Paris PCGIS Téléchargements	Offres d'emploi Banque de CV Formations Démarche Métiers	Geo-Entreprises Geo-Communiqué Appels d'offres	L'association Qui sommes-nous ? Nous aider Bienfaiteurs Partenaires Historique Statuts	Tout le site Job Géomatique Services GeoBlogs ...	Faire un don Mentions légales

Le portail francophone de la géomatique

Annonce

#1 Thu 14 December 2023 13:11

Trigger PostgreSQL/PostGIS : découper des lignes sur des polygones

Code:

#2 Tue 19 December 2023 17:33

Re: Trigger PostgreSQL/PostGIS : découper des lignes sur des polygones

Code:

Code:

#3 Tue 19 December 2023 19:32

Re: Trigger PostgreSQL/PostGIS : découper des lignes sur des polygones

Pied de page des forums