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