OSM-Datenbeschaffung
Werbung
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
