Annonce
L'appel à participation est ouvert jusqu'au 19 janvier 2025!
#1 Fri 12 August 2022 11:23
- tweaxy
- Participant actif
- Lieu: Abbeville
- Date d'inscription: 27 Dec 2018
- Messages: 76
UPDATE attributs sur TRIGGER AFTER INSERT OR UPDATE
Bonjour,
Nous travaillons sur des données des friches de notre collectivité.
Deux attributs sont présents :
- surf_tup : Surface du foncier
- idpar_tup : Liste des IDU des parcelles constituant la friche
Je souhaiterais donc mettre à jour ces informations de façon automatique.
Cela passe par une comparaison spatiale donc demande un minimum de temps. J'utilise un ST_PointOnSurface pour ne prendre uniquement les véritables parcelles qui constituent la friche et non celles voisines en plus.
J'ai d'abord mis en place le process sur un TRIGGER BEFORE ISNERT OR UPDATE qui fonctionne mais qui est extrêmement long.
J'aurais donc souhaité passer sur un AFTER INSERT OR UPDATE en pensant diminuer le temps d'enregistrement pour l'utilisateur.
J'ai donc mis en place ceci :
CREATE FUNCTION schema.table_saisi_auto()
RETURNS trigger
LANGUAGE 'plpgsql'
COST 100
VOLATILE NOT LEAKPROOF
AS $BODY$
BEGIN
-- champs automatisés
NEW.sit_datmaj = current_date;
-- CALCUL SOMME SURFACE CADASTRE DES PARCELLES QUI SONT CONCERNEES PAR LA FRICHE (INTERSECT FRICHE AVEC POINT ON SURFACE PARCELLE)
NEW.surf_tup = SUM("SUPF")
FROM cadastre."PARCELLE" par
WHERE ST_Intersects(ST_PointOnSurface(NEW.geom),par."GEOM");
-- LISTE LES PARCELLES QUI SONT DANS CETTE FRICHE
NEW.idpar_tup = string_agg(parc."IDU", ', '::text)
FROM cadastre."PARCELLE" parc
WHERE ST_Intersects(ST_PointOnSurface(parc."GEOM"), NEW.geom);
RETURN NEW;
END;
$BODY$;
-- CREATION DES TRIGGERS
CREATE TRIGGER schema_table_control_saisi
BEFORE INSERT OR UPDATE
ON schema.table
FOR EACH ROW
EXECUTE PROCEDURE schema.table_control_saisi();
CREATE TRIGGER table_saisi_auto
AFTER INSERT OR UPDATE
ON schema.table
FOR EACH ROW
EXECUTE PROCEDURE schema.table_saisi_auto();
Aucune erreur lors de l'enregistrement d'une modification géométrique sous QGIS, mais temps de chargement très long + ne se passe rien dans les 2 champs en question.
--> Egalement, comment optimiser la mise à jour de ces informations ? Le cadastre utilisé est sur 466 communes et nous travaillons sur 39 communes. Définir une table spécifique des parcelles (avec le millésime) uniquement sur ces 39 communes me ferait-il gagner un temps non négligeable ? Le trigger ne devrait plus à passer toutes les parcelles mais beaucoup moins, non ?
--> Je peux aussi passer par une vue qui reconstitue ces deux informations (et n'ayant donc plus besoin de les faire disposer sur ma table), mais sera-t-elle plus performante ?
Bien Cordialement,
Léandre Béron
Hors ligne
#2 Fri 12 August 2022 17:36
- Nicolas Ribot
- Membre
- Lieu: Toulouse
- Date d'inscription: 9 Sep 2005
- Messages: 1554
Re: UPDATE attributs sur TRIGGER AFTER INSERT OR UPDATE
Bonjour,
Comme vous l'avez vu, BEFORE ou AFTER ne change pas le temps d'execution d'un trigger: normal: une requete/transaction est finie quand le trigger est entièrement fini.
Un trigger BEFORE sert à changer une valeur de la table sur laquelle agit le trigger (on veut faire le changement BEFORE l'écriture de nouvelles données)
Un trigger AFTER sert à changer une ou des valeurs d'autres tables que la table sur laquelle le trigger agit.
Dans votre cas, vous modifiez les champs de la table source, il faut un BEFORE trigger (d'ailleurs, rien ne se passe dans le cas du AFTER: c'est trop tard pour modifier NEW, la table est déjà écrite quand le trigger agit).
Concernant la perf, vous avez des index spatiaux sur les tables (cadastre."PARCELLE" notamment) ? comme vous utilisez st_pointOnSurface sur les geom des deux tables, il faut que vous créiez des index fonctionnels sur st_pointOnSurface(geom) pour les deux tables:
Code:
create index on schema.table using gist(st_pointOnSurface(geom));
create index on cadastre."PARCELLE" using gist(st_pointOnSurface("GEOM"));
vacuum analyze schema.table;
vacuum analyze cadastre."PARCELLE";Vérifiez l'usage des index en faisant un explain <query> et en utilisant comme <query> la même que vous utilisez dans un trigger.
Normalement, ca devrait aller assez vite de trouver des parcelles contenant des points.
(je vous conseille d'utiliser des minuscule pour les noms des tables et colonnes: plus simple à utiliser, moins source d'erreurs)
Nicolas
Hors ligne
#3 Tue 16 August 2022 08:52
- tweaxy
- Participant actif
- Lieu: Abbeville
- Date d'inscription: 27 Dec 2018
- Messages: 76
Re: UPDATE attributs sur TRIGGER AFTER INSERT OR UPDATE
Bonjour,
Merci pour ces éléments et l'utilisation d'index sur la fonction st_PointOnSurface(geom), je n'avais pas connaissance de cette possibilité.
J'avais bien des index sur mes geom mais non sur la fonction utilisée.
Je vais mettre en place cela et vérifier leur usage.
Cordialement,
Léandre Béron
Hors ligne
