Skip to content

Instantly share code, notes, and snippets.

@rmarianski

rmarianski/migration-20150930-2.sql

Created October 7, 2015 00:05
Show Gist options
  • Save rmarianski/630abcd8cf239338424f to your computer and use it in GitHub Desktop.
Save rmarianski/630abcd8cf239338424f to your computer and use it in GitHub Desktop.
Download ZIP
Raw
migration-20150930-2.sql
ALTER TABLE planet_osm_point DISABLE TRIGGER USER;
ALTER TABLE planet_osm_polygon DISABLE TRIGGER USER;
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;
CREATE OR REPLACE FUNCTION temp_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;
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_calculate_ferry_level(way geometry)
RETURNS SMALLINT AS $$
DECLARE
way_length real := st_length(way);
BEGIN
RETURN (
CASE
-- this is about when the way is >= 2px in length
WHEN way_length > 1223 THEN 8
WHEN way_length > 611 THEN 9
WHEN way_length > 306 THEN 10
WHEN way_length > 153 THEN 11
WHEN way_length > 76 THEN 12
ELSE 13
END);
END;
$$ LANGUAGE plpgsql IMMUTABLE;
CREATE OR REPLACE FUNCTION mz_calculate_road_level(highway_val text, railway_val text, aeroway_val text, route_val text, way geometry)
RETURNS SMALLINT AS $$
BEGIN
RETURN (
CASE WHEN highway_val IN ('motorway', 'trunk', 'motorway_link', 'primary') THEN 8
WHEN aeroway_val IN ('runway') THEN 9
WHEN highway_val IN ('secondary') THEN 10
WHEN (highway_val IN ('tertiary')
OR aeroway_val IN ('taxiway')) THEN 11
WHEN highway_val IN ('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='rail') THEN 14
WHEN (highway_val IN ('service', 'footpath', 'track', 'footway', 'steps', 'pedestrian', 'path', 'cycleway', 'living_street')
OR railway_val IN ('tram', 'light_rail', 'narrow_gauge', 'monorail')) THEN 15
WHEN route_val = 'ferry' THEN mz_calculate_ferry_level(way)
ELSE NULL END
);
END;
$$ LANGUAGE plpgsql IMMUTABLE;
CREATE INDEX temp_planet_osm_line_road_level_index ON planet_osm_line(mz_calculate_road_level(highway, railway, aeroway, route, way)) WHERE mz_calculate_road_level(highway, railway, aeroway, route, way) IS NOT NULL;
ALTER TABLE planet_osm_point ADD COLUMN temp_mz_poi_min_zoom REAL;
UPDATE planet_osm_point SET
temp_mz_poi_min_zoom = temp_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 ADD COLUMN temp_mz_poi_min_zoom REAL;
ALTER TABLE planet_osm_polygon ADD COLUMN mz_landuse_min_zoom REAL;
UPDATE planet_osm_polygon SET
temp_mz_poi_min_zoom = temp_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;
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);
ALTER FUNCTION temp_mz_calculate_poi_level(text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,real) RENAME TO mz_calculate_poi_level;
ALTER TABLE planet_osm_point DROP COLUMN mz_poi_min_zoom;
ALTER TABLE planet_osm_point RENAME COLUMN temp_mz_poi_min_zoom TO mz_poi_min_zoom;
ALTER TABLE planet_osm_polygon DROP COLUMN mz_poi_min_zoom;
ALTER TABLE planet_osm_polygon RENAME COLUMN temp_mz_poi_min_zoom TO mz_poi_min_zoom;
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;
DROP INDEX planet_osm_line_road_level_index;
ALTER INDEX temp_planet_osm_line_road_level_index RENAME TO planet_osm_line_road_level_index;
DROP FUNCTION mz_calculate_road_level(text, text, text);
COMMIT;
ALTER TABLE planet_osm_point ENABLE TRIGGER USER;
ALTER TABLE planet_osm_polygon ENABLE TRIGGER USER;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment