Grenzüberschreitender Fernverkehr

Mal wieder auf “Wunsch” einer unbekannten Person fand sich ein neues Daten-Bastelprojekt. Diesmal: “Gibt es eine Karte mit grenzüberschreitenden Fernverkehrs (FV)-Verbindungen?” (oder so ähnlich, auf jeden Fall irgendwie™ mit Daten realisierbar.)

Also gings los: Dank des DELFI-Datensatzes (Durchgängige ELektronische FahrgastInformation) war zumindest schon der erste Grundstein gesetzt. Diese Daten im GTFS-Format sind allerdings in ihrer Textform in der weiteren Verarbeitung etwas unhandlich. Irgendwo auf GitHub fand sich ein relativ einfaches Skript, welches die gut 1,2 Millionen Datensätze aus den GTFS-Tabellen in eine Sqlite-Datenbank transferierte. Das hat den Vorteil, dass dann später mit einfachen SQL-Queries die Daten besser gefiltert und aggregiert werden können.

 SELECT
        trips.*,
        agency.agency_name AS agency_name
    FROM
        trips
    LEFT JOIN
        routes
    ON
        routes.route_id = trips.route_id
    LEFT JOIN
        agency
    ON
        routes.agency_id = agency.agency_id
    WHERE
        routes.route_type IN (2, 101, 102, 105, 106, 107)
        OR routes.agency_id IN (10918, 12681, 10785, 10451, 12623)

(Beispiel zur Abfrage aller “trips” und Koppeln der Trips mit den jeweiligen Verkehrsverbünden “agency” und “Routenverläufen”. Wie so oft bei größeren “Data Lakes” schwankt die Qualität sehr stark. Von perfekt Standardkonformen Datensätzen der schweizer SBB bis hin zu ‘interessanten’ oder gemäß Standard veralteten Labels ist alles dabei. Deshalb sind die Where-bedinungen im gegebenen Beispiel relativ diffus.)

Soweit so gut, nach einer guten Stunde waren die Daten soweit aufbereitet. Die nächste Herausforderung war nun, den Faktor “grenzüberschreitend” aufzubereiten. Ansatz war hier ein “Shapefile” - also die Koordinaten der Deutschland-Grenzen und dann für jede Haltestelle zu überprüfen, ob sich diese innerhalb dieser Grenze befindet, oder eben nicht. Wenn nun auf einem “Trip”/einer Linie eine Station in und eine andere außerhalb der Grenze liegt, so muss diese Grenzüberschreitend sein.

Schwierig war hier vor allem, an das Shapefile ranzukommen. Auf den diversen offiziellen Seiten gibt es ganz viele “kuriosen” Datenformate und Koordinatensysteme (die in den jeweiligen Fachgebieten sicherlich ihre Berechtigung haben) - letztendlich fand ich eine geeignete Europakarte die ich um alle Ländern außer DE erleichterte. Da bei jeder der 1,2 Millionen stattfindenden Fahrten mehrere Koordinaten abgeglichen werden müssen wurde dieser Teil paralellisiert auf mehreren Rechenkernen gleichzeitig ausgeführt. So konnte dieser Prozess in nichteinmal fünf Minuten durchgeführt werden. Ausgegeben wurden die Ergebnisse in form einer sogenannten GeoJSON-Datei.

Download der GeoJSON zum selbst-Visualisieren.

Wie man allerdings auf dieser Karte erkennen kann, sind lediglich die Koordinaten der Stationen angegeben, nicht die der Routen unterwegs. Deshalb machte ich mich auf die Suche nach einem Tool, welches aus diesen einzelnen Koordinatenpunkten richtige Routen erstellt. Als vermutlich geeignet stellte sich das Werkzeug “pfaedle” heraus. Das Tool benötigt für die Routenerstellung allerdings eine lokale Datenbank-Kopie der OpenStreetMap. Weil der GTFS-Datensatz vor allem Grenzüberschreitende Verbindungen prüfen soll, lag ein gesamter EU-Download nahe. Allerdings unterschätzte ich die Größe der entpackten Datenbank. Sie wuchs nach 36 GB Download letztendlich beim Entpacken auf gut 412 GB an. Das sprengte erstmal die Kapazität meiner lokalen SSD, nun muss eine externe Festplatte daran glauben, der EU-Datensatz geht tatsächlich in die Kategorie “Big Data ist das, was nicht mehr in deinen (Arbeits-)Speicher passt.". So weit, so gut, wenn die einzel-Pfade durch die 400 GB OSM gelaufen sind melde ich mich nochmal, das wird vermutlich eine Weile dauern. :D