postgis_no_overlap_constraint.sql

DROP TABLE if EXISTS boundaries;

CREATE TABLE boundaries (
    id BIGSERIAL primary key,
	geometry geometry(multipolygon, 4326) NOT null
);

CREATE INDEX idx_boundaries_geometry
ON boundaries
USING gist (geometry);


CREATE or replace FUNCTION boundaries_no_overlap()
RETURNS trigger
LANGUAGE plpgsql AS
$$
BEGIN
  -- To avoid concurrent insert/update, lock the table but allow read access
  LOCK boundaries IN SHARE MODE;
  IF (
    SELECT count(1)
    FROM boundaries as b
    WHERE b.id <> new.id
    AND ST_Overlaps(b.geometry, new.geometry)
  ) > 0
  THEN
    RAISE EXCEPTION 'This field geometry is invalid, overlaps existing boundaries(s)';
  END IF;
  RETURN NEW;
END;
$$;
 
CREATE TRIGGER boundaries_overlap_check
BEFORE INSERT OR UPDATE
ON boundaries
FOR EACH ROW EXECUTE PROCEDURE boundaries_no_overlap();

------------------------------------------------
-- TESTING

------------------------------------------------
-- GOOD

insert into boundaries (geometry) 
values ('MULTIPOLYGON(((10 20, 20 20, 20 10, 10 10, 10 20)))');

-- to the right touches but does not overlap
insert into boundaries (geometry) 
values ('MULTIPOLYGON(((20 20, 30 20, 30 10, 20 10, 20 20)))');

-- update to nudge it right
update boundaries
set geometry = 'MULTIPOLYGON(((21 20, 31 20, 31 10, 21 10, 21 20)))'
where id = 2;

------------------------------------------------
-- BAD, overlaping

insert into boundaries (geometry) 
values ('MULTIPOLYGON(((22 20, 32 20, 32 10, 22 10, 22 20)))');

update boundaries
set geometry = 'MULTIPOLYGON(((22 20, 32 20, 32 10, 22 10, 22 20)))'
where id = 1;