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

Probleme und Fragen zum Thema Trigger und Funktionen.
Antworten
Beate
Neuer Benutzer
Beiträge: 4
Registriert: Mi 7. Mär 2018, 13:41

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

Beitrag von Beate » Mi 7. Mär 2018, 14:09

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

akretschmer
Erfahrener Benutzer
Beiträge: 7410
Registriert: So 29. Okt 2006, 13:35
Wohnort: Kaufbach, Sachsen, Germany, Germany
Kontaktdaten:

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

Beitrag von akretschmer » Mi 7. Mär 2018, 14:17

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
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Beate
Neuer Benutzer
Beiträge: 4
Registriert: Mi 7. Mär 2018, 13:41

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

Beitrag von Beate » Mi 7. Mär 2018, 14:25

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

akretschmer
Erfahrener Benutzer
Beiträge: 7410
Registriert: So 29. Okt 2006, 13:35
Wohnort: Kaufbach, Sachsen, Germany, Germany
Kontaktdaten:

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

Beitrag von akretschmer » Mi 7. Mär 2018, 14:28

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?
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Beate
Neuer Benutzer
Beiträge: 4
Registriert: Mi 7. Mär 2018, 13:41

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

Beitrag von Beate » Mi 7. Mär 2018, 14:32

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

akretschmer
Erfahrener Benutzer
Beiträge: 7410
Registriert: So 29. Okt 2006, 13:35
Wohnort: Kaufbach, Sachsen, Germany, Germany
Kontaktdaten:

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

Beitrag von akretschmer » Mi 7. Mär 2018, 15:14

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.
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

akretschmer
Erfahrener Benutzer
Beiträge: 7410
Registriert: So 29. Okt 2006, 13:35
Wohnort: Kaufbach, Sachsen, Germany, Germany
Kontaktdaten:

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

Beitrag von akretschmer » Mi 7. Mär 2018, 15:17

&& — 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?
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

akretschmer
Erfahrener Benutzer
Beiträge: 7410
Registriert: So 29. Okt 2006, 13:35
Wohnort: Kaufbach, Sachsen, Germany, Germany
Kontaktdaten:

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

Beitrag von akretschmer » Do 8. Mär 2018, 08:19

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.
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Beate
Neuer Benutzer
Beiträge: 4
Registriert: Mi 7. Mär 2018, 13:41

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

Beitrag von Beate » Do 8. Mär 2018, 15:33

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

akretschmer
Erfahrener Benutzer
Beiträge: 7410
Registriert: So 29. Okt 2006, 13:35
Wohnort: Kaufbach, Sachsen, Germany, Germany
Kontaktdaten:

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

Beitrag von akretschmer » Do 8. Mär 2018, 16:12

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

Code: Alles auswählen

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.
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Antworten