Custom OpenStreetMap data processing with osm2pgsql Flex backend

Osm2pgsql tool is quite famous in the OpenStreetMap community : it allows to transform raw OSM data into a ready-to-use PostgreSQL database. It is commonly used for setting up rendering or geocoding databases. People also use it to run various data analysis, which are made quite simple using extensions like PostGIS. However, for a quite long time, it was not so intuitive to fine tune osm2pgsql to go further. This is now past as Jochen Topf released a new « flex back-end » (still in experimental phase). This mode allows to configure how the database should look at the end of data process. In particular, you can process relations and copy tags they carry on other features like nodes or ways. Some documentation and examples are now available.

As I’m working on GeoDataMine (an easy-to-use OSM data extractor for local authorities), I was stuck on a very simple use case : getting street names from associatedStreet relations. In street numbers exports I was producing, street name were available when it was set as addr:street tag on nodes, but not when a node was a part of associatedStreet relation. This was frustrating : information exists, but you can’t access it. When Flex back-end was released, I was curious about if it allowed to solve this issue. TL;DR : it does ! Let’s get through my Flex journey.

Compiling osm2pgsql

First step is to make available latest osm2pgsql version available on your machine. If it is not (yet) available in your distribution repositories, you can compile it. Don’t be afraid, the process is quite straightforward. Just make sure you’re getting latest version (master branch of the Git repository).

Configuring Flex back-end

Next step is to create a Lua script to define what the database will look like after osm2pgsql import process. Some examples are available in the repository. I’m going for the compatible.lua file, as it mimics original back-end structure.

Columns

I want to have a rel_tags columns, similar to tags hstore but for storing all tags coming from relation. So, I just add a new column in my existing tables structure :

tables.point = osm2pgsql.define_table{
    ...
    columns = {
        ...
        { column = 'tags', type = 'hstore' },
        { column = 'rel_tags', type = 'hstore' },
        { column = 'way', type = 'point' }
    }
}

Note that this will work for addresses, as each node will only be part of one associatedStreet relation. If you’re working with bus lines for example, you will need a database structure handling features being part of several relations of same type.

Relation tags > node table

Creating your custom tables is a really interesting new feature in this Flex back-end. Here, I will create a table to associate relation tags to a node ID. This can be done after other tables declaration :

tables.relation_node = osm2pgsql.define_relation_table(
    'planet_osm_relation_node', -- The name of the table
    {
        { column = 'node_id', type = 'bigint' },
        { column = 'rel_tags', type = 'hstore' }
    } -- List of supplementary columns
)

We do this only for nodes, as ways will be processed using another mechanism.

Process stages for ways

Flex back-end offers to process ways and relations in two separate stages :

  • Stage 1 : default initial processing. Each OSM object is reviewed, but you can only access information concerning directly this object. For a node its tags and coordinates, for a way its tags and list of nodes, for a relation its tags and list of members. So no way to get relation tags when reviewing a node or way. But you can mark here a feature as needing a second review, after every feature has already been seen.
  • Stage 2 : marked features are viewed a second time. There, you can access global variables from your script where you have stored useful information. For example, for a way ID, all tags of relations it is a part of. You can then put these tags in the database for definitive storage. Note that features marking only works for ways and relations as now (that’s why I’m using the relation_node table above) !

So let’s use this system to store relation metadata in a global variable, then associate it to ways. First, we create an empty global variable (near the start of script) :

by_way_id = {}

Then, we set-up the two stages system in the way-processing function, near the start lines of this function :

-- Ways are processed in stage 2 to retrieve relation tags
if osm2pgsql.stage == 1 then
    osm2pgsql.mark_way(object.id)
    return
end

Everything put in this function after these lines will be executed only in stage 2. That’s where we will add the code to get relation metadata and store it into the rel_tags we created earlier. This can be done using these lines (to put before any :add_row call) :

-- If there is any data from relations, add it in
local d = by_way_id[object.id]
if d then
    object.rel_tags = d
end

Everything is now ready for both nodes and ways.

Store in memory relations metadata

Now, we have to edit osm2pgsql.process_relation(object) function to define what we want to save in memory or in relation_node table. This will be done by looping through all members of each relation.

-- Go through all the members and store relation tags
for _, member in ipairs(object.members) do
    -- When member is a node
    if member.type == 'n' then
        -- We add metadata to relation_node table
        local d = {}
        copy_rel_tags(type, d, object)
        tables.relation_node:add_row({ node_id = member.ref, rel_tags = d })
    -- When member is a way
    elseif member.type == 'w' then
        -- We put metadata in by_way_id global variable
        if not by_way_id[member.ref] then
            by_way_id[member.ref] = {}
        end
        local d = by_way_id[member.ref]
        copy_rel_tags(type, d, object)
    end
