Funktion zur Gewichtung

Hallo,

ich versuche innerhalb einer Tabelle mit Beobachtungen eine einfache Gewichtung zu erstellen, die sich zunächst sehr einfach aus dem Verhältnis alle Beobachtungen im Umkreis 1000m mit gleichem Attribut zu allen Beobachtungen im Umkreis 1000m zusammensetzt.
ich habe hierzu ein Script geschrieben welches die Tabellen und Wertebereiche anlegt.

DROP FUNCTION IF EXISTS calcoapb() CASCADE;
DROP FUNCTION IF EXISTS calcstpb() CASCADE;
DROP TABLE IF EXISTS observation CASCADE;
DROP DOMAIN IF EXISTS sed_ol_cov CASCADE;
DROP DOMAIN IF EXISTS sed_ol CASCADE;

CREATE DOMAIN sed_ol AS TEXT
CHECK(
	VALUE ~ 'Klei'			OR
	VALUE ~ 'Schlick' 		OR
	VALUE ~ 'sandiger Schlick' 	OR
	VALUE ~ 'schlickiger Sand'	OR
	VALUE ~ 'Feinsand'   		OR
	VALUE ~ 'Mittel-/Grobsand'	OR
	VALUE ~	'Kies/Kiesel'		OR
	VALUE ~ 'Steine'		OR
	VALUE ~ 'Torf/Gyttien'		OR
	VALUE ~ 'vollständig abgedeckt'
);

CREATE DOMAIN sed_ol_cov AS TEXT
CHECK(
	VALUE ~ 'nein'			OR
	VALUE ~ 'stellenweise'		OR
	VALUE ~ 'vorherrschend'		OR
	VALUE ~ 'deckend'		
);

CREATE TABLE observation(
       obsid		serial PRIMARY KEY,
       geom		geometry(POINT),
       obsus		text NOT NULL,
       obstime		timestamp WITH time zone NOT NULL,
       obsprobe		text,
       obsphoto		text,
       obssedol		sed_ol NOT NULL,
       obssedolcov	sed_ol_cov NOT NULL DEFAULT 'nein',
       obscrowdpb	NUMERIC(3,2) DEFAULT 0.00,
       obstechpb	NUMERIC(3,2),
       obscomplete	NUMERIC(3,2)
);

Um den Wert von obscrowdpb wie oben beschrieben zu berechnen habe ich ein Update-Statement geschrieben, welches so auch funktioniert:

UPDATE observation
SET obscrowdpb = round(
	(SELECT count(*)
	FROM observation
	WHERE ST_DWITHIN(
		(SELECT geom
		FROM observation
		WHERE obsid = 2),
		observation.geom,1000)
		AND observation.obssedol =
			(SELECT obssedol
			FROM observation
			WHERE obsid = 2)
	)/
	(SELECT count(*)
	FROM observation
	WHERE ST_DWITHIN(
		(SELECT geom
		FROM observation
		WHERE obsid = 2),
		observation.geom,1000)
	)::numeric,2)
WHERE obsid = 2;

Beim Versuch das ganze als Insert/Update Trigger ausführen zu lassen, bekomme ich leider nur ein teilweise korrektes Ergebnis. Hier der Code für die Funktion und die Trigger:

CREATE FUNCTION calcstpb() RETURNS TRIGGER AS $cstpb$
DECLARE
	rowid INTEGER;
	rowsed TEXT;
BEGIN
	rowid = new.obsid;
	rowsed = new.obssedol;
	UPDATE observation
	SET obscrowdpb = round(
		(SELECT count(*) --find all observations within a distance of 1000m that have the same sediment layer
		FROM observation
		WHERE ST_DWITHIN(
			(SELECT geom
			FROM observation
			WHERE obsid = rowid),
			observation.geom,1000)
		AND observation.obssedol = rowsed)
		/
		(SELECT count(*) -- find all observations within a distance of 1000m
		FROM observation
		WHERE ST_DWITHIN(
			(SELECT geom
			FROM observation
			WHERE obsid = rowid),
			observation.geom,1000)
		)::numeric,2)
	WHERE obsid = rowid;
	RETURN NEW;
