Migrate osm data (landuse, road, poi)

migration.sql

BEGIN;

-- update functions

CREATE OR REPLACE FUNCTION mz_calculate_is_landuse(
    landuse_val text, leisure_val text, natural_val text, highway_val text, amenity_val text, aeroway_val text)
RETURNS BOOLEAN AS $$
BEGIN
    RETURN
        landuse_val IN ('park', 'forest', 'residential', 'retail', 'commercial',
                        'industrial', 'railway', 'cemetery', 'grass', 'farmyard',
                        'farm', 'farmland', 'wood', 'meadow', 'village_green',
                        'recreation_ground', 'allotments', 'quarry')
     OR leisure_val IN ('park', 'garden', 'playground', 'golf_course', 'sports_centre',
                        'pitch', 'stadium', 'common', 'nature_reserve')
     OR natural_val IN ('wood', 'land', 'scrub', 'wetland', 'glacier')
     OR highway_val IN ('pedestrian', 'footway')
     OR amenity_val IN ('university', 'school', 'college', 'library', 'fuel',
                        'parking', 'cinema', 'theatre', 'place_of_worship', 'hospital')
     OR aeroway_val IN ('runway', 'taxiway', 'apron');
END;
$$ LANGUAGE plpgsql IMMUTABLE;

CREATE OR REPLACE FUNCTION mz_calculate_road_level(highway_val text, railway_val text, aeroway_val text)
RETURNS SMALLINT AS $$
BEGIN
    RETURN (
        CASE WHEN highway_val IN ('motorway') THEN 7
             WHEN highway_val IN ('trunk', 'primary', 'secondary') THEN 10
             WHEN (highway_val IN ('tertiary')
                OR aeroway_val IN ('runway', 'taxiway')) THEN 11
             WHEN highway_val IN ('motorway_link', 'trunk_link', 'residential', 'unclassified', 'road') THEN 12
             WHEN highway_val IN ('primary_link', 'secondary_link') THEN 13
             WHEN (highway_val IN ('tertiary_link', 'minor')
                OR railway_val IN ('rail', 'subway')) THEN 14
             WHEN (highway_val IN ('service', 'footpath', 'track', 'footway', 'steps', 'pedestrian', 'path', 'cycleway')
                OR railway_val IN ('tram', 'light_rail', 'narrow_gauge', 'monorail')) THEN 15
             ELSE NULL END
    );
END;
$$ LANGUAGE plpgsql IMMUTABLE;

CREATE OR REPLACE FUNCTION mz_calculate_road_sort_key(
    layer_val text, bridge_val text, tunnel_val text, highway_val text, railway_val text, aeroway_val text)
RETURNS FLOAT AS $$
DECLARE v_layer_as_float FLOAT DEFAULT NULL;
BEGIN
    v_layer_as_float := mz_safe_convert_to_float(layer_val);
    RETURN (
        (CASE WHEN v_layer_as_float IS NOT NULL THEN 1000 * v_layer_as_float
            ELSE 0
            END)

        +

        --
        -- Bridges and tunnels have an implicit physical layering.
        --
        (CASE WHEN bridge_val IN ('yes', 'true') THEN 100
            WHEN tunnel_val IN ('yes', 'true') THEN -100
            ELSE 0
            END)

        +

        --
        -- Large roads are drawn on top of smaller roads.
        --
        (CASE WHEN highway_val IN ('motorway') THEN 0
            WHEN railway_val IN ('rail', 'tram', 'light_rail', 'narrow_guage', 'monorail') THEN -.5
            WHEN highway_val IN ('trunk') THEN -1
            WHEN highway_val IN ('primary') THEN -2
            WHEN highway_val IN ('secondary') THEN -3
            WHEN aeroway_val IN ('runway') THEN -3
            WHEN aeroway_val IN ('taxiway') THEN -3.5
            WHEN highway_val IN ('tertiary') THEN -4
            WHEN highway_val LIKE '%_link' THEN -5
            WHEN highway_val IN ('residential', 'unclassified', 'road') THEN -6
            WHEN highway_val IN ('unclassified', 'service', 'minor') THEN -7
            WHEN railway_val IN ('subway') THEN -8
            ELSE -9 END)
    );
END;
$$ LANGUAGE plpgsql IMMUTABLE;

