Gemeindedaten mit Leika-DB verknüpfen

« Fragen-Übersicht PLZ

Kontaktdaten einer Gemeinde

Die Daten des Leika-Katalog sollen nun mit den Daten von Gemeinden erweitert werden, um über den Leika-Katalog zu einer Gemeinde und einen schnellen Kontakt zu einem Mitarbeiter für ein gesuchtes Fachverfahren herstellen zu können, wie es das Online-Zugangs-Gesetz in Zukunft verlangt. Es handelt sich dann um eine Nachschlagetabelle, die genutzt wird, wenn der Ort gefunden worden ist.

Ausgangsdaten

Diese wurden von der Website der Gemeinde Schwielowsee extrahiert und in ein Tabellenformat gebracht. Es stellte sich bald heraus, dass das Laika-Vokabular mit dem der Gemeindeverwaltung Schwielowsee nicht immer ein 1:1-Mapping erlaub. Die ersten drei Spalten wurden später hinzugefügt um ein Protokoll zu haben, welcher Leikaschlüssel zugeordnet worden ist und wie der Datensatz über den Begriff und die ausgewählte Spalte (Attribut) in der Leikatabelle gefunden werden kann. Ab Spalte 4 sollen alle Daten in einer neuen Tabelle »behoerden« eingepflegt werden. Hier die ersten zehn Datensätz von 54.

Rathaus

Ansprechpartner

Telefon

Fax

E-Mail

Website

Besonderheit

Zentrale Bürgerservice

033209-7690

gemeinde@schwielowsee.de

Bürgermeisterin

Frau Hoppe

033209-76929

gemeinde@schwielowsee.de

Büro der Bürgermeisterin

Frau Junge

033209-76929

a.junge@schwielowsee.de

Kultur- und Tourismusmanagerin

Frau Trumbull

033209-76947

m.trumbull@schwielowsee.de

Kultur- und Tourismusmarketing

Frau Jänike

033209-70886

m.jaenike@schwielowsee-tourismus.de

Tourist-Information

Frau Lehmann

033209-70899

70898

u.lehmann@schwielowsee-tourismus.de

Tourist-Information

Frau Cornely

033209-70899

70898

c.cornely@schwielowsee-tourismus.de

Fachbereichsleiterin

Frau Wieteck-Barthel

033209-76923

s.wieteck-barthel@schwielowsee.de

Personalangelegenheiten

Fr. Junghans

033209-76933

b.junghans@schwielowsee.de

Lohnbuchhaltung

Fr. Schwarz

033209-76932

p.schwarz@schwielowsee.de

Neue Tabelle

Aufgaben

  • Legen Sie eine Neue Tabelle an, die alle Daten aufnehmen kann.

  • Fügen Sie auch eine Spalte für die Verbindung zu den Relationen »orte« und leika ein.

    Hinweis zeigen:
    Verwende die Funktion »count«...

    Postgres-Doku: »CREATE TABLE (DDL):«

    Lösung zeigen:
    drop table behoerden;
    CREATE TABLE behoerden (
      schluessel bigint,
      osm_id bigint,
      funktion varchar(100) NULL,
      ansprechpartner varchar(50) NULL,
      telefon varchar(20) NULL,
      fax varchar(10) NULL,
      email varchar(100) Null,
      website varchar(100) NULL,
      besonderheit varchar(50) NULL
    );
    

Manuelle Testeingabe

Nun muss herausgefunden werden, welche Daten zu extrahieren sind, um einen gültigen Eintrag in der Realation »behoerden« tätigen zu können.

Schlüssel aus der Leika-Tabelle ermitteln

Beginnen wir im Beispiel mit dem Bereich Tourismus. Wir sehen hier schon den Konflikt im verwendeten Vokabular:

Laika

select schluessel,gruppierung, bezeichnung
from leika where lower(gruppierung) like '%tourismus%';


  schluessel   | gruppierung |           bezeichnung
