Ein Mitglied der lokalen OpenStreetMap community hat mich gefragt ob ich ihm dabei helfen kann einen speziellen Extrakt aus den Daten zu erzeugen.
Das könnte auch für andere interessant sein, daher beschreibe ich hier die einzelnen Schritte etwas ausführlicher.
Thailands Tankstellen in Excel
Das Ziel sollte sein eine Liste Thailands Tankstellen in Excel zu bekommen. Dabei soll der Name der Tankstelle (Shell, Esso, usw.) und die Koordinate ausgegeben werden.
Die Grundidee ist für die Abfrage die großartige Overpass API von Roland Olbricht zu verwenden. Mit der overpass turbo GUI von Martin Raifer ist auch die Entwicklung der Abfrage nicht so schwierig.
Die einfachste Art die Tankstellen abzufragen ist der Wizard von overpass turbo. Dazu muss man nur wissen, dass der Tag für Tankstellen amenity=fuel lautet.
Die Angabe von
|
1 |
amenity=fuel in Thailand |
erzeugt dann eine Abfrage die alle so getaggten Knoten, Wege und Relationen ausgibt.
Mehr Zeit
Die erste Veränderung besteht darin der Abfrage mehr Zeit zu geben, da der default-timeout von 25 Sekunden recht knapp ist. Im Quelltext der Abfrage ändere ich den timeout auf 60 Sekunden.
|
1 |
[timeout:60] |
Export nach Excel
Um die Daten in Excel zu bekommen ändere ich das Ausgabeformat von Overpass da Excel mit JSON nichts anfangen kann. Mit einem Tabulator-getrennten Format funktioniert es in Excel viel besser.
Es müssen auch nicht alle Tags exportiert werden sondern nur der Name. Historisch bedingt ist das etwas komplizierter. Das was gesucht ist steckt eigentlich im Key „brand„. In der Vergangenheit wurde das allerdings auch häufig im „name“ oder „operator“ abgelegt. Ist kein größeres Problem, ich lasse einfach alle passenden Keys ausgeben und selektiere später im Excel das passende Feld.
Die Ausgabe nach CSV, bzw Tabulator-getrennt erfolgt mit diesem Statement:
|
1 |
[out:csv(::lat,::lon,"brand:en","brand","operator:en","name:en","operator","name")] |
Nur Tankstellen mit Namen
Die Abfrage lieft jetzt allerdings auch Tankstellen zurück die überhaupt keinen Namen haben. Ich erweitere daher die Abfrage um eine Klausel die in mindestens einem meiner ausgegebenen Keys einen Wert erfordert.
Für Nodes sieht es dann so aus:
|
1 |
node["amenity"="fuel"][~"(brand:en|brand|operator:en|name:en|operator|name)"~".+"](area.searchArea); |
Flächen mit Koordinaten
Die Abfrage liefert schon einige Tankstellen zurück, allerdings fehlen bei Tankstellen die als Fläche getaggt sind die Koordinaten. Das liegt daran, dass in OpenStreetMap nur Knoten eine Koordinate haben.
Glücklicherweise kann Overpass für Wege eine Koordinate berechnen. Dazu wird der Mittelpunkt der Bounding-Box aller Knoten eines Weges verwendet. Für diesen Anwendungsfall ist das genau genug.
Die Ausgabe passe ich entsprechend an. Da ich keine spezielle Sortierung brauche verwende ich die Reihenfolge der Datenbank was die Abfrage nochmal etwas schneller macht.
|
1 |
out qt center; |
Abfrage ausführen
Die fertige Abfrage sieht nun so aus:
|
1 2 3 4 5 6 7 8 |
[out:csv(::lat,::lon,"brand:en","brand","operator:en","name:en","operator","name")][timeout:60]; {{geocodeArea:Thailand}}->.searchArea; ( node["amenity"="fuel"][~"(brand:en|brand|operator:en|name:en|operator|name)"~".+"](area.searchArea); way["amenity"="fuel"][~"(brand:en|brand|operator:en|name:en|operator|name)"~".+"](area.searchArea); relation["amenity"="fuel"][~"(brand:en|brand|operator:en|name:en|operator|name)"~".+"](area.searchArea); ); out qt center; |
Im Export-Menü der overpass turbo Oberfläche findet man einen Link mit dem die erzeugte Abfrage direkt an overpass übergeben werden kann. Diesen Link speichere ich für zukünftige Aktualisierungen. Die Ausgabe der Abfrage speichere ich als fuel.csv ab.
UTF-8 Kodierung
Da sich zumindest mein Excel etwas komisch anstellt die thailändischen Schriftzeichen korrekt anzuzeigen wenn ich die csv-Datei direkt öffne wandle ich zunächst die Kodierung in UTF-8 mit BOM um. Ich verwende dazu Notepad++, das geht aber auch mit jedem anderen Editor der das unterstützt.
In Excel erscheint nun auch das Thai Script korrekt.
Import in Excel
Mit der Excel Funktion „Text in Spalten“ lasse ich die einzelnen Felder auftrennen. Je nach Ländereinstellung kann der Punkt der Koordinate Probleme verursachen.
Abhilfe schafft es die Spalte als „Text“ zu behandeln. Dann lassen sich auch die Punkte durch Kommas ersetzten um das Feld in Excel als Zahl verwenden zu können.
Nach dem Import der Tankstellen in Excel
Namen auswählen
Ich will nur einen Namen für die Tankstellen haben. Daher verwende ich Excel-Funktionen um das erste nicht-leere Feld zu selektieren. Das lasse ich dann in einer neuen Spalte berechnen. Die Formel muss mit Strg-Umschalt-Enter bestätigt werden.
|
1 |
=INDEX(D2:I2;VERGLEICH(FALSCH;ISTLEER(D2:I2);FALSCH)) |
Die Formel wird durch Excel nach einem Doppelklick auf das kleine Quadrat unten rechts in der Auswahl auf alle weiteren Zeilen kopiert.
Thailands Tankstellen in Excel mit passender Bezeichnung
Im Ergebnis hat man nun in den ersten drei Spalten eine Liste der Tankstellen mit Koordinate.
Mission erfüllt.
Stephan,
U R a genius – thanks for all your help, and now i dont have to worry about way-pointing gas stations that are already there, as they show up on the Zumo screen, courtesy of the POI file.
I can now keep my eyes more on the road, and less on the Garmin!!
Vielen Dank für den gelungenen Post 🙂 Da das CSV in Overpass API noch recht neu ist, sind Schritt-für-Schritt-Anleitungen immer sehr gerne gesehen.
Nun noch ein paar Anmerkungen…
Beim Testen mit LibreOffice sind bisher keine Probleme mit dem Zeichensatz aufgetreten. Es scheint aber so zu sein, dass zumindest für Excel zusätzlich ein BOM für UTF-8 benötigt wird. Kannst Du bitte dafür ein Ticket auf https://github.com/drolbr/Overpass-API/issues aufmachen, damit wir uns das mal ansehen können?
Auch würde ich empfehlen, den timeout deutlich höher zu setzen. Momentan werden für CSV noch keine Fehlermeldungen ausgegeben, und wenn zwischenzeitlich der Timeout zuschlägt, ist das Ergebnis unvollständig. Dazu gibt es auch ein Ticket, allerdings wurde noch nicht final festgelegt, wie genau Fehlermeldungen im CSV-Format ausgegeben werden sollen (https://github.com/drolbr/Overpass-API/pull/145)
Pingback: weekly 231 – 16.12.-22.12.2014 | weekly – semanario – săptămânal – haftalık – 週刊
Hello, thanks for the nice tutorial. As you may be aware, it was featured in OSM Weekly issue 231 and it piqued my interest.
Would it be possible for me to translate it into English and Czech? I would of course link it back to your original article, note that my work is a translation and acknowledge you as the original author.
I’m happy to hear that this article is useful for the OSM community. Please go on with the translation. You might need to translate the Excel formula as well. You can find the translated function names in the net.
Moin !
das muss ich gleich einmal ausprobieren. Klasse wäre es jetzt noch, wenn dieses direkt in der overpass turbo möglich wäre.
Gruß Jan