Nous utilisons des cookies pour vous garantir la meilleure expérience sur notre site. Si vous continuez à utiliser ce dernier, nous considèrerons que vous acceptez l'utilisation des cookies. J'ai compris ! ou En savoir plus !.
Nom d'utilisateur    Mot de passe              Toujours pas inscrit ?   Mot de passe oublié ?

#1 mer. 22 mars 2017 05:09

thure
Membre
Date d'inscription: 21 mars 2017
Messages: 9

[PostGIS] Trigger et update de deux tables ayant une relation spatiale

Bonjour,

Je dispose de deux couches géographiques gérées sous PostGIS.
L'une est de type polygones. L'autre contient des points.
Il existe une relation entre ces deux tables : les points sont positionnés dans les polygones.

Je souhaiterais écrire un déclencheur et sa fonction de telle manière que :
pour chaque mise à jour de la colonne géométrique ou insertion dans la table qui contient les polygones,
la tables des points soient également mise à jour.

Cette table de points contient un champ "id_polygon".
Je souhaiterais que ce champ se remplisse ainsi automatiquement à partir des valeurs id des polygones au sein desquels il sont positionnés.

De quelle manière procéderiez-vous ?
Je vous remercie par avance

Hors ligne

 

#2 mer. 22 mars 2017 09:29

JP LLORENS
Membre
Lieu: Villeneuve-sur-Lot
Date d'inscription: 12 nov. 2008
Messages: 173

Re: [PostGIS] Trigger et update de deux tables ayant une relation spatiale

Bonjour
Le trigger doit être posé sur la table des polygones. Comme il va mettre à jour une autre table je pense qu'il doit se déclencher en AFTER
Le code ci-dessous devrait se rapprocher de vos besoins.

Code:

CREATE OR REPLACE FUNCTION maj()
  RETURNS trigger AS
$BODY$DECLARE
BEGIN

IF TG_OP = 'INSERT' or TG_OP = 'UPDATE THEN
  update point set point.id_polygone = poly.id_polygone where st_within (point.geom, poly.geom)

END IF;

return     NEW; 
END;$BODY$
  LANGUAGE plpgsql VOLATILE SECURITY DEFINER
  COST 100;
ALTER FUNCTION maj()
  OWNER TO postgres;

Cordialement
JP

Hors ligne

 

#3 mer. 22 mars 2017 15:26

thure
Membre
Date d'inscription: 21 mars 2017
Messages: 9

Re: [PostGIS] Trigger et update de deux tables ayant une relation spatiale

Merci pour votre réponse.

Alors je viens de tester en supposant que la table polygone s'appelle "commune" et celle des points... "points"

Code:

CREATE OR REPLACE FUNCTION public.maj()
RETURNS trigger AS
$BODY$
DECLARE
BEGIN

IF TG_OP = 'INSERT' or TG_OP = 'UPDATE' THEN
  update points set id_commune = commune.gid 
  where st_within (points.geometrie, commune.geometrie);
END IF;
return     NEW; 
END;$BODY$
  LANGUAGE plpgsql VOLATILE SECURITY DEFINER
  COST 100;
ALTER FUNCTION public.maj()
  OWNER TO postgres;
  
CREATE TRIGGER trigger_maj
AFTER INSERT OR UPDATE OF geometrie
ON public.commune
FOR EACH ROW
EXECUTE PROCEDURE public.maj();

J'obtiens l'erreur