end
 
-- Outside of the process_relation, you can make available
-- the copy_rel_tags function, which is an helper to merge
-- all tags coming from various relations
function copy_rel_tags(rel_type, d, object)
    for k,v in pairs(object.tags) do
        if k ~= "type" then
            d[rel_type .. '_' .. k] = v
        end
    end
end

So, we’re good : metadata is stored, ways processed in stage 2 will retrieve it. The complete Lua script is here (a bit different because less tags are used in my database compared to compatible.lua file). Let’s run osm2pgsql.

Launch osm2pgsql

Just run the osm2pgsql command using appropriate option to use Flex back-end :

osm2pgsql --create \
	-H localhost -U postgres -P 5432 -d mydb \
	--slim --drop \
	--style "my_flex_rules.lua" \
	--output=flex \
	extract.osm.pbf

If you have Lua-related issues, they are most likely to be originating in your script. As I’m no Lua expert, I had to redo this several times before having the perfectly-working script. Once done, your database is ready to explore.

Database final processing

Connect to your PostgreSQL database. You will see the four tables you defined. Check that each of them has data and its columns filled. For our street names use case, we’re not done yet ! In fact, ways have their rel_tags filled, but not nodes (point table). Let’s run a SQL request to fill rel_tags columns in point table :

UPDATE planet_osm_point
SET rel_tags = a.rel_tags
FROM (
    -- Merge all tags for a certain node
    SELECT node_id, array_to_string(array_agg(rel_tags),',')::hstore AS rel_tags
    FROM planet_osm_relation_node
    GROUP BY node_id
) a
WHERE a.node_id = osm_id; DROP TABLE planet_osm_relation_node;

At this point, you have your rel_tags columns filled everywhere. So now, we can get street names from both nodes and relations tag. For example :

SELECT
    osm_id, "addr:housenumber",
    -- Take first not null value
    COALESCE(rel_tags->'associatedStreet_name', tags->'addr:street') AS street
FROM planet_osm_point
WHERE "addr:housenumber" IS NOT NULL;

Quite handy, right ?

Conclusion

I’m really glad the osm2pgsql development team has released this new feature. Even if Lua is not the programming language I use the most, it’s still relatively easy to create your own configuration file. The given examples are really useful, I hope more will be released in the future to cover more use cases. I was a bit disappointed by the two-stages system not being available for nodes, but it seems temporary and shown workaround works well. It will definitely make advanced reuses of OSM data easier for developers and data analysts. You should give it a try !

Looking for OpenStreetMap data expertise for your projects ? Contact me and let’s discuss !

Calculs d’itinéraires à grande échelle avec PgRouting

Les calculs d’itinéraires sont un type d’analyse spatiale très classique, permettant de connaître l’itinéraire le plus court d’un point A à un point B. Pour réaliser ces calculs, nous devons nous appuyer sur des données géographiques de bonne qualité. Cet article va vous présenter comment réaliser des calculs d’itinéraires à grande échelle simplement avec :

  • PostgreSQL, la base de données libre de référence pour les données relationnelles volumineuses
  • PostGIS, l’extension pour l’analyse géospatiale sur PostgreSQL
  • PgRouting, l’extension pour les calculs d’itinéraires sur PostgreSQL
  • OpenStreetMap, la base de données géographique mondiale de référence
  • Osm2PgRouting, l’outil d’import de données OpenStreetMap pour PgRouting
  • Osmium, un outil de traitement de données OpenStreetMap pour convertir le format de fichier, filtrer les données et découper en plusieurs zones.

Notez que d’autres outils existent pour réaliser des calculs d’itinéraires à grande échelle sur des réseaux complexes. Ces outils sont plus pertinents dans les cas d’usages classiques (calculs pour affichage sur une page web). Ici, utiliser PgRouting présente plusieurs avantages, notamment une mise en place simple (faible besoin en compétences d’administration système) et la gestion centralisée de toutes les données dans PostgreSQL.

Vue d’ensemble de la chaîne de traitement

Je passe volontairement sur l’installation des différents outils, qui est documenté sur leurs sites respectifs. L’installation dépend largement du système d’exploitation utilisé, mais reste la plus simple à mon avis sous GNU/Linux.

La logique de traitement que nous allons mettre en place est la suivante :

Extrait des données OpenStreetMap avec Geofabrik, découpage et filtre avec Osmium, import en base avec Osm2PgRouting, calcul d'itinéraire en base avec PgRouting

Commençons tout de suite par la récupération des données.

