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();
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.
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.
Can anyone help me with this?
أكثر...
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();
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.
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.
Can anyone help me with this?
أكثر...