Calcul du nombre de km de chaque type de voie par commune

km_voie_commune.sql

WITH insee AS
  (SELECT tags->'ref:INSEE' AS ref_insee,
          way
   FROM planet_osm_polygon
   WHERE tags ? 'ref:INSEE'
     AND tags->'ref:INSEE' < '97100' /* DOM à traiter à part pour la projection */
     AND admin_level IN ('8',
                         '9')
     AND boundary='administrative')
SELECT insee.ref_insee,
       v.highway,
       sum((CASE WHEN oneway IN ('yes','-1') THEN 0.5 ELSE 1.0 END) * /* traitement des sens unique avec coeff de 0.5 */  st_length(st_transform(ST_Intersection(v.way,insee.way),2154)))/1000 AS km  /* projection Lambert 93 */
FROM insee
JOIN planet_osm_line v ON (st_intersects(v.way,insee.way)
                           AND v.highway IS NOT NULL
                           AND highway IN ('motorway',
                                           'motorway_link',
                                           'trunk',
                                           'trunk_link',
                                           'primary',
                                           'primary_link',
                                           'secondary',
                                           'secondary_link',
                                           'tertiary',
                                           'tertiary_link',
                                           'unclassified',
                                           'residential',
                                           'service',
                                           'living_street'))
GROUP BY ref_insee,
         highway
ORDER BY ref_insee,
         highway;