[PostNAS] Problematik bei der Variante *kill - Löschen alter Elemente

Mi Jan 29 06:05:28 PST 2014

Hallo,

es gab in der Vergangenheit immer wieder Probleme, dass bei der Aktualisierung der PostNAS-Datenbank mit der Variante *kill immer noch alte Elemente in der Datenbank erhalten blieben.

Uns ist es konkret in mehreren Fällen aufgefallen. Dabei wurden Einträge in der Tabelle alkis_beziehungen nicht gelöscht. Dieses konnte auch nicht mehr zurückgeführt werden, sodass der derzeitige Trigger diese Daten nicht finden konnte.

Daher haben wir uns folgendes überlegt:

Zuerst haben wir eine weiter Tabelle in das Schema eingefügt. Diese Tabelle soll zum einen die Importierte Dateien protokollieren und zum anderen eine id erzeugen, die jedem Eintrag in der alkis_beziehungen zugeordnet werden. Dadurch ist zu erkennen, zu welchem Zeitpunkt eine Beziehung in diese Tabelle eingefügt wurde.

Hier das SQL zum erzeugen der Tabelle:

CREATE TABLE import

(

  id serial NOT NULL,

  datum timestamp without time zone,

  verzeichnis text,

  importart text,

  CONSTRAINT import_pk PRIMARY KEY (id)

);

CREATE UNIQUE INDEX import_id ON import USING btree (id);

Bei jedem Import durch das Skript konv_batch.sh muss ein weiterer Eintrag in diese Tabelle erfolgen. Dieses geschieht über folgendes Komando in der konv_batch.sh:

echo "INSERT INTO import (datum,verzeichnis,importart) VALUES ('"$(date '+%Y-%m-%d %H:%M:%S')"','"${ORDNER}"','"${verarb}"');" | psql $con

Dieser Befehl muss in der konv_batch.sh muss vor dem Ausführen des Befehls ogr2ogr abgesetzt werden.

Damit jede Beziehung genau einem Datenimport zugeordnet werden kann, benötigt man in der alkis_beziehungen ein weiteres Datenfeld. Dieses nennen wir import_id.

Hier das SQL zum erzeugen der Tabelle alkis_beziehungen:

CREATE TABLE alkis_beziehungen (

            ogc_fid                       serial NOT NULL,

            beziehung_von           character(16),            --> gml_id

            beziehungsart             varchar,                      --> Liste siehe unten

            beziehung_zu             character(16),            --> gml_id

            import_id                    integer,                       --> import_id aus der Tabelle import

            CONSTRAINT alkis_beziehungen_pk PRIMARY KEY (ogc_fid)

);

Wie kommt nun die richtige ID in das jeweilige Datenfeld?

Dazu muss man zuerst wissen, dass ogr2ogr bei jeder aktualisierung versucht, alle Beziehungen aus dem neu geliefertem Objekt zu importieren. Das wird uns in diesem Fall zu Vorteil.

Es muss ein weitere Trigger eingerichtet werden. Dieser wird diesmal auf die Tabelle alkis_beziehungen gehen.

Hier der SQL für den Trigger, der bei der Kill Variante zum Einsatz kommt:

CREATE TRIGGER update_import_id

            BEFORE INSERT ON alkis_beziehungen

            FOR EACH ROW

            EXECUTE PROCEDURE get_import_id();

Die Funktion get_import_id(); sucht sich zuerst die letzte, und damit höchste id aus der Tabelle id. Bei jedem Import einer Beziehung in die alkis_beziehungen wird nun über diese Funktion die höchste import_id der neu importierten Beziehung zugeordnet. 

Die Funktion sieht wie folgt aus:

CREATE OR REPLACE FUNCTION get_import_id() RETURNS TRIGGER AS $$

BEGIN

            EXECUTE 'SELECT max(id) FROM import' INTO NEW.import_id;

            RETURN NEW;

END;

$$ LANGUAGE plpgsql;

Somit kann jetzt jeder Beziehung zugeordnet werden, mit welchem Import der NBA-Dateien diese Beziehung in die Datenbank geladen wurde. Wie vorher schon gesagt, versucht ogr2ogr jede Beziehung aus einer NBA-Datei in die Tabelle zu importieren. Früher wurde dieses jedoch nicht ausgeführt, da es schon genau so einen Datensatz gab. Nun wird er eingetragen, da es kleine Unterschiede gibt, nämlich die import_id.

Da jetzt natürlich doppelte Datensätze vorhanden sind, müssen alle Datensätze, die kleiner als die höchste id in der Tabelle import gelöscht werden. Aber nur für das Objekt, welches zuvor importiert wurde und nicht global für alle Datensätze.

Dazu ist der Trigger auf der delete Tabelle ebenfalls anzupassen.

