Andreas Bruns

Softwareentwicklung für Oldenburg und Bremen

Osmosis, Osm2postgresql & Osm2pgsql – OpenStreetMap-Daten, Datenbanken und Spielplätze

Ich möchte gerne wissen, welche interessanten Orte (POI = Points of Interest) es für Kinder in meiner Umgebung gibt, wie Spielplätze oder Tiergehege. Eine Recherche im Internet bietet für meinen kleinen Ort nicht sehr viele Ergebnisse. Das bedeutet, entweder selbst die Gegend erkunden oder vielleicht mal die Daten von OpenStreetMap (OSM) durchforsten und dort die Daten gegebenenfalls ergänzen.

Natürlich will ich als Softwareentwickler die OSM-Rohdaten analysieren und nicht mit den Augen auf den fertig gezeichneten OSM-Karten suchen, auf denen eventuell auch noch die für mich spannenden POIs fehlen. Um die OSM-Daten performant durchsuchen zu können, müssen die Daten erst einmal in eine Geo-Datenbank importiert werden. Im OpenStreetMap-Wiki findet man dafür die Werkzeuge Osmosis, Osm2postgresql und Osm2pgsql, die jeweils eine OSM-Datei in eine PostgreSQL-Datenbank mit PostGIS importieren. PostgreSQL-Datenbank mit PostGIS – das passt ja super, weil ich das bei mir schon installiert habe (Blog-Artikel). Nur welches der drei OSM-Werkzeuge ist das passende, um alle Spielplätze (Tag: „leisure“=“playground“) in meiner Nähe zu finden?

Mein Ergebnis: Bremer Spielplätze der OpenStreetMap-Daten

Um das herauszufinden, benötigen wir erst einmal die OSM-Daten. Wir starten natürlich nicht mit ganz Europa (10 GB) oder Deutschland (1,5 GB), sondern wir nehmen uns einen kleineren Datensatz, wie den vom Bundesland Bremen (10 MB): http://downloads.cloudmade.com/europe/western_europe/germany/bremen. Die gepackte OSM-Datei bremen.osm.bz2 nimmt entpackt dann schon 130 MB Platz auf der Fetsplatte ein. Wieviel Platz werden die Daten wohl in den jeweiligen Datenbanken benötigen? Wenn wir alle drei Werkzeuge ausprobiert haben, werden wir es wissen:

Osmosis – roh, kompakt, komplett mit Metadaten

Mit Osmosis können wir die OSM-Rohdaten in eine Datenbank speichern. Das sind: Nodes, Ways, Relations, deren Tags und deren Metadaten (Version, Timestamp, Changeset, User). Die Tags zu den Nodes, Ways und Relations können in zwei verschiedenen Schematas gespeichert werden:

  • pg_simple: in eigenen Tabellen
  • pg_snapsnot: innerhalb HStore-Spalten (PostgreSQL-Spalte für Key/Value-Werte)

Um standardmäßiges SQL einsetzen zu können, habe ich mich für die ältere einfachere Variante (pg_simple) entschieden, wobei ich zukünftig die modernere Variante mit der Key/Value-Spalte (pg_snapsnot) nehmen würde.

Für den Import der Daten in eine Datenbank mit osmosis nach dem pg_simple-Schema müssen wir zunächst die Datenbank erstellen und sie dann mit dem Skript pgsimple_schema_0.6.sql füttern:

$ psql -U bruno -d postgres -c "CREATE DATABASE osmosis0401_bremen WITH TEMPLATE = template_postgis"
$ psql -U bruno -d osmosis0401_bremen -f osmosis-0.40.1/script/pgsimple_schema_0.6.sql
$ bin/osmosis --read-xml file="Download/Bremen/bremen.osm.bz2" --write-pgsimp database="osmosis0401_bremen"  user="bruno" password="xxx"
INFO: Total execution time: 86660 milliseconds.

Innerhalb 1,5 Minuten waren die Daten in der Datenbank und zwar aufgeteilt auf die Tabellen: nodes, node_tags, ways, way_nodes, way_tags, relations, relation_membership, relation_tag und users. In den Tag-Tabellen können wir dann nach den Datensätzen mit key=“leisure“ und value=“playground“ suchen:

SELECT n.id, st_asewkt(n.geom) FROM node_tags nt INNER JOIN nodes n ON n.id = nt.node_id WHERE nt.k = 'leisure' AND nt.v = 'playground';
-- 268 rows
SELECT wt.way_id FROM way_tags wt WHERE wt.k = 'leisure' AND wt.v = 'playground';
-- 176 rows
SELECT r.relation_id FROM relation_tags r WHERE r.k = 'leisure' AND r.v = 'playground';
-- 0 rows

Wir finden 268 Nodes, 176 Ways und keine Relation als Spielplätze. Nur die Node-Tabelle enthält eine Spalte ‚geom‘ mit einem Geometry-Typ und die Daten in dieser Spalte sind stets Punkte. Mit einem einfachen Join zwischen der nodes-Tabelle und der node_tags-Tabelle können wir die Positionen der ersten 268 Spielplätze, die als Punkt vorliegen, herausfinden (1. SQL-Zeile).

