[GIS] How to use interacting PostgreSQL/PostGIS triggers in QGIS to create multiple labels on polygons

labelingpostgispostgresqlqgistrigger

EDIT:

The trigger functions posted are well done but there are still a few issues. I've uploaded two screen recordings:

http://workupload.com/file/ygRnYtp9

http://workupload.com/file/OBY7i9Fh

I use point geometries (table 'label_point') to place multiple labels on polygon geometries (table 'soil'). After doing layer edits I've to update the affected label_point features. Therefore I've created a trigger function.

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 soil (
gid serial NOT NULL,
geom geometry(polygon, SRID),
label varchar(255),
CONSTRAINT soil_pkey PRIMARY KEY (gid)
);

CREATE OR REPLACE FUNCTION sample_label()
RETURNS trigger AS $body$
    BEGIN
    IF GeometryType(NEW.geom) = 'POINT' THEN
        EXECUTE 'SELECT label FROM soil WHERE ST_Within($1, soil.geom) LIMIT 1'
        USING NEW.geom 
        INTO NEW.label_sample;
        RETURN NEW;
    ELSEIF GeometryType(NEW.geom) = 'POLYGON' THEN
        EXECUTE 'UPDATE label_point SET label_sample = NULL WHERE ST_Within(label_point.geom, $1)'
        USING NEW.geom;
        RETURN NEW;
    END IF;
    END;
$body$ LANGUAGE plpgsql;

CREATE TRIGGER tg_sample_label BEFORE INSERT OR UPDATE
ON label_point FOR EACH ROW
EXECUTE PROCEDURE sample_label();


CREATE TRIGGER tg_sample_label AFTER INSERT OR UPDATE
ON soil FOR EACH ROW
EXECUTE PROCEDURE sample_label();

enter image description here

Unfortunately this solution suffers from two issues.

1.) When deleting a soil feature or moving a soil feature (ST_Within(label_point.geom, soil.geom) = FALSE) the sample_point features are not updated to NULL.

enter image description here
enter image description here

2.) When splitting a soil feature using the QGIS 'Split Feature Tool' and changing the label of one polygon part the label_point features are not updated proberly after saving the edits.

enter image description here
enter image description here
enter image description here

Can anyone help me with this?

Best Answer

You could write a before-delete trigger and modify your insert and update-trigger like my following example. The workflow works so far but the code could still be "cleaned" and optimized to prevent recursions between the different triggers... so I post my code as "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 soil (
gid serial NOT NULL,
geom geometry(polygon, 3857),
label varchar(255),
CONSTRAINT soil_pkey PRIMARY KEY (gid)
);


------------------------------------------

-- Trigger for point Layer

CREATE OR REPLACE FUNCTION sample_label_point() RETURNS TRIGGER AS
$BODY$
DECLARE
BEGIN
raise notice 'point trigger starts now: %', now();
IF TG_OP = 'INSERT'
THEN
  IF
    (SELECT COUNT(*)
     FROM
       (SELECT t.gid
        FROM label_point AS t,
        soil AS s
        WHERE st_Within(NEW.geom, s.geom)) AS foo) > 0
  THEN
    SELECT soil.label
INTO NEW.label_sample
FROM soil
WHERE ST_Intersects(NEW.geom, soil.geom);
raise notice 'point trigger ends now: %', now();
    RETURN NEW; 
  ELSE
    RAISE notice 'no intersection';
    RAISE notice 'point trigger ends now: %', now();
    RETURN NEW;
  END IF;
ELSIF TG_OP = 'UPDATE'
THEN
IF
(ST_Equals(NEW.geom , OLD.geom)=FALSE)
THEN

  IF
    (SELECT COUNT(*)
     FROM
       (SELECT t.gid
        FROM label_point AS t,
        soil AS s
        WHERE st_Within(NEW.geom, s.geom)
        AND (t.gid <> OLD.gid)) AS foo) > 0 
  THEN
SELECT soil.label
INTO NEW.label_sample
FROM soil
WHERE ST_Intersects(NEW.geom, soil.geom);
    RAISE Notice 'Intersection found!';
    RETURN NEW; 

  ELSE 
  SELECT NULL
    INTO NEW.label_sample;
  RETURN NEW; 
  raise notice 'point trigger ends now: %', now();
  END IF; 
