OSM-Datenbeschaffung

Datenbeschaffung über OSM
Stefan Maihack Dipl. Ing. (FH)
Datum: 21.04.2015
1
MySQL – Datenbeschaffung
1. 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 „history
tables“ (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
MySQL – Datenbeschaffung
1. 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 Kartenausschnitts
herunterladen.
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
3
MySQL – Datenbeschaffung
1. Möglichkeit - Osmosis
OSMOSIS-Befehl der alle Arztadressen aus der Boundingbox von Bruchsal heraus sucht.
time /t
..\osmosis\bin\osmosis --read-xml file="baden-wuerttemberg.osm.bz2„
--bb left=8.3294 right=8.8684 bottom=49.0235 top=49.2364
--tf accept-nodes amenity=doctors
--write-xml "aerzte_Bruchsal.osm„
time /t
(Zeitpunkt ausgeben)
(Datenquelle)
(Rechteckausschnitt)
(Schlüssel)
(Schlüsselwert)
(Zeitpunkt ausgeben)
4
MySQL – Datenbeschaffung
OSM-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 – Datenbeschaffung
Eigenschaften 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 – Datenbeschaffung
Eigenschaften 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 WikiOSM 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 – Datenbeschaffung
Eigenschaften der OSM-Daten - Beispiele
8
MySQL – Datenbeschaffung
OSMOSIS
•
•
•
•
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
/script
changes
copying
readme
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-xml
file=map.osm
9
MySQL – Datenbeschaffung
Die 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 „–tf
reject-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:
osmosis --read-xml file="baden-wuerttemberg.osm" --bb
left=8.69661 right=8.71781 bottom=49.40548 top=49.41488
--tf accept-nodes highway=bus_stop --write-xml "busstop_hd_altstadt.osm"
10
MySQL – Datenbeschaffung
OSMOSIS-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-xml
enableDateParsing=no file=/dev/stdin \ --bounding-box
top=49.5138 left=10.9351 bottom=49.3866 right=11.201 \ --writexml 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 – Datenbeschaffung
OSMOSIS-Beispiele
Alle administrativen Grenzen in Baden-Württemberg werden extrahiert.
osmosis --read-xml baden-wuerttemberg.osm --tf accept-relations
boundary=administrative --used-way --used-node --wx admin_grenzen.osm
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
12
MySQL – Datenbeschaffung
OSMOSIS-Beispiele
Erstellen von 16 individuellen BZ2-OSM-Files
osmosis \ --rx full/planet.osm.bz2 \ --tee 16 \ --bp
file=polygons/europe/germany/baden-wuerttemberg.poly \ --wx badenwuerttemberg.osm.bz2 \ --bp file=polygons/europe/germany/bayern.poly \ --wx
bayern.osm.bz2 \ --bp file=polygons/europe/germany/berlin.poly \ --wx
berlin.osm.bz2 \ --bp file=polygons/europe/germany/brandenburg.poly \ --wx
brandenburg.osm.bz2 \ --bp file=polygons/europe/germany/bremen.poly \ --wx
bremen.osm.bz2 \ --bp file=polygons/europe/germany/hamburg.poly \ --wx
hamburg.osm.bz2 \ --bp file=polygons/europe/germany/hessen.poly \ --wx
hessen.osm.bz2 \ --bp file=polygons/europe/germany/mecklenburgvorpommern.poly \ --wx mecklenburg-vorpommern.osm.bz2 \ --bp
file=polygons/europe/germany/niedersachsen.poly \ --wx
niedersachsen.osm.bz2 \ --bp file=polygons/europe/germany/nordrheinwestfalen.poly \ --wx nordrhein-westfalen.osm.bz2 \ --bp
file=polygons/europe/germany/rheinland-pfalz.poly \ --wx rheinlandpfalz.osm.bz2 \ --bp file=polygons/europe/germany/saarland.poly \ --wx
saarland.osm.bz2 \ --bp file=polygons/europe/germany/sachsen-anhalt.poly \
--wx sachsen-anhalt.osm.bz2 \ --bp
file=polygons/europe/germany/sachsen.poly \ --wx sachsen.osm.bz2 \ --bp
file=polygons/europe/germany/schleswig-holstein.poly \ --wx schleswigholstein.osm.bz2 \ --bp file=polygons/europe/germany/thueringen.poly \ --wx
13
thueringen.osm.bz2
MySQL – Datenbeschaffung
OSMOSIS
•
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.
14
MySQL – Datenbeschaffung
OSMOSIS - Datengröße
•
•
•
Europe-latest.osm
Karlsruhe
Karlsruhe_tram
•
Koordinaten Bruchsal
49,2389
8,3898
49,0093
8,8062
196 GB (als bz2 nur 18GB)
245 MB
7,6 MB
15
Datenbeschaffung
2. 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
…
Datenbeschaffung
2. Möglichkeit – overpass-turbo.eu
• Seitenaufrufe/Benutzer
17
Datenbeschaffung
2. Möglichkeit – overpass-turbo.eu
• Exportdatenformate von Overpass-Turbo
GEOJSON
GPX
KML
Rohdaten
GIST
18
GIS-Formate
GeoJSON
• 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.
19
GIS-Formate
GeoJSON
Im Vergleich ist in diesem Beispiel die XML-Datei 29% größer als die JOSN-Datei
JOSN
XML
20
GIS-Formate
GPX
• 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.
21
GIS-Formate
KML
• KML ist eine Auszeichnungssprache zur Beschreibung von
Geodaten.
• Bekannt wurde sie durch die Anwendung im GoogleEarthProgramm.
• 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.
22
GIS-Formate
Rohdaten
• Beim Rohdatenformat handelt es sich um das OSM-Format XMLDaten.
• Da es sich auch hier um XML-Daten handelt, lassen sich auch nur
schlecht mit herkömmlichen Datenbanken verarbeiten.
23
GIS-Formate
Umwandeln 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.
24
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.
25
MySQL – Datenbeschaffung
Aufbereiten der Daten mittels Tabellenkalkulationsprogramme (z.B. Excel) XML-Daten
aufbereiten:
26
MySQL – Datenbeschaffung
•
4. Ladedatei für MySQL mit den Daten aus OSM erstellen.
/* 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. Wittek
49,2200384
8,6368942
amenity
doctors
49,2200384
8,6368942
name
Dr. D. Baierl und Dr. A. Grahmann-Baierl
49,2200384
8,6368942
wheelchair
no
49,1248666
8,5929033
amenity
doctors
49,1248666
8,5929033
healthcare
centre
49,1248666
8,5929033
name
Ärztehaus Postcenter
49,1248666
8,5929033
wheelchair
yes
27
MySQL – Datenbeschaffung
• Vorgehensweise:
1. Exportieren der Exceltabellen in CSV-Dateien (Comma-separatedValue)
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.
28
MySQL – Datenbeschaffung
Verwendung Regulärer Ausdrücke
•
Suche in allen Textstellen in einer Markierung, die
Groß- und Kleinbuchstaben enthalten
Suche nach: ([A-Z a-z]+)
Ersetze durch: ´\1´
•
Ein Komma statt einem Leerzeichen einfügen
Egal was noch kommt,
Es ist eine Gruppe
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
29
MySQL – Datenbeschaffung
OSMOSIS - 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 Bahnlinien
osmosis --read-xml karlsruhe.osm --way-key-value
keyValueList="railway.tram,railway.tram_stop" --used-node --write-xml
karlsruhe_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 Packprogramms
bzcat downloaded.osm.bz2 | osmosis --read-xml enableDateParsing=no file=- -bounding-box top=49.5138 left=10.9351 bottom=49.3866 right=11.201 --write-xml
file=- | bzip2 > extracted.osm.bz2
30
JOSM –
OSM-Daten visualisieren
31
MySQL – Datenbeschaffung
Linksammlung
•
•
•
•
•
•
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 Datenextrakte
http://download.geofabrik.de/
OpenStreeMap-FAQ
http://www.openstreetmap.de/faq.html
Overpass-turbo
http://overpass-turbo.eu/
JOSM
https://josm.openstreetmap.de/
32
MySQL – Datenbeschaffung
Die Ladedatei
* Erstellen einer MySQL-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 COLLATE
utf8_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;
33
MySQL – Datenbeschaffung
Die 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 );
Auflistung möglicher
Contraints:
•
•
•
•
•
NOT NULL:
der Skalar darf nicht Null sein.
PRIMARY KEY:
der Skalar muss einzigartig und
nicht NULL sein.
Foreign Key:
der Skalar muss auf referentielle
Integrität geprüft sein.
UNIQUE:
der Skalar muss innerhalb eines
Attributes einzigartig sein.
CHECK():
explizite Überprüfungsanweisung
34
MySQL – Datenbeschaffung
Übung 1
•
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.
35