To forbid overlappings in geometry (polygons) using a function or constraint or rule

hi,
does anybody know how you can forbid in the database to save a geometry wich overlaps another geometry in my table? In my table should be allowed st_disjoint and st_touches only. I think maybe a constraint or function or rule could manage this?
I am not looking for a SELECT to find overlapping geometries!
I want to avoid to insert a new object in mytable wich overlays area of another object. And I want to avoid to update a geometry in mytable wich would overlap an existing object after save.

CREATE TABLE myscheme.mytable
(
gid serial NOT NULL,
object bigint,
the_geom geometry(MultiPolygon,25832),
CONSTRAINT mytable_pkey PRIMARY KEY (gid)
)
WITH (
OIDS=FALSE
);
thanks, Beate

you can use a exclude-constraint:


test=# CREATE TABLE mytable (id serial, the_geom geometry(MultiPolygon,25832), exclude using gist (the_geom with &&));
CREATE TABLE
test=
#

Btw.: this is a german speaking forum, can we switch the language?


Regards, Andreas

Hallo Andreas, ich habe deinen Vorschlag angelegt und habe ein erstes Objekt erzeugt (in QGIS). Beim Speichern kommt folgende Fehlermeldung:
Konnte Änderungen am Layer mytable nicht festschreiben

Fehler: FEHLER: Ein Objekt nicht hinzugefügt.

Datenanbieterfehler:
PostGIS-Fehler beim Attributhinzufügen: FEHLER: NULL-Wert in Spalte »id« verletzt Not-Null-Constraint
DETAIL: Fehlgeschlagene Zeile enthält (null, 0106000020E86400000100000001030000000100000004000000F0F73A146F5B…).

ja, nun, dann gibt halt einen Wert an, oder setze einen Default-Wert oder verwende SERIAL. Der Kern Deiner Frage war doch der exclusion constraint, oder?

ja richtig, habe jetzt die id manuell eingetragen (sorry)
dann kommt diese Meldung, obwohl die zwei Flächen keine Geometrie teilen:
Konnte Änderungen am Layer mytable nicht festschreiben

Fehler: FEHLER: Ein Objekt nicht hinzugefügt.

Datenanbieterfehler:
PostGIS-Fehler beim Attributhinzufügen: FEHLER: kollidierender Schlüsselwert verletzt Exclusion-Constraint »mytable_the_geom_excl«
DETAIL: Schlüssel (the_geom)=(0106000020E86400000100000001030000000100000004000000C86A9698865E1841A3B724DF4B91554170E3AE9C40601841AA8064655E915541834B5484DC60184144F5C6183A915541C86A9698865E1841A3B724DF4B915541) kollidiert mit vorhandenem Schlüssel (the_geom)=(0106000020E864000001000000010300000001000000040000000961A74ECD5B18415F4A7ECD4791554197705318295D1841084A915C5C9155417AA8565E115F1841B0C698D9349155410961A74ECD5B18415F4A7ECD47915541).

Ich vermute, es liegt an dem check der boundingboxen der zwei Objekte, ein weiter entfernt liegendes Objekt konnte ich jedenfalls erfolgreich speichern

test=# create table foobar(id int primary key, a geometry, b geometry);
CREATE TABLE
test=
# commit;
COMMIT
test=# insert into foobar values (1, ‘0106000020E86400000100000001030000000100000004000000C86A9698865E1841A3B724DF4B91554170E3AE9C40601841AA8064655E915541834B5484DC60184144F5C6183A915541C86A9698865E1841A3B724DF4B915541’,‘0106000020E864000001000000010300000001000000040000000961A74ECD5B18415F4A7ECD4791554197705318295D1841084A915C5C9155417AA8565E115F1841B0C698D9349155410961A74ECD5B18415F4A7ECD47915541’);
INSERT 0 1

test=*# select id, st_overlaps(a,b), st_intersects(a,b), st_overlaps(st_envelope(a), st_envelope(b)), st_intersects(st_envelope(a), st_envelope(b)) from foobar;
id | st_overlaps | st_intersects | st_overlaps | st_intersects
----±------------±--------------±------------±--------------
1 | f | f | t | t
(1 Zeile)


Hrm. Offenbar wird in der Tat die Bounding Box geprüft, nicht das Objekt selbst. Hab ich jetzt erst einmal keine Idee, sorry.

&& — Returns TRUE if A’s 2D bounding box intersects B’s 2D bounding box.

Gibt es eine Operator, der nicht die Bounding Box prüft, sondern direkt die Geometry?

Hab noch mal mit einigen Leuten gesprochen. Der wohl einzige weg wäre, via TRIGGER beim Insert/Update und st_overlaps() zu prüfen. Nicht schön und bei größeren Mengen wohl auch nicht performant. Du kannst aber dem PostGIS-Team das mal reporten, vielleicht bauen die einen passenden Operator dafür. Sollte prinzipiell möglich sein.

Habe mit dieser Funktion getestet, leider kann ich trotzdem in der Tabelle Überlappungen sowohl bei INSERT als auch bei UPDATE speichern.
CREATE FUNCTION myscheme.no_overlaps_in_mytable(id int, g geometry)
RETURNS boolean AS $$
SELECT NOT EXISTS (
SELECT 1 FROM myscheme.mytable
WHERE myscheme.mytable.id != id —<>id speichert auch alles
AND ST_Overlaps(myscheme.mytable.the_geom, g));
$$ LANGUAGE sql;

ALTER TABLE myscheme.mytable ADD CONSTRAINT no_overlaps_in_mytable CHECK (myscheme.no_overlaps_in_mytable(id, the_geom));

Ohne dem Test auf verschiedene id lassen sich keine UPDATES der Geometrie speichern, weil dann die eigene alte Fläche Fehler wirft.
Stimmt in der Funktion hier vielleicht nur eine Kleinigkeit nicht? Performance ist nicht so wichtig

Wir lassen mal id weg und prüfen nur, ob sich die Geometrie überlappt, und mit einem TRIGGER:

test=*# create table mytable (id int primary key, g geometry);
CREATE TABLE
test=*# create function check_g() returns trigger as $$declare c int; begin c:= 0; select count(1) from mytable where st_overlaps(g, new.g) into c; if c > 0 then raise 'gibt es schon'; else return new; end if; end;$$ language plpgsql;
CREATE FUNCTION
test=*# create trigger trg1 before insert or update on mytable for each row execute procedure check_g();CREATE TRIGGER

Deine 2 genannten Geometrien lassen sich damit beide einlesen, ich hab jetzt keine zur Hand, die sich überlappen würde.