Routing von OSM-Daten mit OSRM und Postgres
Posted on Fr 01 Jänner 2021 in Blog
Vor rund 7 Jahren habe ich schon einmal eine Karte mit Routingdaten auf Basis der Openstreetmap erstellt (siehe Erreichbarkeit von Oberzentren bzw. Erreichbarkeiten auf Basis der Daten der OpenStreetMap). Seit damals hat sich doch einiges getan. Vielleicht nicht unbedingt hinsichtlich veränderter Erreichbarkeiten, aber zumindest technologisch. Und so kam die Lust auf ein wenig mit der Open Source Routing Machine zu spielen.
Inhalt:
- CentOS Stream
- Datenbank
- Open Source Routing Machine
- PostgrSQL HTTP Client
- osm2pgsql
- SQL-Spaß
- Zusammenfassung
CentOS Stream
Auch wenn ich alles in der aktuellen CentOS Stream Version getestet habe, sollte es auch ohne Probleme auf anderen Plattformen laufen. Meine virtuelle Maschine bei Exoscale war mit 4 Cores und 16 GB RAM ausgestattet und hatte eine 200 GB Festplatte.
Der Container von OSRM hat in der installierten
Daher musste ich zuerst einmal die podman
Version nicht funktioniert.Docker CE
Edition installieren.
Wie man das macht, kann man beispielsweise bei Linux Handbook nachlesen.
Zusätzlich müssen wir noch EPEL
und PowerTools
installiern
sudo dnf install epel-release
sudo dnf config-manager --set-enabled powertools
Danach sollte man noch die aktuelle Postgres-Version installieren. Die Anleitung dafür findet sich auf der Seite von
Postgres.
Danach müssen noch Postgis
und die Postgres Devel Files
installiert werden
sudo dnf install postgis31_13 postgresql13-devel
Zusätzlich müssen wir für die (spätere) Installation PostgrSQL HTTP Client
und osm2pgsql
noch zusätzliche
Pakete installieren
sudo dnf install redhat-rpm-config libcurl-devel cmake make gcc-c++ boost-devel expat-devel zlib-devel bzip2-devel postgresql-devel proj-devel proj lua-devel pandoc
Datenbank
Als nächstes werden ein Benutzer osm
und eine Datenbank osm
erstellt.
In der Datenbank osm
werden dann auch gleich die entsprechenden Erweiterungen installiert.
CREATE EXTENSION postgis;
CREATE EXTENSION hstore;
Open Source Routing Machine
In meinem Home-Verzeichnis erstelle ich einen Ordner osrm
, der die Daten für die OSRM beinhalten wird.
Im ersten Schritt holen wir uns von der Geofabrik die aktuellen Daten für Österreich
wget http://download.geofabrik.de/europe/austria-latest.osm.pbf
Danach starten wir den Container (siehe auch: Quick Start Guide)
docker run -t -v "${PWD}:/data" osrm/osrm-backend osrm-extract -p /opt/car.lua /data/austria-latest.osm.pbf
docker run -t -v "${PWD}:/data" osrm/osrm-backend osrm-partition /data/austria-latest.osrm
docker run -t -v "${PWD}:/data" osrm/osrm-backend osrm-customize /data/austria-latest.osrm
docker run -t -i -p 5000:5000 -v "${PWD}:/data" osrm/osrm-backend osrm-routed --algorithm mld /data/austria-latest.osrm
Wenn alles funktioniert hat, ist der Container über localhost:5000
erreichbar.
Update: 04.01.2021
Ich hatte einen kleinen Fehler in meiner Podman-Ausführung. Funktioniert auch ohne Problem mit Podman. Einfach folgende Befehle ausführen:
podman run -t -v "${PWD}:/data:z" osrm/osrm-backend osrm-extract -p /opt/car.lua /data/austria-latest.osm.pbf
podman run -t -v "${PWD}:/data:z" osrm/osrm-backend osrm-partition /data/austria-latest.osrm
podman run -t -v "${PWD}:/data:z" osrm/osrm-backend osrm-customize /data/austria-latest.osrm
podman run -t -i -p 5000:5000 -v "${PWD}:/data:z" osrm/osrm-backend osrm-routed --algorithm mld /data/austria-latest.osrm
PostgreSQL HTTP Client
Zusätzlich ist die Idee den PostgreSQL HTTP Client zu verwenden aufgekommen. Und da ich das bisher noch nicht gemacht hatte, dachte ich, warum nicht ;-)
Da das bin
Verzeichnis der Postgres-Installation bei mir nicht im Pfad war, habe ich das noch hinzugefügt,
denn es wird für pg_config
benötigt.
sudo su
PATH=$PATH:/usr/pgsql-13/bin/
Im nächsten Schritt habe ich unter /opt
einen Ordner erstellt und in dem erstellten Ordner dann das
Repository von Github geklont.
mkdir /opt/postgres
cd /opt/postgres
git clone https://github.com/pramsey/pgsql-http.git
Danach wird noch die Installation durchgeführt:
cd pgsql-http
make
make install
Im nächsten Schritt installieren wir in unserer osm
Datenbank noch die Erweiterung:
CREATE EXTENSION http
osm2pgsql
Für den Import der OSM-Daten in die Datenbank, verwenden wir das Tool osm2pgsql. Über osm2pgsql
gibt es einen lesenswerten Artikel bei
Cybertec mit dem Titel
OSM to PostGIS – The Basics.
Aber warum wollen wir die Daten überhaupt in die Datenbank laden? So können wir aus den Daten die Punkte auswählen,
die dann bei OSRM
abgefragt werden sollen.
Die Installation geht folgendermaßen. Zuerst wird das Repository von GitHub geklont und danach folgen wir der Anleitung Building
cd /opt/postgres
git clone https://github.com/openstreetmap/osm2pgsql.git
cd osm2pgsql/
mkdir build && cd build
cmake ..
make
make install
ln -s /opt/postgres/osm2pgsql/build/osm2pgsql /usr/bin/osm2pgsql
Danach sollte osm2pgsql
funktionieren und man kann man dem Import der Daten beginnen.
Das machen wir mit folgendem Befehl:
osm2pgsql -U osm -W -d osm -H localhost --style=/opt/postgres/osm2pgsql/default.style -G -s --hstore --number-processes 4 -C 4096 austria-latest.osm.pbf
SQL-Spaß
Nachdem die Daten alle in unserer Datenbank sind, können wir uns daran machen, die Dinge auszuwerten. Für uns
ist im ersten Schritt die Tabelle public.planet_osm_point
interessant, da sich hier unsere möglichen Kandidaten für
das Routing befinden. Am einfachsten verschafft man sich mittels psql
einen Überblick:
\d public.planet_osm_point
Für mich sind hier drei Spalten interessant: osm_id
(eindeutige ID im OSM Datensatz),
place
(gibt an, ob es ein Ort ist) und way
(die Koordinaten des Ortes). Um sich einen Überblick
über die Werte zu verschaffen, kann man folgendes machen:
select place, count(*) from public.planet_osm_point group by place order by 2 desc;
Danach sollte das Resultat ungefähr so aussehen:
place | count
---------------------+---------
null | 1881607
locality | 15364
hamlet | 14229
village | 6574
isolated_dwelling | 3796
farm | 1556
neighbourhood | 651
suburb | 410
town | 297
square | 38
region | 24
quarter | 18
yes | 16
city | 8
state | 8
island | 5
municipality | 2
borough | 2
FIXME | 2
islet | 2
archaeological_site | 2
single_dwelling | 1
natural | 1
country | 1
climbing | 1
Ausgehend von diesen Werten habe ich mich dann für das Routing für die Kategorien town
und city
entschieden.
Daher wird im nächsten Schritt ein eigenes Schema für die Vorbereitung und Resultate des Routings
erstellt: create schema routing;
Danach erstellen wir eine Tabelle, die alle möglichen Routing-Kombinationen speichert. Hierbei wird angenommen, dass
der Weg in beide Richtungen gleich ist, d.h. a -> b = b -> a
. Dies mag nicht immer stimmen, aber
verringert die Anzahl der Abfragen beträchtlich (in unserem Fall um knapp 50 Prozent von 93.000 auf 46.600).
Auch wenn es für die Auswahl von town
und city
nicht so gravierend ist, kann es die
Laufzeit, wenn man beispielsweise village
noch hinzunehmen würde, beträchtlich verringern.
create table routing.edges as with get_all as (
select osm_id from public.planet_osm_point where place in ('town', 'city')
)
select a.osm_id as id1, b.osm_id as id2 from get_all as a, get_all as b where
a.osm_id<b.osm_id;
Danach fügen wir noch die Spalte routing_id
hinzu:
alter table routing.edges add column routing_id serial
(Disclaimer: Wahrscheinlich wäre es sinnvoller die serial
Spalte durch eine identity column
zu ersetzen. Siehe auch
PostgreSQL 10 identity columns explained)
Im nächsten Schritt starten wir unsere Routingabfragen. Mit diesen Abfragen berechnen wir die
Entfernungen zwischen zwei Orten. Dafür müssen wir uns auch noch den Abfrage-String für OSRM zusammenbasteln. Zusätzlich
müssen wir unsere Koordinaten noch nach WGS84
transformieren. Außerdem verwenden wir auch noch einen lateral join
,
der uns ermöglicht die Parameter auch an den HTTP-Request
zu übergeben.
create table routing.routes as
with select_edge as (
select routing_id, ST_X(ST_Transform(b.way, 4326)) || ',' || ST_Y(ST_Transform(b.way, 4326)) || ';' || ST_X(ST_Transform(c.way, 4326)) || ',' || ST_Y(ST_Transform(c.way, 4326)) as route
from routing.edges as a, public.planet_osm_point as b, public.planet_osm_point as c
where b.osm_id=a.id1 and c.osm_id=a.id2
)
select routing_id, (content::json->'routes'->0->>'distance')::numeric as distance, (content::json->'routes'->0->>'duration')::numeric as duration
from select_edge left join lateral http_get('http://127.0.0.1:5000/route/v1/driving/' || route || '?steps=false&overview=simplified&annotations=false') as b on true;
Zusammenfassung
Das Zusammenspiel zwischen den einzelnen Tools funktioniert ganz gut.
Natürlich kommt die Dauer der Abfragen auch darauf an, wie viele place
-Typen ausgewählt werden, da die Datenmenge,
je nach Auswahl, sehr schnell anwachsen kann.
Sehr positiv überrascht war ich, wie problemlos der PostgreSQL HTTP Client
funktioniert hat. Andrerseits wird die
Erweiterung auch von Paul Ramsey entwickelt, der unter anderem Postgis
core contributor ist. Daher vielleicht doch nicht so überraschend ;-)
Die Routing-Abfragen per OSRM
gingen wirklich sehr schnell, was mich durchaus sehr beeindruckt hat.
Warum man überhaupt die Abfragen an OSRM
direkt aus der Datenbank schicken möchte?
Man erspart sich durchaus einige Netzwerktraffic. Denn ansonsten würde der Weg wohl in die
Richtung aussehen, dass man die Daten aus der Datenbank abfragen muss, an OSRM
schickt und danach das Resultat
wieder in die Datenbank schreibt.