Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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
share|improve this question
    
Can you provide an example of what geom triggers the exception? And are you inserting geometries one at a time? –  Jakub Kania Jun 22 '13 at 22:27
    
@JakubKania: Thanks for your response! I added information in the question. –  Cao Minh Tu Jun 24 '13 at 8:19
    
Your second query searches for identical polygons. Use the same function you used before. –  Jakub Kania Jun 24 '13 at 12:02
    
@JakubKania: fixed my second query. I must have used the wrong query for all that time. After fixing my query, I discovered every once in a while, Qgis will generate a polygon that will overlap others, even with "Avoid intersections" turned on. I guess this is a rounding problem of newly generated polygons. –  Cao Minh Tu Jun 25 '13 at 1:07
    
It's quite possible that's just a diffrence cause by diffrent method used by the two systems. You could try using ST_Buffer with a small negative value to trim around the edges. –  Jakub Kania Jun 25 '13 at 19:28
show 1 more comment

1 Answer

up vote 0 down vote accepted

This is due to a bug in the GEOS library and is being discussed here.

Until this bug is fixed in Qgis code, the user can avoid it by only crosses boundaries on vertices (digitize a snapped vertex on an existing one on boundary) while digitizing polygons.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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