/ Sql

Spaß mit Open Street Map Daten

Im Wintersemester 2014/15 haben wir im Fach "Softwarearchitektur" eine kleine Applikation zur Suche und Verwaltung von Routen und Stationen der öffentlichen Verkehrsmittel in Graz entwickelt (sie Bild oben). Die Daten haben wir damals aus Openstreetmap extrahiert.

Aus Spaß und der Interesse halber habe ich den heutigen Tag damit verbracht, verschiedene Wege und Methoden zu finden, um einfach und gezielt die verschiedenen Daten aus einem Openstreetmap Datenfile zu extrahieren. Den meiner Meinung nach besten Weg (Spoiler: mittels dem Tool Osmosis und einer Postgres Datenbank) versuche ich hier auf verständliche Art und Weise für Später niederzuschreiben.

Den Datenberg verkleinern

Am Beginn steht einmal ein großer Download. Man lädt sich die aktuellen österreichischen Daten austria-latest.osm.bz2 von http://download.geofabrik.de/europe.html herunter und extrahiert sie. Möglicherweise können die verwendeten Tools auch mit den komprimierten Daten umgehen, aber dies kann die Verarbeitungszeit möglicherweise erheblich verlängern (aber man erspart sich ~8GB Festplattenspeicher).

Ich brauche nur die Daten von Graz und will deshalb die restlichen Daten aus dem riesigen Datenberg entfernen. Mein Kollege Thorsten hat das während der Übung auf der TU folgendermaßen erklärt:

Das ist wie zu Weihnachten mit einem Keksausstecher: Man sticht mit dem Ausstecher in der Form von Graz die Daten aus Österreich heraus.

Das geschieht mittels einem Polygon-File. Ich verwende hier das File von unserer Übung unter http://kti.tugraz.at/staff/rkern/courses/sa/index.html http://kti.tugraz.at/staff/rkern/courses/sa/data/Graz.poly. Um mit dem Ausstechen zu beginnen benötige ich noch das Kommandozeilenwerkzeug osmconvert (Installationsanleitung gibt es auf der Seite). Das Tool funktioniert möglicherweise nur unter Linux, Mac und Co, ich habe aber nicht probiert, es unter Windows zu kompilieren.

Mit folgendem Befehl kann man letztendlich Graz aus dem schönen Österreich ausstechen

osmconvert austria-latest.osm -B=Graz.poly -o=graz.osm

und erhält die Datei graz.osm, die alle Daten aus Graz beinhalten. Das hat ungefähr 3-5 Minuten gedauert.

In die Datenbank laden und filtern

Analog zu osmconvert existiert mit osmfilter ein weiteres Tool, das die Arbeit ebenfalls durchführen könnte. Jedoch fand ich nach vielem Herumprobieren die Datenbank einfacher, da man einfach mit Joins und Co die Daten noch besser nutzen kann. Und auch das Einbauen in andere Projekte fällt so leichter.

Zuerst brauchen wir einen Postgres Server mit installierter PostGis Erweiterung. Mein Mac-Postgres hat PostGis schon von Haus aus Installiert, falls das nicht der Fall sein sollte, findet man auf http://www.postgis.net/ diverse Anleitungen.

Nach dem Anlegen einer neuen Datenbank muss man die beiden Erweiterungen postgis und hstore aktivieren. Hstore ermöglicht Key-Value Stores innerhalb einer Tabelle, welcher für die Tags innerhalb der OSM-Objekte verwendet wird.

Zum Aktivieren führt man in der Datenbank einfach zwei CREATE-Statements aus.

CREATE EXTENSION postgis;
CREATE EXTENSION hstore;

Zum Datenbankimport verwende ich Osmosis, ein Java Programm, das außer dem Datenbankimport auch viel mehr kann. Dazu lädt man einfach die aktuelle Version herunter und entpackt die ZIP in das Verzeichnis mit graz.osm. Dann muss man das Datenbankschema anlegen.

psql -d <datenbankname> -f <osmosis-verzeichnis>/script/pgsnapshot_schema_0.6.sql

Nun kann man mittels osmosis, das sich in <osmosis-verzeichnis>/bin befinden die Daten Importieren.

osmosis --read-xml file="graz.osm" --write-apidb host="localhost" database="<dbname>" user="<dbuser>" password="<dbpassword>"

Das hat bei mir ungefähr eine Minute gedauert.

Damit die Datenbankabfragen nicht ewig dauern (tun sie trotz der Maßnahmen trotzdem, ca. 2-10 Sek. je nach Abfrage), kann man noch einen Index über die drei Key-Value-Stores legen.

CREATE INDEX idx_nodes_tags ON nodes USING gist(tags);
CREATE INDEX idx_relations_tags ON relations USING gist(tags);
CREATE INDEX idx_ways_tags ON ways USING gist(tags);

Queries

Nun kommen wir zum Spaßigen Teil: Den Abfragen. Die Daten sind grundsätzlich in drei Tabellen aufgeteilt:

  • nodes Alle POIs, Punkte auf der Karte
  • ways "Linien" auf der Karte wie Straßen, Wege, Schienen,...
  • relations Relationen, die verschiedene Elemente zusammenfassen wie z. B. eine Straße und ein Bahnübergang

Darüber hinaus gibt es noch Tabellen mit Metadaten

  • users Jeder Datensatz hat eine User-ID, die auf den User zeigt, der den Datensatz angelegt hat. Diese Tabelle ist wichtig für Produktivsysteme, damit die Urheber OSM-Konform genannt werden können.
  • relation_members Zwischentabelle für die aufgelöste N-M Beziehung zwischen relations, nodes und ways
  • way_nodes Name ist Programm

Einige Tabellen (und tausende Funktionen) werden noch von PostGis und Hstore hinzugefügt, haben aber mit den Abfragen nicht direkt zu tun.

Da das Uni-Projekt von den Stationen und Routen von Graz gehandelt hat, habe ich auch die dafür nötigen Abfragen formuliert.

Eine Große Hilfe bei den Formulierungen war die Seite über öffentlichen Verkehr im OSM-Wiki http://wiki.openstreetmap.org/wiki/DE:Public_transport.

Alle Stationen in Graz

SELECT
  n.tags->'name' AS name,
  ST_X(n.geom) AS longitude, 
  ST_Y(n.geom) AS latitude,
  CASE
      WHEN exist(n.tags,'highway') THEN n.tags->'highway'  
      WHEN exist(n.tags,'railway') THEN n.tags->'railway'
  END AS type,
  n.tags
FROM nodes n
WHERE n.tags->'public_transport' LIKE 'platform'
  AND (exist(n.tags,'highway') OR exist(n.tags,'railway'))
  AND exist(n.tags,'name')
ORDER BY name

Alle Stationen mit den dazugehörigen Linien

Diese Query liefert alle Stationen inklusive aller Linie, die auf dieser Route verkehren.

SELECT
  n.tags->'name' AS station_name,
  ST_X(n.geom) AS station_longitude, 
  ST_Y(n.geom) AS station_latitude,
  r.tags->'name' as route_name_human
FROM nodes n
INNER JOIN relation_members rm ON(rm.member_id=n.id)
INNER JOIN relations r ON(rm.relation_id=r.id) 

WHERE n.tags->'public_transport' LIKE 'platform'
  AND (exist(n.tags,'highway') OR exist(n.tags,'railway'))
  AND exist(n.tags,'name')
  AND r.tags->'route' IN('train','bus','tram')
  AND exist(r.tags,'ref')