END $cstpb$ LANGUAGE plpgsql;

CREATE TRIGGER stci AFTER INSERT ON observation
FOR EACH ROW EXECUTE PROCEDURE calcstpb();
CREATE TRIGGER stcu AFTER UPDATE OF obssedol ON observation
FOR EACH ROW EXECUTE PROCEDURE calcstpb();

Das Ergebnis Punktnummer | Resultat [Soll-Wert] | Attribut(obssedol):
1 | 1,0 [0,33] | Klei
2 | 1,0 [0,67] | Klei
3 | 0,5 [0,5] | Feinsand
4 | 0,33 [0,33] | Kies/Kiesel
5 | 1,0 [1,0] | Klei
Da die Abweichungen immer nur Punkte betreffen die den gleichen Attributwert haben, gehe ich davon aus, dass Punkt 1 und 2 vom durch den Wert von Punkt 5 ersetzt werden (gleicher Attributwert), ich kann jedoch den Fehler in der Funktion nicht finden. Ich hänge noch die Datei mit der die Beispielpunkte erzeugt werden hier an:

INSERT INTO observation VALUES(DEFAULT, ST_GeomFromText('POINT(477629.06565442960709333 6030905.84408905450254679)', 32632), 'Fred', to_timestamp('25-11-2014 14:30','DD-MM-YYYY HH24:MI'), 'PN12345', './pics/test123.jpg', 'Klei', 'vorherrschend', DEFAULT, 1.0, DEFAULT);
INSERT INTO observation VALUES(DEFAULT, ST_GeomFromText('POINT(477906.37000995135167614 6032278.50064888689666986)', 32632), 'Feuerstein', to_timestamp('22-11-2014 11:39','DD-MM-YYYY HH24:MI'), 'PN12346', './pics/test145.jpg', 'Klei', 'vorherrschend', DEFAULT, 0.69, DEFAULT);
INSERT INTO observation VALUES(DEFAULT, ST_GeomFromText('POINT(477816.24609440675703809 6030170.98754692170768976)', 32632), 'Barney', to_timestamp('24-11-2014 18:15','DD-MM-YYYY HH24:MI'), 'PN12346', './pics/test12.jpg', 'Feinsand', 'vorherrschend', DEFAULT, 0.20, DEFAULT);
INSERT INTO observation VALUES(DEFAULT, ST_GeomFromText('POINT(478107.41566770459758118 6031488.18323564995080233)', 32632), 'Geröllheimer', to_timestamp('26-11-2014 08:10','DD-MM-YYYY HH24:MI'), 'PN12347', './pics/test325.jpg', 'Kies/Kiesel', 'vorherrschend', DEFAULT, 0.45, DEFAULT);
INSERT INTO observation VALUES(DEFAULT, ST_GeomFromText('POINT(477032.86129005788825452 6032271.56803999841213226)', 32632), 'Klaas', to_timestamp('23-11-2014 07:00','DD-MM-YYYY HH24:MI'), 'PN12348', './pics/test15.jpg', 'Klei', 'vorherrschend', DEFAULT, 0.97, DEFAULT);

Die Datenbank (PostgreSQL-9.3) mit Erweiterung (PostGIS 2.1.4 r12) laüft unter Virtualbox und xubuntu. Ich hoffe ich habe nichts vergessen und Entschuldigung für den langen ersten Post.

Beste Grüße

Krümelmonster

Ohne jetzt alles mental verkraftet zu haben denke ich aber, daß es ein prinzipieller Fehler ist, in einer Tabelle ein Feld durch Berechnungen über dieselbe Tabelle zu berechnen.

Wäre ich Du würde ich das Feld, was Du da berechnen willst, aus der Tabelle rauslassen und statt dessen z.B. eine VIEW definieren, der die Berechnung dann zugrunde liegt.

Hallo

und vielen Dank für die zügige Antwort. Ich hatte schon vermutet (beim Erstellen des Update-Statements), dass hier ein eher grundlegender Fehler besteht. Da ich blutiger Anfänger im Erstellen von Datenbanken bin, werde ich mich jetzt erst mal Einlesen in Views und deren Eigenschaften, um eine (hoffentlich funktionsfähige) Lösung zu finden.

