ich habe in PostgreSQL/PostGIS eine “punkt” und eine “polygon” Tabelle:
CREATE TABLE punkt (
gid serial NOT NULL,
geom geometry(point, SRID),
attribut_von_polygon varchar(255),
CONSTRAINT punkt_pkey PRIMARY KEY (gid)
);
CREATE TABLE polygon (
gid serial NOT NULL,
geom geometry(polygon, SRID),
attribut varchar(255),
CONSTRAINT polygon_pkey PRIMARY KEY (gid)
);
Ist es möglich für die Spalte “attribut_von_polygon” (Tabelle “punkt”) einen Default Wert mittels ST_Within zu setzen? Es soll der Wert der Spalte “attribut” des beinhaltenden Polygons übernommen werden.
Danke für den Hinweis, ich habe mittlerweile einen Lösungsansatz gefunden.
CREATE OR REPLACE FUNCTION abfrage_attribut()
RETURNS trigger AS $body$
BEGIN
NEW.attribut_von_polygon = (SELECT attribut FROM polygon
WHERE ST_Within(NEW.geom, polygon.geom));
RETURN NEW;
END;
$body$ LANGUAGE plpgsql;
CREATE TRIGGER abfrage_attribut_trigger BEFORE INSERT OR UPDATE
ON punkt FOR EACH ROW
EXECUTE PROCEDURE abfrage_attribut();
Nun benötige ich den erstellten Trigger allerdings für mehrere Schemen, wobei sich die räumlich Abfrage jeweils auf die Geometrien eines Schemas beziehen soll. Wie gehe ich diesbezüglich am besten vor?
CREATE TABLE schema_a.punkt (
gid serial NOT NULL,
geom geometry(point, SRID),
attribut_von_polygon varchar(255),
CONSTRAINT punkt_pkey PRIMARY KEY (gid)
);
CREATE TABLE schema_a.polygon (
gid serial NOT NULL,
geom geometry(polygon, SRID),
attribut varchar(255),
CONSTRAINT polygon_pkey PRIMARY KEY (gid)
);
CREATE TABLE schema_b.punkt (
gid serial NOT NULL,
geom geometry(point, SRID),
attribut_von_polygon varchar(255),
CONSTRAINT punkt_pkey PRIMARY KEY (gid)
);
CREATE TABLE schema_b.polygon (
gid serial NOT NULL,
geom geometry(polygon, SRID),
attribut varchar(255),
CONSTRAINT polygon_pkey PRIMARY KEY (gid)
);
Schnellschuß: den TRIGGER mit Angabe des Pfades zum Schema, wo er liegt, definieren. IIRC wird immer zuerst im aktuellen Schema gesucht, also sollte er immer die korrekte Tabelle treffen. Ohne Garantie, probier es aus.
Ich glaube mich dunkel daran erinnern zu können das ich das gleiche mal mit einem Subselect als default-Statement gelöst habe. Aber da du das mehrfach verwenden willst ist der Trigger eine bessere Alternative.
So, ich habe mein ursprüngliches Problem mittlerweile gelöst:
CREATE TRIGGER abfrage_attribut_trigger BEFORE INSERT OR UPDATE
ON punkt FOR EACH ROW
EXECUTE PROCEDURE abfrage_attribut();
CREATE OR REPLACE FUNCTION abfrage_attribut()
RETURNS trigger AS $body$
BEGIN
EXECUTE 'SELECT attribut FROM ' || TG_TABLE_SCHEMA || '.polygon WHERE ST_Within($1, polygon.geom) LIMIT 1'
USING NEW.geom
INTO NEW.attribut_von_polygon;
RETURN NEW;
END;
$body$ LANGUAGE plpgsql;
Allerdings habe ich nicht bedacht, dass bei Änderungen eines Polygons (Spalte “attribut”, Geometrie) auch die jeweiligen Punkte ein Update benötigen. Funktioniert dies nur mit einer zusätzlichen Trigger Funktion oder kannn ich meine bestehende Trigger Funktion erweitern?
Ich vermute, dass auch die Trigger Funktion adaptiert werden muss. Allerdings weiß ich noch nicht wie, in PL/pgSQL habe ich mich noch nicht so recht eingelesen.
Ich meinte, das du in der Funktion ein Update auf eine Tabelle absetzen kannst. Dann wird die 2. Tabelle angepasst wenn der Trigger der ersten feuert. Da müsstest du einfach ein zweites EXECUTE einfügen. Dazu musst du am Trigger nichts ändern. Zumindest hab ich es so verstanden.
Wenn du die Funktion mehrfach verwenden willst brauchst du nur für jede Tabelle, einen Trigger anlegen, der die Funktion aufruft.
Wie sieht das praktisch aus? Ich stehe gerade etwas auf der Leitung
Edit: Anbei eine kleine Grafik, um mein Problem zu illustrieren. Wenn ich eine Geometrie “punkt” innerhalb einer Geometrie “polygon” setze, wird der Wert der Spalte “attribut” (z.B. “Wald”) übernommen (siehe Code Beitrag #6). Nun möchte ich bei Änderungen eines Polygons auch die darin liegenden Punkte aktualisieren.
CREATE TRIGGER abfrage_attribut_trigger BEFORE INSERT OR UPDATE
ON punkt FOR EACH ROW
EXECUTE PROCEDURE abfrage_attribut();
CREATE OR REPLACE FUNCTION abfrage_attribut()
RETURNS trigger AS $body$
BEGIN
EXECUTE 'SELECT attribut FROM ' || TG_TABLE_SCHEMA || '.polygon WHERE ST_Within($1, polygon.geom) LIMIT 1'
USING NEW.geom
INTO NEW.attribut_von_polygon;
EXECUTE 'HIER KOMMT DAS SQL HIN DAS DIE PUNKTE ÄNDERT'
RETURN NEW;
END;
$body$ LANGUAGE plpgsql;
Aber eigentlich kannst du das auch lösen in dem du die beiden Tabellen in einer Abfrage per ST_WITHIN joinst. So brauchst du das Attribut ‘Wald’ nur bei den Polygonen mitführen und ändern. Also in etwa so:
select punkt.punkt, polygon.attribut
from punkt
join polygon on st_within(punkt.punkt, polygon.polygon)
danke für die abermals schnelle Rückmeldung. Ursprünglich wollte ich den Join direkt in QGIS mithilfe des refFunctions PlugIn durchführen. Allerdings gab es schon bei wenigen hundert Geometrien Performanceschwierigkeiten. Daher habe ich auch Bedenken bezüglich einer PostgreSQL/PostGIS Abfrage, die betroffenen Projekte umfassen teilweise mehrere tausend Geometrien.
EXECUTE ‘HIER KOMMT DAS SQL HIN DAS DIE PUNKTE ÄNDERT’
Wie diese SQL Anweisung aussehen soll erschließt sich mir nicht. Angenommen ich ändere die gepostete Polygongeometrie von “Wald” auf “See”, wie soll das Update des Punktes ohne Trigger “BEFOREE INSERT OR UPDATE on polygon” erfolgen?
Update point set attribute = new.attribute where st_within(point.geom, new.geom)
Ohne Kaffee und Gewähr.
QGIS: Ich mache meine Joins eigentlich immer auf Datenbankebene und lade das Ergebnis als View/Materialized View in QGIS. Wichtig dabei ist natürlich auch ein korrekte Wahl der Indices in der Datenbank. Eine andere Möglichkeit besteht natürlich auch darin, die Struktur als explizite Jointabelle zu führen, die die jeweiligen IDs der Punkte und Polygone enthält und per Trigger aktualisiert wird. Das spart die permanente teure geometrische Abfrage. Oder du hängst die Polygon-ID als Attribut an den Punkt wenn das technisch möglich ist und eine 1:n Beziehung sichergestellt ist.