Télécharger un extrait OSM sur Geofabrik

Geofabrik est une société allemande spécialisée sur OpenStreetMap. Cette société propose des extraits quotidiens de la base OpenStreetMap, découpés par régions (continent, pays, région administrative). Vous pouvez donc télécharger la zone de votre choix, au format .osm.pbf (le plus compact et rapide à traiter). Ici, on récupère le fichier france-latest.osm.pbf.

Filtre thématique avec Osmium

L’outil d’import Osm2PgRouting est bien pratique, mais présente deux limitations qui ont leur importance dans le choix des traitements à mettre en place :

  • Il lit uniquement des fichiers OSM XML (format .osm)
  • Il charge l’intégralité des données d’entrée en mémoire vive pour réaliser son analyse avant import en base de données.

Nous devons donc nous assurer que les fichiers en entrée de cet outil soient les plus légers possibles. Heureusement, il est possible de réaliser l’import de manière progressive en découpant par zones géographiques distinctes nos données. C’est pourquoi on utilise l’outil Osmium : pour filtrer et découper le fichier PBF téléchargé en plusieurs fichiers OSM XML correspondants au réseau par zone géographique.

On commence donc par filtrer les données OpenStreetMap pour ne garder uniquement le réseau routier. À noter qu’il est possible de s’intéresser à d’autres réseaux (eau, électricité, gaz, Internet, chemins de fer…), à condition de changer quelques configurations. La commande pour filtrer avec Osmium est la suivante :

osmium tags-filter \
	france-latest.osm.pbf \
	w/highway=motorway,motorway_link,trunk,trunk_link,primary,primary_link,secondary,secondary_link,tertiary,tertiary_link,unclassified,residential \
	-f pbf,add_metadata=false \
	-o france-roads.pbf

On peut modifier les options de cette commande pour prendre par exemple toutes les routes (au lieu d’une sélection ici), ou justement sélectionner d’autres types d’objets. Le fichier en sortie france-roads.pbf contient donc uniquement le réseau routier.

Découpage géographique avec Osmium

Nous allons désormais découper ce fichier en plusieurs zones distinctes. Cette étape est fastidieuse car vous devez définir le découpage à mettre en œuvre. L’idée est d’avoir un fichier GeoJSON par zone distincte, plus un fichier JSON décrivant votre découpage pour Osmium. Pour vos zones, vous pouvez utiliser des découpages administratifs, ou créer une grille régulière avec QGIS. Au final, vous devez avoir :

  • Plusieurs fichiers GeoJSON, contenant chacun la géométrie de la zone concernée. On peut les nommer area_1.geojson, area_2.geojson, area_3.geojson
  • Un fichier de configuration global nommé areas.json

Pour ce fichier areas.json, il doit avoir la structure suivante :

{
	"directory": "/tmp/",
	"extracts": [
		{
			"output": "area_1.osm",
			"polygon": {
				"file_name": "area_1.geojson",
				"file_type": "geojson"
			}
		},
		{
			"output": "area_2.osm",
			"polygon": {
				"file_name": "area_2.geojson",
				"file_type": "geojson"
			}
		},
		{
			"output": "area_3.osm",
			"polygon": {
				"file_name": "area_3.geojson",
				"file_type": "geojson"
			}
		}
	]
}

Vous devez avoir autant d’objets dans le tableau extracts que vous avez de zone à prendre en compte. Chacun de ces objets permet de connaître le nom du fichier GeoJSON à utiliser, et le nom du fichier OSM XML en sortie. Une fois tous ces fichiers en place, vous pouvez lancer la commande Osmium suivante :

osmium extract -c areas.json france-roads.pbf

Après quelques instants, vos fichiers filtrés et découpés, au format XML, seront disponibles dans le dossier de sortie choisi. On peut désormais créer la base de données.

Création de la base de données PostgreSQL

On commence donc par mettre en place la base et ses extensions :

CREATE DATABASE osmroads;
CREATE EXTENSION postgis;
CREATE EXTENSION pgrouting;
CREATE EXTENSION hstore;

La base est désormais prête pour l’import des données. On utilise l’outil osm2pgrouting, qui transforme directement les données brutes OSM filtrées en données utilisables en base pour réaliser des calculs d’itinéraires. De nombreuses options sont disponibles, nous utiliserons ici les commandes suivantes :

# Import des premières zones
osm2pgrouting \
	-f /tmp/area_1.osm \
	-c /usr/local/share/osm2pgrouting/mapconfig_for_cars.xml \
	--tags \
	--addnodes \
	--no-index \
	-d osmroads -U postgres
 
