Gemeindedaten mit Leika-DB verknüpfen¶
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 erlaubt. 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ätzen von 54.
Rathaus |
Ansprechpartner |
Telefon |
Fax |
Website |
Besonderheit |
|
Zentrale Bürgerservice |
033209-7690 |
|||||
Bürgermeisterin |
Frau Hoppe |
033209-76929 |
||||
Büro der Bürgermeisterin |
Frau Junge |
033209-76929 |
||||
Kultur- und Tourismusmanagerin |
Frau Trumbull |
033209-76947 |
||||
Kultur- und Tourismusmarketing |
Frau Jänike |
033209-70886 |
||||
Tourist-Information |
Frau Lehmann |
033209-70899 |
70898 |
|||
Tourist-Information |
Frau Cornely |
033209-70899 |
70898 |
|||
Fachbereichsleiterin |
Frau Wieteck-Barthel |
033209-76923 |
||||
Personalangelegenheiten |
Fr. Junghans |
033209-76933 |
||||
Lohnbuchhaltung |
Fr. Schwarz |
033209-76932 |
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«...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-Statement 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 Varianten 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 | ||');