Accueil / Blog / Métier / 2018 / Ré-ordonner des lignes dans PostGIS avec une requête récursive

Ré-ordonner des lignes dans PostGIS avec une requête récursive

Par Frédéric Bonifas — publié 09/02/2018
Comment ordonner des lignes les unes à la suite des autres dans PostGIS

Dans un réseau routier existant, provenant d'OpenStreetMap par exemple, les différentes lignes représentant un itinéraire ne sont pas ordonnées les unes à la suite des autres. Voici par exemple les différentes lignes composant la RN 20, colorées par osm_id, l'identifiant unique OpenStreetMap.

/blog/metier/2018/lignes-postgis-non-triees/image

Pour obtenir une liste de ces différentes lignes classées les unes après les autres, on peut utiliser une requête récursive PostgreSQL :

WITH RECURSIVE t(id,g,explored_paths) AS (
    SELECT osm_id, way, ARRAY[osm_id]
    FROM planet_osm_line
    WHERE osm_id=93378070
  UNION ALL
    SELECT osm_id, way, explored_paths || osm_id
    FROM planet_osm_line, t
    WHERE ST_Intersects(way, t.g)
    AND NOT (osm_id = ANY(explored_paths))
    AND ref='N 20'
)
SELECT *, row_number() over () FROM t LIMIT 30;

La première partie de la requête récursive consiste à l'initialiser avec la première ligne, ici l'objet ayant osm_id=93378070. Une union de ces données initiales est réalisée récursivement avec la ligne qui touche les lignes précédemment sélectionnées, mais qui ne fait pas encore partie des lignes sélectionnées (explored_paths). Cette requête s'arrête lorsque plus aucune ligne touchant les lignes précédentes n'est trouvée.

Voici un extrait des résultats de cette requête :

id explored_paths row_number
93378070 "{93378070}" 1
93378068 "{93378070,93378068}" 2
93378071 "{93378070,93378068,93378071}" 3
93378069 "{93378070,93378068,93378071,93378069}" 4
127721449 "{93378070,93378068,93378071,93378069,127721449}" 5

Les lignes peuvent maintenant être affichées en les classant par row_number, c'est à dire par leur ordre de sortie de la requête récursive.

/blog/metier/2018/lignes-postgis-triees/image
ABONNEZ-VOUS À LA NEWSLETTER !
Voir aussi
Paralléliser des requêtes avec PostgreSQL 31/03/2020

PostgreSQL permet de découper les requêtes pour en exécuter des parties en parallèle. Il faut ...

Une Cagnotte reversée à la fondation OpenStreetMap a été organisée par Makina Corpus lors du Paris Open Source Summit ! Une Cagnotte reversée à la fondation OpenStreetMap a été organisée par Makina Corpus lors du Paris Open Source Summit ! 28/01/2020

Les 10 et 11 décembre derniers, Makina Corpus était présente à la 5ème édition du Paris Open ...

SIG : Préparation de données pour la création de tuiles vectorielles 30/01/2020

Pour servir des données sous forme des tuiles vectorielles une préparation est nécessaire comme ...

Recherche et développement d’indicateurs pour l’aménagement d’un territoire Recherche et développement d’indicateurs pour l’aménagement d’un territoire 14/01/2020

Nous explorons l’intérêt de calculer automatiquement des indicateurs d’aménagement du ...

Améliorez votre SQL : utilisez des invariants dans les conditions Améliorez votre SQL : utilisez des invariants dans les conditions 05/11/2019

Il suffit parfois de repenser la façon d'exprimer une condition de filtrage dans une requête SQL ...