ELSE
Raise Notice 'Update of attribute data';
raise notice 'point trigger ends now: %', now();
Return NEW;
END IF;
END IF; 
END; 
$BODY$ 
LANGUAGE plpgsql;


CREATE TRIGGER label_point_trigger
BEFORE INSERT OR UPDATE ON label_point
FOR EACH ROW EXECUTE PROCEDURE sample_label_point();

-----------------------------------------

-- Trigger for Soil Layer

CREATE OR REPLACE FUNCTION soil_label() RETURNS TRIGGER AS
$BODY$
DECLARE
new_label text := quote_ident(NEW.label);  -- assign at declaration
BEGIN
IF TG_OP = 'INSERT'
THEN
raise notice 'soil insert-trigger starts now: %', now();
  IF
    (SELECT COUNT(*)
     FROM
       (SELECT t.gid
        FROM label_point AS t,
        soil AS s
        WHERE st_Within(t.geom, NEW.geom)) AS foo) > 0
  THEN
    EXECUTE 'UPDATE label_point SET label_sample = $2 WHERE ST_Within(label_point.geom, $1)'
        USING NEW.geom, NEW.label;
--   raise notice 'soil trigger ends now: %', now();
    RETURN NEW; 
  ELSE
    RAISE Notice 'no intersection';
    RETURN NEW;
  END IF;
ELSIF TG_OP = 'UPDATE'
THEN
raise notice 'soil update-trigger starts now: %', now(); 
  IF
    (SELECT COUNT(*)
     FROM
       (SELECT t.gid
        FROM label_point AS t,
        soil AS s
        WHERE st_Within(t.geom, NEW.geom)
        --AND (t.gid <> OLD.gid)
        ) 
        AS foo) > 0 
  THEN
EXECUTE 'UPDATE label_point SET label_sample = ' ||  quote_literal(NEW.label)  || ' WHERE ST_Within(label_point.geom, $1)'
        USING NEW.geom;

   raise notice 'UPDATE label_point SET label_sample = % WHERE ST_Within(label_point.geom, %)', new_label, NEW.geom;

   raise notice'Label found: %', NEW.label;
    RAISE Notice 'Intersection found!';
    RETURN NEW; 

  ELSE 
  EXECUTE 'UPDATE label_point SET label_sample = NULL WHERE ST_Within(label_point.geom, $2)'
        USING NEW.geom, OLD.geom;
  RAISE NOTICE 'no intersection (anymore) of feature with gid=%', NEW.gid;
  RETURN NEW; 
  END IF; 
END IF; 
RAISE NOTICE 'Soil-trigger ends now: %', now();
END; 
$BODY$ 
LANGUAGE plpgsql;

CREATE TRIGGER label_soil_trigger
BEFORE INSERT OR UPDATE ON soil
FOR EACH ROW EXECUTE PROCEDURE soil_label();



------------------------------------------------------

-- Delete Trigger

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.label;
 RAISE NOTICE 'Label % was deleted for Point with gid=%', 
               OLD.label, OLD.gid;
 UPDATE label_point SET label_sample = NULL WHERE ST_Within(label_point.geom, OLD.geom);
 RETURN OLD;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION public.before_delete_soil()
  OWNER TO postgres;


CREATE TRIGGER trigger_before_delete_soil
  BEFORE DELETE
  ON public.soil
  FOR EACH ROW
  EXECUTE PROCEDURE public.before_delete_soil();

EDIT1: Updated/Cleaned Code:

I have cleaned the code and build in one check to prevent recursive execution of the trigger. Now it works pretty fast:

BEGIN;

-------------------------------------------------------

-- 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 soil (
gid serial NOT NULL,
geom geometry(polygon, 3857),
label varchar(255),
CONSTRAINT soil_pkey PRIMARY KEY (gid)
);


-------------------------------------------------------

-- Trigger function for label_point layer

CREATE OR REPLACE FUNCTION sample_label_point() RETURNS TRIGGER AS
$BODY$
DECLARE
BEGIN
IF TG_OP = 'INSERT'
THEN
  IF
    (SELECT COUNT(*)
     FROM
       (SELECT t.gid
        FROM label_point AS t,
        soil AS s
        WHERE st_Within(NEW.geom, s.geom)) AS foo) > 0
  THEN
    SELECT soil.label
