Issue créée pour tracer (en mode bloc-note / wiki) les actions réalisées sur le serveur pour la création de la base OSM au schéma osm2pgsql et le déploiement de l'instance underpass.

installation et réglage de osm2pgsql

installation osm2pgsql

En suivant : https://osm2pgsql.org/doc/install.html sudo apt install osm2pgsql besoin de 383 MB On est sous ubuntu 24 => installation release 1.11.0

Remarque : il est possible de forcer l'installation de la nouvelle version 2.0.1 à condition de compiler soi même, en suivant le readme. C'est ce que mael a fait pour les premiers tests.

installation postgreSQL

a priori déjà fait lors de la commande précédente => a installé la version 16.8

création de la DB

https://osm2pgsql.org/doc/manual-v1.html#preparing-the-database En utilisant le user ubuntu avec lequel je suis connecté au serveur pour profiter ensuite de la peer authentification. ➡️​ Je ne sais pas si c'est une bonne idée, si il ne faut pas plutôt créer un utilisateur spécifique ?

sudo -u postgres createuser ubuntu
sudo -u postgres createdb --encoding=UTF8 --owner=ubuntu osm
sudo -u postgres psql osm --command='CREATE EXTENSION postgis;'
sudo -u postgres psql osm --command='CREATE EXTENSION hstore;'

Remarque : hstore inutile si on définit des tables personnalisées (flex output) sans les tags.

Si besoin de vider la base : sudo -u postgres psql postgres --command='DROP DATABASE osm;' Si besoin de renommer la DB : sudo -u postgres psql postgres --command='ALTER DATABASE osmfrance RENAME TO osm;' (et semblable pour une table avec ALTER TABLE)

ajustement de la configuration

https://osm2pgsql.org/doc/manual-v1.html#tuning-the-postgresql-server en suivant strictement les valeurs proposées (annoncées pour 64GB de RAM et un disque SSD) pour trouver où est la conf : sudo -u postgres psql -c 'SHOW config_file' puis : sudo vi /etc/postgresql/16/main/postgresql.conf et modifications de valeurs puis redémarrage : sudo service postgresql restart

Sources extrait d'OSM

OSM-FR

osm-fr ne fournit plus l'extrait europe mais seulement pays par pays. En revanche on pourrait demander les diff, et recréer manuellement l'Europe par ajout successif des pays (ou fusion préalable avec osmosis)

Geofabrik

Attention, les public downloads de geofabrik (disponibles sans connexion) n'ont pas les metadata curl -O https://ftp5.gwdg.de/pub/misc/openstreetmap/download.geofabrik.de/europe-latest.osm.pbf (30GB, 8min20)

Besoin de se connecter via l'oauth osm pour accéder à : https://osm-internal.download.geofabrik.de/europe.html Et je ne sais pas le faire en ligne de commande sur le serveur 🤔​

remarque sur fusion

il est possible de lister plusieurs fichiers à la suite sur la ligne de commande pour qu'ils soient traités ensemble : osm2pgsql -U ubuntu -d osm -c -s -S cartes-v1.style --extra-attributes --hstore-all --hstore-match-only france.osm.pbf suisse.osm.pbf belgique.osm.pbf mais alors on ne pourra faire les update automatique avec osm2pgsql-replication ...

Bilan tests initiaux ➡️ Réfléxion sur la taille de la DB et le besoin de filtrage

Pour pouvoir faire les updates automatiques à partir des diff, il est nécessaire d'activer l'option --slim. Cela entraine la création de 3 tables middle pour stocker TOUS les _nodes, _ways et _relations de l'extrait traité. C'est nécessaire pour enregistrer les ajouts ou suppression d'éléments à chaque update. Et cela même si les tables "filtrées" spécifiques de osm2pgsql (output table) et contenant la géométrie (tables _point, _line et _polygon) ne contiennent que quelques éléments. Donc contrairement à ce que son nom laisse croire, cette option coûte cher en espace disque ! Exemple sur la Bretagne :

• l'extrait compressé fait 346 Mo
• j'avais imaginé un filtrage (dans cartes-v1.style) pour que la base fasse 300Mo, mais
• activer --slim coûte déjà 3782 Mo (sans aucun élément filtré)
• activer --slim --extra-attributes coûte 4636 Mo (sans aucun élément filtré)
• avec en plus les éléments filtrés selon cartes-v1.style, la base fait 4973 Mo
  • soit +337 Mo pour les éléments filtrés avec leur géométrie, c'est logique
