# 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.

• Just because individual geometries don't overlap doesn't guarantee integrity -- What if they exist where they shouldn't, or if gaps exist where they shouldn't. Please provide your trigger function for implementing the constraint. Feb 8, 2018 at 15:25
• @Vince ...independent of how strict the integrity is enforced, isn't one going to need to use triggers instead of 'real' constraints anyways (i.e. no means to compare new geom to existing within table/column constraints)? Matt, could that be an answer to your question? Feb 8, 2018 at 16:00
• You can also use a constraint that references a user-defined function that checks for overlaps. Feb 8, 2018 at 16:19
• @dbaston, could you elaborate on this? I'd like to avoid doing an O(n^2) operation to check that all rows are non overlapping with all other rows every time an insertion is performed. The ideal scenario would be, we know that the rows are pairwise non-overlapping and then when we perform the insert, in order to preserve the non-overlapping invariant all we need to do is check that the row to be inserted does not overlap with any existing rows (i.e. avoid doing the pairwise overlap check for all other rows already existing in the table).
– Matt
Feb 8, 2018 at 17:37

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"
``````
• Very nice approach, much clearer than a trigger. Feb 8, 2018 at 18:46
• Yep, this is exactly what I was looking for. Thanks!
– Matt
Feb 8, 2018 at 20:33
• nice one, indeed! Feb 8, 2018 at 22:01
• @dbaston: is it possible to modify this constraint to be a unique constraint, such that it can be used for `ON CONFLICT` on `INSERT`. See my question: gis.stackexchange.com/questions/372001/… Aug 20, 2020 at 19:27
• Has something changed in later versions of postgres? I'm only able to get the overlap check to throw the error if the id check is removed `WHERE my_data.id != id`. But as @dbaston mentioned above, it then throws the error whenever performing an `UPDATE`. Aug 24, 2023 at 4:00

You also have the exclusion constraint in PostgreSQL for this purpose.