3

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 '18 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? – ThingumaBob Feb 8 '18 at 16:00
  • 1
    You can also use a constraint that references a user-defined function that checks for overlaps. – dbaston Feb 8 '18 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 '18 at 17:37
7

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. – John Powell Feb 8 '18 at 18:46
  • Yep, this is exactly what I was looking for. Thanks! – Matt Feb 8 '18 at 20:33
  • nice one, indeed! – ThingumaBob Feb 8 '18 at 22:01
1

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 '18 at 19:12
-1

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

Beate

  • See edits above – dbaston Mar 2 '18 at 19:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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