Bei den 176 Spielplätzen, die als Polygon (Tabelle way) vorliegen und nicht als Punkt, müssen wir die benötigten Daten über mehrere Tabellen zusammenführen und zu einem Polygon verknüpfen (Tabellen: way_tags, ways, way_nodes, nodes). Das ist mir zu kompliziert und deshalb widmen wir uns jetzt dem nächsten Werkzeug.

Osm2postgresql – komplett, bequem und speicherintensiv

Osm2postgresql benutzt Osmosis und erweitert dann die von Osmosis erstellten Tabellen um weitere Spalten und um eigene Tabellen: dumped_multilinestring, nodes_with_tags, polygons, simple_polys. Damit sind alle ursprünglichen Daten (inklusive Metadaten) noch verfügbar und wir können bequem die neuen Spalten und Tabellen auswerten. Insbesondere die ergänzten Text-Spalten class (enthält eine Klassifizierung, z.B. ‚playground‘) und tags (enthält eine Liste aller Tags) kann man einfach in eigenen Anwendungen oder innerhalb eines Geoinformationssystems (GIS), wie QGIS (Blog-Artikel), auswerten.

Auf meinem MAC hatte ich ein paar Probleme, das Linux-Skript zum Laufen zu bekommen. Zunächst musste ich mich von der aktuellsten Version ‚osm2postgresql_05rc4.sh‘ verabschieden und bin auf die stabilere ‚osm2postgresql_04.sh‘ umgesattelt. Dann benötigte das Skript in PostgreSQL eine Datenbank ‚bruno‘ (mein Benutzername), damit die plsql-Befehle ausgeführt werden konnten. Außerdem brauchte das Skript für das Herunterladen von ‚osmosis-0.38‘ das Programm ‚wget‘, das auf meinem MAC standardmäßig nicht installiert ist. Als das alles funktionierte und ich etwa gefühlte 100 mal mein Datenbank-Passwort eingegeben hatte, wurde ich mit einer gefüllten Datenbank und der Meldung ‚Importation completed correctly!‘ belohnt.

Während der Import der Bremer-Daten 11 Minuten dauerte, arbeitete mein MAC-Book an den Daten von Niedersachsen (300MB gepackt, 3,7 GB entpackt) ganze 38 Stunden! Aber das schiebe ich jetzt mal auf die Kombination von PosgreSQL mit Mac – das geht sicherlich schneller auf einem sauber konfigurierten Linux.

$ ./osm2postgresql_04.sh --postgis /Library/PostgreSQL/9.1/share/postgresql/contrib/postgis-1.5 --hstore /usr/share/postgresql/contrib --osm Download/Bremen/bremen.osm.bz2 --createdb --dbname osm2postgresql_bremen

Time: Started 2012-03-13_22:24:50
Will try to create the database 'osm2postgresql_bremen'
...........
Time: Finished importation at 2012-03-13_22:35:54
Importation completed correctly!

Dank der neuen Spalten und Tabellen können wir mit dem nun folgenden SQL-Code die Spielplätze suchen. Die erste Abfrage liefert Point-Geometrien und die zweite Abfrage liefert Polygon-Geometrien.

SELECT nt.idint4 AS id, st_asewkt(nt.geom) FROM nodes_with_tags nt WHERE nt.tags like '%"leisure"="playground"%';
-- 268 rows
SELECT sp.idint4 AS id, st_asewkt(sp.polygon) FROM simple_polys sp WHERE sp.tags like '%"leisure"="playground"%';
-- 175 rows

Damit wir alle gefundenen Spielplätze gleich behandeln können, können wir uns mit der Methode st_centroid(Geometry) den Mittelpunkt eines Polygons geben lassen. Außerdem wollen wir die Daten der beiden Abfragen zusammenführen, was uns das SQL-Schlüsselwort UNION ermöglicht. Eine Abfrage nach allen Spielplätzen wäre dann der folgende SQL-Befehl:

SELECT nt.idint4 AS id, st_asewkt(nt.geom) AS geom_text, st_asewkt(st_centroid(nt.geom)) AS center_text
FROM nodes_with_tags nt
WHERE nt.tags like '%"leisure"="playground"%'
UNION
SELECT sp.idint4 AS id, st_asewkt(sp.polygon) AS geom_text, st_asewkt(st_centroid(sp.polygon)) AS center_text
FROM simple_polys sp
WHERE sp.tags like '%"leisure"="playground"%'
ORDER BY id;
--443 rows

Osm2pgsql – gefiltert und bequem

Osm2pgsql filtert bestimmte Attribute aus den OSM-Daten und speichert sie in einer entsprechenden Datenbank. Dabei gehen allerhand Informationen verloren. Das sind bei den Standard-Einstellungen insbesondere die Metadaten (Version, Timestamp, Changeset, User) und die Beziehungen (also Relations) zwischen den Elementen. Welche Attribute relevant sind, muss man in einer Style-Datei (default.style) angeben. Dort kann man übrigens mit den Schaltern
–extra-attributes und –hstore-all das Abspeichern der Metadaten beziehungsweise aller Tags aktivieren.

