Calcul km de type de voie et limite de vitesse par commune

commune_type_vitesse.sql

\copy 
(SELECT *
FROM (WITH insee AS
        (SELECT tags->'ref:INSEE' AS ref_insee,
                way
         FROM planet_osm_polygon
         WHERE tags ? 'ref:INSEE'
           AND admin_level IN ('8',
                               '9')
           AND boundary='administrative')
      SELECT insee.ref_insee,
             v.highway,
             replace(v.tags->'maxspeed','FR:urban','50') AS maxspeed,
             sum((CASE WHEN oneway IN ('yes','-1') THEN 0.5 ELSE 1.0 END) * st_length(st_transform(ST_Intersection(v.way,insee.way),2154)))/1000 AS km
      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,
               replace(v.tags->'maxspeed','FR:urban','50')
      ORDER BY ref_insee,
               v.highway,
               maxspeed) AS d
WHERE km > 0.1)
 to '/home/cquest/public_html/commune_voie_vitesse_km.csv' csv ;