Heute mal was wirklich wichtiges:
Nachdem ich ja auf dem Land wohne und trotzdem gerne ungesunde Hähnchenflügel esse, ist die Entfernung des nächsten „Kentucky Fried Chicken“ eine essentielle Sache für mich. Nur: Wie finde ich den nächsten Kentucky Fried Chicken?
Nun aber mal etwas ernsthafter: Ihr wollt die Entfernung zu bestimmten geografischen Merkmalen zu euch wissen. Das kann natürlich ein Kentucky Fried Chicken sein, oder ein McDonalds, oder ein Burger King. Das kann aber natürlich auch ein Wasserfall, ein Berg oder eine Ruine sein.
Soetwas zu wissen wird möglich dank des außergewöhnlichen Projekts Openstreetmap. Ein Crowdsourcing Projekt welches 2004 begonnen hat eine freie Landkarte der Welt zu erstellen
Die Daten die dieses Projekt gesammelt hat werden mittlerweile in den unterschiedlichen Apps und Anwendungen verwendet. Auch ich verlasse mich zum Wandern oft auf die iOS-App „MapOut“ welche ihre Daten aus Openstreetmap bezieht.
Die Datenbankgenauigkeit ist dabei oft besser als kommerzielle Karten.
Manchmal lässt die Genauigkeit aber noch zu wünschen übrig, und das obwohl das Zeichnen eines Flußes oder einer Straße in wenigen Klicks gemacht ist. Online oder am Handy!
Außerdem (was ich persönlich sehr sympathisch finde) muss man nicht genau sein. Die Devise (so wie sie im Buch „OpenStreetMap“ von Jonathan Bennett rüberkommt) lautet: Egal ob Daten genau sind oder nicht: Erstmal erfassen und nachträglich kann es verbessert werden. Auch schlechte Daten sind besser als garkeine!
Man merkt hier ganz deutlich dass ich etwas Werbung für Openstreetmap machen will – weil ich das Projekt (ähnlich wie die Wikipedia) für eine der großartigsten Errungenschaften der Menschheit halte. So lächerlich überzogen das auch klingen mag 🙂
Jetzt aber zum technischen:
Openstreetmap kennt drei Datenarten:
- Nodes, welche einfach nur Punkte sind die Koordinaten haben
- Ways, welches einfach nur eine Reihe an Punkten sind die ein Gebäude (also ein Polygon) oder eine Straße (also einen Weg) abbilden
- Relations, welche die Beziehungen zwischen Ways, Nodes und anderen Relations abbilden. Sie werden verwendet um z.B. zu sagen dass ein bestimmter Way zu einer Landstraße „B14“ gehört, oder dass man zwischen Way A und Way B nicht nach rechts abbiegen darf.
Alle drei der obigen Elemente haben dabei zugehörige Tags. Das sind Name/Wert-Strukturen. Solche Tags können z.B. sein: „Name: Kentucky Fried Chicken“ oder „Amenity: fast_food“. Dieses Tags werden im Openstreetmap Wiki genau erklärt und wenn man sich ein neues ausdenken sollte (weil man es tatsächlich unbedingt braucht!) so sollte man es im Wiki erklären.
Von den drei Datentypen haben nur Nodes auch Koordinaten hinterlegt.
Und jetzt aber zum installieren: Wir benötigen eine Postgres-Datenbank mit der PostGis Erweiterung. Ich handle die Installation auf einem komplett frischen Ubuntu 16.04 System ab. Zuerst mal holen wir uns das Postgres-Repository, also erstellen wir die Datei ‚/etc/apt/sources.list.d/pgdg.list‘ mit folgendem Inhalt:
deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main
Nun führen wir folgende Befehle aus um Postgres mit Postgis zu installieren:
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add - apt-get update apt-get install postgresql postgis
Als nächstes Tool benötigen wir ein Tool welches Openstreetmap-Daten im Format ‚osm.pbf‘ lesen kann (komprimiertes XML), her also mit „Osmosis“, welches uns leider auch Java mit aufs System schaufelt 🙁
apt-get install osmosis
Nun starten wir als User ‚postgres‘ das Tool ‚psql‘ um mit der Datenbank zu reden. Postgres authentifiziert sich direkt über die Linux Betriebssystemanmeldung, deswegen benötigen wir kein Passwort:
sudo -u postgres psql postgres
Ein kleiner SQL zeigt uns ob alles richtig installiert ist:
SELECT name, default_version, installed_version FROM pg_available_extensions WHERE name LIKE 'postgis%';
Und das sieht gut aus:
(verlassen von psql mit ‚\q‘)
name | default_version | installed_version ------------------------+-----------------+------------------- postgis | 2.3.0 | postgis_tiger_geocoder | 2.3.0 | postgis_sfcgal | 2.3.0 | postgis_topology | 2.3.0 | (4 rows)
Jetzt erzeugen wir uns einen User mit dem wir arbeiten. Damit er sich mit Passwort anmelden kann nimmt er als Host nicht den Socket sondern ‚127.0.0.1‘ – denn jeder der über Netzwerk kommt kriegt keine Betriebssystemauthenifizierung sondern braucht ein Passwort. So steht es in der Datei ‚/etc/postgresql/9.6/main/pg_hba.conf‘.
Zum erzeugen des Users nutzen wir (als User postgres) das Programm ‚createuser‘ und erzeugen unseren User (der Einfachheit halber) als Superuser:
sudo -u postgres createuser -U postgres -d -E -l -P -r -s gisuser
Das Passwort bei mir ist ‚gispass‘ – aber bitte nicht weitersagen!
Jetzt erstellen wir uns eine leere Datenbank ‚gisdb‘, und aktivieren die benötigten Extensions ‚hstore‘ und ‚postgis‘:
psql -U gisuser -h 127.0.0.1 postgres create database gisdb; \q psql -U gisuser -h 127.0.0.1 gisdb create extension postgis; create extension hstore; \q
Nun muss noch das richtige Datenbankschema erzeugt werden, das ist bei Osmosis aber dabei:
psql -U gisuser -h 127.0.0.1 gisdb < /usr/share/doc/osmosis/examples/pgsnapshot_schema_0.6.sql
Nun holen wir uns noch von der Website Geofabrik die Daten für das Bundesland Bayern. Geofabrik nimmt die offizielle (sehr große) Planet-Datei von Openstreetmap und teil sie in kleinere Stücke:
wget http://download.geofabrik.de/europe/germany/bayern-latest.osm.pbf
Und diese laden wir nun in unsere Datenbank. Osmosis hilft 🙂
(der Import kann lange dauern, bei mir waren es ca. 30 Minuten trotz SSD)
osmosis --read-pbf bayern-latest.osm.pbf --log-progress --write-pgsql database=gisdb host=127.0.0.1 user=gisuser password=gispass
Und da wir nun die Daten drin haben können wir uns zu unserer gisdb verbinden und Abfragen machen:
psql -U gisuser -h 127.0.0.1 gisdb
Zum aufwärmen lassen wir uns alle Wasserfälle (in Bayern) anzeigen:
SELECT tags -> 'name', ST_AsText(ST_Transform(geom, 4326)), tags FROM nodes WHERE tags -> 'waterway' = 'waterfall';
Diese komische 4326 nennt sich übrigens SRID und ist die SRID für die WGS84 Projektion. In diesem Format liegen die Geodaten von Openstreetmap vor!
(Dabei geht es drum wie die kartoffelförmige Erde in eine Kugel abgebildet wird und wie man die Kugel dann in eine flache Karte presst)
Und jetzt wollen wir wissen wie weit diese Wasserfälle von Oettingen entfernt sind. (Bitte in den Kommentaren keine Witze über das schlechte Bier… die kenn ich alle schon!)
SELECT a.tags -> 'name' AS name, round(ST_DistanceSphere(a.geom, b.geom) / 1000) AS "Entfernung im km", ST_AsText(ST_Transform(a.geom, 4326)) AS Koordinaten FROM nodes a, nodes b WHERE a.tags -> 'waterway' = 'waterfall' and a.tags -> 'name' != '' and b.tags -> 'name' = 'Oettingen' and b.tags -> 'place' = 'town' order by 2;
Und jetzt auch noch die Richtung:
SELECT a.tags -> 'name' AS name, round(ST_DistanceSphere(a.geom, b.geom) / 1000) AS "Entfernung im km", ST_AsText(ST_Transform(a.geom, 4326)) AS Koordinaten, degrees(ST_Azimuth(b.geom, a.geom)) AS Richtung FROM nodes a, nodes b WHERE a.tags -> 'waterway' = 'waterfall' and a.tags -> 'name' != '' and b.tags -> 'name' = 'Oettingen' and b.tags -> 'place' = 'town' order by 2;
Beim Kentucky Fried Chicken wird es etwas schwerer. Denn das Tag für einen Kentucky kann entweder an einem Node oder an einem Way sein. Der Way selbst hat aber keine Koordinaten. Meine Lösung ist ein ekelhafter UNION-Select der wahrscheinlich alle Treffen sollte.
Wenn jemand einen besseren SQL hat oder diesen genauer erklärt bekommen möchte -> Ab in die Kommentare!
SELECT a.tags -> 'name' AS name, a.tags -> 'addr:city' AS Stadt, round(ST_DistanceSphere(c.geom, b.geom) / 1000) AS "Entfernung im km", ST_AsText(ST_Transform(c.geom, 4326)) AS Koordinaten, concat(round(degrees(ST_Azimuth(b.geom, c.geom))), ' °') AS Richtung FROM ways a, nodes b, nodes c WHERE c.id = a.nodes[1] and lower(a.tags -> 'amenity') = 'fast_food' and (lower(a.tags -> 'name') = 'kfc' or lower(a.tags -> 'name') = 'kentucky fried chicken') and b.tags -> 'name' = 'Oettingen' and b.tags -> 'place' = 'town' UNION ALL SELECT a.tags -> 'name' AS name, a.tags -> 'addr:city' AS Stadt, round(ST_DistanceSphere(a.geom, b.geom) / 1000) AS "Entfernung im km", ST_AsText(ST_Transform(a.geom, 4326)) AS Koordinaten, concat(round(degrees(ST_Azimuth(b.geom, a.geom))), ' °') AS Richtung FROM nodes a, nodes b WHERE lower(a.tags -> 'amenity') = 'fast_food' and (lower(a.tags -> 'name') = 'kfc' or lower(a.tags -> 'name') = 'kentucky fried chicken') and b.tags -> 'name' = 'Oettingen' and b.tags -> 'place' = 'town' order by 3 asc;
Und zum Abschluss noch ein kleines Geocoding Experiment. Ich hätte gerne die Koordinaten der Adresse von Hetzner (Industriestr. 25, 91710 Gunzenhausen, Deutschland):
(Wir suchen hier nach einem Gebäude – also einem Way. Bekommen tun wir alle Nodes die das Gebäude ausmachen. Einen der Nodes (2436744740) nehmen wir um seine Koordinaten anzuzeigen.)
SELECT nodes FROM ways WHERE tags -> 'addr:housenumber' = '25' and tags -> 'addr:street' = 'Industriestraße' and tags -> 'addr:postcode' = '91710' and tags -> 'addr:city' = 'Gunzenhausen'; SELECT *, ST_AsText(ST_Transform(geom, 4326)) FROM nodes where id = 2436744740;
Die Antwort:
(Damit die Antwort horizontal ausgeben wird vorher ‚\x‘ im psql eingeben!)
-[ RECORD 1 ]+--------------------------------------------------- id | 2436744740 version | 2 user_id | 1217864 tstamp | 2013-10-07 13:59:28 changeset_id | 18228385 tags | geom | 0101000020E61000006F6AA0F99C8F2540365B79C9FF8F4840 st_astext | POINT(10.7804945 49.1249935)
Der Punkt liegt also an den Koordinaten:
N 49.1249935 E 10.7804945
Das war ja einfach 🙂
Schreibe einen Kommentar