tunnels.sql

SELECT ST_AsBinary("geometry") AS geom,"bridge","render","stylegroup","tunnel","type" FROM ( SELECT geometry, type, 0 AS bridge, access, render, layer, 1 as tunnel,
          CASE
            WHEN type IN ('motorway', 'trunk') THEN 'motorway'
            WHEN type IN ('primary', 'secondary') THEN 'mainroad'
            WHEN type IN ('motorway_link', 'trunk_link', 'primary_link', 'secondary_link', 'tertiary', 'tertiary_link', 'residential', 'unclassified', 'road', 'living_street') THEN 'minorroad'
            WHEN type IN ('service', 'track') THEN 'service'
            WHEN type IN ('path', 'cycleway', 'footway', 'pedestrian', 'steps', 'bridleway') THEN 'noauto'
            WHEN type IN ('light_rail', 'subway', 'narrow_gauge', 'rail', 'tram') THEN 'railway'
            ELSE 'other' END AS stylegroup
          FROM (
            SELECT geometry, type, access, layer, '1_outline' AS render FROM osm_roads
            WHERE tunnel NOT IN (0)
            UNION ALL
            SELECT geometry, type, access, layer, '2_line' AS render FROM osm_roads
            WHERE tunnel NOT IN (0)
            UNION ALL
            SELECT geometry, type, access, layer, '3_inline' AS render FROM osm_roads
            WHERE tunnel NOT IN (0)
          ) AS tunnels
          ORDER BY layer ASC , render ASC
        ) AS data WHERE "geometry" && ST_SetSRID('BOX3D(459845.1621638667 6721566.519288868,635956.0753330085 6897677.432458006)'::box3d, 3857);SELECT ST_AsBinary("geometry") AS geom,"bridge","render","stylegroup","tunnel","type" FROM ( SELECT geometry, type, 0 AS bridge, access, render, layer, 1 as tunnel,
          CASE
            WHEN type IN ('motorway', 'trunk') THEN 'motorway'
            WHEN type IN ('primary', 'secondary') THEN 'mainroad'
            WHEN type IN ('motorway_link', 'trunk_link', 'primary_link', 'secondary_link', 'tertiary', 'tertiary_link', 'residential', 'unclassified', 'road', 'living_street') THEN 'minorroad'
            WHEN type IN ('service', 'track') THEN 'service'
            WHEN type IN ('path', 'cycleway', 'footway', 'pedestrian', 'steps', 'bridleway') THEN 'noauto'
            WHEN type IN ('light_rail', 'subway', 'narrow_gauge', 'rail', 'tram') THEN 'railway'
            ELSE 'other' END AS stylegroup
          FROM (
            SELECT geometry, type, access, layer, '1_outline' AS render FROM osm_roads
            WHERE tunnel NOT IN (0)
            UNION ALL
            SELECT geometry, type, access, layer, '2_line' AS render FROM osm_roads
            WHERE tunnel NOT IN (0)
            UNION ALL
            SELECT geometry, type, access, layer, '3_inline' AS render FROM osm_roads
            WHERE tunnel NOT IN (0)
          ) AS tunnels
          ORDER BY layer ASC , render ASC
        ) AS data WHERE "geometry" && ST_SetSRID('BOX3D(459845.1621638667 6721566.519288868,635956.0753330085 6897677.432458006)'::box3d, 3857);