migration-20150930

gistfile1.sql

CREATE OR REPLACE FUNCTION mz_one_pixel_zoom(
  way_area real)
RETURNS real AS $$
BEGIN
  RETURN
    CASE WHEN way_area < 5.704
           THEN 16.0
         ELSE (17.256-ln(way_area)/ln(4))
    END;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

BEGIN;

DROP FUNCTION mz_calculate_poi_level(text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,real);

CREATE OR REPLACE FUNCTION mz_calculate_poi_level(
    aerialway_val text,
    aeroway_val text,
    amenity_val text,
    barrier_val text,
    craft_val text,
    highway_val text,
    historic_val text,
    leisure_val text,
    lock_val text,
    man_made_val text,
    natural_val text,
    office_val text,
    power_val text,
    railway_val text,
    shop_val text,
    tourism_val text,
    waterway_val text,
    way_area real
)
RETURNS REAL AS $$
DECLARE
  zoom REAL;
BEGIN
  zoom = mz_one_pixel_zoom(way_area);
  RETURN
    CASE
      WHEN aeroway_val IN ('aerodrome', 'airport')
        THEN LEAST(zoom + 4.12, 13)
      WHEN amenity_val = 'hospital'
        THEN LEAST(zoom + 3.32, 15)

      WHEN natural_val IN ('peak', 'volcano')
        THEN 11 -- these are generally point features
      WHEN railway_val IN ('station')
        THEN LEAST(zoom + 0.38, 13)
      WHEN tourism_val = 'zoo'
        THEN LEAST(zoom + 3.00, 15)
      WHEN (natural_val IN ('spring')
            OR railway_val IN ('level_crossing'))
        THEN 14 -- these are generally points
      WHEN amenity_val IN ('bank', 'cinema', 'courthouse', 'embassy',
          'fire_station', 'fuel', 'library', 'police', 'post_office',
	  'theatre')
        THEN LEAST(zoom + 2.7, 16)
      WHEN amenity_val IN ('biergarten', 'pub', 'bar', 'restaurant',
          'fast_food', 'cafe')
        THEN LEAST(zoom + 2.50, 17)
      WHEN (amenity_val IN ('pharmacy', 'veterinary')
            OR craft_val IN ('brewery', 'carpenter', 'confectionery', 'dressmaker',
                'electrician', 'gardener', 'handicraft', 'hvac', 'metal_construction',
                'painter', 'photographer', 'photographic_laboratory', 'plumber',
                'pottery', 'sawmill', 'shoemaker', 'stonemason', 'tailor', 'winery'))
        THEN LEAST(zoom + 3.3, 17)
      WHEN amenity_val  = 'nursing_home'     THEN LEAST(zoom + 1.25, 16)
      WHEN shop_val     = 'music'            THEN LEAST(zoom + 1.27, 17)
      WHEN amenity_val  = 'community_centre' THEN LEAST(zoom + 3.98, 17)
      WHEN shop_val     = 'sports'           THEN LEAST(zoom + 1.53, 17)
      WHEN amenity_val  = 'college'          THEN LEAST(zoom + 2.35, 16)
      WHEN shop_val     = 'mall'             THEN LEAST(zoom + 2.74, 17)
      WHEN leisure_val  = 'stadium'          THEN LEAST(zoom + 2.30, 15)
      WHEN amenity_val  = 'university'       THEN LEAST(zoom + 2.55, 15)
      WHEN tourism_val  = 'museum'           THEN LEAST(zoom + 1.43, 16)
      WHEN historic_val = 'landmark'         THEN LEAST(zoom + 1.76, 15)
      WHEN leisure_val  = 'marina'           THEN LEAST(zoom + 3.45, 17)
      WHEN amenity_val  = 'place_of_worship' THEN LEAST(2 * zoom - 9.55, 17)
      WHEN (barrier_val IN ('gate')
            OR craft_val IN ('sawmill')
            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')
            OR aerialway_val IN ('station')
            OR railway_val IN ('halt', 'tram_stop')
            OR tourism_val IN ('alpine_hut'))
        THEN 15
      WHEN (aeroway_val IN ('helipad')
                   OR amenity_val IN ('bus_station', 'car_sharing',
                                      'picnic_site',
                                      'prison', 'recycling', 'shelter')
                   OR barrier_val IN ('block', 'bollard', 'lift_gate')
                   OR craft_val IN ('brewery', 'winery', 'sawmill')
                   OR highway_val IN ('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', 'bicycle_rental', 'bicycle_parking', 'bus_stop',
                 'drinking_water', 'emergency_phone',
                 'parking', 'post_box', 'telephone', 'theatre',
                 'toilets')
                   OR highway_val IN ('bus_stop', 'traffic_signals')
                   OR historic_val IN ('memorial')
                   OR leisure_val IN ('playground', 'slipway')
                   OR man_made_val IN ('mast', 'water_tower')
                   OR office_val IN ('accountant', 'administrative', 'advertising_agency',
                'architect', 'association', 'company', 'consulting', 'educational_institution',
                'employment_agency', 'estate_agent', 'financial', 'foundation', 'government',
                'insurance', 'it', 'lawyer', 'newspaper', 'ngo', 'notary', 'physician',
                'political_party', 'religion', 'research', 'tax_advisor', 'telecommunication',
                'therapist', 'travel_agent', 'yes')
                   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')
                   OR railway_val IN ('subway_entrance')) THEN 17
             WHEN (amenity_val IN ('bench', 'waste_basket')) THEN 18
             ELSE NULL END;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

