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 !.
banniere

Le portail francophone de la géomatique


Toujours pas inscrit ? Mot de passe oublié ?
Nom d'utilisateur    Mot de passe              Toujours pas inscrit ?   Mot de passe oublié ?

Annonce

Suite à un problème technique intervenu entre le 22 et le 23 mars, nous avons du procéder dans la soirée du 25 mars, à la restauration de la base de données du 24 mars (matinée).

En clair, nous avons perdu vos contributions et inscriptions du dimanche 24 et du lundi 25 mars.
Nous vous prions de nous excuser.

#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: 1534

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

En 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

 

Pied de page des forums

Powered by FluxBB