Forums PostgreSQL.fr

Christof

Membre

Trigger UPDATE depuis geometry

Bonjour,
J'ai un soucis dans l'application d'un trigger.

Dans une table ETAB, je souhaite mettre à jour un de mes champs "5_rayon_ppi" = 1 ou 0 selon que mon établissement soit situé dans un des rayon PPI ou non.
J'ai les géométries des rayons PPI dans une autre base de données.

Voici mon code :

-- Function: update_rayon_ppi()

-- DROP FUNCTION update_rayon_ppi();

CREATE OR REPLACE FUNCTION update_rayon_ppi()
  RETURNS trigger AS
$BODY$

BEGIN

SELECT foo.wkb_geometry, new.wkb_geometry
FROM etab, dblink('hostaddr=XXXXX port=XXXXX dbname=mabase user=postgres password=XXXXX', 'SELECT wkb_geometry FROM ppi') 
AS foo (wkb_geometry geometry); 

	NEW."5_rayon_ppi" = '1'
	WHERE ST_contains (foo.wkb_geometry, NEW.wkb_geometry);


RETURN NEW;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;
ALTER FUNCTION update_rayon_ppi() OWNER TO postgres;

-- Trigger: update_rayon_pprt on etab

-- DROP TRIGGER update_rayon_pprt ON etab;

CREATE TRIGGER update_rayon_ppi
  BEFORE INSERT OR UPDATE
  ON etab
  FOR EACH ROW
  EXECUTE PROCEDURE update_rayon_ppi();

Le trigger est executé, mais lorsque je met à jour une donnée dans ma table etab, j'ai le message d'erreur suivant :

---------------------------
pgAdmin III
---------------------------
Une erreur s'est produite :

ERROR:  query has no destination for result data
HINT:  If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT:  PL/pgSQL function "update_rayon_ppi" line 4 at SQL statement

Etant novice en la matière, je sollicite votre aide afin de pouvoir résoudre ce problème.

Merci à tous

rjuju

Administrateur

Re : Trigger UPDATE depuis geometry

Bonjour,

vous ne pouvez pas exécuter une requête sql dans votre trigger comme cela. Si vous voulez utiliser le résultat d'une requête, il faut utiliser SELECT champ INTO variable FROM latable WHERE ... et ensuite utiliser cette variable.

---

Julien.
https://rjuju.github.io/

Christof

Membre

Re : Trigger UPDATE depuis geometry

J'ai opté pour une solution temporaire plus simple mais moins "propre"  : Elle consiste à récupérer directement la géométrie de mes zones  sans passer par un lien avec une autre table.

L'inconvénient pour le moment, c'est que si je modifie mes zones, ou si j'en ajoute, alors mon trigger sera obsolète ... et il faudra le refaire.
Autre inconvénient : pour des Rayons SEVESO, j'ai une trentaine de rayons ... donc 30 ELSE IF ...

CREATE OR REPLACE FUNCTION update_rayon_pprt()
  RETURNS trigger AS
$BODY$

BEGIN


	IF ST_within (NEW.wkb_geometry,'0103000020AB6B...') = TRUE THEN
			NEW."5_rayon_pprt" = '1';
	ELSEIF ST_within (NEW.wkb_geometry,'0103000020A...') = TRUE THEN
			NEW."5_rayon_pprt" = '1';
	ELSEIF ST_within (NEW.wkb_geometry,'0103000020AB6B...') = TRUE THEN
			NEW."5_rayon_pprt" = '1';
	ElSE
			NEW."5_rayon_pprt" = '0';
	END IF;

RETURN NEW;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;
ALTER FUNCTION update_rayon_pprt() OWNER TO postgres;

Mais ça fonctionne, pour le moment, ça me dépanne.

rjuju

Administrateur

Re : Trigger UPDATE depuis geometry

Vous pourriez essayer quelque chose du genre :

DECLARE
  nb integer;
BEGIN
  SELECT COUNT(*) INTO nb FROM etab, dblink('hostaddr=XXXXX port=XXXXX dbname=mabase user=postgres password=XXXXX', 'SELECT wkb_geometry FROM ppi') 
  AS foo (wkb_geometry geometry); 
  WHERE ST_contains (foo.wkb_geometry, NEW.wkb_geometry);
  IF (nb=0) THEN
    NEW."5_rayon_pprt" = '0';
  ELSE
    NEW."5_rayon_pprt" = '1';
  END IF;
  RETURN NEW;
END;

à adapter  bien sur selon vos tables, dblink et fonctions GIS voulus.

---

Julien.
https://rjuju.github.io/

Christof

Membre

Re : Trigger UPDATE depuis geometry

Parfait ! merci beaucoup, cela fonctionne bien pour la plupart de mes cas.
Mais j'ai un cas particulier que je n'arrive pas à résoudre.

