Wie weit ist der nächste Kentucky Fried Chicken entfernt?

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 🙂

2 Antworten auf „Wie weit ist der nächste Kentucky Fried Chicken entfernt?“

    1. Hallo Pit,

      Openstreetmap bietet in der Tag noch viele Möglichkeiten über eine API Daten zu bekommen. Außerdem gibts dutzende Apps die die Daten von Openstreetmap verwenden.
      [URL=https://www.thomaschristlieb.de/koordinaten-zu-einer-adresse-und-umgekehrt-dank-google-maps/#comment-101]Hier[/URL] habe ich auch mal die Openstreetmap API angerissen zum Geocodieren.

      Mein Weg mit einer eigenen Datenbank bietet so gesehen natürlich erstmal keinen Vorteil – außer dass man eine Menge dabei lernt 🙂

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht.