Enforce PostGIS constraint that geometries do not overlap

I'm trying to figure out what is the most efficient way to create a constraint on a table that enforces the property that no two geometries within a table overlap with one another.

Is there an elegant way to enforce this with a table constraint, or would it be better to first just do a select query to check if the row to be inserted overlaps with any geometry already in the table. This doesn't give me the same confidence in the integrity of the table, but at least should be fast as you are only comparing a single geometry to an indexed set of geometries.

Answer

You can do this by creating a constraint against a user-defined function that checks for overlaps in your table. Here's an example:

CREATE TABLE my_data (

  id   int PRIMARY KEY,
  geom geometry
);

CREATE INDEX ON my_data USING gist(geom);

Now that the table exists, we can create a function to check for overlaps in it. I like to use ST_Relate for this, since it can check for what most of us mean by "overlaps". (ST_Overlaps reports false when one polygon is contained within another). Because we've created a spatial index, the insert check to insert n records will be O(n log n), not O(n^2). I've included an id check too, so that we won't check a new version of a row against an old version of a row if we run an UPDATE query.

CREATE FUNCTION no_overlaps_in_my_data(id int, g geometry)
RETURNS boolean AS $$
SELECT NOT EXISTS (

  SELECT 1 FROM my_data
  WHERE my_data.id != id
    AND my_data.geom && g 
    AND ST_Relate(my_data.geom, g, '2********'));
$$ LANGUAGE sql;

Now, we can create a constraint using this function:

ALTER TABLE my_data ADD CONSTRAINT no_overlaps CHECK (no_overlaps_in_my_data(id, geom));

And verify that it works:

INSERT INTO my_data VALUES (1, ST_Buffer(ST_MakePoint(1, 1), 1));
-- OK
INSERT INTO my_data VALUES (2, ST_Buffer(ST_MakePoint(3, 1), 1));
-- OK
INSERT INTO my_data VALUES (3, ST_Buffer(ST_MakePoint(2, 1), 1));
-- ERROR:  new row for relation "my_data" violates check constraint "no_overlaps"