CREATE OR REPLACE FUNCTION mz_calculate_poi_level(
    aerialway_val text,
    aeroway_val text,
    amenity_val text,
    barrier_val text,
    highway_val text,
    historic_val text,
    leisure_val text,
    lock_val text,
    man_made_val text,
    natural_val text,
    power_val text,
    railway_val text,
    shop_val text,
    tourism_val text,
    waterway_val text
)
RETURNS SMALLINT AS $$
BEGIN
    RETURN (
        CASE WHEN aeroway_val IN ('aerodrome', 'airport') THEN 9
             WHEN natural_val IN ('peak', 'volcano') THEN 11
             WHEN railway_val IN ('station') THEN 12
             WHEN (aerialway_val IN ('station')
                   OR railway_val IN ('halt', 'tram_stop')
                   OR tourism_val IN ('alpine_hut')) THEN 13
             WHEN (natural_val IN ('spring')
                   OR railway_val IN ('level_crossing')) THEN 14
             WHEN (amenity_val IN ('hospital', 'parking')
                   OR barrier_val IN ('gate')
                   OR highway_val IN ('gate', 'mini_roundabout')
                   OR lock_val IN ('yes')
                   OR man_made_val IN ('lighthouse', 'power_wind')
                   OR natural_val IN ('cave_entrance')
                   OR power_val IN ('generator')
                   OR waterway_val IN ('lock')) THEN 15
             WHEN (aeroway_val IN ('helipad')
                   OR amenity_val IN ('biergarten', 'bus_station', 'bus_stop', 'car_sharing',
                                    'picnic_site', 'place_of_worship',
                                    'prison', 'pub', 'recycling', 'shelter')
                   OR barrier_val IN ('block', 'bollard', 'lift_gate')
                   OR highway_val IN ('bus_stop', 'ford')
                   OR historic_val IN ('archaeological_site')
                   OR man_made_val IN ('windmill')
                   OR natural_val IN ('tree')
                   OR shop_val IN ('department_store', 'supermarket')
                   OR tourism_val IN ('camp_site', 'caravan_site', 'information', 'viewpoint')) THEN 16
             WHEN (aeroway_val IN ('gate')
                   OR amenity_val IN (
                 'atm', 'bank', 'bar', 'bicycle_rental',
                 'cafe', 'cinema', 'courthouse', 'drinking_water', 'embassy', 'emergency_phone',
                 'fast_food', 'fire_station', 'fuel', 'library', 'pharmacy',
                 'police', 'post_box', 'post_office', 'restaurant', 'telephone', 'theatre',
                 'toilets', 'veterinary')
                   OR highway_val IN ('traffic_signals')
                   OR historic_val IN ('memorial')
                   OR leisure_val IN ('playground', 'slipway')
                   OR man_made_val IN ('mast', 'water_tower')
                   OR shop_val IN ('bakery', 'bicycle', 'books', 'butcher', 'car', 'car_repair',
                                 'clothes', 'computer', 'convenience',
                                 'doityourself', 'dry_cleaning', 'fashion', 'florist', 'gift',
                                 'greengrocer', 'hairdresser', 'jewelry', 'mobile_phone',
                                 'optician', 'pet')
                   OR tourism_val IN ('bed_and_breakfast', 'chalet', 'guest_house',
                                    'hostel', 'hotel', 'motel', 'museum')) THEN 17
             WHEN (amenity_val IN ('bench', 'waste_basket')
                   OR railway_val IN ('subway_entrance')) THEN 18
             ELSE NULL END
    );
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- update trigger functions

CREATE OR REPLACE FUNCTION mz_trigger_function_road()
RETURNS TRIGGER AS $$
BEGIN
    NEW.mz_road_level := mz_calculate_road_level(NEW.highway, NEW.railway, NEW.aeroway);
    IF NEW.mz_road_level IS NOT NULL THEN
        NEW.mz_road_sort_key := mz_calculate_road_sort_key(
            NEW.layer,
            NEW.bridge,
            NEW.tunnel,
            NEW.highway,
            NEW.railway,
            NEW.aeroway
        );
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql VOLATILE;

CREATE OR REPLACE FUNCTION mz_trigger_function_is_landuse()
RETURNS TRIGGER AS $$
BEGIN
    IF mz_calculate_is_landuse(
        NEW."landuse", NEW."leisure", NEW."natural", NEW."highway", NEW."amenity", NEW."aeroway") THEN
        NEW.mz_is_landuse := TRUE;
    ELSE
        NEW.mz_is_landuse := NULL;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql VOLATILE;

COMMIT;

-- migrate affected data

-- must stop updates to database, as triggers are first temporarily
-- disabled to allow reads to continue

BEGIN;

ALTER TABLE planet_osm_line DISABLE TRIGGER USER;
ALTER TABLE planet_osm_polygon DISABLE TRIGGER USER;
ALTER TABLE planet_osm_point DISABLE TRIGGER USER;

COMMIT;

BEGIN;

UPDATE planet_osm_polygon
  SET mz_is_landuse = TRUE,
      mz_way11 = ST_SimplifyPreserveTopology(way, 76.44)
  WHERE (mz_is_landuse IS NOT TRUE) AND
        ("natural" IN ('wetland', 'glacier') OR aeroway IN ('runway', 'taxiway', 'apron'));

UPDATE planet_osm_point
  SET mz_poi_level = 17
  WHERE aeroway='gate' AND (mz_poi_level > 17 OR mz_poi_level IS NULL);

UPDATE planet_osm_line
  SET mz_road_level = (CASE WHEN (mz_road_level is NULL OR mz_road_level > 11) AND aeroway IN ('runway', 'taxiway') THEN 11
                            WHEN (mz_road_level is NULL OR mz_road_level > 14) AND railway='subway' THEN 14
                            ELSE NULL END)
  WHERE ((mz_road_level is NULL OR mz_road_level > 11) AND aeroway IN ('runway', 'taxiway')) OR
        ((mz_road_level is NULL OR mz_road_level > 14) AND railway='subway');

UPDATE planet_osm_line
  SET mz_road_sort_key = mz_calculate_road_sort_key(layer, bridge, tunnel, highway, railway, aeroway)
  WHERE mz_road_level IS NOT NULL AND (aeroway IN ('runway', 'taxiway') OR railway='subway');

COMMIT;

-- re-enable triggers

BEGIN;

ALTER TABLE planet_osm_line ENABLE TRIGGER USER;
ALTER TABLE planet_osm_polygon ENABLE TRIGGER USER;
ALTER TABLE planet_osm_point ENABLE TRIGGER USER;

COMMIT;