7

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?

  • You could add a foreign key to your label point records referring back tot he polygon they match. That would help you with the label points no longer being inside the polygon – Peter Horsbøll Møller Sep 1 '15 at 6:23
  • I want to use a spatial join (st_wihtin). – Lunar Sea Sep 7 '15 at 11:16
  • Yeah, but you can't use a spatial join if the polygon has been moved so that it no longer covers the label points – Peter Horsbøll Møller Sep 7 '15 at 13:01
  • But you could of course use ST_Within in conjunction with the "old" polygon to find the points that were within before the change – Peter Horsbøll Møller Sep 7 '15 at 13:27
  • Thanks Peter, can you please help me with the function? – Lunar Sea Sep 8 '15 at 19:41
7
+50

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

  • Seems that everything is working fine, thank you! I've to study your trigger function the next days. It's more complex that the one I've posted. – Lunar Sea Sep 12 '15 at 8:07
  • I just saw that you opened a bounty for this question. What is not working yet for you? – Thomas B Nov 9 '15 at 16:02
  • Your code is quite complex, not sure if it can be "cleaned and optimized". My trigger function has only a few lines of code. Well, it isn't working as it should :/ – Lunar Sea Nov 9 '15 at 21:20
  • I posted cleaned/optimized code above. I think with a few lines of code you won't be able to get all the functionality you are looking for ;) – Thomas B Nov 12 '15 at 14:57
  • I noticed that the code has two issues: 1.) 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. 2.) Every time when saving layer edits in QGIS the saving process takes longer. After saving a few times QGIS crashes. I suppose it's a QGIS bug. – Lunar Sea Nov 22 '15 at 14:53
2

Another way to handle this is to use a view for the labels. Depending on how big your dataset is, of course, as your data gets very big (10,000+ features in each table) then it will slow down.

So here's what you could have:

  1. label_point table (editable)
  2. soils table (editable)
  3. v_label_soil view (might be editable?)

The view can be something like:

select 
    att1, 
    att2,
    s.att1
    l.geom 
from
    label_point l
join soils s on ST_Within(l.geom, s.geom)
  • Thanks Alex, I've already tried to create a view but QGIS gets quite slow when doing this. There are a few thousands features in the lable_point table. – Lunar Sea Nov 11 '15 at 11:31
  • Ah, ok @LunarSea. I guess triggers are important then. Note that you'll need to do a check of all points each time you change the polygons, since, as someone else above eluded to, your polygon changed and it's new area might not encompass points that were previously in it. – Alex Leith Nov 11 '15 at 20:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.