INTO NEW.label_sample
FROM soil
WHERE ST_Intersects(NEW.geom, soil.geom);
    RETURN NEW; 
  ELSE
    RETURN NEW;
  END IF;
ELSIF TG_OP = 'UPDATE'
THEN
IF
(ST_Equals(NEW.geom , OLD.geom)=FALSE)
THEN
  IF
    (SELECT COUNT(*)
     FROM
       (SELECT t.gid
        FROM label_point AS t,
        soil AS s
        WHERE st_Within(NEW.geom, s.geom)
        ) AS foo) > 0 
  THEN
SELECT soil.label
INTO NEW.label_sample
FROM soil
WHERE ST_Intersects(NEW.geom, soil.geom);
    RETURN NEW; 
  ELSE 
  SELECT NULL
    INTO NEW.label_sample;
  RETURN NEW; 
  END IF; 
ELSE
Return NEW;
END IF;
END IF; 
END; 
$BODY$ 
LANGUAGE plpgsql;

-------------------------------------------------------

-- CREATE TRIGGER for label_point layer

 CREATE TRIGGER label_point_trigger_insert
  BEFORE INSERT 
  ON public.label_point
  FOR EACH ROW
  EXECUTE PROCEDURE public.sample_label_point();

    CREATE TRIGGER label_point_trigger_update
  BEFORE UPDATE OF geom
  ON public.label_point
  FOR EACH ROW
    WHEN (OLD.geom IS DISTINCT FROM NEW.geom) 
  EXECUTE PROCEDURE public.sample_label_point();


-------------------------------------------------------

-- Trigger function for soil_label layer

CREATE OR REPLACE FUNCTION soil_label() RETURNS TRIGGER AS
$BODY$
DECLARE
new_label text := quote_ident(NEW.label);  -- assign at declaration
BEGIN
IF TG_OP = 'INSERT'
THEN
  IF
    (SELECT COUNT(*)
     FROM
       (SELECT t.gid
        FROM label_point AS t,
        soil AS s
        WHERE st_Within(t.geom, NEW.geom)) AS foo) > 0
  THEN
    EXECUTE 'UPDATE label_point SET label_sample = $2 WHERE ST_Within(label_point.geom, $1)'
        USING NEW.geom, NEW.label;
    RETURN NEW; 
  ELSE
    RETURN NEW;
  END IF;
ELSIF TG_OP = 'UPDATE'
THEN
  IF
    (SELECT COUNT(*)
     FROM
       (SELECT t.gid
        FROM label_point AS t,
        soil AS s
        WHERE st_Within(t.geom, NEW.geom)
        ) 
        AS foo) > 0 
  THEN
EXECUTE 'UPDATE label_point SET label_sample = ' ||  quote_literal(NEW.label)  || ' WHERE ST_Within(label_point.geom, $1)'
        USING NEW.geom;
    RETURN NEW; 
  ELSE 
  EXECUTE 'UPDATE label_point SET label_sample = NULL WHERE ST_Within(label_point.geom, $2)'
        USING NEW.geom, OLD.geom;
  RETURN NEW; 
  END IF; 
END IF; 
END; 
$BODY$ 
LANGUAGE plpgsql;


-------------------------------------------------------

-- CREATE TRIGGER for soil layer


CREATE TRIGGER label_soil_trigger_insert
  BEFORE INSERT
  ON public.soil
  FOR EACH ROW
  EXECUTE PROCEDURE public.soil_label();


CREATE TRIGGER label_soil_trigger_update
  BEFORE UPDATE OF geom
  ON public.soil
  FOR EACH ROW
  WHEN (OLD.geom IS DISTINCT FROM NEW.geom) 
  EXECUTE PROCEDURE public.soil_label();



------------------------------------------------------

-- Delete Trigger

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);
 RETURN OLD;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

CREATE TRIGGER trigger_before_delete_soil
  BEFORE DELETE
  ON public.soil
  FOR EACH ROW
  EXECUTE PROCEDURE public.before_delete_soil();

  COMMIT;

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

Related Question