Die Daten von Osm2pgsql werden oft für das Zeichnen von Karten (z.B. mit Mapnik) ausgewertet, bei denen eben nur ein Teil der gesamten OSM-Daten relevant sind. Da Karten oft die ‚Spherical Mercator‘-Projektion verwenden, ist diese wohl bei Osm2pgsql voreingestellt. Wir hätten allerdings unsere Geo-Koordinaten lieber im Grad-Format (Latitude/Longitude), was wir mit dem Schalter ‚–latlong‘ einstellen können. Damit klappte auch der Import meiner Bremen-Daten fast dreimal schneller (101 Sekunden mit Lat/Lon anstatt 288 Sekunden mit Mercator).

$ psql -U bruno -d postgres -c "CREATE DATABASE osm2pgsql_bremen WITH TEMPLATE = template_postgis"
$ osm2pgsql -c -d osm2pgsql_bremen -U bruno -W --latlong -H localhost Download/Bremen/bremen.osm.bz2 --style ./OpenStreetMap/default.style
osm2pgsql SVN version 0.70.5 (32bit id space)
Password:
Using projection SRS 4326 (Latlong)
...
Osm2pgsql took 101s overall

Wie bei den Tabellen der anderen Werkzeuge befinden sich die für uns spannenden Daten in zwei unterschiedlichen Tabellen.

SELECT point.osm_id, st_asewkt(point.way) AS geom_text FROM planet_osm_point point WHERE point.leisure = 'playground' ORDER BY osm_id;
--268 rows
SELECT polygon.osm_id, st_asewkt(polygon.way) AS geom_text FROM planet_osm_polygon polygon WHERE polygon.leisure = 'playground' ORDER BY osm_id;
--175 rows

Auch diese Daten führen wir zu einer Abfrage zusammen, die uns zu den Geometrien (Point und Polygon) auch die Mittelpunkte liefert:

SELECT point.osm_id, st_asewkt(point.way) AS geom_text, st_asewkt(st_centroid(point.way)) AS center_text
FROM planet_osm_point point
WHERE point.leisure = 'playground'
UNION
SELECT polygon.osm_id, st_asewkt(polygon.way) AS geom_text, st_asewkt(st_centroid(polygon.way)) AS center_text
FROM planet_osm_polygon polygon
WHERE polygon.leisure = 'playground'
ORDER BY osm_id;
--443 rows

Vergleich der Werkzeuge

Mit Osm2postgresql und Osm2pgsql haben wir es leicht geschafft, nicht nur die als Punkt sondern auch die als Polygon vorliegenden Spielplätze herauszufinden. Beim Einsatz von Osmosis ist es recht mühselig, die vorliegenden Spielplätze im Polygon-Format aufzubereiten. Ein direkter Vergleich der beiden Ergebnisse von Osm2postgresql und Osm2pgsql offenbart, beide Werkzeuge kommen (mit kleinen Gleitkomma-Abweichungen) zum gleichen Ergebnis.

Vergleich der Spielplätze mit den Werkzeugen Osm2postgresql und Osm2pgsql

Mit dem PostgreSQL-Befehl pg_database_size($datenbankname) lässt sich übrigens die Größe unserer angelegten Datenbanken bestimmen.

SELECT pg_size_pretty(pg_database_size('osm2pgsql_bremen'));

Der folgenden Tabelle kann man die Ergebnisse meines Vergleichs entnehmen:

Osmosis Osm2postgresql Osm2pgsql
Version 0.40.1 0.4 0.70.5
komplette Daten vorhanden ja ja nein
Metadaten vorhanden (z.B. User) ja ja nein
verwendete Geometrien Point Point,Polygon Point,Polygon
notwendige Abfrage-Komplexität hoch gering gering
Import-Zeit (Bremen/Nieders.) * 86 s / 50 m 11 m / 38 h 30 s / 78 m
DB-Platz in GB (Bremen/Nieders.) 0,2 / 5,5 0,5 / 13 0,05 / 0,9
Dauer Spielplatz-Abfrage in Sek. ** keine Abfrage 0,09 / 2 0,05 / 0,8

* System: MacBook Pro, 2.2 GHz Intel Core i7, 4 GB Memory, PostgreSQL 9.1 (ohne Tuning)
** beim ersten Mal dauert die Abfrage länger, danach entspricht die Zeit dem angegbenen Wert

Alle drei Werkzeuge sind tolle OpenSource-Programme. Ich würde zunächst mal für meinen Anwendungszweck mit Osm2pgsql starten und prüfen, ob mir die gebotenen Daten ausreichen (insbesondere mit den Schaltern –extra-attributes und –hstore-all). Falls das nicht der Fall ist, würde ich entweder komplett auf Osm2postgresql wechseln oder Osm2pgsql & Osmosis gemeinsam benutzen. Einen weiteren und eventuell aktuelleren Überblick mit zwei zusäzlichen Werkzeugen (osm2gis/OpenStreetMap-in-a-Box und imposm) gibt es übrigens hier: OsmInABox -> Similar Software.