Eigene PLZ- und Straßendatenbanken erstellen

Knowledgebase
, ,
1

Einleitung

Empfehlung: Lesen Sie zunächst den gesamten Artikel durch, um sich einen Überblick über den kompletten Vorgang zu machen.

Der combit Relationship Manager bietet seit Service Pack 10.005 die Möglichkeit, für weitere Länder eigene PLZ- und Straßendatenbanken zur Geokodierung der Datensätze für Umkreissuche, Landkarte und Adresseingabe-Assistent zu erstellen.

Hinweis: Es können dabei nicht die bereits mitgelieferten PLZ- und Straßendatenbanken für Deutschland (DE), Österreich (AT), Schweiz (CH), Liechtenstein (LI) und Luxemburg (LU) ersetzt werden. Sollten Sie für diese Länder in den Daten für Gebiete, Städte, Adressen etc. Fehler entdeckt haben oder möchten Sie eine Änderung vorschlagen, beachten Sie bitte den Artikel Geokodierung: Hinweise/Fehler in OpenStreetMap-Karten melden in unserer Knowledgebase.

Damit die Daten Ihrer PLZ- und Straßendatenbanken konform zu unserem Datenmodell in die erforderliche SQLite-Datenbank transferiert werden können, führen Sie bitte die folgenden 5 Schritte der Reihe nach aus.

Schritt 1: Analyse und Präparation der vorliegenden PLZ- und Straßendatenbank

Im Folgenden sei davon ausgegangen, dass Ihre PLZ- und Straßendatenbank in Form einer Excel-Tabelle vorliegt. Dabei muss diese Excel-Tabelle für einen erfolgreichen Import in die erforderliche SQLite-Datenbank zwingend folgendes Schema aufweisen, von links nach rechts müssen die folgenden 21 Spaltentitel in der ersten Zeile vorhanden sein:

FederalState_Name
AdministrativeDistrict_Name
County_Name
PostCode
PostCode_DialCode
PostCode_GC_latitude
PostCode_GC_longitude
CityName_Official
CityName_DE
CityName_EN
StreetName
StreetName_GC_latitude
StreetName_GC_longitude
Housenumber
Housenumber_GC_latitude
Housenumber_GC_longitude
PostBox_PostboxNrFrom
PostBox_PostboxNrTo
PostBox_CityPostCodeFrom
PostBox_CityPostCodeTo
PostBox_PostboxPostCode

Erläuterung der Spalten in der Excel-Tabelle

  • Die Spalte FederalState_Name ist hierbei für die gröbste territoriale Unterteilung des jeweiligen Landes gedacht, AdministrativeDistrict_Name für eine mittlere und County_Name für die feinste Unterteilung.
    Für Deutschland beispielsweise könnte man FederalState_Name für die Bundesländer, AdministrativeDistrict_Name für die Regierungsbezirke und County_Name für die Landkreise verwenden.
    Für Frankreich beispielsweise würde man FederalState_Name für die Régions, AdministrativeDistrict_Name für die Departements und County_Name für die Arrondissements verwenden.
    Für die USA beispielsweise könnte man FederalState_Name für die States und County_Name für die Counties verwenden, wobei man AdministrativeDistrict_Name als leeres Feld belässt.

  • PostCode enthält die jeweiligen PLZ-Daten und PostCode_DialCode die zugehörige Vorwahl.

  • PostCode_GC_latitude bzw. PostCode_GC_longitude enthält den Breiten- bzw. Längengrad der Geokoordinate, die als Mittelpunkt des jeweiligen PLZ-Gebiets interpretiert wird („GC“ steht hierbei für GeoCoordinate).

Achtung: Beim Import der Daten dieser beiden Spalten in die SQLite-Datenbank wird davon ausgegangen, dass als Dezimaltrennzeichen für die numerischen Werte das Komma , und nicht der Punkt . verwendet wurde.

  • CityName_Official , CityName_DE und CityName_EN enthalten den jeweiligen Ortsnamen in Landesprache, auf Deutsch bzw. auf Englisch.

Wichtige Hinweise zum Aufbau der Excel-Tabelle

