Suite

Comment utiliser les déclencheurs PostgreSQL/PostGIS en interaction dans QGIS pour créer plusieurs étiquettes sur des polygones ?


ÉDITER:

Les fonctions de déclenchement affichées sont bien faites mais il y a encore quelques problèmes. J'ai téléchargé deux enregistrements d'écran :

http://workupload.com/file/ygRnYtp9

http://workupload.com/file/OBY7i9Fh


J'utilise des géométries ponctuelles (table 'label_point') pour placer plusieurs étiquettes sur des géométries polygonales (table 'sol'). Après avoir modifié les couches, je dois mettre à jour les entités label_point affectées. J'ai donc créé une fonction de déclenchement.

CREATE TABLE label_point ( gid serial NOT NULL, geom geometry(point, SRID), label_sample varchar(255), CONSTRAINT label_point_pkey PRIMARY KEY (gid) ); CREATE TABLE sol ( gid serial NOT NULL, geom geometry(polygon, SRID), label varchar(255), CONSTRAINT soil_pkey PRIMARY KEY (gid) ); CRÉER OU REMPLACER LA FONCTION sample_label() RETURNS trigger AS $body$ BEGIN IF GeometryType(NEW.geom) = 'POINT' ALORS EXÉCUTER 'SELECT label FROM soil WHERE ST_Within($1, soil.geom) LIMIT 1' USING NEW.geom INTO NEW .label_sample; RETOUR NEUF ; ELSEIF GeometryType(NEW.geom) = 'POLYGON' PUIS EXECUTER 'UPDATE label_point SET label_sample = NULL WHERE ST_Within(label_point.geom, $1)' USING NEW.geom; RETOUR NEUF ; FIN SI; FINIR; $body$ LANGUE plpgsql; CREATE TRIGGER tg_sample_label AVANT L'INSERTION OU LA MISE A JOUR SUR label_point POUR CHAQUE LIGNE EXECUTER LA PROCEDURE sample_label(); CRÉER UN DÉCLENCHEUR tg_sample_label APRÈS L'INSERTION OU LA MISE À JOUR SUR le sol POUR CHAQUE LIGNE EXÉCUTER LA PROCÉDURE sample_label();

Malheureusement, cette solution souffre de deux problèmes.

1.) Lors de la suppression d'une entité de sol ou du déplacement d'une entité de sol (ST_Within(label_point.geom, soil.geom) = FALSE), les entités sample_point ne sont pas mises à jour sur NULL.

2.) Lors de la division d'une entité de sol à l'aide de l'outil QGIS 'Split Feature Tool' et de la modification de l'étiquette d'une partie de polygone, les entités label_point ne sont pas mises à jour correctement après l'enregistrement des modifications.

Est-ce que quelqu'un peut m'aider avec ça?


Vous pouvez écrire un déclencheur avant suppression et modifier vos déclencheurs d'insertion et de mise à jour comme dans mon exemple suivant. Le workflow fonctionne jusqu'à présent mais le code pourrait encore être "nettoyé" et optimisé pour éviter les récursions entre les différents déclencheurs… donc je poste mon code comme "work in progress" ;)

https://gist.github.com/neogis-de/a1d08c38d8b9c5d316c7

