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 podman Version nicht funktioniert.~~ Daher musste ich zuerst einmal die 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.