ERREUR:  entrée manquante de la clause FROM pour la table « commune »
LINE 2:   where st_within (points.geometrie, public.commune."geometr...


On ne peut pas faire de jointure dans un update non ?
Ou faudrait-il passer par un UPDATE... SELECT ?

Merci

Hors ligne

 

#4 mer. 22 mars 2017 15:51

thure
Membre
Date d'inscription: 21 mars 2017
Messages: 9

Re: [PostGIS] Trigger et update de deux tables ayant une relation spatiale

En effet, il manquait la clause "FROM" pour la table commune dans mon code.

Si cela peut servir à d'autres, voici le résultat qui fonctionne :
Code de la fonction :

Code:

CREATE OR REPLACE FUNCTION public.maj()
  RETURNS trigger AS
$BODY$
DECLARE
BEGIN

IF TG_OP = 'INSERT' or TG_OP = 'UPDATE' THEN
  update public.points set id_commune = public.commune.gid FROM public.commune where st_within (points.geometrie, commune.geometrie);

END IF;
return     NEW; 
END;$BODY$
  LANGUAGE plpgsql VOLATILE SECURITY DEFINER
  COST 100;
ALTER FUNCTION public.maj()
  OWNER TO postgres;

Code du trigger :

Code:

CREATE TRIGGER trigger_maj
  AFTER INSERT OR UPDATE OF geometrie
  ON public.commune
  FOR EACH ROW
  EXECUTE PROCEDURE public.maj();

Un grand merci à toi JP !

Hors ligne

 

#5 mer. 22 mars 2017 16:23

thure
Membre
Date d'inscription: 21 mars 2017
Messages: 9

Re: [PostGIS] Trigger et update de deux tables ayant une relation spatiale

Et un petit ajout pour prendre en compte que potentiellement certains points se retrouvent ensuite en dehors du polygone...

Code:

CREATE OR REPLACE FUNCTION public.maj()
  RETURNS trigger AS
$BODY$
DECLARE
identifiant INTEGER;
BEGIN 
identifiant := NEW.gid;
IF TG_OP = 'INSERT' or TG_OP = 'UPDATE' THEN
update public.points SET id_commune = NULL WHERE id_commune = identifiant;    
  update public.points set id_commune = public.commune.gid FROM public.commune where st_within (points.geometrie, commune.geometrie);
    
END IF;
return     NEW; 
END;$BODY$
  LANGUAGE plpgsql VOLATILE SECURITY DEFINER
  COST 100;
ALTER FUNCTION public.maj()
  OWNER TO postgres;

Hors ligne

 

#6 mar. 12 juin 2018 09:40

Olivoued
Membre
Date d'inscription: 9 mars 2007
Messages: 7

Re: [PostGIS] Trigger et update de deux tables ayant une relation spatiale

Bonjour,

Mon exercice est similaire, cependant j'ai un retard d’exécution et je ne sais pas comment le résoudre:

J'ai 4 couches; point(p), tampon(t) et polygones(h) et (h2).

une première fonction p_tamp génère ou supprime un tampon t sur p lors d'ajout ou modification de p :

--fonction création de buffer sur p
CREATE OR REPLACE FUNCTION p_tamp() RETURNS trigger AS $update_new_p_buffers$
    BEGIN
   
        -- INSERT
        IF TG_OP = 'INSERT' or TG_OP = 'UPDATE' THEN
            INSERT INTO
                p (id, geom)
            SELECT
                NEW.id
                , ST_Buffer(NEW.geom, 100);
            RETURN NEW;
        -- UPDATE
        ELSIF (TG_OP = 'UPDATE') THEN
            UPDATE
                p
            SET
                geom = ST_Buffer(NEW.geom, 100)
            WHERE
                p.id = NEW.id;
            RETURN NEW;
        -- DELETE
        ELSIF (TG_OP = 'DELETE') THEN
            DELETE FROM p  where p.id = OLD.id;
            RETURN OLD;
        END IF;
    END;
$update_new_pav_buffers$ LANGUAGE plpgsql;


--trigger


DROP TRIGGER IF EXISTS trg_p_buffers ON p;
CREATE TRIGGER update_new_p_buffers AFTER INSERT OR UPDATE OR DELETE ON p
    FOR EACH ROW EXECUTE PROCEDURE p_tamp();

Cela fonctionne bien jusqu'ici. J'ai une 2éme fonction qui copie les polygones (h) intersectés par les tampon (t) sur la même action:


--fonction copie de h intersecté par les tampons p
CREATE OR REPLACE FUNCTION public.maj_h_p_t()
RETURNS trigger AS
$BODY$

BEGIN
-- INSERT

IF (TG_OP = 'INSERT') OR (TG_OP = 'UPDATE') OR (TG_OP = 'DELETE') THEN
DROP TABLE IF EXISTS h2;
CREATE TABLE h2 AS


SELECT         h.id, h.geom
       
       
FROM         h, t
WHERE         ST_Intersects(h.geom, t.geom);
RETURN NEW;   
END if;
RETURN NEW;
END;

$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

--trigger
DROP TRIGGER IF EXISTS trg_maj_h_p_t ON p;
CREATE TRIGGER trg_maj_h_p_t
AFTER INSERT OR UPDATE OR DELETE
ON p
FOR EACH ROW
EXECUTE PROCEDURE public.maj_h_p_t();

Ici, lorsque que je crée ou modifie p (sous qgis) j'ai bien le tampon mais pas de mise à jour de h2. Il me faut effectuer une autre action sur p pour voir h2 se mettre à jour de l'action précédente. ...j'ai tourné les requêtes dans beaucoup de sens sans trouver.

D'avance merci pour les retours

Hors ligne

 

#7 mar. 12 juin 2018 10:37

jmarsac
Membre
Lieu: NICE
Date d'inscription: 26 oct. 2005
Messages: 265
Site web

Re: [PostGIS] Trigger et update de deux tables ayant une relation spatiale

Bonjour,

Comme le dit la doc, :

Si plusieurs déclencheurs du même genre sont définis sur le même événement, ils sont exécutés selon l'ordre alphabétique de leur nom.

Il faut donc nommer vos triggers en fonction de l'ordre d'exécution souhaité


Jean-Marie

Hors ligne

 

#8 mar. 12 juin 2018 11:14

Olivoued
Membre
Date d'inscription: 9 mars 2007
Messages: 7

Re: [PostGIS] Trigger et update de deux tables ayant une relation spatiale

Merci Jean-Marie,

Oui je me souviens effectivement c'est clairement dit dans la doc.

Hors ligne

 

Pied de page des forums

Powered by FluxBB

Partagez  |