---------------+-------------+---------------------------------
99139001000000 | Tourismus   | Kurort
99139001016000 | Tourismus   | Kurort Anerkennung
99139002000000 | Tourismus   | Kurabgabe
99139003000000 | Tourismus   | Übernachtungssteuer
99139005000000 | Tourismus   | Tourismus
99139005013000 | Tourismus   | Tourismus Informationserteilung

Auf der Website der Gemeinde Schwielowsee finden sich vier Einträge die mit »Tourismus« in Verbindung stehen:

  • Kultur- und Tourismusmanagerin

  • Kultur- und Tourismusmarketing

  • Tourist-Information

  • Tourist-Information

Am besten geeignet scheint der folgende Datensatz:

99139005000000 | Tourismus | Tourismus

Ortsangaben

Aus der Tabelle »orte« benötigen wir die »osm_id« für Schwielowsee, um später auch eine eindeutige Zuordnung zum Ort herstellen zu können.

select * from orte where ort = 'Schwielowsee';

 osm_id |     ort      |  plz  |          landkreis           | bundesland
--------+--------------+-------+------------------------------+-------------
365821 | Schwielowsee | 14548 | Landkreis Potsdam-Mittelmark | Brandenburg

Testdatensatz hinzufügen

Die oben verwendeten Abfragen werden nun in ein größeres insert-Statment eingefügt, der Rest der Daten wurde manuell übernommen.

insert into behoerden
(schluessel, osm_id, funktion, ansprechpartner, telefon, fax, email, website, besonderheit)
values ((select schluessel from leika
    where bezeichnung = 'Tourismus'),
   (select osm_id from orte
    where ort = 'Schwielowsee'),
    /* funktion */       'Tourist-Information',
    /* ansprechpartner*/ 'Frau Jänike' ,
    /* telefon */        '033209-70886' ,
    /* fax */            '',
    /* email */          'm.jaenike@schwielowsee-tourismus.de' ,
    /* website */        '' ,
    /* besonderheit */   '' );

Daten abfragen

select * from behoerden;

Automatisierung

Nun kann man sich Gedanken über eine Automatisierung (oder Teilautomatisierung) machen. Hier einige Überlegungen

  • Ein Applikation schreiben, die alle Varianen anzeigt und dem Anwender eine Zuordnung erlaubt.

  • Ein Tool verwenden z.B »Power-BI Desktop«, …?

  • Ein CSV-Import, der die Schlüsselfelder leer läßt, ein anschließender Abgleich, wie oben gezeigt, kann dann die Felder »osm_id« und »schluessel« füllen.

  • Eine SQL-Funktion schreiben die ein halbautomatisches Insert realisiert

Ein erster Versuch (Test)

In diesem ersten Versuch sind Ort und Schlüssel noch fest kodiert!

CREATE or REPLACE FUNCTION getValuesFromLine(s TEXT)
RETURNS void  AS $$
DECLARE
a  text;
b  text;
c  text;
d  text;
e  text;
f  text;
g  text;

BEGIN

a := TRIM(BOTH FROM SPLIT_PART(s,'|', 2));
b := TRIM(BOTH SPLIT_PART(s,'|', 3));
c := TRIM(BOTH SPLIT_PART(s,'|', 4));
d := TRIM(BOTH SPLIT_PART(s,'|', 5));
e := TRIM(BOTH SPLIT_PART(s,'|', 6));
f := TRIM(BOTH SPLIT_PART(s,'|', 7));
g := TRIM(BOTH SPLIT_PART(s,'|', 8));

insert into behoerden
(schluessel, osm_id, funktion, ansprechpartner, telefon, fax, email, website, besonderheit)
values ((select schluessel from leika
where  gruppierung = 'Steuern' limit 1),
(select osm_id from orte
where ort = 'Schwielowsee'),
a, b,c,d,e,f,g);

END;
$$  LANGUAGE plpgsql;

Ein Aufruf sieht dann wie folgt aus:

select getValuesFromLine('| Tourist-Information| Frau Lehmann | 033209-70899 | 70898 | u.lehmann@schwielowsee-tourismus.de | ||');