In der Funktion delete_feature_kill() ist folgender Befehl hinzuzufügen:

            EXECUTE 'DELETE FROM alkis_beziehungen WHERE beziehung_von = ''' || gml_id || ''' AND import_id < (SELECT max(id) FROM import)';

Der Datensatz in der Tabelle delete wird erst erzeugt, wenn alle anderen Tabellen bereits mit den Informationen gefüllt wurden.

Nun sind wirklich alle Informationen auf dem aktuellen Stand, da nur die Beziehungen, die mit dem letzten Import hinzugefügt wurden, in der Datenbank vorhanden sind.

Ich habe unsere Änderungen einmal in die Skripte aus dem aktuellen Trunk eingebaut.

Um zu sehen, welche Beziehungen gelöscht wurden, habe ich eine weitere Tabelle alkis_beziehungen_alt eingerichtet, in die alle Datensätze kopiert werden, bevor diese aus der Tabelle alkis_beziehungen gelöscht werden.

Hier noch eine Übersicht, an welchen Stellen die Änderungen in den Dateien zu finden ist:

alkis_PostNAS_schema.sql

Zeile 148 - 164 (Tabelle alkis_beziehungen)

Zeile 4498 - 4508 (Anlegen der import Tabelle)

alkis-trigger-kill.sql

Zeile 6 - 9 (Trigger für die alkis_beziehungen Tabelle)

alkis-functions.sql

Zeile 423 - 425 (Beziehungen sichern und löschen)

Zeile 822 - 828 (Funktion für den Trigger auf der Tabelle alkis_beziehungen)

konv_batch.sh

Zeile 100 - 101 (Erzeugen des Eintrages in die Import Tabelle)

Meine Änderungen vom 10.07.2013 nutze ich nicht mehr. Auch die entsprechenden Felder und Funktionen sind in unserer Datenbank entfernt worden.

Nachdem wir auf diese Lösung umgestellt haben, hatten wir keine Probleme mehr mit alten Einträgen.

Mit freundlichen Grüßen

Im Auftrag

Marvin Brandt

Kreis Unna - Der Landrat

Zentrale Datenverarbeitung

DV-Verfahren 

Friedrich-Ebert-Straße 17

59425 Unna

Fon 0 23 03 / 27-14 16

Fax 0 23 03 / 27-28 96

marvin.brandt at kreis-unna.de <mailto:marvin.brandt at kreis-unna.de> 

www.kreis-unna.de <http://www.kreis-unna.de/> 

Denken Sie an die Umwelt. Pruefen Sie deshalb bitte, 
ob der Ausdruck dieser E-Mail wirklich notwendig ist.

Diese E-Mail wurde beim Ausgang auf Viren geprueft. Wegen der 
potentiellen Gefahr auf den Uebertragungswegen wird zu einer 
Vireneingangskontrolle geraten. Eine Haftung für Virenfreiheit
wird ausgeschlossen.

-------------- nächster Teil --------------
Ein Dateianhang mit HTML-Daten wurde abgetrennt...
URL: <http://lists.osgeo.org/pipermail/nas/attachments/20140129/4f9e85b6/attachment-0001.html>
-------------- nächster Teil --------------
Ein Dateianhang mit Binärdaten wurde abgetrennt...
Dateiname   : alkis_PostNAS_schema.sql
Dateityp    : application/octet-stream
Dateigröße  : 198873 bytes
Beschreibung: alkis_PostNAS_schema.sql
URL         : <http://lists.osgeo.org/pipermail/nas/attachments/20140129/4f9e85b6/attachment-0004.obj>
-------------- nächster Teil --------------
Ein Dateianhang mit Binärdaten wurde abgetrennt...
Dateiname   : alkis-functions.sql
Dateityp    : application/octet-stream
Dateigröße  : 53061 bytes
Beschreibung: alkis-functions.sql
URL         : <http://lists.osgeo.org/pipermail/nas/attachments/20140129/4f9e85b6/attachment-0005.obj>
-------------- nächster Teil --------------
Ein Dateianhang mit Binärdaten wurde abgetrennt...
Dateiname   : alkis-trigger-kill.sql
Dateityp    : application/octet-stream
Dateigröße  : 238 bytes
Beschreibung: alkis-trigger-kill.sql
URL         : <http://lists.osgeo.org/pipermail/nas/attachments/20140129/4f9e85b6/attachment-0006.obj>
-------------- nächster Teil --------------
Ein Dateianhang mit Binärdaten wurde abgetrennt...
Dateiname   : konv_batch.sh
Dateityp    : application/octet-stream
Dateigröße  : 6419 bytes
Beschreibung: konv_batch.sh
URL         : <http://lists.osgeo.org/pipermail/nas/attachments/20140129/4f9e85b6/attachment-0007.obj>