Hinweis: Achten Sie unbedingt auf die exakte Einhaltung des vorgegebenen Schemas.

  • Falls in Ihrer Excel-Tabelle weitere Spalten mit zusätzlichen Informationen enthalten sein sollten, so müssen Sie diese löschen.

  • Falls in Ihrer Excel-Tabelle eine (oder mehrere) Spalten mit entsprechenden Daten fehlen sollten, so müssen Sie die Spalte (mit dem vorgegebenen Spaltennamen in der ersten Zeile) dennoch hinzufügen und die entsprechenden Zellen einfach leer (d.h. ohne Daten) lassen.

  • Falls in Ihrer Excel-Tabelle eine (oder mehrere) Spalten mit entsprechenden Daten aber unter anderem Spaltennamen existieren sollten, so benennen Sie diese bitte einfach um.

  • Falls sich in Ihrer Excel-Tabelle die vorgesehenen Spalten in anderer Reihenfolge befinden, so müssen Sie diese entsprechend umsortieren.

Die Datenbankstruktur (d.h. die zur Verfügung stehenden Tabellen, deren Felder und Beziehungen
zueinander) der zu erstellenden SQLite-Datenbank können Sie dem folgenden Entity-Relationship-Diagramm entnehmen:

SQLite Adressdatenbank ERD
SQLite Adressdatenbank ERD1478×615 32.4 KB

Schritt 2: Export in das CSV-Format

Die so angepasste Excel-Tabelle muss nun als CSV-Datei exportiert und gespeichert werden, beispielsweise unter dem Namen „Postcode_DB.csv“. Hierzu können Sie beispielsweise die Funktion Datei > Speichern unter oder Datei > Exportieren > Dateityp ändern verwenden und hierbei „CSV (Trennzeichen-getrennt)(*.csv)“ oder „CSV UTF-8 (durch Trennzeichen getrennt)“ als Dateityp auswählen.

Hinweis: Je nach verwendeter Excel-Version kann der Export geringfügig abweichen.

Zur Überprüfung, ob die Excel-Datei erfolgreich und anforderungsgemäß in das CSV-Format exportiert wurde, können Sie die soeben gespeicherte CSV-Datei mit einem Texteditor Ihrer Wahl öffnen. Die erste Zeile sollte genau aus den obigen 21 Spaltentiteln (jeweils getrennt durch ein Semikolon) bestehen. Achten Sie dabei insbesondere auf die Reihenfolge der Spaltennamen und dass als Trennzeichen tatsächlich das Semikolon ; verwendet wird. Sollte die erste Zeile in Ihrer CSV-Datei nicht übereinstimmen, müssen Sie die Schritte 1 und 2 wiederholen.

Schritt 3: Installation des relationalen Datenbanksystems SQLite

Damit nun die Daten aus der exportierten CSV-Datei in eine SQLite-Datenbank importiert werden können, müssen Sie zunächst die SQLite command-line tools des Open-Source Datenbanksystems „SQLite“ installieren.

Hinweis: Da es sich bei SQLite um ein eigenständiges Datenbanksystem handelt, das keinen Server und keine Konfiguration benötigt, gestaltet sich der Installationsvorgang relativ einfach. Weitere Informationen finden Sie unter About SQLite und unter What is SQLite? Top SQLite Features You Should Know.

Laden Sie nun zunächst die SQLite command-line tools unter SQLite Download Page in der Rubrik „Precompiled Binaries for Windows“ als ZIP-Datei herunter.

Hinweis: Diese Anleitung basiert auf der Version 3.32.3 (Dateiname „sqlite-tools-win32-x86-3320300.zip“), bei Verwendung einer anderen Version kann es u.U. zu unerwartetem Verhalten kommen.

Anschließend entpacken Sie die heruntergeladene ZIP-Datei in ein Verzeichnis Ihrer Wahl, beispielsweise „C:/SQLite/SQLite Tools“. Die SQLite command-line tools sind damit installiert. Weitere Informationen zum Einstieg in SQLite finden Sie unter How To Download & Install SQLite Tools.

Falls Sie einen komfortablen Zugriff auf SQLite-Datenbanken mit grafischer Benutzeroberfläche wünschen, können Sie zusätzlich unter https://sqlitebrowser.org das freie Programm sqlitebrowser herunterladen und installieren. Erforderlich ist dies jedoch nicht.

Schritt 4: Erzeugung der SQLite-Datenbank aus der exportierten CSV-Datei

Schritt 4.1: SQLite Command-Line-Tool aufrufen

Nun muss das SQLite Command-Line-Tool gestartet werden. Öffnen Sie hierzu die Windows-Eingabeaufforderung (beispielsweise mittels Windows-Taste+R und Eingabe von cmd) und navigieren Sie zu dem zuvor in Schritt 3 gewählten Installationspfad des SQLite Command-Line-Tools. Lautet der Pfad beispielsweise „C:\SQLite\SQLite Tools“, so geben Sie einfach das Kommando cd "C:\SQLite\SQLite Tools" ein. Starten Sie nun das SQLite Command-Line-Tool, indem Sie Sqlite3 eingeben und die Eingabetaste betätigen.