# Import de la dernière zone (avec création des index)
osm2pgrouting \
	-f /tmp/area_n.osm \
	-c /usr/local/share/osm2pgrouting/mapconfig_for_cars.xml \
	--tags \
	--addnodes \
	-d osmroads -U postgres

La première commande est à répéter autant de fois que vous avez de zones lors du découpage. La dernière zone à importer utilise la seconde commande, qui lance la création des index en base de données. L’option --addnodes est essentielle pour permettre l’import progressif des données. Ces commandes peuvent prendre un certain temps à s’exécuter selon les capacités de votre machine.

Vous avez désormais à disposition une base prête à réaliser des calculs d’itinéraires pour automobile (principalement avec la table ways).

Calculer un premier itinéraire

L’extension PgRouting dispose de nombreuses fonctions pour réaliser toutes sortes de calculs. Ici, on va réaliser un premier calcul en utilisant l’algorithme A*. Il permet d’obtenir un chemin relativement court de manière efficace. Le calcul est à lancer en SQL de cette façon :

SELECT *
FROM pgr_bdAstar(
	'SELECT gid AS id, source, target, cost, reverse_cost, x1, y1, x2, y2 FROM ways', -- Requête de sélection
	2365623, -- ID noeud de départ
	1779025 -- ID noeud d'arrivée
) p;

La fonction pgr_bdAstar prend trois paramètres :

  • Une requête SQL, permettant de récupérer les chemins à prendre en compte. On peut ici par exemple filtrer certains chemins non-pertinents, ou changer les valeurs de coûts de traversée (cost et reverse_cost).
  • Deux identifiants de nœuds (point de départ et d’arrivée). Ceux-ci correspondent à la colonne id de la table ways_vertices_pgr. Ce sont les intersections et extrémités de chemins de votre réseau routier.

La requête va vous retourner ce type de réponse :

 seq | path_seq | node | edge | cost | agg_cost
-----+----------+------+------+------+----------
   1 |        1 |    2 |    4 |    1 |        0
   2 |        2 |    5 |    8 |    1 |        1
   3 |        3 |    6 |    9 |    1 |        2
   4 |        4 |    9 |   16 |    1 |        3
   5 |        5 |    4 |    3 |    1 |        4
   6 |        6 |    3 |   -1 |    0 |        5
(6 ROWS)

Chaque ligne correspond à un point de passage sur le graphe routier. Le premier est le point de départ, le deuxième le premier nœud intermédiaire, le troisième est le deuxième nœud intermédiaire… Jusqu’au nœud d’arrivée. À noter que si la requête ne renvoie pas de résultats, c’est qu’aucun itinéraire n’a été trouvé entre les deux points précisés. On retrouve en colonnes l’ordre de passage, le nœud visité, le segment visité, le coût de traversée du segment, et enfin le coût agrégé de l’itinéraire. Ce type d’interrogation simple peut être réalisé graphiquement à l’aide de QGIS et PgRoutingLayer.

Si l’on souhaite obtenir un résultat plus concis, à savoir la géométrie du parcours et son coût total, on peut joindre la table ways au résultat et fusionner les segments :

SELECT ST_LineMerge(ST_Union(w.the_geom)) AS geom, MAX(a.agg_cost) AS total_cost
FROM pgr_bdAstar(
	'SELECT gid AS id, source, target, cost, reverse_cost, x1, y1, x2, y2 FROM ways', -- Requête de sélection
	2365623, -- ID noeud de départ
	1779025 -- ID noeud d'arrivée
) a
LEFT JOIN ways w ON a.edge != -1 AND w.gid = a.edge;

Voilà, vous avez créé votre premier itinéraire !

Aller plus loin

Grâce à ces quelques conseils, vous êtes désormais en mesure de réaliser des calculs d’itinéraires à grande échelle avec un ensemble de logiciels plutôt portables. Vous pouvez adapter ce scénario à différents cas d’usages, et par exemple :

  • Changer de type de réseau : électrique, hydrographique, piéton, vélo… Vous pouvez imaginer toutes sortes de cas d’usages. La seule contrainte est d’adapter le fichier mapconfig.xml de osm2pgrouting selon les attributs utilisés.
  • Réaliser des calculs d’itinéraires en lot. Les fonctions « Many to many » de PgRouting permettent de calculer de n’importe quel point de départ vers n’importe quel point d’arrivée. Si vous êtes sur une logique de combinaisons départ > arrivée plus restreinte, l’idéal est de passer sur une fonction PLPgSQL pour boucler sur vos combinaisons.

Envie d’être accompagné sur ce sujet ? Je vous propose des formations sur ces thématiques, ou contactez-moi pour que nous discutions de vos besoins précis.

