To forbid overlappings in geometry (polygons) using a function or constraint or rule
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
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
-
- Erfahrener Benutzer
- Beiträge: 7677
- 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
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
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
Re: To forbid overlappings in geometry (polygons) using a function or constraint or rule
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...).
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...).
-
- Erfahrener Benutzer
- Beiträge: 7677
- 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
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
Re: To forbid overlappings in geometry (polygons) using a function or constraint or rule
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
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
-
- Erfahrener Benutzer
- Beiträge: 7677
- 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
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.
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
-
- Erfahrener Benutzer
- Beiträge: 7677
- 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
&& — 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?
Gibt es eine Operator, der nicht die Bounding Box prüft, sondern direkt die Geometry?
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
-
- Erfahrener Benutzer
- Beiträge: 7677
- 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
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
Re: To forbid overlappings in geometry (polygons) using a function or constraint or rule
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
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
-
- Erfahrener Benutzer
- Beiträge: 7677
- 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
Wir lassen mal id weg und prüfen nur, ob sich die Geometrie überlappt, und mit einem TRIGGER:
Deine 2 genannten Geometrien lassen sich damit beide einlesen, ich hab jetzt keine zur Hand, die sich überlappen würde.
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
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services