Beste Grüße

Krümelmonster

Hallo,

so ich habe jetzt, wie von akretschmer vorgeschlagen (hier nochmal ein Dankeschön), die Spalte (eigentlich zwei - jedoch habe ich eine, die funktionierte, im ersten Post unterschlagen) mit der Berechnung (und die dazugehörigen Funktionen und Trigger) entfernt. Dann habe ich einen View erstellt, der die Spalten der Tabelle enthält und die Berechnungen durchführt und als Spalten einfügt. Es ist immer noch etwas umständlich, aber das Ergebnis wird korrekt berechnet und ich kann auf den View mit externer Software (z. B. QGIS zugreifen). Insofern sehe ich das Thema (für mich) als gelöst an. Ich hänge nochmals das Skript hier an (vielleicht hilft es ja einmal einem weiteren Anfänger)

DROP VIEW IF EXISTS pb CASCADE;
DROP TABLE IF EXISTS observation CASCADE;
DROP DOMAIN IF EXISTS sed_ol_cov CASCADE;
DROP DOMAIN IF EXISTS sed_ol CASCADE;

CREATE DOMAIN sed_ol AS TEXT
CHECK(
	VALUE ~ 'Klei'			OR
	VALUE ~ 'Schlick' 		OR
	VALUE ~ 'sandiger Schlick' 	OR
	VALUE ~ 'schlickiger Sand'	OR
	VALUE ~ 'Feinsand'   		OR
	VALUE ~ 'Mittel-/Grobsand'	OR
	VALUE ~	'Kies/Kiesel'		OR
	VALUE ~ 'Steine'		OR
	VALUE ~ 'Torf/Gyttien'		OR
	VALUE ~ 'vollständig abgedeckt'
);

CREATE DOMAIN sed_ol_cov AS TEXT
CHECK(
	VALUE ~ 'nein'			OR
	VALUE ~ 'stellenweise'		OR
	VALUE ~ 'vorherrschend'		OR
	VALUE ~ 'deckend'		
);

CREATE TABLE observation(
       obsid		serial PRIMARY KEY,
       geom		geometry(POINT),
       obsus		text NOT NULL,
       obstime		timestamp WITH time zone NOT NULL,
       obsprobe		text,
       obsphoto		text,
       obssedol		sed_ol NOT NULL,
       obssedolcov	sed_ol_cov NOT NULL DEFAULT 'nein',
       obstechpb	NUMERIC(3,2)
);

CREATE OR REPLACE VIEW pb AS
SELECT observation.obsid,
       observation.geom,
       observation.obsus,
       observation.obstime,
       observation.obsprobe,
       observation.obsphoto,
       observation.obssedol,
       observation.obssedolcov,
       observation.obstechpb,
       round( 	(SELECT count(*)
       		FROM observation AS s
		WHERE ST_DWITHIN(observation.geom,s.geom,1000)
		AND observation.obssedol = s.obssedol)::numeric
		/
		(SELECT count(*)
		FROM observation AS t
		WHERE ST_DWITHIN(observation.geom,t.geom,1000))::numeric,2 ) AS obsstpb,
		
       round(	((SELECT count(*)
       		FROM observation AS s
		WHERE ST_DWITHIN(observation.geom,s.geom,1000)
		AND observation.obssedol = s.obssedol)::numeric
		/
		(SELECT count(*)
		FROM observation AS t
		WHERE ST_DWITHIN(observation.geom,t.geom,1000))::numeric + observation.obstechpb)/2,2) AS obsoapb
FROM observation;

Ich würde das Thema gerne als gelöst setzen, habe auf die Schnelle jedoch nicht gefunden ob und wie es in diesem Forum üblich ist, wäre also für einen kurzen Hinweis dankbar.
Falls es zudem noch Verbesserungsvorschläge von den erfahreneren Benutzern gibt, wäre ich natürlich daran interessiert zu hören, wie dies eleganter zu lösen ist.

Beste Grüße

Krümelmonster