CREATE TABLE label_point ( gid serial NOT NULL, geom geometry(point, 3857), label_sample varchar(255), CONSTRAINT label_point_pkey PRIMARY KEY (gid) ); CREATE TABLE sol ( gid serial NOT NULL, geom geometry(polygon, 3857), label varchar(255), CONSTRAINT soil_pkey PRIMARY KEY (gid) ); ------------------------------------------ -- Déclencheur pour la couche de points CREATE OR REMPLACER LA FONCTION sample_label_point() RETOURNE LE DÉCLENCHEUR COMME $BODY$ DECLARE BEGIN relance la notification 'le déclencheur de point commence maintenant : %', now(); IF TG_OP = 'INSERT' ALORS IF (SELECT COUNT(*) FROM (SELECT t.gid FROM label_point AS t, sol AS s WHERE st_Within(NEW.geom, s.geom)) AS foo) > 0 ALORS SELECT Sol.label INTO NEW.label_sample FROM sol WHERE ST_Intersects(NEW.geom, soil.geom); augmenter la notification 'le déclencheur de point se termine maintenant : %', maintenant(); RETOUR NEUF ; AUTREMENT RELEVER l'avis « pas d'intersection » ; RAISE avis 'le déclencheur de point se termine maintenant : %', maintenant(); RETOUR NEUF ; FIN SI; ELSIF TG_OP = 'UPDATE' ALORS SI (ST_Equals(NEW.geom , OLD.geom)=FALSE) ALORS SI (SELECT COUNT(*) FROM (SELECT t.gid FROM label_point AS t, sol AS s WHERE st_Within(NEW.geom) , s.geom) AND (t.gid <> OLD.gid)) AS foo) > 0 ALORS SÉLECTIONNER sol.étiquette DANS NEW.label_sample FROM sol WHERE ST_Intersects(NEW.geom, sol.geom); RAISE Avis « Intersection trouvée ! » ; RETOUR NEUF ; ELSE SELECT NULL INTO NEW.label_sample; RETOUR NEUF ; augmenter la notification 'le déclencheur de point se termine maintenant : %', maintenant(); FIN SI; ELSE Raise Notice 'Mise à jour des données d'attributs' ; augmenter la notification 'le déclencheur de point se termine maintenant : %', maintenant(); Retour NOUVEAU ; FIN SI; FIN SI; FINIR; $BODY$ LANGUE plpgsql; CRÉER UN DÉCLENCHEUR label_point_trigger AVANT L'INSERTION OU LA MISE À JOUR SUR label_point POUR CHAQUE LIGNE EXÉCUTER LA PROCÉDURE sample_label_point(); ----------------------------------------- -- Déclencheur pour la couche de sol CRÉER OU REMPLACER FONCTION label_sol() RETOURNE LE DÉCLENCHEMENT COMME $BODY$ DECLARE new_label text := quote_ident(NEW.label); -- assigner à la déclaration BEGIN IF TG_OP = 'INSERT' ALORS lever la notification 'le déclencheur d'insertion de sol commence maintenant : %', now(); IF (SELECT COUNT(*) FROM (SELECT t.gid FROM label_point AS t, sol AS s WHERE st_Within(t.geom, NEW.geom)) AS foo) > 0 ALORS EXECUTE 'UPDATE label_point SET label_sample = $2 WHERE ST_Within( label_point.geom, $1)' USING NEW.geom, NEW.label; -- lever la notification 'le déclencheur de sol se termine maintenant : %', maintenant(); RETOUR NEUF ; AUTREMENT RAISE Avis 'pas d'intersection'; RETOUR NEUF ; FIN SI; ELSIF TG_OP = 'UPDATE' THEN lève la notification 'le déclencheur de mise à jour du sol commence maintenant : %', now(); IF (SELECT COUNT(*) FROM (SELECT t.gid FROM label_point AS t, sol AS s WHERE st_Within(t.geom, NEW.geom) --AND (t.gid <> OLD.gid) ) AS foo) > 0 ALORS EXÉCUTER 'UPDATE label_point SET label_sample = ' || quote_literal(NOUVEAU.label) || ' WHERE ST_Within(label_point.geom, $1)' USING NEW.geom; soulever la notification 'UPDATE label_point SET label_sample = % WHERE ST_Within(label_point.geom, %)', new_label, NEW.geom; lever la notification'Étiquette trouvée : %', NOUVELLE.étiquette ; RAISE Avis « Intersection trouvée ! » ; RETOUR NEUF ; ELSE EXECUTE 'UPDATE label_point SET label_sample = NULL WHERE ST_Within(label_point.geom, $2)' USING NEW.geom, OLD.geom; RELEVER NOTICE 'pas d'intersection (plus) d'entité avec gid=%', NEW.gid ; RETOUR NEUF ; FIN SI; FIN SI; RAISE NOTICE 'Le déclenchement du sol se termine maintenant : %', now(); FINIR; $BODY$ LANGUE plpgsql; CRÉER LE DÉCLENCHEUR label_soil_trigger AVANT L'INSERTION OU LA MISE À JOUR SUR le sol POUR CHAQUE LIGNE EXÉCUTER LA PROCÉDURE SOL_label(); -------------------------------------------------- ---- -- Supprimer le déclencheur CREATE OR REPLACE FUNCTION public.before_delete_soil() RETURNS trigger AS $BODY$ BEGIN RAISE NOTICE 'Trigger % of table % is active % % for record %', TG_NAME, TG_RELNAME, TG_WHEN, TG_OP, OLD .étiqueter; AVIS D'AVIS 'L'étiquette % a été supprimée pour le point avec gid=%', OLD.label, OLD.gid ; MISE À JOUR label_point SET label_sample = NULL WHERE ST_Within(label_point.geom, OLD.geom); RETOUR VIEUX; FINIR; $BODY$ LANGUE plpgsql VOLATILE COST 100 ; ALTER FUNCTION public.before_delete_soil() PROPRIÉTAIRE À postgres; CREATE TRIGGER trigger_before_delete_soil AVANT DE SUPPRIMER SUR public.soil POUR CHAQUE LIGNE EXECUTER LA PROCEDURE public.before_delete_soil();