• au maximum la base fait 8151 Mo (avec --hstore-all pour stocker tous les objets avec tous leurs tags, et toutes leurs géométries)
  • si on ne demande pas les extra attributes, elle fait 6300 MB

Même chose avec la France, les 3 middle tables avec métadonnées (-x) prennent déjà 29 Go.

Remplissage de la base avec un flex output personnalisé

principe

On écrit un fichier geometries-alone.lua, basé sur l'exemple geometries.lua, mais encore plus simple :

• on ne garde que les géométries
  • en SRID 4326 pour être compatible avec overpass/underpass (même si frodrigo a mis à jour underpass depuis)
• on ne garde pas les tags dans ces output tables
  • on utilisera les tags bruts qui sont déjà présents dans les middle tables _nodes, _ways et _rels. Il faudra néanmoins les indexer après coup car ça n'est pas fait par défaut
• on stocke 1 seule ligne par élément OSM avec toute la géométrie en point, linestring ou en geometry
  • d'habitude osm2pgsql coupe les grosses géométries sinon ça ralentit le calcul des tuiles (car il faut charger toute la géométrie même si un seul petit bout passe sur la tuile). On fait l'hypothèse ici que garder les grosses géométries entières ne ralentit pas trop les requêtes sql/postgis, mais ça méritera d'être vérifié.

remarque : donc plus besoin de l'extension hstore lors de la création de la DB

Remarque sur la structure la DB

Avec la version initiale geometries.lua, les relations étaient stockées avec un index négatif dans les tables polygons et lines. ça empêchait postgres d'utiliser l'index pour les JOIN. J'ai alors modifié le lua pour utiliser les vrais id des éléments OSM, ce qui implique de créer 1 table par type d'élément OSM (node, way, rel), et non 1 table par type de géométrie (point, line, polygon).

remarque sur les flat nodes

Attention : à partir de l'Europe, il est très fortement recommandé d'utiliser le mode --flat-nodes FILE pour stocker les nodes dans un fichier et pas dans la base. Le fichier va prendre un peu plus de 100 GB quelle que soit la taille de l'extrait utilisé. Dans ce cas, il est conseillé de désactive le cache avec --cache 0

Mise en oeuvre

récupération de l'extrait

curl -O https://download.openstreetmap.fr/extracts/merge/france_metro_dom_com_nc.osm.pbf

commande pour osm2pgsql :

osm2pgsql -U ubuntu -d osm -c -s -x --flat-nodes DB-flat-nodes --middle-with-nodes -O flex -S geometries-alone.lua france_metro_dom_com_nc.osm.pbf

explication :