J'ai un établissement qui peut être situé :
- dans un rayon  R1 (petit rayon avec la géométrie A) donc dans un rayon R2 (c'est le rayon R1 élargi, avec la géométrie B) --> l'étab est contenu dans A et B
- dans un rayon R2 uniquement (il est dans le grand cercle mais pas dans le petit) --> l'étab est contenu dans B uniquement

Pour chacun, je voudrais mettre en place un trigger qui me récupère le nom de la géométrie du rayon selon l'emplacement de l'établissement par rapport à ces rayons :

-- Si l'étab est dans R2 et dans R1 --> "5_rayon" = 'R1' (R1 est prioritaire sur R2)
-- Si l'étab est dans R2 mais pas dans R1 --> "5_rayon" = 'R2'
-- Si l'étab n'est dans aucun rayon --> "5_rayon" = 'Aucun'

Voici ce que j'ai mis en place pour le moment :

-- Function: update_rayon_seveso_test()

-- DROP FUNCTION update_rayon_seveso_test();

CREATE OR REPLACE FUNCTION update_rayon_seveso_test()
  RETURNS trigger AS
$BODY$
DECLARE 
nb integer;
v_nom character varying;
BEGIN


SELECT COUNT(*),foo.nom INTO nb, v_nom FROM dblink('hostaddr=xxxxxx port=xxxx dbname=xxxxx user=postgres password=xxxxx', 'SELECT wkb_geometry, nom FROM seveso') 
  AS foo (geom geometry, nom character varying) 
  WHERE ST_contains (foo.geom, NEW.wkb_geometry)
  GROUP BY foo.nom;

	IF (nb =0) THEN
		NEW."5_rayon_seveso" = 'Aucun';
	ELSE 
		NEW."5_rayon_seveso" = v_nom;
	END IF ; 	
	    
  
  RETURN NEW;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;
ALTER FUNCTION update_rayon_seveso_test() OWNER TO postgres;



CREATE TRIGGER update_rayon_seveso_test
  BEFORE INSERT OR UPDATE
  ON etab
  FOR EACH ROW
  EXECUTE PROCEDURE update_rayon_seveso_test();
 

Le soucis avec cela est que lorsque un établissement est compris dans un rayon, peu importe lequel, il me renvoi "R2". Lorsqu'un établissement n'est pas dans un rayon, rien n'est ajouté dans la colonne lors de la création d'un point.

Voyez-vous ou je veux en venir, et ou pourrait être mon erreur ?

Merci bien

Christof

Membre

Re : Trigger UPDATE depuis geometry

Je pensais à une solution qui serait la suivante :

Lorsque un établissement est dans plusieurs rayons (R1 et R2), alors je pourrais récupérer la géométrie des polygones A et B, les comparer, et retourner le nom du rayon le plus petit des 2.

Ou autrement, lorsqu'un établissement est contenu dans la géométrie d'un rayon R1 (et dans la géométrie d'un rayon R2), alors retourner le nom R1.

Le soucis est que je ne parviens pas à mettre cela en pratique ...

rjuju

Administrateur

Re : Trigger UPDATE depuis geometry

Si aucun rayon n'est trouvé, votre requête ne renvoie aucun enregistrement. Vous pouvez initialiser NEW."5_rayon_seveso" à 'Aucun' avant l'appel de la requête, ou gérer if (nb IS NULL) plutôt que IF (nb = 0).

Votre SELECT renvoie autant de lignes que de rayons trouvés. Le moyen le plus simple serait que votre référentiel (seveso ?) associe une "priorité" à chaque rayon, (1 pour R1, 2 pour R2 par exemple), et remplacer votre select comme ceci :

SELECT COUNT(*),foo.nom INTO nb, v_nom FROM dblink('hostaddr=xxxxxx port=xxxx dbname=xxxxx user=postgres password=xxxxx', 'SELECT wkb_geometry, nom, prio FROM seveso') 
  AS foo (geom geometry, nom character varying) 
  WHERE ST_contains (foo.geom, NEW.wkb_geometry)
  GROUP BY foo.nom
  HAVING count(*) > 0
  ORDER BY prio
  LIMIT 1;

Ainsi, le nom renvoyé sera celui voulu. Si vous avez la taille du rayon dans votre référentiel, vous pouvez l'utiliser comme priorité.

---

Julien.
https://rjuju.github.io/

Christof

Membre

Re : Trigger UPDATE depuis geometry

Mouhahahaha ça y est !!

Un grand merci rjuju !!

J'ai ajouté la colonne "priorite" dans ma table SEVESO, avec priorite = 1 where nom = R1 et priorite = 2 where nom = R2

Voici mon code qui fonctionne si ça peut en aider d’autres ...

CREATE OR REPLACE FUNCTION update_rayon_seveso()
  RETURNS trigger AS
$BODY$
DECLARE 
nb integer;
v_nom character varying;
BEGIN


SELECT COUNT(*),foo.nom INTO nb, v_nom FROM dblink('hostaddr=xxxxxx port=xxxxxx dbname=xxxxxx user=postgres password=xxxxxx', 'SELECT wkb_geometry, nom, priorite FROM seveso') 
  AS foo (geom geometry, nom character varying, priorite integer) 
  WHERE ST_contains (foo.geom, NEW.wkb_geometry)
  GROUP BY foo.nom, foo.priorite
  HAVING count(*) > 0
  ORDER BY priorite
  LIMIT 1;

	IF (nb IS NULL) THEN
	    NEW."5_rayon_seveso" = 'Aucun';    
	ELSE 
		NEW."5_rayon_seveso" = v_nom;
	END IF ; 	
	    
  
  RETURN NEW;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;
ALTER FUNCTION update_rayon_seveso() OWNER TO postgres;

CREATE TRIGGER update_rayon_seveso
  BEFORE INSERT OR UPDATE
  ON etab
  FOR EACH ROW
  EXECUTE PROCEDURE update_rayon_seveso();

Merci encore !!