EDIT1 : Code mis à jour/nettoyé :

J'ai nettoyé le code et intégré une vérification pour empêcher l'exécution récursive du déclencheur. Maintenant, ça marche assez vite :

COMMENCER; -------------------------------------------------- ----- -- CREATE test tables CREATE TABLE label_point ( gid serial NOT NULL, geom geometry(point, 3857), label_sample varchar(255), CONSTRAINT label_point_pkey PRIMARY KEY (gid) ); CREATE TABLE sol ( gid serial NOT NULL, geom geometry(polygon, 3857), label varchar(255), CONSTRAINT soil_pkey PRIMARY KEY (gid) ); -------------------------------------------------- ----- -- Fonction de déclenchement pour la couche label_point CREATE OR REPLACE FUNCTION sample_label_point() RETURNS TRIGGER AS $BODY$ DECLARE BEGIN IF TG_OP = 'INSERT' ALORS IF (SELECT COUNT(*) FROM (SELECT t.gid FROM label_point AS t, sol AS s WHERE st_Within(NEW.geom, s.geom)) AS foo) > 0 ALORS SÉLECTIONNER sol.label DANS NEW.label_sample FROM sol WHERE ST_Intersects(NEW.geom, sol.geom); RETOUR NEUF ; AUTREMENT RETOURNER NOUVEAU; FIN SI; ELSIF TG_OP = 'UPDATE' ALORS SI (ST_Equals(NEW.geom , OLD.geom)=FALSE) ALORS SI (SELECT COUNT(*) FROM (SELECT t.gid FROM label_point AS t, sol AS s WHERE st_Within(NEW.geom) , s.geom) ) AS foo) > 0 ALORS SELECTIONNER sol.étiquette DANS NEW.étiquette_échantillon FROM sol WHERE ST_Intersects(NEW.geom, sol.geom); RETOUR NEUF ; ELSE SELECT NULL INTO NEW.label_sample; RETOUR NEUF ; FIN SI; AUTRE Retour NOUVEAU; FIN SI; FIN SI; FINIR; $BODY$ LANGUE plpgsql; -------------------------------------------------- ----- -- CRÉER UN TRIGGER pour la couche label_point CRÉER UN TRIGGER label_point_trigger_insert AVANT L'INSERTION SUR public.label_point POUR CHAQUE RANGÉE EXÉCUTER LA PROCÉDURE public.sample_label_point(); CRÉER LE DÉCLENCHEUR label_point_trigger_update AVANT LA MISE À JOUR DE geom SUR public.label_point POUR CHAQUE LIGNE QUAND (OLD.geom EST DISTINCT DE NEW.geom) EXÉCUTER LA PROCÉDURE public.sample_label_point(); -------------------------------------------------- ----- -- Fonction de déclenchement pour la couche label_sol CRÉER OU REMPLACER LA FONCTION label_sol() RETOURNE LE DÉCLENCHEMENT COMME $BODY$ DECLARE new_label text := quote_ident(NEW.label); -- affecter à la déclaration BEGIN IF TG_OP = 'INSERT' THEN IF (SELECT COUNT(*) FROM (SELECT t.gid FROM label_point AS t, sol AS s WHERE st_Within(t.geom, NEW.geom)) AS foo) > 0 ALORS EXÉCUTER 'UPDATE label_point SET label_sample = $2 WHERE ST_Within(label_point.geom, $1)' USING NEW.geom, NEW.label; RETOUR NEUF ; AUTREMENT RETOURNER NOUVEAU; FIN SI; ELSIF TG_OP = 'UPDATE' THEN IF (SELECT COUNT(*) FROM (SELECT t.gid FROM label_point AS t, sol AS s WHERE st_Within(t.geom, NEW.geom) ) AS foo) > 0 ALORS EXECUTE 'UPDATE label_point SET label_sample = ' || quote_literal(NOUVEAU.label) || ' WHERE ST_Within(label_point.geom, $1)' USING NEW.geom; RETOUR NEUF ; ELSE EXECUTE 'UPDATE label_point SET label_sample = NULL WHERE ST_Within(label_point.geom, $2)' USING NEW.geom, OLD.geom; RETOUR NEUF ; FIN SI; FIN SI; FINIR; $BODY$ LANGUE plpgsql; -------------------------------------------------- ----- -- CRÉER UN DÉCLENCHEUR pour la couche de sol CRÉER UN DÉCLENCHEUR label_soil_trigger_insert AVANT L'INSERTION SUR public.soil POUR CHAQUE LIGNE EXÉCUTER LA PROCÉDURE public.soil_label(); CRÉER LE DÉCLENCHEUR label_soil_trigger_update AVANT LA MISE À JOUR DE geom SUR public.soil POUR CHAQUE LIGNE QUAND (OLD.geom EST DISTINCT DE NEW.geom) EXÉCUTER LA PROCÉDURE public.soil_label(); -------------------------------------------------- ---- -- Supprimer le déclencheur CREATE OR REPLACE FUNCTION public.before_delete_soil() RETURNS trigger AS $BODY$ BEGIN UPDATE label_point SET label_sample = NULL WHERE ST_Within(label_point.geom, OLD.geom); RETOUR VIEUX; FINIR; $BODY$ LANGUE plpgsql VOLATILE COST 100 ; CREATE TRIGGER trigger_before_delete_soil AVANT DE SUPPRIMER SUR public.soil POUR CHAQUE LIGNE EXECUTER LA PROCEDURE public.before_delete_soil(); S'ENGAGER;

https://gist.github.com/neogis-de/27bcf7ee4f36a93fd62e


Une autre façon de gérer cela consiste à utiliser une vue pour les étiquettes. En fonction de la taille de votre ensemble de données, bien sûr, à mesure que vos données deviennent très volumineuses (plus de 10 000 fonctionnalités dans chaque table), elles ralentiront.

Voici donc ce que vous pourriez avoir :

  1. table label_point (modifiable)
  2. table des sols (modifiable)
  3. v_label_soil view (peut-être modifiable ?)

La vue peut être quelque chose comme :

sélectionnez att1, att2, s.att1 l.geom à partir de label_point l joignez les sols s sur ST_Within(l.geom, s.geom)


Voir la vidéo: Utilisation de QField avec PostgreSQLPostgis, configuration dans QGIS (Octobre 2021).