I am implementing a postgis trigger on polygon layer to check and reject newly created polygon if it overlaps others.
The problem shows when I digitize new polygons adjacent to others in Quantum GIS with option "avoid intersections" in Settings -> Snapping Options:
- Sometimes Postgis allow me to save and some time it doesn't with a message saying "overlapping polygon detected" (programmed into trigger).
- Sometimes Postgis doesn't allow saving the first time but when I save again (same unmodified geometry), it's done without any notice.
My trigger function:
CREATE OR REPLACE FUNCTION check_overlapping() RETURNS TRIGGER AS
$BODY$
DECLARE
BEGIN
IF TG_OP = 'INSERT'
THEN
IF
(SELECT COUNT(*)
FROM
(SELECT id
FROM polygon_layer AS t
WHERE st_overlaps(NEW.geom, t.geom)) AS foo) > 0
THEN
RAISE EXCEPTION 'OPERATION ABORTED: overlapping polygons!';
RETURN NULL;
ELSE
RETURN NEW;
END IF;
ELSIF TG_OP = 'UPDATE'
THEN
IF
(SELECT COUNT(*)
FROM
(SELECT id
FROM polygon_layer AS t
WHERE st_overlaps(NEW.geom, t.geom)
AND (t.id <> OLD.id)) AS foo) > 0
THEN
RAISE EXCEPTION 'OPERATION ABORTED: overlapping polygons!';
RETURN NULL;
ELSE RETURN NEW;
END IF;
END IF;
END;
$BODY$
LANGUAGE plpgsql;
CREATE TRIGGER check_overlapping_trg
BEFORE INSERT OR UPDATE ON polygon_layer
FOR EACH ROW EXECUTE PROCEDURE check_overlapping();
My software version: Qgis 1.8.0, Postgis 2.0
Added June 24th, 2013:
Create table query:
CREATE TABLE polygon_layer
(
id serial primary key,
geom geometry(MultiPolygon,3857)
);
CREATE INDEX polygon_layer_idx
ON polygon_layer
USING gist
(geom);
Test data:
insert into polygon_layer (geom) select st_GeomFromEwkt('SRID=3857;MULTIPOLYGON(((11805973.8551485 1136129.8216546,11757217.3210366 1107981.71948687,11790391.87002 1102117.53153526,11823733.9672305 1116526.6790735,11801114.95656 1119207.45070852,11805973.8551485 1136129.8216546)))');
insert into polygon_layer (geom) select st_GeomFromEwkt('SRID=3857;MULTIPOLYGON(((11819880.3580052 1063581.43928182,11791397.1593831 1067602.59673435,11799483.2424957 1095075.79477947,11809995.0126011 1081844.19604541,11843504.6580388 1127249.76561359,11817272.092427 1129138.51033764,11832111.37859 1146852.90819468,11881203.0091563 1142329.10606058,11819880.3580052 1063581.43928182)))');
insert into polygon_layer (geom) select st_GeomFromEwkt('SRID=3857;MULTIPOLYGON(((11809995.0126011 1081844.19604541,11792995.1839403 1103242.5817722,11823733.9672305 1116526.6790735,11801114.95656 1119207.45070852,11804235.9486147 1130077.11269212,11843504.6580388 1127249.76561359,11809995.0126011 1081844.19604541)))');
Trigger behavior:
When run 3 insert queries, the first 2 will go smoothly but the 3rd will raise exception as defined in trigger function.
If I drop the trigger, insert all 3 polygons and check to find overlapping polygons with following query, I can not find anything:
select *
from polygon_layer as a
inner join
polygon_layer as b
on st_overlaps(a.geom, b.geom)
where a.id < b.id