Schritt 4.2: Datenbankdatei erstellen

Hinweis: Achten Sie zunächst darauf, dass in dem Verzeichnis, in dem die Datenbankdatei (im Folgenden db-Datei) erstellt werden soll, nicht bereits eine gleichnamige db-Datei existiert. SQLite würde in diesem Fall im Laufe des Importvorgangs versuchen, diese db-Datei zu aktualisieren, was zu Konflikten führen und die db-Datei unbrauchbar machen kann. Das angegebene Verzeichnis hingegen muss aber bereits existieren und Schreibrechte gesetzt haben, da die db-Datei sonst nicht erstellt werden kann.

Der Dateiname der zu erstellenden db-Datei (im Folgenden <DB-FILENAME>) muss das Länderkürzel des jeweiligen Landes entsprechend nach ISO 3166 ALPHA-2 sein, bspw. „US“ für die Vereinigten Staaten von Amerika, „FR“ für Frankreich oder „ES“ für Spanien.

Hinweis: Beachten Sie bitte, dass SQLite als Trennzeichen für Verzeichnisse in Pfaden den einfachen Slash / und nicht den Backslash \ verwendet. Wenn Sie also Pfade kopieren anstatt diese manuell einzugeben, müssen Sie etwaige Backslashes durch einfache Slashes ersetzen.

Geben Sie nun den SQLite-Befehl .open <DB-FILENAME> ein, wobei der Parameter <DB-FILENAME> neben dem o.g. Länderkürzel auch den vollständigen Pfad der zu erstellenden db-Datei enthalten muss, beispielsweise:

.open "C:/SQLite/Postcode_import/US.db"

Bestätigen Sie den Befehl nun mit der Eingabetaste. Hiermit wird die Datenbank in Form der im Parameter <DB-FILENAME> spezifizierten db-Datei erstellt und die Verbindung zu dieser hergestellt.

Schritt 4.3: Fremdschlüssel für die Datenbank aktivieren

Da die zu erstellende SQLite-Datenbank Fremdschlüssel verwendet, muss die entsprechende Funktion für die Datenbank aktiviert werden. Geben Sie hierzu den Befehl PRAGMA foreign_keys = ON; ein und bestätigen Sie mit der Eingabetaste. Weitere Informationen hierzu finden Sie unter SQLite Foreign Key Support.

Schritt 4.4: Datenbanktabelle für den Import erstellen

Als Nächstes laden Sie die Datei SQLite_Postcodes_import_complete.txt (1,2 KB) herunter und öffnen diese in SQLite, indem Sie den SQLite-Befehl .read <FILENAME> eingeben und mit der Eingabetaste bestätigen. Als Wert für den Parameter <FILENAME> verwenden Sie hierbei den vollständigen Pfad dieser Datei. Wenn Sie die Datei beispielsweise im Verzeichnis „C:\SQLite\Postcode_import“ gespeichert haben, so lautet der Befehl

.read "C:/SQLite/Postcode_import/SQLite_Postcodes_import_complete.txt"

Durch das Öffnen dieser Datei in SQLlite wird die SQL-Tabelle Postcodes_import erstellt, welche der Aufnahme der Datensätze aus der in Schritt 2 exportierten CSV-Datei dient.

Hinweis: Lassen Sie sich nicht durch den Namensbestandteil „Postcode“ im Dateinamen „SQLite_Postcodes_import_complete.txt“ irritieren, es werden alle Adressdaten importiert, nicht nur die Postcode- bzw. PLZ-Daten.

Schritt 4.5: Import der CSV-Datei

Die Daten der exportierten CSV-Datei müssen nun in die soeben erstellte SQL-Tabelle Postcodes_import importiert werden. Geben Sie hierzu nacheinander folgende SQLite-Befehle ein und bestätigen Sie diese jeweils mit der Eingabetaste:

.mode csv
.separator ";"
.import <FILENAME> Postcodes_import

Als Wert für den Parameter <FILENAME> müssen Sie hierbei den vollständigen Pfad der CSV-Datei verwenden. Lautet der vollständige Pfad beispielsweise „C:\SQLite\Postcode_import\Postcode_DB.csv“, so müssen Sie folgendes eingeben:

