Entfernungen zu GPX Tracks mit OpenRefine ermitteln
Bild von GeoJango Maps auf Unsplash.In diesem Beitrag verarbeiten wir XML in Form von GPX-Dateien mit OpenRefine und berechnen via WebAPIs die Entfernung von unserem Standort zu dem Startpunkt der GPX-Tracks.
Aktuelle Wanderführer bieten die darin beschriebenen Wanderungen häufig zusätzlich als GPX-Datei zum Nachwandern als Download an. Auch Tourismusbüros/verbände/gemeinschaften/… bieten in Kooperation mit Diensten wie OutdoorActive lokale Wanderungen digital an. Planen wir einen Betriebsausflug, oder eine Wanderung, dann ist die Länge der Anfahrt zum jeweiligen Startpunkt durchaus ein wesentliches Entscheidungskriterium für oder gegen eine Wanderung. Anstatt manueller Einzelrecherchen testen wir in diesem Beitrag, wie sich mit OpenRefine die Entfernung und Fahrtdauer mit dem Auto bzw. dem öffentlichen Nahverkehr von unserem Standort zu den Startpunkten der einzelnen Wanderungen berechnen lässt.
Dabei kombinieren wir OpenRefine mit Geodaten im XML basierten GPX-Format und fragen mit den extrahierten Daten verschiedene WebAPIs an.
Unsere Testdaten
Als Testdaten verwenden wir die aktuell 21 Wanderungen aus dem Programm der hochgehberge. Diese lassen sich direkt von der Webseite als GPX-Datei einzeln herunterladen. Dafür muss nach Auswahl einer Tour etwas nach oben gescrollt werden. Wie in Abbildung 1 gezeigt, aktivieren wir zusätzlich die Checkbox bei “Verknüpfte Wegpunkte exportieren”. Das ermöglicht uns bei den Daten der “hochgehberge” nicht nur einen Abgleich mit dem Startpunkt der Wanderungen durchzuführen, sondern auch mit alternativen Parkplätzen, die sich als Einstieg zur Tour eignen.
Routing APIs
Wir verwenden für unseren Test die WebAPIs von Google Maps, HERE und OpenRouteService. Für all diese Dienste benötigen wir einen Account und müssen einen API Schlüssel erstellen, um mit OpenRefine eine Anfrage stellen zu können. Alle Dienste haben ein “freies” Kontingent, welches für unsere Anwendung vollkommen ausreichend ist. Im Falle von Google Maps ist jedoch eine Kreditkarte notwendig, um den Dienst aktivieren zu können.
Da das Warten auf die Bestätigungs-E-Mail manchmal mehrere Minuten dauert, lohnt es sich die Registrierung schon jetzt durchzuführen. Wie das funktioniert und wie bei den einzelnen Diensten API Schlüssel erstellt wird, ist in der jeweiligen Dokumentation deutlich besser und umfangreicher beschrieben, als wir es hier können.
Daten importieren und aufarbeiten
Daten in OpenRefine importieren
Bei vielen Dateien oder verschachtelten Strukturen mit Unterordnern empfiehlt es sich, diese in eine ZIP-Datei zu packen, was das Laden in OpenRefine vereinfacht. Wie in Abbildung 2 gezeigt, kann eine ZIP-Datei direkt als Datenquelle für ein neues Projekt ausgewählt werden.
Wie in Abbildung 3 gezeigt, werden die einzelnen Dateien vor dem Import aus den Unterordnern geladen, und können in einem separaten Dialog nach Dateityp gefiltert werden. Auch eine Filterung basierend auf Mustern im Dateinamen ist möglich.
Im XML-Import-Dialog wählen wir anschließend das GPX-Wurzelelement aus und erhalten die in Abbildung 4 gezeigten Daten in OpenRefine angezeigt.
Überflüssige Spalten entfernen
In Standard GPX-Tracks würden wir hier einfach den ersten Punkt des GPX-Tracks verwenden und den Rest verwerfen. Da wir in den GPS-Daten der “hochgehberge” aber pro Tour teilweise mehrere mögliche Parkplätze zum Starten als Wegpunkte hinterlegt haben, machen wir den Abgleich hier über die Wegpunkte und verwerfen die Track-Daten.
Wie in Abbildung 5 gezeigt, entfernen wir dafür überflüssige Spalten via “All" "Edit columns" "Re-order / remove columns…”.
Die folgenden Spalten behalten wir vorläufig:
- File => als Record Identifier, also Zuordnung von Startpunkten zu einzelnen Touren.
- WPT lon, lat => Zur Routenplanung via API.
- WPT Name + Type => zum Filtern von Startpunkten (Parkplätzen).
- WPT DESC + Link => ggf. zur weiteren Recherche hilfreich.
- Metadata Name + Link => Information über Wanderung.
Spalten umbenennen
Um auf die Werte einfacher zugreifen zu können, benennen wir die restlichen Spalten passend nach folgendem Schema um:
- gpx - wpt - lon Lon
- gpx - wpt - lat Lat
- gpx - wpt - name Wegpunkt Name
- gpx - wpt - type Wegpunkt Typ
- gpx - wpt - desc Wegpunkt Beschreibung
- gpx - wpt - link-href Wegpunkt Link
- gpx -metadata - name Name
- gpx - metadata - link - href Link
Spalten sortieren
Nachdem wir geprüft haben, dass die Spalte “Name” für jeden GPS-Track eindeutig ist (Text Facet oder Duplicates Facet), löschen wir die Spalte “File” und verschieben die Spalte “Name” ganz nach links. Dadurch wird der “Name” zum neuen Record Identifier der einzelnen Wanderungen. Ordnungsliebend verschieben wir die Spalte “Link” direkt daneben auf die zweite Position. Das geht in einem Bearbeitungsschritt über den Dialog zum Löschen und Umsortieren mehrerer Spalten via “All" "Edit columns" "Re-order / remove columns…”.
Überflüssige Zeilen entfernen
Wir haben in den Daten einige Leerzeilen, da wir die Spalten zu den einzelnen Trackpunkten gelöscht haben. Außerdem haben wir einige Wegpunkte, die wir ebenfalls noch löschen wollen, da wir uns auf die Parkplätze konzentrieren wollen.
Dies machen wir in vier Schritten:
- Werte in Spalte “Name” nach unten auffüllen via “Name" "Edit cells" "Fill down”.
- Werte in Spalte “Link” nach unten auffüllen via “Link" "Edit cells" "Fill down”.
- Wie in Abbildung 6, ein Text Facet auf der Spalte “Wegpunkt Typ” erstellen, dort die Wegpunkte vom Typ “Parkplatz” auswählen, und die Auswahl invertieren.
- Die überflüssigen Zeilen entfernen via “All" "Edit rows" "Remove matching rows”.
Nochmal überflüssige Spalten entfernen
Bei den Parkplätzen sind die Spalten “Wegpunkt Link” und “Wegpunkt Beschreibung” nicht mit hilfreichen Informationen versehen, so dass wir sie löschen können.
Auf Duplikate prüfen
Der Vollständigkeit halber prüfen wir mit dem “Duplicates Facet” oder dem “Text Facet” die Spalte “Wegpunkt” auf mögliche Duplikate und löschen diese. Dafür die entsprechenden Zeilen zum Beispiel mit Flaggen markieren, via “Flag Facet” filtern und anschließend löschen.
Records erstellen
Um wieder eine Record Struktur zu erstellen, bei der es zu jeder Tour mehrere mögliche Parkplätze als Startpunkte gibt, machen wir das “Fill down” auf den Spalten “Name” und “Link” aus dem vorherigen Schritt wieder rückgängig, indem wir analog dazu auf beiden Spalten “Name|Link" "Edit cells" "Blank down” verwenden.
Die fertig gefilterten Daten sind in Abbildung 7 zu sehen.
Startkoordinaten suchen
Für einen Abgleich via WebAPI benötigen wir noch die Startkoordinaten. Der Einfachheit halber kopieren wir diese von Google Maps und fügen sie als neue Spalten “Start Lon” und “Start Lat” dem Projekt hinzu (wir starten von unserer Zentrale in der Eugenstraße 7 in Stuttgart). Um Geokoordinaten von Google Maps zu kopieren klicken wir mit dem rechten Maustaste auf den gesuchten Punkt oder den Marker auf der Karte. Wie in Abbildung 8 gezeigt, erscheint anschließend ein Kontextmenü, wo die Geokoordinaten mit einem Linksklick direkt in die Zwischenablage übernommen werden können. Der erste Wert stellt den Breitengrad (Latidude) dar, der zweite den Längengrad (Longitude).
Wegstrecke mit WebAPIs ermitteln
Nun haben wir alle Daten für unseren Datenabgleich zusammen. In Abbildung 9 sind diese noch einmal abgebildet, wobei wir zur Übersichtlichkeit die ersten beiden Spalten ausgeblendet haben. Im nächsten Schritt berechnen wir die Entfernung zwischen unserem Standort und den möglichen Startpunkten der einzelnen Wanderungen.
Um die Daten von einer WebAPI abzurufen, verwenden wir so genannte GET-Requests (siehe HTTP-Anfragemethoden auf Wikipedia). Das hat den Vorteil, dass wir die Daten direkt mit der OpenRefine Funktion des Hinzufügens neuer Spalten via URL abfragen können.
Bei der (prinzipiell) für diese Art von Anfragen empfohlene POST-Request Methode, müssten wir zusätzlich in OpenRefine mit Clojure oder Python programmieren, was wir aus Gründen der Komplexität für dieses Experiment vermeiden möchten.
Verwendung von HERE
Bei HERE handelt es sich um ein ehemaliges Berliner Startup, welches mehrfach verkauft wurde und inzwischen hauptsächlich aus dem Automotive Bereich bekannt ist.
Für die Verwendung der HERE Routing API benötigen wir einen Account und einen API Schlüssel. Die Benutzung der API ist in der Entwickler Dokumentation beschrieben.
Daten abrufen
In Abbildung 10 ist der Dialog zum Hinzufügen neuer Spalten via URLs gezeigt. Diesen können wir aufrufen via “Beliebige Spalte" "Edit columns" "Add column by fetching URLs…”.
Die neue Spalte nennen wir “HERE” und die URL bauen wir für jede Zeile dynamisch zusammen:
"https://router.hereapi.com/v8/routes?transportMode=car&origin="+row.cells["Start Lat"].value +","+row.cells["Start Lon"].value+"&destination="+row.cells["Lat"].value +","+row.cells["Lon"].value+"&return=summary&apikey={YOUR_API_KEY}"
Wir geben in dieser Anfrage an, dass wir die Route für ein Auto planen möchten und laden die Start- und Zielkoordinaten aus den entsprechenden OpenRefine Spalten. Hier ist der Platzhalter {YOUR_API_KEY} durch einen eigenen API Key zu ersetzen.
Bei HERE haben wir zusätzlich die Möglichkeit, lediglich die Zusammenfassung zurückgeben zu lassen und damit auf die expliziten Einzelschrittanweisungen für die Route zu verzichten.
Daten aufbereiten
Die Antwort der HERE Routing API erfolgt im JSON Format. In OpenRefine ist das JSON nicht menschenlesbar formatiert, so dass wir es mit dem Online Service JSON formatter menschenlesbar formatieren.
{
"routes": [
{
"id": "5dae984c-da74-4bf3-b126-a4b8f44f35f7",
"sections": [
{
"id": "ff1eaf83-4cae-498e-b0f0-b2fe68227106",
"type": "vehicle",
"departure": {
"time": "2022-10-24T11:10:38+02:00",
"place": {
"type": "place",
"location": {
"lat": 48.7789779,
"lng": 9.1864948
},
"originalLocation": {
"lat": 48.7792369,
"lng": 9.186687
}
}
},
"arrival": {
"time": "2022-10-24T12:17:31+02:00",
"place": {
"type": "place",
"location": {
"lat": 48.389022,
"lng": 9.3690754
},
"originalLocation": {
"lat": 48.388977,
"lng": 9.369079
}
}
},
"summary": {
"duration": 4013,
"length": 62748,
"baseDuration": 3617
},
"transport": {
"mode": "car"
}
}
]
}
]
}
Die für uns interessanten Daten sind im Bereich summary zu finden. Unter length finden wir die Entfernung in Metern und unter baseDuration die von der aktuellen Verkehrssituation unabhängige und daher optimistisch berechnete Fahrtdauer in Sekunden. Weitere Informationen zu unterschiedlichen Angaben zur Fahrdauer und wie sie angefragt werden können, sind in der Entwickler Dokumentation zu finden.
Um aus dem JSON die für uns relevanten Daten in OpenRefine Spalten zu überführen, nutzen wir die Funktion “HERE" "Edit columns" "Add column based on this column…”.
Die erste neue Spalte nennen wir “HERE Entfernung” und verwenden den folgenden GREL Ausdruck zur Extraktion und Umwandlung der Entfernung in Kilometern:
(value.parseJson().routes[0].sections[0].summary.length/1000).round()
Die zweite neue Spalte nennen wir “HERE Dauer” und verwenden den folgenden GREL Ausdruck zur Extraktion und Umwandlung der Dauer in Minuten:
(value.parseJson().routes[0].sections[0].summary.baseDuration/60)
Verwendung von OpenRouteService
Bei OpenRouteService handelt es sich um einen OpenSource Geodatendienst, welcher auf Daten von OpenStreepMaps basiert. Die Anmeldung und die Verwendung ist sehr einfach gehalten. Entsprechend ist auch die API im Vergleich zu den hier verwendeten kommerziellen Diensten nicht ganz so umfangreich. Die Verwendung der API zur Berechnung von Routen ist in der Entwickler Dokumentation beschrieben.
Daten abrufen
Das Vorgehen zur Abfrage einer WebAPI via GET-Requests haben wir schon bei der Verwendung der HERE Routing API beschrieben.
Wir laden die Daten in eine neue Spalte namens “OpenRouteService”. Der GREL Code zum dynamischen Erstellen der URLs für die einzelnen Zeilen sieht wie folgt aus:
"https://api.openrouteservice.org/v2/directions/driving-car?start="+row.cells["Start Lon"].value +","+row.cells["Start Lat"].value+"&end="+row.cells["Lon"].value +","+row.cells["Lat"].value+"&api_key={YOUR_API_KEY}"
Daten aufbereiten
Die JSON Antwort von OpenRouteService besteht aus mehreren tausend Elementen im JSON Format, da wir hier zusätzlich die komplette Route mitgeschickt bekommen. Die für uns interessanten Daten finden wir wieder im Element summary. Die Entfernung ist wieder in Metern und die Dauer in Sekunden angeben.
{
...
"summary": {
"distance": 62204.3,
"duration": 3993.7
}
...
}
Um aus dem JSON die für uns relevanten Daten in OpenRefine Spalten zu überführen, nutzen wir die Funktion “OpenRouteService" "Edit columns" "Add column based on this column…”.
Die erste neue Spalte nennen wir “ORS Entfernung” und verwenden den folgenden GREL Ausdruck zur Extraktion und Umwandlung der Entfernung in Kilometern:
(value.parseJson().features[0].properties.summary.distance/1000).round()
Die zweite neue Spalte nennen wir “ORS Dauer” und verwenden den folgenden GREL Ausdruck zur Extraktion und Umwandlung der Dauer in Minuten:
(value.parseJson().features[0].properties.summary.duration/60).round()
Verwendung von Google Maps
Google hat bei den hier verwendeten Webdiensten das weitaus umfangreichste API Angebot. Der Account Setup ist hier jedoch im Vergleich auch etwas aufwendiger.
Wir verwenden die Directions API von Google Maps. Es gibt zwar auch eine neuere Routes API, die derzeit in einer Vorschau-Version zur Verfügung steht. Diese bietet jedoch zum Zeitpunkt dieses Experiments (noch?) keine GET-Requests oder Routing via ÖPNV an.
Daten abrufen
Über die Directions API von Google Maps lassen sich auch Routen mit dem öffentlichen Nahverkehr planen. Daher fokussieren wir uns bei diesem Dienst auf diese Funktionalität.
Da Ausflüge wie Wanderungen häufig an Sonntagen stattfinden, dort jedoch der öffentliche Nahverkehr anders getaktet ist als unter der Woche, geben wir bei dieser Abfrage zusätzlich unsere gewünschte Ankunftszeit an (Sonntag, 31. Oktober 2022 um 10:00 Uhr). Die Zeitangaben werden in “Sekunden seit dem 1. Januar 1970” angegeben. In OpenRefine berechnen wir das mit dem folgenden GREL Ausdruck:
"2022-10-31 10:00".toDate("yyyy-MM-dd HH:mm").datePart("time")/1000
Hier eingefügt in den GREL-Code zur dynamischen Erstellung von URLs für das Hinzufügen neuer Spalten via URLs für OpenRefine:
"https://maps.googleapis.com/maps/api/directions/json?origin="+row.cells["Start Lat"].value +","+row.cells["Start Lon"].value+"&destination="+row.cells["Lat"].value +","+row.cells["Lon"].value+"&mode=transit&arrival_time="+ "2022-10-31 10:00".toDate("yyyy-MM-dd HH:mm").datePart("time")/1000 +"&language=de&key={YOUR_API_KEY}"
Daten aufbereiten
Die JSON Antwort der Google Maps Direction API ist recht ausführlich, wenn denn eine funktionierende Strecke gefunden werden konnte. Hier ein kleiner Ausschnitt:
{
...
"copyrights": "Map data ©2022 GeoBasis-DE/BKG (©2009)",
"legs": [
{
"arrival_time": {
"text": "10:22",
"time_zone": "Europe/Berlin",
"value": 1667208156
},
"departure_time": {
"text": "09:06",
"time_zone": "Europe/Berlin",
"value": 1667203588
},
"distance": {
"text": "48,2 km",
"value": 48218
},
"duration": {
"text": "1 Stunde, 16 Minuten",
"value": 4568
},
"end_address": "Panorama Therme, 72660 Beuren, Deutschland",
"end_location": {
"lat": 48.5655941,
"lng": 9.3967464
},
"start_address": "Eugenstraße 7, 70182 Stuttgart, Deutschland",
"start_location": {
"lat": 48.77899370000001,
"lng": 9.1865211
},
"steps": ...
}
Interessant ist hier, dass wir die Werte für die Entfernung und Dauer sowohl als Text, als auch in Metern bzw. Sekunden erhalten. Wir extrahieren auch hier die Zahlenwerte, da diese einfacher in ein einheitliches Format umgewandelt werden können.
Die erste neue Spalte nennen wir “Google Entfernung (ÖPNV)” und verwenden den folgenden GREL Ausdruck zur Extraktion und Umwandlung der Entfernung in Kilometern:
(value.parseJson().routes[0].legs[0].distance.value/1000).round()
Die zweite neue Spalte nennen wir “Google Dauer (ÖPNV)” und verwenden den folgenden GREL Ausdruck zur Extraktion und Umwandlung der Dauer in Minuten:
(value.parseJson().routes[0].legs[0].duration.value/60).round()
Formate anpassen
Zur Verbesserung der Lesbarkeit, ergänzen wir die Entfernungsspalten um die Angabe “km”. Dafür nutzen wir “Spalte" "Edit cells" "Transform” und den folgenden GREL Ausdruck.
value+"km"
In den Spalten zur Dauer wandeln wir die Angaben von Minuten in Stunden und Minuten um.
with(value.toDate("mm"), d,
with(d.datePart("h"), h,
with(d.datePart("min"), min,
if(h > 0,
h + "h " + min + "min",
min + "min"))))
Das Endergebnis ist in Abbildung 11 gezeigt.
Fazit
Dieser Artikel ist überraschend lang geworden. Das liegt hauptsächlich daran, dass wir versuchen Hintergründe zu erklären, mit der Auswertung der Wegpunkte einen etwas komplizierteren Fall durchspielen und uns gleich drei unterschiedliche WebAPIs ansehen.
In einem vereinfachten Test mit einem anderen Datensatz unter der Verwendung des ersten Punktes der Wanderung als Zielpunkt und nur einer API, haben wir die Bearbeitung in etwa 10 Minuten abgeschlossen. Und zwar inklusive Export in eine Software für Tabellenkalkulation und anschließender visueller Formatierung für den Druck.
Generell waren wir überrascht, wie wenig aufwendig es ist, eine Menge von GPX-Dateien im XML-Format in OpenRefine einzulesen, die relevanten Geokoordinaten herauszufiltern und damit Anfragen an eine WebAPI zu stellen.
Bei den WebAPIs hat uns OpenRouteService in seiner Einfachheit gefallen. Der Funktionsumfang der API ist nicht so umfangreich, wie bei den kommerziellen Anbietern. Jedoch ist der initiale Aufwand für die Account Registrierung geringer, sowie die Erstellung eines API Schlüssels sehr übersichtlich gehalten.
Das Erstellen eines Accounts und API Schlüssels für die HERE Routing API war ebenfalls sehr direkt und die Arbeit mit der API machte uns sofort Spaß. Die HERE Routing API lädt dazu ein, noch viel mehr und umfangreicher mit ihr zu arbeiten.
Bei Google fühlten wir uns zuerst von der Menge der Dienste und möglichen Alternativen erschlagen. Die Google Maps Direction API ist dann auch sehr umfangreich, so dass wir im Vergleich mit den anderen APIs etwas länger brauchten, um genau unseren Use-Case damit abzubilden. Dafür konnten wir damit problemlos die Anfahrt mit dem Öffentlichen Personennahverkehr berechnen.
Auch wenn es sich bei den Daten für dieses Experiment nicht um “klassische” Archivdaten handelt, so hilft uns das Experiment besser zu verstehen, wie OpenRefine uns dabei unterstützt Daten aus unterschiedlichen Quellen zusammenzuführen. Im Kontext der einfachen Installation von OpenRefine steigert die Anbindung von generischen WebAPIs ohne Programmierkenntnisse die Attraktivität von OpenRefine weiter.
Benjamin Rosemann
Data Scientist
Ich evaluiere KI- und Software-Lösungen und integriere sie in den Archivalltag.