COMMIT;

CREATE OR REPLACE FUNCTION mz_calculate_landuse_min_zoom(
  landuse_val TEXT,
  leisure_val TEXT,
  natural_val TEXT,
  highway_val TEXT,
  amenity_val TEXT,
  aeroway_val TEXT,
  tourism_val TEXT,
  man_made_val TEXT,
  power_val TEXT,
  boundary_val TEXT,
  way_area REAL)
RETURNS REAL AS $$
DECLARE
  zoom REAL;
BEGIN
  zoom = mz_one_pixel_zoom(way_area);
  RETURN
    CASE
      WHEN (boundary_val IN ('national_park', 'protected_area')
            OR leisure_val = 'nature_reserve')
        THEN zoom
      ELSE
        GREATEST(LEAST(zoom, 16), 9)
    END;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

CREATE OR REPLACE FUNCTION mz_trigger_function_polygon()
RETURNS TRIGGER AS $$
DECLARE
    mz_is_landuse BOOLEAN = mz_calculate_is_landuse(NEW."landuse", NEW."leisure", NEW."natural", NEW."highway", NEW."amenity", NEW."aeroway", NEW."tourism", NEW."man_made", NEW."power", NEW."boundary");
    mz_poi_min_zoom REAL = mz_calculate_poi_level(NEW."aerialway", NEW."aeroway", NEW."amenity", NEW."barrier", NEW."craft", NEW."highway", NEW."historic", NEW."leisure", NEW."lock", NEW."man_made", NEW."natural", NEW."office", NEW."power", NEW."railway", NEW."shop", NEW."tourism", NEW."waterway", NEW.way_area);
BEGIN
    IF mz_is_landuse THEN
        NEW.mz_is_landuse := TRUE;
	NEW.mz_landuse_min_zoom := mz_calculate_landuse_min_zoom(NEW."landuse", NEW."leisure", NEW."natural", NEW."highway", NEW."amenity", NEW."aeroway", NEW."tourism", NEW."man_made", NEW."power", NEW."boundary", NEW.way_area);
    ELSE
        NEW.mz_is_landuse := NULL;
	NEW.mz_landuse_min_zoom := NULL;
    END IF;

    NEW.mz_poi_min_zoom := mz_poi_min_zoom;

    IF mz_is_landuse OR mz_poi_min_zoom IS NOT NULL THEN
        NEW.mz_centroid := ST_Centroid(NEW.way);
    ELSE
        NEW.mz_centroid := NULL;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql VOLATILE;

CREATE OR REPLACE FUNCTION mz_trigger_function_point()
RETURNS TRIGGER AS $$
BEGIN
    NEW.mz_poi_min_zoom := mz_calculate_poi_level(NEW."aerialway", NEW."aeroway", NEW."amenity", NEW."barrier", NEW."craft", NEW."highway", NEW."historic", NEW."leisure", NEW."lock", NEW."man_made", NEW."natural", NEW."office", NEW."power", NEW."railway", NEW."shop", NEW."tourism", NEW."waterway", 0::real);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql VOLATILE;

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

ALTER TABLE planet_osm_point ALTER COLUMN mz_poi_min_zoom TYPE REAL;

UPDATE planet_osm_point SET
    mz_poi_min_zoom = mz_calculate_poi_level("aerialway", "aeroway", "amenity", "barrier", "craft", "highway", "historic", "leisure", "lock", "man_made", "natural", "office", "power", "railway", "shop", "tourism", "waterway", 0::real)
    WHERE coalesce("aerialway", "aeroway", "amenity", "barrier", "craft", "highway", "historic", "leisure", "lock", "man_made", "natural", "office", "power", "railway", "shop", "tourism", "waterway") IS NOT NULL;

ALTER TABLE planet_osm_polygon ALTER COLUMN mz_poi_min_zoom TYPE REAL;
ALTER TABLE planet_osm_polygon ADD COLUMN mz_landuse_min_zoom REAL;

UPDATE planet_osm_polygon SET
    mz_poi_min_zoom = mz_calculate_poi_level("aerialway", "aeroway", "amenity", "barrier", "craft", "highway", "historic", "leisure", "lock", "man_made", "natural", "office", "power", "railway", "shop", "tourism", "waterway", way_area)
    WHERE coalesce("aerialway", "aeroway", "amenity", "barrier", "craft", "highway", "historic", "leisure", "lock", "man_made", "natural", "office", "power", "railway", "shop", "tourism", "waterway") IS NOT NULL;

UPDATE planet_osm_polygon SET
    mz_landuse_min_zoom = mz_calculate_landuse_min_zoom("landuse", "leisure", "natural", "highway", "amenity", "aeroway", "tourism", "man_made", "power", "boundary", way_area)
    WHERE mz_is_landuse = TRUE;

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