• dans la database -d DBNAME
• création initiale : -c ou --create
• en mode slim (nécessaire pour ensuite gérer les update) : -s ou --slim
• et on garde les metadata : -x ou --extra-attributes (si on les a dans l'extrait osm évidemment)
• avec les nodes stockés dans un fichier texte séparé --flat-nodes FILE
• mais en ajoutant quand même dans la base les noeuds qui ont des tags : --middle-with-nodes
• on utilise le flex output pour avoir des tables personnalisées : -O flex
• on indique le fichier .lua où trouver notre script pour le flex output : -S FILE.lua

log

025-04-07 19:51:37  Reading input files done in 3608s (1h 0m 8s).                        
2025-04-07 19:51:37    Processed 524198002 nodes in 562s (9m 22s) - 933k/s
2025-04-07 19:51:37    Processed 73315494 ways in 2171s (36m 11s) - 34k/s
2025-04-07 19:51:37    Processed 1076415 relations in 875s (14m 35s) - 1k/s
2025-04-07 19:51:41  Done postprocessing on table 'planet_osm_nodes' in 0s
2025-04-07 20:18:42  Done postprocessing on table 'planet_osm_ways' in 1621s (27m 1s)
2025-04-07 20:18:42  Done postprocessing on table 'planet_osm_rels' in 123s (2m 3s)
2025-04-07 20:18:42  All postprocessing on table 'points' done in 427s (7m 7s).
2025-04-07 20:18:42  All postprocessing on table 'lines' done in 509s (8m 29s).
2025-04-07 20:18:42  All postprocessing on table 'areas' done in 1414s (23m 34s).
2025-04-07 20:18:42  osm2pgsql took 5233s (1h 27m 13s) overall.

Modifs sur la base post création

Après création des tables, besoin de donner accès en lecture à l'utilisateur idoine :

GRANT SELECT ON planet_osm_nodes TO readonlyuser;
GRANT SELECT ON planet_osm_ways TO readonlyuser;
GRANT SELECT ON planet_osm_rels TO readonlyuser;
GRANT SELECT ON planet_osm_users TO readonlyuser;
GRANT SELECT ON nodes_geom TO readonlyuser;
GRANT SELECT ON ways_geom TO readonlyuser;
GRANT SELECT ON rels_geom TO readonlyuser;

Et de créer des index sur les tags (pas besoin d'indexer les id de ces 3 tables car ce sont déjà des primary keys)

CREATE INDEX nodes_tags_idx ON planet_osm_nodes USING GIN (tags); //
CREATE INDEX ways_tags_idx ON planet_osm_ways USING GIN (tags);
CREATE INDEX rels_tags_idx ON planet_osm_rels USING GIN (tags);

ça prend plusieurs minutes pour chaque.

Sizing

La base fait 57 Go (+ le fichier flat nodes de 100 Go).

Poid des différentes parties de la base. Cas de la France+domtom : le .osm.pbf fait 5,2 Go

partie	poids
éléments OSM bruts dans middle tables	29Go
indexation des tags	+2 Go
ajout des géométries dans output tables	+26 Go
TOTAL	57 Go

Installation et démarrage d'Underpass-API avec docker

adaptation du code à Osm2pgsql

Une PR a été créée pour ajouter la gestion du schéma osm2pgsql. En attendant, j'utilise mon fork : git clone https://github.com/etienneJr/Underpass-API.git récupération de la branche : git switch osm2pgsql-backend

Modifs dans le code

Pour que ça marche avec docker, il faut modifier backends/postgres_osm2pgsql/docker-compose.yaml :

• supprimer/commenter le service osm2pgsql
• supprimer/commenter le service postgress
• modifier la ligne avec l'url de la DB : DATABASE_URL: postgres://readonlyuser:iwanttoreadfree@51.159.100.169:5432/osm
• supprimer/commenter depends_on: - postgres

installations diverses

• installation de docker lui même : sudo apt install docker.io version 26.1.3-0ubuntu1~24.04.1 (302MB)
• maj liste : sudo apt-get update
• installation plugin compose : sudo apt-get install docker-compose-plugin ❌ échec E: Unable to locate package docker-compose-plugin
• installation manuelle du plugin compose en suivant ça
• ajout du user ubuntu dans le groupe docker (d'après ça) : sudo usermod -a -G docker ubuntu puis newgrp docker
• build du container docker compose --profile '*' build

démarrage

• en foreground (ctrl+c pour arrêter) : docker compose up
• en background (reste actif) : docker-compose up -d (comment on l'arrête alors ? down je crois, sinon stop)
  • le yaml a l'option restart: unless-stopped donc a priori il redémarre tout seul ?!? enfin il faut peut-être changer en always. Cf cette page mais qui est pour docker sans compose. Il est écrit qu'il faut désactiver l'option restart: si on préfère utiliser systemd.

Utilisation

• underpass en local : time curl http://localhost:9292/interpreter?data=
• underpass en prod : time curl http://51.159.100.169:9292/interpreter?data=
• pour comparaison avec overpass interne : time curl https://overpass.cartes.app/api/interpreter?data=

Tests de rapidité

filtrage par tag et par bbox

Exemple : 419 aires de jeux à Rennes

[out:json][timeout:25];
nwr["leisure"="playground"](48.05,-1.75,48.15,-1.6);
out center;

data=%5Bout%3Ajson%5D%5Btimeout%3A25%5D%3B%0Anwr%5B%22leisure%22%3D%22playground%22%5D%2848.05%2C-1.75%2C48.15%2C-1.6%29%3B%0Aout%20center%3B > result.json

• en local : (sur base Bretagne 7Go : 50 ms ✅)
• en prod (base FR 60 Go) pour juste la requête SQL : 900 ms ⚠️​
• en prod sur serveur overpass : ✅ 600-700 ms exemple qui renvoie 419 aires de jeux à Rennes :

C'est bizarre que ça passe de 50 ms à 900 ms, on dirait un pb d'indexation.