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.
    – Vince
    Feb 8, 2018 at 15:25
  • 1
    @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?
    – geozelot
    Feb 8, 2018 at 16:00
  • 1
    You can also use a constraint that references a user-defined function that checks for overlaps.
    – dbaston
    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

3 Answers 3


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 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!
    – geozelot
    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.

See at the bottom of this page: https://www.postgresql.org/docs/9.6/static/ddl-constraints.html

and https://www.postgresql.org/docs/9.6/static/sql-createtable.html#SQL-CREATETABLE-EXCLUDE

I haven't tested it myself and don't know if it works beyond bounding box comparing.

  • 1
    Exclusion constraints need the relationship to be expressed by an operator. PostGIS doesn't include an operator for geometry overlap, though it would be possible to define your own.
    – dbaston
    Feb 8, 2018 at 19:12

I just tried your function and it works for new objects. But if I want to Change a node of an existing object, it throws an error. I think it's because the object already exists. If I move an existing object far away it throws no error, probably because it's not within it's own old geometry. How could I make the function run only to other objects and not the changed object itself?

I hope you understand what I meen


  • See edits above
    – dbaston
    Mar 2, 2018 at 19:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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