Embed Size (px)
Citation preview
1
Datenbeschaffung über OSM
Stefan Maihack Dipl. Ing. (FH)Datum: 21.04.2018
MySQL – Datenbeschaffung1. Möglichkeit - Osmosis
• Osmosis ist ein Commandoline-Tool zum Verarbeiten von OSM-Daten
• Anwendungsbeispiele: Erstellen von Dumps (Datenbankauszüge) aus einer Planet.osm (OSM.Datei der ganzen Erde). Importieren von Planetendumps in eine Datenbank Änderungs-Datensätze (Change Sets) unter Benutzung der Datenbank „historytables“ (Tabellen von Änderungen) erzeugen.
• Change Sets auf eine lokale Datenbank anwenden.
• Zwei Planet-Dateien vergleichen und Änderungs-Datensätze (Change Set) daraus erstellen.
• Neusortieren von Daten in einer Planet Datei.
• Daten aus einem rechteckigen Kartenausschnitt (Bounding Box) oder einem
Polygon extrahieren.
2
3
MySQL – Datenbeschaffung1. Möglichkeit - Osmosis
• Die folgenden Schritte erläutern einen Weg, wie man Daten von OpenStreetMap in eigene MySQL-Dateien überträgt.
1. Das Datenbankmodell muss vorhanden sein.
2. Daten von OpenStreetMap downloaden.
Über OSM-Homepage: über OSM-Homepage XML-Daten eines Kartenausschnittsherunterladen.
Planet-Datei: Die Datei „planet-latest.osm.bz2“ downloaden. Länder-Extrakte: Firmen, wie z.B. „geofabrik“ bieten Länderextrakte auf ihrer
Downloadseite an. OSM-Datei selbst zuschneiden: Das Programm „OSMOSIS“ dient dazu
OSM-Dateien zu bearbeiten, so kann man Dateien umwandeln, Teile (geographisch und/oder thematisch)herauskopieren.http://wiki.openstreetmap.org/wiki/Osmosis
MySQL – Datenbeschaffung1. Möglichkeit - Osmosis
OSMOSIS-Befehl der alle Arztadressen aus der Boundingbox von Bruchsal heraus sucht.
time /t (Zeitpunkt ausgeben)
..\osmosis\bin\osmosis --read-xml file="baden-wuerttemberg.osm.bz2„ (Datenquelle)--bb left=8.3294 right=8.8684 bottom=49.0235 top=49.2364 (Rechteckausschnitt)--tf accept-nodes amenity=doctors (Schlüssel)--write-xml "aerzte_Bruchsal.osm„ (Schlüsselwert)
time /t (Zeitpunkt ausgeben)
4
MySQL – DatenbeschaffungOSM-Datentypen
• Grundlegende Datentypen: Die Daten bestehen entweder aus Punkte, Linien (Sammlung von Punkten) oder Relationen (Gruppen von Objekte). Auch kann jedes Objekt noch sogenannte Tags haben.
• Eigenschaften der Daten: Punkte (nodes) werden durch ihre Koordinaten (in WGS-84) verortet und können alle möglichen „Points of Interest“ beschreiben, z. B. Geschäfte, Sehenswürdigkeiten etc. Linien (ways) beschreiben linienhafte Objekte, z.B. Straßen. Wenn Linien geschlossen sind, so können sie als Polygone angesehen werden (hängt von dem Tag ab) und können dadurch alle Art von Flächen beschreiben (Gebäude und Wasserflächen). Relationen gruppieren Objekte thematisch zusammen, wobei jedes Objekt eine Rolle zugewiesen bekommt.
5
MySQL – DatenbeschaffungEigenschaften der OSM-Daten
• OSM lässt prinzipiell alle möglichen Schlüssel und Werte zu.
• Alle Elemente sollten in Englisch eingetragen werden.
• Folgende Themengebiete sind in den Daten enthalten:
Wege, Barrieren, Fahrradwege, Wirtschaftswege,Wasserläufe, Eisenbahn, Flughafen, Seilbahnen,Energieversorgung, Zivilisationsbauten/Kunstbauten,Freizeit, Nutzung/Einrichtung, Amt / Büro / Dienststelle / Geschäftsstelle,Geschäfte, Handwerk, Notfall, Tourismus, Historisch, Landnutzung,Militär, Natur, Geologie
• Link zu den möglichen Schlüssel-Werte-Paare
http://wiki.openstreetmap.org/wiki/DE:Map_Features
6
MySQL – DatenbeschaffungEigenschaften der OSM-Daten
• Ein Tag ist eine Beschreibung eines Objektes in der OSM zur Kategorisierung und zur Auszeichnung mit zusätzlichen Informationen.
• Beispiele: (http://wiki.openstreetmap.org/wiki/DE:Map_Features) Das Tag highway dient der Weise, um Straßen zu kennzeichnen. Es gibt Vereinbarungen über ihre Verwendung in den einzelnen Ländern, die auf der Wiki-OSM Steite beschrieben sind. Das Tag highway fallen auch die ganzen Wasserwege; Bäche, Flüße,etc. Das Tag landuse wird benutzt für Wohngebiete, Mischgebiete. Unter dieses Tag fallen auch die Einkaufszentren, die dann als Polygon gekennzeichnet werden. Das Tag leisure werden Orte beschrieben, die Hauptsächlich mit der Gestaltung der Freizeit zusammenhängen. Das Tag natural beschreibt natürliche Elemente, wie z.B. Landschaften oder geologische Gegebenheiten.
7
MySQL – DatenbeschaffungEigenschaften der OSM-Daten - Beispiele
8
MySQL – DatenbeschaffungOSMOSIS
• Osmosis dient dazu OSM-Dateien zu bearbeiten, so kann man Dateien umwandeln, Teile (geographisch und/oder thematisch) extrahieren, u.s.w.
• Osmosis ist ein Kommandozeilen-Programm.
• Installation:1. Das Programm herunterladen (http://wiki.openstreetmap.org/wiki/Osmosis)
2. Die Datei entpacken, man erhält folgende Verzeichnisse:/bin/config/lib/scriptchangescopyingreadme
• Beispiel: Die Stadt Wien aus den Komplettdaten von Österreich herausschneiden:
osmosis-0.38/bin/osmosis --read-pbf file=austria.osm.pbf --bounding-box top=48.502 left=15.479 bottom=48.325 right=15.718 completeWays=yes --write-xmlfile=map.osm
9
MySQL – DatenbeschaffungDie wichtigsten OSMOSIS-Befehle
• –read-xml file: Input osm-file
• –bb: Befehl zum Extrahieren
• left, right, bottom, top: Begrenzungen der bounding box
• –tf: Filtern von Tags (key=value). Hier: highway=bus_stop für alle Bushaltestellen.
• accept-nodes: Nodes werden für die Filterung akzeptiert. Hier könnte auch noch „–tfreject-ways“ bzw. „–tf reject-relations“ stehen. Dies würde bedeuten, dass ways und relations nicht beachtet werden.
• –write-xml: extrahierte und neu geschriebene Ausgabedatei
Befehle zum extrahieren aller Bushaltestellen der Heidelberger Altstadt:
10
osmosis --read-xml file="baden-wuerttemberg.osm" --bbleft=8.69661 right=8.71781 bottom=49.40548 top=49.41488 --tf accept-nodes highway=bus_stop --write-xml "busstop_hd_altstadt.osm"
MySQL – DatenbeschaffungOSMOSIS-Beispiele
• Zwei Beispiele, die Nürnberg und einen kleinen Bereich darum aus einer Deutschland-OSM-Datei oder möglicherweise einer Planet.osm-Datei extrahieren:
osmosis \ --read-xml enableDateParsing=no file=germany.osm.bz2 \ --bounding-box top=49.5138 left=10.9351 bottom=49.3866 right=11.201 \ --write-xml nürnberg.osm.bz2
bzcat planet.osm.bz2 | osmosis \ --read-xmlenableDateParsing=no file=/dev/stdin \ --bounding-box top=49.5138 left=10.9351 bottom=49.3866 right=11.201 \ --write-xml file=-\ | bzip2 > nürnberg.osm.bz2
• Der zweite Ansatz verwendet bzip2 direkt zum extrahieren und komprimieren, weil es effektiver ist als der eingebaute Archivierer.
• bzcat ist das Werkzeug, dass man auch unter Windows einsetzen sollte, wenn man gute Performance haben möchte. Nur ist /dev/stdin kein gültiger Dateipfad in Windows. Aber man kann CONIN$ als Pfadangabe verwenden.
11
MySQL – DatenbeschaffungOSMOSIS-Beispiele
12
osmosis --read-xml baden-wuerttemberg.osm --tf accept-relations boundary=administrative --used-way --used-node --wx admin_grenzen.osm
Alle administrativen Grenzen in Baden-Württemberg werden extrahiert.
Nürnberg und einen kleinen Bereich darum aus der Deutschland-OSM-Datei extrahieren
osmosis \ --read-xml enableDateParsing=no file=germany.osm.bz2 \--bounding-box top=49.5138 left=10.9351 bottom=49.3866 right=11.201 \--write-xml nürnberg.osm.bz2
MySQL – DatenbeschaffungOSMOSIS-Beispiele
Erstellen von 16 individuellen BZ2-OSM-Files
osmosis \ --rx full/planet.osm.bz2 \ --tee 16 \ --bpfile=polygons/europe/germany/baden-wuerttemberg.poly \ --wx baden-wuerttemberg.osm.bz2 \ --bp file=polygons/europe/germany/bayern.poly \ --wxbayern.osm.bz2 \ --bp file=polygons/europe/germany/berlin.poly \ --wxberlin.osm.bz2 \ --bp file=polygons/europe/germany/brandenburg.poly \ --wxbrandenburg.osm.bz2 \ --bp file=polygons/europe/germany/bremen.poly \ --wxbremen.osm.bz2 \ --bp file=polygons/europe/germany/hamburg.poly \ --wxhamburg.osm.bz2 \ --bp file=polygons/europe/germany/hessen.poly \ --wxhessen.osm.bz2 \ --bp file=polygons/europe/germany/mecklenburg-vorpommern.poly \ --wx mecklenburg-vorpommern.osm.bz2 \ --bpfile=polygons/europe/germany/niedersachsen.poly \ --wxniedersachsen.osm.bz2 \ --bp file=polygons/europe/germany/nordrhein-westfalen.poly \ --wx nordrhein-westfalen.osm.bz2 \ --bpfile=polygons/europe/germany/rheinland-pfalz.poly \ --wx rheinland-pfalz.osm.bz2 \ --bp file=polygons/europe/germany/saarland.poly \ --wxsaarland.osm.bz2 \ --bp file=polygons/europe/germany/sachsen-anhalt.poly \--wx sachsen-anhalt.osm.bz2 \ --bpfile=polygons/europe/germany/sachsen.poly \ --wx sachsen.osm.bz2 \ --bpfile=polygons/europe/germany/schleswig-holstein.poly \ --wx schleswig-holstein.osm.bz2 \ --bp file=polygons/europe/germany/thueringen.poly \ --wxthueringen.osm.bz2
13
MySQL – DatenbeschaffungOSMOSIS - Beispiele
• Extrahiert komplett Karlsruhe aus Europadaten:osmosis --read-xml enableDateParsing=no file=europe-latest.osm --bounding-box top=49.1310 left=8.2013 bottom=48.9008 right=8.6177 --write-xml file=karlsruhe.osm
• Extrahiert aus den Daten von Karlsruhe Haltestellen und Bahnlinienosmosis --read-xml karlsruhe.osm --way-key-valuekeyValueList="railway.tram,railway.tram_stop" --used-node --write-xmlkarlsruhe_tram.osm
• Extrahiert alle Restaurants in Karlsruhe:osmosis --read-xml karlsruhe.osm --way-key-value keyValueList=„amenity.restaurant" --used-node --write-xml karlsruhe_tram.osm
• Verwendung eines Packprogrammsbzcat downloaded.osm.bz2 | osmosis --read-xml enableDateParsing=no file=- --bounding-box top=49.5138 left=10.9351 bottom=49.3866 right=11.201 --write-xmlfile=- | bzip2 > extracted.osm.bz2
14
MySQL – DatenbeschaffungOSMOSIS
• Der einfachste Weg, um die Koordinaten für einen Rechteck-Ausschnitt zu bestimmen, ist der "Export"-Reiter auf www.openstreetmap.org - dort kann man ein Rechteck markieren, dabei werden die Koordinaten angezeigt.
15
MySQL – DatenbeschaffungOSMOSIS - Datengröße
• Europe-latest.osm 196 GB (als bz2 nur 18GB)
• Karlsruhe 245 MB
• Karlsruhe_tram 7,6 MB
• Koordinaten Bruchsal49,2389 8,389849,0093 8,8062
16
Datenbeschaffung2. Möglichkeit – overpass-turbo.eu
• Tool um die enormen Datenmengen von OpenStreeMap zu durchsuchen.
• Overpass-turbo benutzt eine eigene Abfragesprache.
• Funktionsweise:Der overpass-Client setzt direkt Abfragen an die OSM-Karten. Zurück kommen dann die Daten, die durch die Abfrage abgesprochen wurden.
• Welche Abfragen sind z.B. möglich? Abfrage nach Timestamps (z.B. letzte Änderungen) Abfrage nach User Suche in einem Polygon …
Datenbeschaffung2. Möglichkeit – overpass-turbo.eu
Datenbeschaffung2. Möglichkeit – overpass-turbo.eu
• Seitenaufrufe/Benutzer
19
Datenbeschaffung2. Möglichkeit – overpass-turbo.eu
• Exportdatenformate von Overpass-Turbo GEOJSON GPX KML Rohdaten GIST
20
GIS-FormateGeoJSON
• Das GeoJSON – Format (GEO JavaScript Object Notation)
• einfaches kompaktes Datenformat in einer einfach lesbaren Textform zum Zweck des Datenaustauschs zwischen Anwendungen.
• Jedes gültige JOSN-Dokument soll ein gültiges Javascript sein und per „eval()“ interpretiert werden können.
• Die Funktionen umfassen:Punkte (z.B.: Adressen, Standorte)Linienzüge (z.B.: Straßen, Autobahnen, Grenzen)Poilygone (z.B.: Länder, Provinzen, Landflächen)
• Unterschied zu XML: XML ist eine Auszeichnungssprache dadurch vielseitiger einsetzbar als JOSN (Nur Datenaustauschformat).JOSN hat XML aufgrund seiner Einfachheit zurückgedrängt.
• Sinnvoll für direkte JavaScript-Nutzung.21
GIS-FormateGeoJSON
22
Im Vergleich ist in diesem Beispiel die XML-Datei 29% größer als die JOSN-Datei
JSON XML
GIS-FormateGPX
• GPX – GPS Exchange Format: Datenformat zur Speicherung von Geodaten (ursprüngliche für GPS-Daten).
• Entwickelt von der Firma TopoGrafix.
• Basiert auf dem allgemeinen XML-Standard. Ein XML-Schema beschreibt die Elemente und den Aufbau des GPS Exchange Formats.
• Das Daten-Schema ist erweiterbar. Normalerweise nur Wegpunkte, Routen und Tracks. Erweiterbar um z.B.: Autor, Beschreibung etc.
• XML-Daten lassen sich nicht gut in Standard-Datenbanken speichern und verarbeiten.
23
GIS-FormateKML
• KML ist eine Auszeichnungssprache zur Beschreibung von Geodaten.
• Bekannt wurde sie durch die Anwendung im GoogleEarth-Programm.
• KML befolgt die XML-Syntax, liegt in der Version 2.2 vor und ist ein Standard des Open Geospatial Consortium.
• KML-Dokumente können Geodaten sowohl in Vektor- wie auch in Rasterform beinhalten.
• Vektorobjekte wie Punkte, Linien, lineare Ringe, Polygone oder COLLADA-Modelle werden als Placemark-Elemente und Luft- und Satellitenbilder als GroundOverlay-Elemente modelliert.
• Da es sich um XML-Daten handelt, lassen sich auch nur schlecht mit herkömmlichen Datenbanken verarbeiten.
24
GIS-FormateRohdaten
• Beim Rohdatenformat handelt es sich um das OSM-Format XML-Daten.
• Da es sich auch hier um XML-Daten handelt, lassen sich auch nur schlecht mit herkömmlichen Datenbanken verarbeiten.
25
GIS-FormateUmwandeln der XML-Geodaten
• Da es sich bei den Overpass-Turbo-Formaten fast ausschließlich um XML-Daten handelt, müssen diese eine Tabellenähnliche Struktur umgewandelt werden.
• Als Werkzeug dient z.B. Excel und ein Texteditor, der reguläre Ausdrücke verarbeiten kann.
26
Aufbereiten der Daten mittels Excel
1. Aus OVERPASS-TURBO heraus XML-Daten exportieren. ACHTUNG: Die Datei muss die Endung XML besitzen.
2. Excel hat aus den XML-Daten eine Tabelle erstellt. Die nicht notwendigen Spalten eliminieren.
27
MySQL – DatenbeschaffungAufbereiten der Daten mittels Tabellenkalkulationsprogramme (z.B. Excel) XML-Daten aufbereiten:
28
MySQL – Datenbeschaffung• 4. Ladedatei für MySQL mit den Daten aus OSM erstellen.
29
/* create table "aerzte_bruchsal" */
DROP TABLE IF EXISTS aerzte_bruchsal; SHOW WARNINGS;CREATE TABLE IF NOT EXISTS aerzte_bruchsal(
id INT(2), Stadt VARCHAR(20), Land VARCHAR(5),Hausnummer VARCHAR(10),Postleitzahl VARCHAR(5),Straße VARCHAR(30,)Arztart VARCHAR(30),Name VARCHAR(20),Rollstuhl VARCHAR(10),lat Float(10),lon Float(10),PRIMARY KEY (id)
); SHOW WARNINGS;
/*Insert values into table*/INSERT INTO aerzte_bruchsal (id, Stadt, Land, Hausnummer, Postleitzahl, Straße, Arztart, Name, Rollstuhl, lat, lon) VALUES(1,'Dettenheim', 'DE','137a','76706','Hauptstraße','Doktor','Dr. Wächter','limited',49.1655136,8.4151351),(2,'Bad Schönborn-Mingolsheim','DE','3a','76669','Hebelstraße','Doktor','Dr. Zimmermann','limited',49.2237108, 8.6387469),49,2237108 8,6387469 name Dr. Wittek49,2200384 8,6368942 amenity doctors49,2200384 8,6368942 name Dr. D. Baierl und Dr. A. Grahmann-Baierl49,2200384 8,6368942 wheelchair no49,1248666 8,5929033 amenity doctors49,1248666 8,5929033 healthcare centre49,1248666 8,5929033 name Ärztehaus Postcenter49,1248666 8,5929033 wheelchair yes
MySQL – Datenbeschaffung
• Vorgehensweise:
1. Exportieren der Exceltabellen in CSV-Dateien (Comma-separated-Value)
2. CSV-Dateien in einem Texteditor bearbeiten, so dass für den INSERT-Befehl eine fast korrekte Syntax entsteht. Am besten mit Hilfe von Regulären Ausdrücken.
30
MySQL – DatenbeschaffungVerwendung Regulärer Ausdrücke
• Suche in allen Textstellen in einer Markierung, die Groß- und Kleinbuchstaben enthalten und diese dann in Hochkomma setzen.
Suche nach: ([A-Z a-z]+)Ersetze durch: ´\1´
• Ein Komma statt einem Leerzeichen einfügen
Suche nach: ([A-Z]+) ([A-Z]+) ([A-Z]+) ([0-9]+ (.*)Ersetze durch: \1,\2,\3,\4,\5
• Beginn des INSERT´s. Erstes Zeichen in einer Zeile wird ´(
Suche nach: (^)Ersetze durch: \(´\1
• Ende des INSERT´s. Letzte Zeichen in einer Zeile wird ),
Suche nach: ($)Ersetze durch: ´\), \1
31
Egal was noch kommt,Es ist eine Gruppe
MySQL – DatenbeschaffungVerwendung Regulärer Ausdrücke
• Suche in allen Textstellen in einer Markierung, die Groß- und Kleinbuchstaben und Leerzeichen enthalten. Diese dann in Hochkomma setzt.
Suche nach: ([A-Z a-z]+\s+[A-Z a-z]+)Ersetze durch: ´\1´
32
JOSM –OSM-Daten visualisieren
33
MySQL – DatenbeschaffungLinksammlung
• OSMOSIS Download-Seite (enthält auch einige Beispiele)http://wiki.openstreetmap.org/wiki/Osmosis
• Liste aller möglichen Parameter von OSMOSIS: http://wiki.openstreetmap.org/wiki/Osmosis/Detailed_Usage
• OpenStreetmaps Datenextraktehttp://download.geofabrik.de/
• OpenStreeMap-FAQhttp://www.openstreetmap.de/faq.html
• Overpass-turbohttp://overpass-turbo.eu/
• JOSMhttps://josm.openstreetmap.de/
34
MySQL – DatenbeschaffungDie Ladedatei
001 /* Data are taken from Dortmund but reduced and
002 changed to suit different aspects. */
003 DROP DATABASE IF EXISTS sample;
004 SHOW WARNINGS;
005 CREATE DATABASE IF NOT EXISTS sample
006 DEFAULT CHARACTER SET utf8 COLLATEutf8_unicode_ci;
007 SHOW WARNINGS;
008 USE sample;
009 SHOW WARNINGS;
010 DROP TABLE IF EXISTS routes;
011 SHOW WARNINGS;
012 CREATE TABLE IF NOT EXISTS routes (
013 id INT2,
014 name VARCHAR(10) BINARY,
015 color VARCHAR(10) BINARY,
016 colorhex VARCHAR(10) BINARY,
017 PRIMARY KEY (id)
018 );
019 SHOW WARNINGS;
35
* Erstellen einer MySQL-Ladedatei
MySQL – DatenbeschaffungDie Ladedatei - Constraints
Löschen einer Tabelle und Kreieren mit Schlüssel und Constraints(Sql/sample.sql)
003 DROP DATABASE IF EXISTS sample;
005 CREATE DATABASE IF NOT EXISTS sample
006 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
008 USE sample;
056 DROP TABLE IF EXISTS s_r;
058 CREATE TABLE IF NOT EXISTS s_r (
059 s_id INT2,
060 r_id INT2,
061 seq INT2 UNSIGNED DEFAULT NULL,
062 spa INT4 DEFAULT NULL,
063 dur TIME DEFAULT NULL,
064 PRIMARY KEY (s_id, r_id),
065 FOREIGN KEY (s_id) REFERENCES stops(id),
066 FOREIGN KEY (r_id) REFERENCES routes(id)
067 );
36
Auflistung möglicher Contraints:
• NOT NULL:der Skalar darf nicht Null sein.
• PRIMARY KEY:der Skalar muss einzigartig undnicht NULL sein.
• Foreign Key:der Skalar muss auf referentielle Integrität geprüft sein.
• UNIQUE:der Skalar muss innerhalb einesAttributes einzigartig sein.
• CHECK():explizite Überprüfungsanweisung
MySQL – DatenbeschaffungÜbung 1
37
• Aufgabe:
1. Erstellen Sie ein ER-Modell ihrer Studienarbeit
2. Extrahieren sie die Daten aus OpenStreetMap
3. Erzeugen sie analog zu den MySQL-Tabellen Excel-Tabellen und befüllen Sie
diese mit Daten.
4. Erstellen Sie die Datenbank mittels SQL-Script in MySQL.
5. Füllen Sie eine Tabelle davon mit dem SQL-Kommando INSERT.