.import "C:/SQLite/Postcode_import/Postcode_DB.csv" Postcodes_import

Hinweis:
Mittels des SQLite-Befehls .import <FILENAME> <TABLENAME> werden die Datensätze der CSV-Datei in die SQL-Tabelle importiert. Weitere Informationen hierzu finden Sie unter Import a CSV File Into an SQLite Table. Je nach Anzahl der Datensätze Ihrer CSV-Datei kann dieser Importvorgang einige Zeit dauern.

Hinweis:
Beim Import der Datensätze aus der erzeugten CSV-Datei in die SQLite-Datenbank wird davon ausgegangen, dass als Feldtrennungszeichen in der CSV-Datei das Semikolon ; verwendet wird, da dies in Excel das Standard-Feldtrennungszeichen beim Export nach CSV ist. Sollte in Ihrer CSV-Datei jedoch ein anderes Zeichen als Feldtrennungszeichen verwendet worden sein, haben Sie zwei Möglichkeiten:

  1. Sie führen einen erneuten Export in das CSV-Format aus, wobei Sie das Semikolon als Feldtrennungszeichen erzwingen.
    oder
  2. Sie verwenden bei Eingabe des SQLite-Befehls .separator <SEPARATOR>
    dasjenige Trennzeichen als Wert für den Parameter <SEPARATOR>, das in Ihrer CSV-Datei verwendet wird. Das Komma , oder der Punkt . sollten allerdings nicht als Feldtrennungszeichen verwendet werden, da diese Zeichen bereits als Dezimaltrennzeichen für numerische Daten interpretiert werden.

Schritt 4.6: Finalisierung des Imports

Laden Sie nun die Datei SQLite_InitializePostcodeDB_complete.txt (23,3 KB) herunter und öffnen diese in SQLite, indem Sie den SQLite-Befehl .read <FILENAME> eingeben und mit der Eingabetaste bestätigen. Als Wert für den Parameter <FILENAME> verwenden Sie den vollständigen Pfad dieser Datei. Wenn Sie die Datei beispielsweise im Verzeichnis "C:\SQLite\ Postcode_import" gespeichert haben, so lautet der Befehl

.read "C:/SQLite/Postcode_import/SQLite_InitializePostcodeDB_complete.txt"

Durch das Öffnen dieser Datei in SQLite werden die Daten der temporären SQL-Tabelle Postcodes_import in die erforderlichen finalen SQL-Tabellen der SQLite-Datenbank <ISO-Länderkennung>.db importiert. Dieser Importvorgang kann einige Zeit in Anspruch nehmen, warten Sie daher bitte bis die Anzahl der importierten Datensätze unterhalb des Textes amount_of_records in der Konsole angezeigt wird. Die Anzeige könnte beispielsweise folgendermaßen aussehen:

amount_of_records
--------------------
42521

Schritt 4.7: Überprüfung des Imports

Durch Eingabe des SQLite-Befehls .tables können sie überprüfen, ob alle erforderlichen SQL-Tabellen und -Sichten erfolgreich erstellt wurden. Es müssen genau die folgenden 9 Tabellen (tables) und 4 Sichten (views) sein:

FederalStates              (table)    
AdministrativeDistricts    (table)    
Counties                   (table)    
PostCodes                  (table)    
Streets                    (table)    
Housenumbers               (table)    
CityNames                  (table)    
CityNamesToPostBox         (table)        
PostBox                    (table)     
zip                        (view)
street                     (view)
housenumber                (view)
postboxes                  (view)

Schritt 4.8: SQLite Command-Line-Tool beenden

Nachdem die db-Datei erfolgreich erstellt wurde, können Sie das SQLite Command-Line Tool beenden, indem Sie den SQLite-Befehl .quit eingeben und mit der Eingabetaste bestätigen.

Schritt 5: Bereitstellung der erstellten PLZ- und Straßendatenbank für den combit Relationship Manager

Navigieren Sie nun zu dem Verzeichnis, in dem die Datei „<ISO-Länderkennung>.db“ in Schritt 4.2 erstellt wurde und packen Sie diese Datei mit einem beliebigen Packprogramm in eine ZIP-Datei. Abschließend kopieren Sie die Datei „<ISO-Länderkennung>.zip“ in das Unterverzeichnis „Directories“ Ihrer combit Relationship Manager Installation, z.B. „C:\Program Files (x86)\combit\cRM\Directories“.

Ihre neue PLZ- und Straßendatenbank steht dann nach einem Neustart des combit Relationship Managers zur Verfügung.