02 - Première trame - les filaires

Renvoi à cette fiche d'exemple - cas de Toulon - en mars 2026
Vérification de la présence des tags maxspped dans la version OSM Geofabrick
sudo docker exec -it alteris_postgis psql -U alteris_admin -d alteris_geo -c "SELECT count(*) FROM osm_raw.roads WHERE tags ? 'maxspeed';"
-------
 91439
(1 row)
A/ Creer les filaires
c13028_laciotat.c13028_filaires 
sudo docker exec -it alteris_postgis psql -U alteris_admin -d alteris_geo -c "
-- 1. Nettoyage
DROP TABLE IF EXISTS c13028_laciotat.c13028_filaires_v3 CASCADE;
-- 2. Création de la couche avec ta logique 'Toulon'
CREATE TABLE c13028_laciotat.c13028_filaires_v3 AS
WITH extraction AS (
    SELECT 
        osm_id,
        name AS nom_voie,
        highway AS type_osm,
        tags->'lanes' AS nb_voies,
        tags->'maxspeed' AS vitesse_max,
        ST_Transform(way, 2154) AS geom_ligne
    FROM osm_raw.roads
    WHERE highway IS NOT NULL
    AND ST_Intersects(way, (SELECT ST_Union(way) FROM osm_raw.buildings WHERE name = 'La Ciotat' AND admin_level = '8'))
),
classification AS (
    SELECT 
        osm_id, nom_voie, type_osm, geom_ligne,
        CASE 
            -- Transit Lourd (T1 & T2)
            WHEN type_osm IN ('motorway', 'motorway_link') THEN 'F_T1'
            WHEN type_osm IN ('trunk', 'trunk_link') THEN 'F_T2'
            
            -- Transit Urbain (T3) : La complexité que tu voulais garder
            WHEN type_osm IN ('primary', 'secondary') 
                 AND (nb_voies IN ('3', '4', '5', '6', '7', '8') 
                      OR vitesse_max IN ('70', '80', '90', '110', '130')) 
            THEN 'F_T3'
            
            -- Desserte (D1, D2, D3)
            WHEN type_osm = 'primary' THEN 'F_D1' 
            WHEN type_osm = 'secondary' THEN 'F_D2'
            WHEN type_osm IN ('pedestrian', 'living_street') THEN 'F_D3'
            
            -- Local (L1, L2, L3)
            WHEN type_osm IN ('tertiary', 'residential') THEN 'F_L1'
            WHEN type_osm = 'unclassified' THEN 'F_L2'
            ELSE 'F_L3'
        END as code_pattern
    FROM extraction
)
SELECT 
    osm_id, 
    nom_voie, 
    code_pattern,
    ST_Buffer(geom_ligne, 
        CASE 
            WHEN code_pattern = 'F_T1' THEN 100
            WHEN code_pattern = 'F_T2' THEN 75
            WHEN code_pattern = 'F_T3' THEN 50
            WHEN code_pattern = 'F_D1' THEN 50
            WHEN code_pattern = 'F_D2' THEN 35
            WHEN code_pattern = 'F_D3' THEN 35
            WHEN code_pattern = 'F_L1' THEN 25
            ELSE 10 
        END
    )::geometry(Polygon, 2154) as geom
FROM classification;
-- 3. Indexation spatiale
CREATE INDEX idx_c13028_filaires_v3_geom ON c13028_laciotat.c13028_filaires_v3 USING GIST(geom);
"
 
sudo docker exec -it alteris_postgis psql -U alteris_admin -d alteris_geo -c "
SELECT code_pattern, count(*) 
FROM c13028_laciotat.c13028_filaires_v3 
GROUP BY code_pattern 
ORDER BY code_pattern;"
 code_pattern | count
--------------+-------
 F_D1         |    96
 F_D2         |   179
 F_D3         |    67
 F_L1         |  1129
 F_L2         |   148
 F_L3         |  3302
 F_T1         |    40
 F_T3         |    42
(8 rows)