Le portail francophone de la géomatique

Erwan · Thu 03 July 2014 12:20

Bonjour,

Voici ma problématique :
Je souhaite renseigner automatiquement pour chaque tronçon de canalisation AEP le code insee et le nom de la commune correspondant.

J'utilise pour cela deux fonctions postgis
- calcul du point central du segment "canalisation" (fonction PointOnSurface) car un tronçon de canalisation peut être à cheval sur deux communes
- intersection entre ce point et le polygone commune (fonction st_intersects)

Si la requête (select...) fonctionne seule avec une valeur test (gckey = identifiant), l'inclusion dans la fonction trigger ne fonctionne pas.

Code:

CREATE OR REPLACE FUNCTION aep.cana_insee_i()
  RETURNS trigger AS
$BODY$ 
BEGIN 
    IF NEW."gckey" is not null then
    
    NEW."cana_insee" = (select co.com_insee_varchar from aep.canalisation as ca, commune as co where ca.gckey = NEW."gckey" and st_intersects(PointOnSurface(NEW."geometrie"), co.geometrie) is true);
    NEW."cana_commune" = (select co.com_lib_maj from aep.canalisation as ca, commune as co where ca.gckey = NEW."gckey" and st_intersects(PointOnSurface(NEW."geometrie"), co.geometrie) is true);

    END IF;
    return NEW;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION aep.cana_insee_i() OWNER TO postgres;



-- Trigger: trg_cana_insee_i on aep.canalisation

-- DROP TRIGGER trg_cana_insee_i ON aep.canalisation;

CREATE TRIGGER trg_cana_insee_i
  BEFORE INSERT
  ON aep.canalisation
  FOR EACH ROW
  EXECUTE PROCEDURE aep.cana_insee_i();

Nicolas Ribot · Thu 03 July 2014 13:39

Bonjour,

Plusieurs remarques:

• Dans un trigger BEFORE, la ligne courante n'est pas encore insérée dans la base: on ne peut pas comparer la valeur NEW."gckey" avec ca.gckey si ce champ gckey est nouveau lors de l'insertion.
Dans votre cas, il ne faut pas refaire un lien sur la table canalisation, qui est concernée par le trigger, mais directement utiliser la table commune et la geometrie qui arrive dans le trigger.
Une seule requete est necessaire pour cela et vous pouvez stocker les deux colonnes qui vous interessent directement dans les deux variables:

Code:

select co.com_insee_varchar, co.com_lib_maj 
from commune as co 
where st_intersects(st_lineInterpolatePoint(NEW."geometrie", 0.5), co.geometrie)
into NEW."cana_insee", NEW."cana_commune";

• La méthode st_PointOnSurface (attention, PointOnSurface, sans le prefixe, est un vieux nom qu'il ne faut plus utiliser) marche pour des surfaces, normalement
Postgis la supporte sur des lignes en renvoyant le milieu, mais pas sur que ce comportement soit pérenne entre les versions. Pour avoir le milieu d'une ligne, il faut mieux utiliser st_lineLocatePoint(line.geom, 0.5).

• Etes vous sûr que le milieu de la canalisation est le bon critère pour trouver la commune d'appartenance ? Ne peut-il pas y avoir des cas ou 90% de la canalisation est dans une commune mais son milieu dans une autre ? Le pourcentage de longueur de la canalisation par commune ne serait-il pas plus pertinent ? (ca dépend grandement de vos données)

Nicolas

Erwan · Thu 03 July 2014 17:59

Nicolas,

Merci pour ces remarques très intéressantes.

Cordialement
R1

Erwan · Fri 04 July 2014 12:34

bonjour,

Si la requête fonctionne très bien (j'utilise st_geometryN car mon linéaire est en multilinestring)

Code:

select co.com_insee_varchar, co.com_lib_maj from commune co, aep.canalisation ca where st_intersects(st_line_interpolate_point(st_GeometryN(ca.geometrie,1), 0.5), co.geometrie)

, la récupération des deux variables NEW."cana_insee", NEW."cana_commune" via into ne fonctionne pas

Cordialement

Nicolas Ribot · Fri 04 July 2014 14:12

Bonjour,

Ca ne marche que dans le code du trigger.

Vous pouvez poster le code de la fonction du trigger ainsi que sa definition ?
Quel est le message d'erreur ?

Nicolas

Erwan · Fri 04 July 2014 17:07

Code:

-- Trigger: trg_maj_cana_insee_com on aep.canalisation

-- DROP TRIGGER trg_maj_cana_insee_com ON aep.canalisation;

CREATE TRIGGER trg_maj_cana_insee_com
  AFTER INSERT OR UPDATE
  ON aep.canalisation
  FOR EACH ROW
  EXECUTE PROCEDURE aep.maj_cana_insee_com();




- Function: aep.maj_cana_insee_com()

-- DROP FUNCTION aep.maj_cana_insee_com();

CREATE OR REPLACE FUNCTION aep.maj_cana_insee_com()
  RETURNS trigger AS
$BODY$ 


BEGIN 
    IF NEW."geometrie" is not null then
    
    select co.com_insee_varchar, co.com_lib_maj from commune as co where st_intersects(st_line_interpolate_point(st_GeometryN(NEW."geometrie",1), 0.5), co.geometrie) into NEW."cana_insee", NEW."cana_commune";

    END IF;
    return NEW;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION aep.maj_cana_insee_com() OWNER TO postgres;

Erwan · Mon 07 July 2014 13:36

Je n ai pas de message d erreur sur le codage précédent mais rien ne se passe. Comme si la variable new restait vide.

Nicolas Ribot · Mon 07 July 2014 14:02

Bonjour,

Oui normal: vous avez changé la définition du trigger en AFTER INSERT OR UPDATE, au lieu de BEFORE:

Un trigger défini AFTER agit une fois que la donnée a ete insérée ou mise a jour en base, on ne peut donc plus toucher a la ligne concernée a travers NEW.

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 03 July 2014 12:20

[postgis] Récupération de valeur entre 2 couches

Code:

#2 Thu 03 July 2014 13:39

Re: [postgis] Récupération de valeur entre 2 couches

Code:

#3 Thu 03 July 2014 17:59

Re: [postgis] Récupération de valeur entre 2 couches

#4 Fri 04 July 2014 12:34

Re: [postgis] Récupération de valeur entre 2 couches

Code:

#5 Fri 04 July 2014 14:12

Re: [postgis] Récupération de valeur entre 2 couches

#6 Fri 04 July 2014 17:07

Re: [postgis] Récupération de valeur entre 2 couches

Code:

#7 Mon 07 July 2014 13:36

Re: [postgis] Récupération de valeur entre 2 couches

#8 Mon 07 July 2014 14:02

Re: [postgis] Récupération de valeur entre 2 couches

Pied de page des forums