Funktion zur Gewichtung

Probleme und Fragen zum Thema Trigger und Funktionen.
Antworten
kruemelmonster
Neuer Benutzer
Beiträge: 3
Registriert: Do 27. Nov 2014, 16:14

Funktion zur Gewichtung

Beitrag von kruemelmonster » Do 27. Nov 2014, 16:58

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.

Code: Alles auswählen

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:

Code: Alles auswählen

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:

Code: Alles auswählen

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:

Code: Alles auswählen

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

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

Re: Funktion zur Gewichtung

Beitrag von akretschmer » Fr 28. Nov 2014, 09:58

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


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.

kruemelmonster
Neuer Benutzer
Beiträge: 3
Registriert: Do 27. Nov 2014, 16:14

Re: Funktion zur Gewichtung

Beitrag von kruemelmonster » Fr 28. Nov 2014, 11:14

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

kruemelmonster
Neuer Benutzer
Beiträge: 3
Registriert: Do 27. Nov 2014, 16:14

Re: Funktion zur Gewichtung

Beitrag von kruemelmonster » Fr 28. Nov 2014, 19:00

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)

Code: Alles auswählen

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

Antworten