So, lots of queries taking 6,10, 12+ seconds … Here’s an example of 1
SELECT ST_AsBinary("way") AS geom,"access","feature","link" FROM (SELECT
way,
(CASE WHEN feature IN ('highway_motorway_link', 'highway_trunk_link', 'highway_primary_link', 'highway_secondary_link', 'highway_tertiary_link') THEN substr(feature, 0, length(feature)-4) ELSE feature END) AS feature,
horse,
foot,
bicycle,
tracktype,
int_surface,
access,
construction,
service,
link,
layernotnull
FROM ( -- subselect that contains both roads and rail/aero
SELECT
way,
('highway_' || highway) AS feature, --only motorway to tertiary links are accepted later on
horse,
foot,
bicycle,
tracktype,
CASE WHEN surface IN ('unpaved', 'compacted', 'dirt', 'earth', 'fine_gravel', 'grass', 'grass_paver', 'gravel', 'ground',
'mud', 'pebblestone', 'salt', 'sand', 'woodchips', 'clay', 'ice', 'snow') THEN 'unpaved'
WHEN surface IN ('paved', 'asphalt', 'cobblestone', 'cobblestone:flattened', 'sett', 'concrete', 'concrete:lanes',
'concrete:plates', 'paving_stones', 'metal', 'wood', 'unhewn_cobblestone') THEN 'paved'
END AS int_surface,
CASE WHEN access IN ('destination') THEN 'destination'::text
WHEN access IN ('no', 'private') THEN 'no'::text
END AS access,
construction,
CASE
WHEN service IN ('parking_aisle', 'drive-through', 'driveway') OR leisure IN ('slipway') THEN 'INT-minor'::text
ELSE 'INT-normal'::text
END AS service,
CASE
WHEN highway IN ('motorway_link', 'trunk_link', 'primary_link', 'secondary_link', 'tertiary_link') THEN 'yes'
ELSE 'no'
END AS link,
COALESCE(layer,0) AS layernotnull,
osm_id,
z_order
FROM planet_osm_line
WHERE (tunnel IS NULL OR NOT tunnel IN ('yes', 'building_passage'))
AND (covered IS NULL OR NOT covered = 'yes')
AND (bridge IS NULL OR NOT bridge IN ('yes', 'boardwalk', 'cantilever', 'covered', 'low_water_crossing', 'movable', 'trestle', 'viaduct'))
AND highway IS NOT NULL -- end of road select
UNION ALL
SELECT
way,
('railway_' || (CASE WHEN railway = 'preserved' AND service IN ('spur', 'siding', 'yard') THEN 'INT-preserved-ssy'::text
WHEN (railway = 'rail' AND service IN ('spur', 'siding', 'yard')) THEN 'INT-spur-siding-yard'
WHEN (railway = 'tram' AND service IN ('spur', 'siding', 'yard')) THEN 'tram-service'
ELSE railway END)) AS feature,
horse,
foot,
bicycle,
tracktype,
'null',
CASE
WHEN access IN ('destination') THEN 'destination'::text
WHEN access IN ('no', 'private') THEN 'no'::text
END AS access,
construction,
CASE WHEN service IN ('parking_aisle', 'drive-through', 'driveway') OR leisure IN ('slipway') THEN 'INT-minor'::text ELSE 'INT-normal'::text END AS service,
'no' AS link,
COALESCE(layer,0) AS layernotnull,
osm_id,
z_order
FROM planet_osm_line
WHERE (tunnel IS NULL OR NOT tunnel IN ('yes', 'building_passage'))
AND (covered IS NULL OR NOT covered = 'yes')
AND (bridge IS NULL OR NOT bridge IN ('yes', 'boardwalk', 'cantilever', 'covered', 'low_water_crossing', 'movable', 'trestle', 'viaduct'))
AND railway IS NOT NULL -- end of rail select
) AS features
ORDER BY
layernotnull,
z_order,
CASE WHEN substring(feature for 8) = 'railway_' THEN 2 ELSE 1 END,
CASE WHEN feature IN ('railway_INT-preserved-ssy', 'railway_INT-spur-siding-yard', 'railway_tram-service') THEN 0 ELSE 1 END,
CASE WHEN access IN ('no', 'private') THEN 0 WHEN access IN ('destination') THEN 1 ELSE 2 END,
CASE WHEN int_surface IN ('unpaved') THEN 0 ELSE 2 END,
osm_id
) AS roads_sql WHERE "way" && ST_SetSRID('BOX3D(-9245822.941379882 4999593.146079492,-9069712.028210744 5175704.059248632)'::box3d, 3857)
Explain:
- Subquery Scan (rows=209441 loops=1) 209441 1
- Sort (rows=209441 loops=1) 209441 1
- Gather (rows=209441 loops=1) 209441 1
- Result (rows=69814 loops=3) 69814 3
- Append (rows=69814 loops=3) 69814 3
- Subquery Scan (rows=204841 loops=1) 204841 1
- Index Scan using planet_osm_line_way_idx on planet_osm_line as planet_osm_line (rows=204841 loops=1)
Filter: ((highway IS NOT NULL) AND ((tunnel IS NULL) OR (tunnel <> ALL (‘{yes,building_passage}’::text[]))) AND ((covered IS NULL) OR (covered <> ‘yes’::text)) AND ((bridge IS NULL) OR (bridge <> ALL (‘{yes,boardwalk,cantilever,covered,low_water_crossing,movable,trestle,viaduct}’::text[]))))
Index Cond: (way && ‘01030000A0110F00000100000005000000B4C81FDE8FA261C1CC5D59496A1253410000000000000000B4C81FDE8FA261C1C6BACA0366BE53410000000000000000381AE700924C61C1C6BACA0366BE53410000000000000000381AE700924C61C1CC5D59496A1253410000000000000000B4C81FDE8FA261C1CC5D59496A1253410000000000000000’::geometry)
Rows Removed by Filter: 28373
204841 1
- Subquery Scan (rows=4600 loops=1) 4600 1
- Index Scan using planet_osm_line_way_idx on planet_osm_line as planet_osm_line_1 (rows=4600 loops=1)
Filter: ((railway IS NOT NULL) AND ((tunnel IS NULL) OR (tunnel <> ALL (‘{yes,building_passage}’::text[]))) AND ((covered IS NULL) OR (covered <> ‘yes’::text)) AND ((bridge IS NULL) OR (bridge <> ALL (‘{yes,boardwalk,cantilever,covered,low_water_crossing,movable,trestle,viaduct}’::text[]))))
Index Cond: (way && ‘01030000A0110F00000100000005000000B4C81FDE8FA261C1CC5D59496A1253410000000000000000B4C81FDE8FA261C1C6BACA0366BE53410000000000000000381AE700924C61C1C6BACA0366BE53410000000000000000381AE700924C61C1CC5D59496A1253410000000000000000B4C81FDE8FA261C1CC5D59496A1253410000000000000000’::geometry)
Rows Removed by Filter: 228614