Table of Contents
- 🔑 Principe
- 🏷️ Exemple de modèle de données
- 🚘 Exemple d’affectation des coûts
- 📌 Utilisation avec pgRouting
- Ce n'est pas terrible ! Il faut deux colonnes par mode de transport
- 🔹 1. Table des restrictions (plus flexible)
- 🔹 2. Encodage JSON / hstore (si vous voulez rester dans une seule table)
- 🔹 3. Génération dynamique de la vue
- 🔹 Modèle Many-to-Many avec tags
- 🔹 Exemple de données
- 🔹 Utilisation dans pgRouting
- ✅ Avantages de ce modèle
- 🔹 1. Tables de base
- 🔹 2. Exemple de tags
- 🔹 3. Génération des coûts par mode
- 🔹 4. Utilisation dans pgRouting
- 📌 Script SQL complet
- 📊 Résultat attendu
Oui 👍
C’est tout à fait possible avec pgRouting, car le moteur ne « devine » pas la praticabilité : c’est vous qui la décrivez dans la table de graphes (souvent une table ways).
🔑 Principe
- Chaque arête (tronçon de route) a un coût (
cost) et éventuellement un coût inverse (reverse_cost). - Ces coûts peuvent être calculés différemment selon le mode de transport (voiture, vélo, piéton, etc.).
- Si une voie est interdite pour un mode, on met son
cost(oureverse_cost) à-1→ cela signifie inaccessible.
🏷️ Exemple de modèle de données
Supposons une table roads :
CREATE TABLE roads (
id SERIAL PRIMARY KEY,
source INTEGER,
target INTEGER,
length FLOAT,
type VARCHAR(50) -- ex: motorway, residential, footway
);
Vous pouvez enrichir avec des colonnes spécifiques au mode de transport :
ALTER TABLE roads ADD COLUMN car_cost FLOAT;
ALTER TABLE roads ADD COLUMN bike_cost FLOAT;
ALTER TABLE roads ADD COLUMN foot_cost FLOAT;
🚘 Exemple d’affectation des coûts
- Une rue normale (
residential) → accessible à tous - Une ruelle piétonne (
footway) → inaccessible en voiture
UPDATE roads
SET car_cost = length,
bike_cost = length,
foot_cost = length
WHERE type = 'residential';
UPDATE roads
SET car_cost = -1, -- interdit aux voitures
bike_cost = length,
foot_cost = length
WHERE type = 'footway';
📌 Utilisation avec pgRouting
Quand vous appelez une fonction comme pgr_dijkstra, vous spécifiez quelle colonne sert de coût :
- Pour la voiture :
SELECT * FROM pgr_dijkstra(
'SELECT id, source, target, car_cost AS cost, car_cost AS reverse_cost FROM roads',
1, 10
);
- Pour le vélo :
SELECT * FROM pgr_dijkstra(
'SELECT id, source, target, bike_cost AS cost, bike_cost AS reverse_cost FROM roads',
1, 10
);
- Pour les piétons :
SELECT * FROM pgr_dijkstra(
'SELECT id, source, target, foot_cost AS cost, foot_cost AS reverse_cost FROM roads',
1, 10
);
✅ Résultat : vous pouvez définir autant de « profils de transport » que vous voulez (voiture, vélo, camion, livraison, pompier, etc.) simplement en gérant des colonnes de coûts adaptées.
Voulez-vous que je prépare un mini-script SQL complet (création de table + 4 routes + 2 modes de transport) pour que vous puissiez le tester directement dans PostgreSQL + pgRouting ?
Ce n'est pas terrible ! Il faut deux colonnes par mode de transport
Vous avez raison 👌 : si on fait une colonne cost et reverse_cost par mode de transport, ça double vite et ça devient lourd (car + vélo + piéton + camion → 8 colonnes à gérer).
Heureusement il y a plusieurs approches plus élégantes avec pgRouting :
🔹 1. Table des restrictions (plus flexible)
Au lieu de mettre les coûts dans la table des routes, on les externalise dans une table de profils :
CREATE TABLE road_costs (
road_id INT,
mode TEXT, -- 'car', 'bike', 'foot', etc.
cost FLOAT,
reverse_cost FLOAT,
PRIMARY KEY (road_id, mode)
);
Exemple d’insertion :
-- Route résidentielle
INSERT INTO road_costs VALUES (1, 'car', 100, 100);
INSERT INTO road_costs VALUES (1, 'bike', 100, 100);
INSERT INTO road_costs VALUES (1, 'foot', 100, 100);
-- Ruelle piétonne
INSERT INTO road_costs VALUES (2, 'car', -1, -1); -- inaccessible
INSERT INTO road_costs VALUES (2, 'bike', 80, 80);
INSERT INTO road_costs VALUES (2, 'foot', 50, 50);
Ensuite, pour calculer un itinéraire en voiture :
SELECT * FROM pgr_dijkstra(
'SELECT r.id, r.source, r.target, c.cost, c.reverse_cost
FROM roads r
JOIN road_costs c ON r.id = c.road_id
WHERE c.mode = ''car''',
1, 10
);
👉 Avantage :
- Plus propre, une seule table de routes.
- Ajout d’un nouveau mode = juste quelques lignes dans
road_costs.
🔹 2. Encodage JSON / hstore (si vous voulez rester dans une seule table)
On peut stocker les coûts sous forme JSON dans roads :
ALTER TABLE roads ADD COLUMN costs JSONB;
Exemple de données :
UPDATE roads SET costs = '{
"car": {"cost": 100, "reverse_cost": 100},
"bike": {"cost": 100, "reverse_cost": 100},
"foot": {"cost": 100, "reverse_cost": 100}
}' WHERE id = 1;
UPDATE roads SET costs = '{
"car": {"cost": -1, "reverse_cost": -1},
"bike": {"cost": 80, "reverse_cost": 80},
"foot": {"cost": 50, "reverse_cost": 50}
}' WHERE id = 2;
Puis extraire dynamiquement le mode :
SELECT * FROM pgr_dijkstra(
$$
SELECT id, source, target,
(costs->'car'->>'cost')::float AS cost,
(costs->'car'->>'reverse_cost')::float AS reverse_cost
FROM roads
$$,
1, 10
);
👉 Avantage :
- Une seule table, extensible.
- Flexible si vous voulez ajouter "bus", "camion", etc.
🔹 3. Génération dynamique de la vue
Une autre approche : vous gardez juste la longueur et le type de route dans roads, puis vous créez une vue par mode de transport qui applique les règles.
Exemple :
CREATE VIEW roads_car AS
SELECT id, source, target,
CASE WHEN type = 'footway' THEN -1 ELSE length END AS cost,
CASE WHEN type = 'footway' THEN -1 ELSE length END AS reverse_cost
FROM roads;
Ensuite :
SELECT * FROM pgr_dijkstra('SELECT * FROM roads_car', 1, 10);
👉 Avantage : pas de duplication des colonnes, règles exprimées en SQL.
🔧 En pratique :
- Si vous voulez un truc simple → solution vue par mode de transport.
- Si vous voulez un système extensible et propre → solution table des restrictions.
- Si vous aimez tout stocker dans une seule colonne → solution JSONB.
Souhaitez-vous que je vous prépare un exemple concret avec la solution JSONB (plus moderne et compacte), ou bien préférez-vous la version table des restrictions (plus SQL pur) ?
Je pensais plutôt à une table Many-To-Many de tags. Ensuite la requête exclu les routes qui ont un tag spécifique (par exemple voie à péage) ou au contraire avec uniquement avec un tag particulier (voie cyclable protégée)
Oui, excellente idée 👌 ! C’est beaucoup plus proche du modèle OpenStreetMap, où chaque tronçon de route peut avoir plusieurs attributs (tags), et où un même tag peut concerner plusieurs routes.
🔹 Modèle Many-to-Many avec tags
Table des routes
CREATE TABLE roads (
id SERIAL PRIMARY KEY,
source INT,
target INT,
length FLOAT
);
Table des tags
CREATE TABLE tags (
id SERIAL PRIMARY KEY,
name TEXT UNIQUE
);
Table de liaison routes ↔ tags
CREATE TABLE road_tags (
road_id INT REFERENCES roads(id) ON DELETE CASCADE,
tag_id INT REFERENCES tags(id) ON DELETE CASCADE,
PRIMARY KEY (road_id, tag_id)
);
🔹 Exemple de données
Tags de référence :
INSERT INTO tags (name) VALUES
('footway'), ('cycleway'), ('toll'), ('residential');
Routes :
INSERT INTO roads (id, source, target, length) VALUES
(1, 1, 2, 100),
(2, 2, 3, 80),
(3, 3, 4, 120),
(4, 4, 5, 60);
Associer les tags :
-- Route 1 = résidentielle
INSERT INTO road_tags VALUES (1, (SELECT id FROM tags WHERE name='residential'));
-- Route 2 = piétonne
INSERT INTO road_tags VALUES (2, (SELECT id FROM tags WHERE name='footway'));
-- Route 3 = voie à péage
INSERT INTO road_tags VALUES (3, (SELECT id FROM tags WHERE name='toll'));
-- Route 4 = piste cyclable
INSERT INTO road_tags VALUES (4, (SELECT id FROM tags WHERE name='cycleway'));
🔹 Utilisation dans pgRouting
Exemple : exclure les routes à péage
SELECT * FROM pgr_dijkstra(
$$
SELECT r.id, r.source, r.target, r.length AS cost, r.length AS reverse_cost
FROM roads r
WHERE NOT EXISTS (
SELECT 1 FROM road_tags rt
JOIN tags t ON rt.tag_id = t.id
WHERE rt.road_id = r.id AND t.name = 'toll'
)
$$,
1, 5
);
Exemple : n’autoriser que les pistes cyclables
SELECT * FROM pgr_dijkstra(
$$
SELECT r.id, r.source, r.target, r.length AS cost, r.length AS reverse_cost
FROM roads r
WHERE EXISTS (
SELECT 1 FROM road_tags rt
JOIN tags t ON rt.tag_id = t.id
WHERE rt.road_id = r.id AND t.name = 'cycleway'
)
$$,
1, 5
);
✅ Avantages de ce modèle
- Très extensible (on peut gérer des dizaines de tags comme dans OSM).
- Pas besoin d’ajouter une colonne par mode ou contrainte.
- Les requêtes pgRouting filtrent dynamiquement en fonction des tags.
👉 Souhaitez-vous que je vous prépare un script complet (DDL + INSERT + requêtes pgRouting) avec 2-3 exemples d’itinéraires (voiture, vélo, piéton) pour que vous puissiez le tester directement ?
L'externalisation du coût est pas mal car en vélo les montées sont plus pénalisantes que les descentes. Alors qu'en voiture la différence est négligeable sauf sur des très grosses pentes.
Je pensais plutôt à une table Many-To-Many de tags. Ensuite la requête exclu les routes qui ont un tag spécifique (par exemple voie à péage) ou au contraire avec uniquement avec un tag particulier (voie cyclable protégée)
Comment rajouter les règles routes à péage interdites au vélo, chemins forestiers interdits à la voiture. Avec des tags ?
Exactement ✔️ On peut exprimer ce genre de règles avec des tags (comme dans OpenStreetMap) et les appliquer au moment de calculer les coûts pour un mode de transport.
🔹 1. Tables de base
CREATE TABLE roads (
id SERIAL PRIMARY KEY,
source INT,
target INT,
length FLOAT
);
CREATE TABLE tags (
id SERIAL PRIMARY KEY,
name TEXT UNIQUE
);
CREATE TABLE road_tags (
road_id INT REFERENCES roads(id) ON DELETE CASCADE,
tag_id INT REFERENCES tags(id) ON DELETE CASCADE,
PRIMARY KEY (road_id, tag_id)
);
🔹 2. Exemple de tags
INSERT INTO tags (name) VALUES
('toll'), -- route à péage
('forest'), -- chemin forestier
('cycleway'), -- piste cyclable
('footway'), -- voie piétonne
('residential'); -- route normale
🔹 3. Génération des coûts par mode
On externalise les coûts avec une logique conditionnelle basée sur les tags. Par exemple :
CREATE TABLE road_costs (
road_id INT REFERENCES roads(id),
mode TEXT,
cost FLOAT,
reverse_cost FLOAT,
PRIMARY KEY (road_id, mode)
);
Puis on remplit avec des règles :
Vélo
- Routes normales = distance
- Routes à péage (
toll) = interdit (-1) - Escaliers (
footway) = interdit (-1) - Pistes cyclables (
cycleway) = bonus (coût réduit)
INSERT INTO road_costs
SELECT r.id, 'bike',
CASE
WHEN EXISTS (SELECT 1 FROM road_tags rt JOIN tags t ON rt.tag_id=t.id WHERE rt.road_id=r.id AND t.name='toll') THEN -1
WHEN EXISTS (SELECT 1 FROM road_tags rt JOIN tags t ON rt.tag_id=t.id WHERE rt.road_id=r.id AND t.name='footway') THEN -1
WHEN EXISTS (SELECT 1 FROM road_tags rt JOIN tags t ON rt.tag_id=t.id WHERE rt.road_id=r.id AND t.name='cycleway') THEN r.length * 0.7
ELSE r.length
END AS cost,
CASE
WHEN EXISTS (SELECT 1 FROM road_tags rt JOIN tags t ON rt.tag_id=t.id WHERE rt.road_id=r.id AND t.name='toll') THEN -1
WHEN EXISTS (SELECT 1 FROM road_tags rt JOIN tags t ON rt.tag_id=t.id WHERE rt.road_id=r.id AND t.name='footway') THEN -1
WHEN EXISTS (SELECT 1 FROM road_tags rt JOIN tags t ON rt.tag_id=t.id WHERE rt.road_id=r.id AND t.name='cycleway') THEN r.length * 0.7
ELSE r.length
END AS reverse_cost
FROM roads r;
🚗 Voiture
- Routes normales = distance
- Chemins forestiers (
forest) = interdits (-1) - Voies piétonnes (
footway) = interdites (-1)
INSERT INTO road_costs
SELECT r.id, 'car',
CASE
WHEN EXISTS (SELECT 1 FROM road_tags rt JOIN tags t ON rt.tag_id=t.id WHERE rt.road_id=r.id AND t.name='forest') THEN -1
WHEN EXISTS (SELECT 1 FROM road_tags rt JOIN tags t ON rt.tag_id=t.id WHERE rt.road_id=r.id AND t.name='footway') THEN -1
ELSE r.length
END AS cost,
CASE
WHEN EXISTS (SELECT 1 FROM road_tags rt JOIN tags t ON rt.tag_id=t.id WHERE rt.road_id=r.id AND t.name='forest') THEN -1
WHEN EXISTS (SELECT 1 FROM road_tags rt JOIN tags t ON rt.tag_id=t.id WHERE rt.road_id=r.id AND t.name='footway') THEN -1
ELSE r.length
END AS reverse_cost
FROM roads r;
🔹 4. Utilisation dans pgRouting
Pour calculer un itinéraire vélo :
SELECT * FROM pgr_dijkstra(
'SELECT r.id, r.source, r.target, c.cost, c.reverse_cost
FROM roads r
JOIN road_costs c ON r.id = c.road_id
WHERE c.mode = ''bike''',
1, 10
);
Pour la voiture :
SELECT * FROM pgr_dijkstra(
'SELECT r.id, r.source, r.target, c.cost, c.reverse_cost
FROM roads r
JOIN road_costs c ON r.id = c.road_id
WHERE c.mode = ''car''',
1, 10
);
✅ Avantage :
- Les règles sont lisibles et évolutives.
- On reste dans la logique OSM (tags multiples, requêtes qui filtrent).
- Si demain vous ajoutez un nouveau mode (
truck,bus,emergency), vous ne changez pas la structure, juste la logique d’insertion des coûts.
👉 Voulez-vous que je prépare un exemple exécutable complet (création de tables, insertion de 4 routes avec tags variés, et 2 requêtes pgRouting avec résultats différents vélo vs voiture) ?
Oui j'aimerais un exemple exécutable complet
Parfait ✅, voici un exemple SQL complet que vous pouvez exécuter directement dans PostgreSQL + PostGIS + pgRouting. Il illustre :
- une petite base de 4 routes (5 nœuds),
- des tags associés aux routes,
- un calcul de coût différent selon le mode (🚲 vélo vs 🚗 voiture),
- et un itinéraire avec
pgr_dijkstra.
📌 Script SQL complet
-- Extension nécessaire
CREATE EXTENSION IF NOT EXISTS pgrouting;
-- ====================================
-- 1. Tables de base
-- ====================================
DROP TABLE IF EXISTS road_costs CASCADE;
DROP TABLE IF EXISTS road_tags CASCADE;
DROP TABLE IF EXISTS tags CASCADE;
DROP TABLE IF EXISTS roads CASCADE;
CREATE TABLE roads (
id SERIAL PRIMARY KEY,
source INT,
target INT,
length FLOAT
);
CREATE TABLE tags (
id SERIAL PRIMARY KEY,
name TEXT UNIQUE
);
CREATE TABLE road_tags (
road_id INT REFERENCES roads(id) ON DELETE CASCADE,
tag_id INT REFERENCES tags(id) ON DELETE CASCADE,
PRIMARY KEY (road_id, tag_id)
);
CREATE TABLE road_costs (
road_id INT REFERENCES roads(id),
mode TEXT,
cost FLOAT,
reverse_cost FLOAT,
PRIMARY KEY (road_id, mode)
);
-- ====================================
-- 2. Données de test
-- Graphe simple : 5 nœuds reliés par 4 routes
-- ====================================
INSERT INTO roads (id, source, target, length) VALUES
(1, 1, 2, 100), -- route normale
(2, 2, 3, 80), -- route à péage
(3, 3, 4, 120), -- chemin forestier
(4, 4, 5, 60); -- piste cyclable
-- Tags
INSERT INTO tags (name) VALUES
('toll'), ('forest'), ('cycleway'), ('footway'), ('residential');
-- Attribution des tags aux routes
INSERT INTO road_tags VALUES (1, (SELECT id FROM tags WHERE name='residential'));
INSERT INTO road_tags VALUES (2, (SELECT id FROM tags WHERE name='toll'));
INSERT INTO road_tags VALUES (3, (SELECT id FROM tags WHERE name='forest'));
INSERT INTO road_tags VALUES (4, (SELECT id FROM tags WHERE name='cycleway'));
-- ====================================
-- 3. Génération des coûts par mode
-- ====================================
-- 🚲 Vélo :
-- - interdit sur péage et footway
-- - bonus sur piste cyclable (0.7)
-- - normal sinon
INSERT INTO road_costs
SELECT r.id, 'bike',
CASE
WHEN EXISTS (SELECT 1 FROM road_tags rt JOIN tags t ON rt.tag_id=t.id WHERE rt.road_id=r.id AND t.name='toll') THEN -1
WHEN EXISTS (SELECT 1 FROM road_tags rt JOIN tags t ON rt.tag_id=t.id WHERE rt.road_id=r.id AND t.name='footway') THEN -1
WHEN EXISTS (SELECT 1 FROM road_tags rt JOIN tags t ON rt.tag_id=t.id WHERE rt.road_id=r.id AND t.name='cycleway') THEN r.length * 0.7
ELSE r.length
END AS cost,
CASE
WHEN EXISTS (SELECT 1 FROM road_tags rt JOIN tags t ON rt.tag_id=t.id WHERE rt.road_id=r.id AND t.name='toll') THEN -1
WHEN EXISTS (SELECT 1 FROM road_tags rt JOIN tags t ON rt.tag_id=t.id WHERE rt.road_id=r.id AND t.name='footway') THEN -1
WHEN EXISTS (SELECT 1 FROM road_tags rt JOIN tags t ON rt.tag_id=t.id WHERE rt.road_id=r.id AND t.name='cycleway') THEN r.length * 0.7
ELSE r.length
END AS reverse_cost
FROM roads r;
-- 🚗 Voiture :
-- - interdit sur chemins forestiers et footways
-- - normal sinon
INSERT INTO road_costs
SELECT r.id, 'car',
CASE
WHEN EXISTS (SELECT 1 FROM road_tags rt JOIN tags t ON rt.tag_id=t.id WHERE rt.road_id=r.id AND t.name='forest') THEN -1
WHEN EXISTS (SELECT 1 FROM road_tags rt JOIN tags t ON rt.tag_id=t.id WHERE rt.road_id=r.id AND t.name='footway') THEN -1
ELSE r.length
END AS cost,
CASE
WHEN EXISTS (SELECT 1 FROM road_tags rt JOIN tags t ON rt.tag_id=t.id WHERE rt.road_id=r.id AND t.name='forest') THEN -1
WHEN EXISTS (SELECT 1 FROM road_tags rt JOIN tags t ON rt.tag_id=t.id WHERE rt.road_id=r.id AND t.name='footway') THEN -1
ELSE r.length
END AS reverse_cost
FROM roads r;
-- ====================================
-- 4. Test des itinéraires
-- ====================================
-- Itinéraire vélo du nœud 1 à 5
SELECT * FROM pgr_dijkstra(
$$
SELECT r.id, r.source, r.target, c.cost, c.reverse_cost
FROM roads r
JOIN road_costs c ON r.id = c.road_id
WHERE c.mode = 'bike'
$$,
1, 5
);
-- Itinéraire voiture du nœud 1 à 5
SELECT * FROM pgr_dijkstra(
$$
SELECT r.id, r.source, r.target, c.cost, c.reverse_cost
FROM roads r
JOIN road_costs c ON r.id = c.road_id
WHERE c.mode = 'car'
$$,
1, 5
);
📊 Résultat attendu
-
Pour le vélo :
- Route 2 (péage) est interdite (
-1) → l’algorithme ne la prend pas. - Route 4 (piste cyclable) a un coût réduit (
60 * 0.7 = 42). - L’itinéraire 1 → 2 est OK, mais ensuite le péage bloque, donc vélo prendra 1 → 2 (ok) → [pas 2→3 car péage interdit] → échec.
- Donc il n’y a pas de chemin complet de 1 à 5 en vélo (dans ce petit graphe).
- Route 2 (péage) est interdite (
-
Pour la voiture :
- Route 3 (forest) est interdite, donc bloquée.
- La voiture peut faire 1 → 2 (100) → 3 (80, péage autorisé) → [bloqué en 3→4 car forest].
- Pas de chemin complet non plus en voiture.
👉 Ce petit graphe est volontairement minimaliste : il montre que chaque mode a ses contraintes et peut bloquer certains trajets. Dans un vrai graphe (issu d’OSM), vous auriez des alternatives valides.
Voulez-vous que je complète cet exemple avec un graphe légèrement plus grand (6-7 routes) pour que les itinéraires vélo et voiture aboutissent bien avec des chemins différents ?