Listes structurées automatiques sur WordPress

Vous l’avez vu, ce site s’est offert un coup de jeune. L’occasion pour moi de tester les nouveautés de WordPress 5. Globalement l’interface est la même qu’avant, mis à part l’éditeur d’articles et pages qui s’est largement simplifié, et qui permet de facilement organiser le contenu.

Afin de faciliter la maintenance et mise à jour du site, je cherche à disposer de pages générées automatiquement à partir de contenus structurés. J’avais en tête un mécanisme qui lirait un fichier type JSON pour en générer une page propre et bien présentée. Et comme WordPress est bien pensé, ce mécanisme existe : les « Custom Content Types » (types de contenus personnalisés). L’idée est de gérer ses propres données à la manière des articles : une page de saisie standardisée, et un affichage automatique. Impeccable, comment ça marche ? C’est là que ça se complique !

Ces types personnalisés peuvent être gérés de deux façons : soit via du code (en modifiant les modèles PHP des pages), soit à l’aide d’extensions. Rappelez-vous, l’idée est d’avoir une maintenance simple, j’opte donc pour les extensions. Plusieurs extensions existent, certaines gratuites, d’autres payantes. Après quelques tests, c’est l’extension « Pods » qui a retenu mon attention. Elle permet de créer simplement des formulaires dans l’interface d’administration pour saisir ses données personnalisées.

Créer un type de données

Une fois que l’extension est installée, vous avez une nouvelle entrée dans le menu de l’interface d’administration. Vous pouvez notamment « Ajouter un nouveau pod ».

Écran d'ajout d'un pod

Ici, on va donc « Créer un nouveau », pour initier notre nouveau formulaire de saisie. Quelques informations sont ensuite demandées.

Le type de contenu est à choisir avec prudence : il conditionne la manière dont vous pourrez utiliser ce formatage par la suite. Dans la logique d’une liste structurée, on opte donc pour « Type de contenu personnalisé (semblable aux articles) ». On renseigne ensuite un nom au singulier et pluriel, qui servira pour les différents affichages.

On arrive ensuite à la configuration des champs. C’est ici que l’on définit quelles informations on souhaite pouvoir rentrer pour constituer les données.

On peut donc créer un premier champ « Intitulé », qui contiendra du texte. Veillez bien à noter la valeur du champ « Nom », qui nous servira pour la page d’affichage de ces données.

Dans cette même logique, on peut donc créer autant de champs que l’on souhaite, de types variés (adresses web, numéro de téléphone, texte riche…). Une fois vos champs créés, pensez bien à cliquer sur « Enregistrer le pod » à gauche.

Créer des données

Maintenant que le formulaire est en place, on peut commencer à constituer son jeu de données. Votre type personnalisé apparaît désormais dans le menu de gauche de l’interface d’administration de WordPress. Vous pouvez commencer à créer une nouvelle entrée.

La saisie se fait sur le mode classique d’un formulaire, c’est l’étape la plus simple mais potentiellement la plus fastidieuse. Une fois les champs remplis, cliquez sur « Publier ».

Il ne reste plus qu’à afficher ces données sous forme de liste.

Créer la page d’affichage

Pour afficher notre liste d’éléments, il faut créer soi-même une nouvelle page classique. Celle-ci peut être construite comme vous le souhaitez. Vous devez juste placer dans celle-ci un bloc de type « Code court ». C’est celui-ci qui va contenir les données à afficher. Dans ce bloc, vous allez entrer un code HTML et des codes courts qui vont permettre de structurer vos données. Un exemple :

[pods name="prix" shortcodes="1"]
 <div class="award">
   <h3>{@nom}</h3>
   <p>{@place} - {@date_evt} - {@organisateur}</p>
   <img src="{@logo}">
   <p>{@summary}</p>
 </div>
[/pods]

Quelques explications : le bloc pods va s’exécuter autant de fois que vous aurez d’objets saisis grâce à votre formulaire. Au sein de ce bloc, on peut utiliser les informations saisies dans le formulaire grâce à la syntaxe {@mavariable} (les noms que vous avez noté tout à l’heure). Le reste est du code HTML classique.

Le bloc pods doit être configuré en changeant principalement la valeur de « name », qui prendra le nom de votre pod. L’ensemble des configurations possibles est recensé sur la documentation officielle.

Il ne vous reste plus qu’à publier cette page, et voilà ! Vous avez une page qui est générée automatiquement à partir de vos saisies.

Conclusion

Vous avez découvert comment facilement créer des listes d’informations structurées sous WordPress. J’espère que ce tutoriel vous fera gagner du temps, et n’hésitez pas à me contacter si vous avez des questions.