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);
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)))');
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