用PL/pgSQL对osm数据进行矢量分级 // NerdFei

执行总流程介绍

Perform-sql-updates.sql是总执行脚本

#!/bin/bash

# subsequent sql depends on functions installed
echo "Creating functions..."
psql $@ -f functions.sql
echo "done."

# apply updates in parallel across tables
echo -e "\nApplying updates in parallel across tables..."
psql $@ -f apply-updates-non-planet-tables.sql &
psql $@ -f apply-planet_osm_polygon.sql &
psql $@ -f apply-planet_osm_line.sql &
psql $@ -f apply-planet_osm_point.sql &
wait
echo "done."

echo -e '\nApplying triggers...'
psql $@ -f triggers.sql
echo 'done.'

echo -e "\nAll updates complete. Exiting."

这是总的执行脚本，干了一下几件事：

导入了一些函数，这些函数在之后的sql处理中会用到
对point, line, polygon几个表分别进行了处理，会在下面详细介绍
导入必要的触发器

对non-planet表进行处理

apply-updates-non-planet-tables.sql

DO $$
BEGIN

-- add way_area columns to all tables that use it
PERFORM mz_add_area_column('ne_110m_ocean', 'way_area', 'the_geom');
PERFORM mz_add_area_column('ne_110m_lakes', 'way_area', 'the_geom');
PERFORM mz_add_area_column('ne_50m_ocean', 'way_area', 'the_geom');
PERFORM mz_add_area_column('ne_50m_lakes', 'way_area', 'the_geom');
PERFORM mz_add_area_column('ne_50m_playas', 'way_area', 'the_geom');
PERFORM mz_add_area_column('ne_10m_ocean', 'way_area', 'the_geom');
PERFORM mz_add_area_column('ne_10m_lakes', 'way_area', 'the_geom');
PERFORM mz_add_area_column('ne_10m_playas', 'way_area', 'the_geom');
PERFORM mz_add_area_column('water_polygons', 'way_area', 'the_geom');
PERFORM mz_add_area_column('ne_10m_urban_areas', 'way_area', 'the_geom');
PERFORM mz_add_area_column('ne_50m_urban_areas', 'way_area', 'the_geom');
PERFORM mz_add_area_column('ne_10m_parks_and_protected_lands', 'way_area', 'the_geom');

-- way_area indexes
PERFORM mz_create_index_if_not_exists('ne_110m_ocean_wayarea_index', 'ne_110m_ocean', 'way_area');
PERFORM mz_create_index_if_not_exists('ne_110m_lakes_wayarea_index', 'ne_110m_lakes', 'way_area');
PERFORM mz_create_index_if_not_exists('ne_50m_ocean_wayarea_index', 'ne_50m_ocean', 'way_area');
PERFORM mz_create_index_if_not_exists('ne_50m_lakes_wayarea_index', 'ne_50m_lakes', 'way_area');
PERFORM mz_create_index_if_not_exists('ne_50m_playas_wayarea_index', 'ne_50m_playas', 'way_area');
PERFORM mz_create_index_if_not_exists('ne_50m_urban_areas_way_area_index', 'ne_50m_urban_areas', 'way_area');
PERFORM mz_create_index_if_not_exists('ne_10m_ocean_wayarea_index', 'ne_10m_ocean', 'way_area');
PERFORM mz_create_index_if_not_exists('ne_10m_lakes_wayarea_index', 'ne_10m_lakes', 'way_area');
PERFORM mz_create_index_if_not_exists('ne_10m_playas_wayarea_index', 'ne_10m_playas', 'way_area');
PERFORM mz_create_index_if_not_exists('ne_10m_urban_areas_way_area_index', 'ne_10m_urban_areas', 'way_area');
PERFORM mz_create_index_if_not_exists('ne_10m_parks_and_protected_lands_way_area_index', 'ne_10m_parks_and_protected_lands', 'way_area');
PERFORM mz_create_index_if_not_exists('water_polygons_wayarea_index', 'water_polygons', 'way_area');

-- additional updates
PERFORM mz_create_index_if_not_exists('ne_10m_populated_places_scalerank_index', 'ne_10m_populated_places', 'scalerank');

PERFORM AddGeometryColumn('ne_50m_urban_areas', 'mz_centroid', 900913, 'Geometry', 2);
UPDATE ne_50m_urban_areas SET mz_centroid=ST_Centroid(the_geom);
CREATE INDEX ne_50m_urban_areas_centroid_index ON ne_50m_urban_areas USING gist(mz_centroid);

PERFORM AddGeometryColumn('ne_10m_parks_and_protected_lands', 'mz_centroid', 900913, 'Geometry', 2);
UPDATE ne_10m_parks_and_protected_lands SET mz_centroid=ST_Centroid(the_geom);
CREATE INDEX ne_10m_parks_and_protected_lands_centroid_index ON ne_10m_parks_and_protected_lands USING gist(mz_centroid);

PERFORM AddGeometryColumn('ne_10m_urban_areas', 'mz_centroid', 900913, 'Geometry', 2);
UPDATE ne_10m_urban_areas SET mz_centroid=ST_Centroid(the_geom);
CREATE INDEX ne_10m_urban_areas_centroid_index ON ne_10m_urban_areas USING gist(mz_centroid);

END $$;

对planet_osm_polygon表做处理

apply_planet_osm_polygon.sql

对于面的处理向较与point和line来说算是比较复杂，因为需要对面进行抽希处理。抽希处理需要在两个方面进行：

去掉一些不必要的Feature。
对与一个Feature，去掉其Geometry的一些点。


DO $$
BEGIN

--------------------------------------------------------------------------------
-- planet_osm_polygon
--------------------------------------------------------------------------------

-- indexes on existing columns
PERFORM mz_create_index_if_not_exists('planet_osm_polygon_wayarea_index', 'planet_osm_polygon', 'way_area');
PERFORM mz_create_partial_index_if_not_exists('planet_osm_polygon_building_index', 'planet_osm_polygon', 'building', 'building IS NOT NULL');
PERFORM mz_create_partial_index_if_not_exists('planet_osm_polygon_admin_level_index', 'planet_osm_polygon', 'admin_level', 'boundary = ''administrative''');

-- indexes on functions
CREATE INDEX planet_osm_polygon_is_building_or_part_index ON planet_osm_polygon(mz_calculate_is_building_or_part(building, "building:part")) WHERE mz_calculate_is_building_or_part(building, "building:part") = TRUE;
CREATE INDEX planet_osm_polygon_is_water_index ON planet_osm_polygon(mz_calculate_is_water("waterway", "natural", "landuse")) WHERE mz_calculate_is_water("waterway", "natural", "landuse") = TRUE;

-- update polygon table to add centroids
ALTER TABLE planet_osm_polygon ADD COLUMN mz_is_landuse BOOLEAN;
ALTER TABLE planet_osm_polygon ADD COLUMN mz_centroid GEOMETRY;

-- at the moment we only add centroids to landuse features
UPDATE planet_osm_polygon SET
    mz_is_landuse = TRUE,
    mz_centroid = ST_Centroid(way)
    WHERE mz_calculate_is_landuse("landuse", "leisure", "natural", "highway", "amenity", "aeroway") = TRUE;

-- indexes for centroid queries
CREATE INDEX planet_osm_polygon_is_landuse_col_index ON planet_osm_polygon(mz_is_landuse) WHERE mz_is_landuse=TRUE;
CREATE INDEX planet_osm_polygon_centroid_landuse_index ON planet_osm_polygon USING gist(mz_centroid) WHERE mz_is_landuse=TRUE;

END $$;

对planet_osm_point表做处理

apply-planet_osm_point.sql

DO $$
BEGIN

--------------------------------------------------------------------------------
-- planet_osm_point
--------------------------------------------------------------------------------

CREATE INDEX planet_osm_point_place_index ON planet_osm_point(place) WHERE name IS NOT NULL AND place IN ('city', 'continent', 'country', 'county', 'district', 'hamlet', 'island', 'isolated_dwelling', 'lake', 'locality', 'neighbourhood', 'ocean', 'province', 'sea', 'state', 'suburb', 'town', 'village');

CREATE INDEX planet_osm_point_level_index ON planet_osm_point(mz_calculate_poi_level("aerialway", "aeroway", "amenity", "barrier", "highway", "historic", "leisure", "lock", "man_made", "natural", "power", "railway", "shop", "tourism", "waterway")) WHERE mz_calculate_poi_level("aerialway", "aeroway", "amenity", "barrier", "highway", "historic", "leisure", "lock", "man_made", "natural", "power", "railway", "shop", "tourism", "waterway") IS NOT NULL;

END $$;

对planet_osm_line表做处理

apply-planet_osm_line.sql

DO $$
BEGIN

--------------------------------------------------------------------------------
-- planet_osm_line
--------------------------------------------------------------------------------

-- indexes on existing columns
PERFORM mz_create_partial_index_if_not_exists('planet_osm_line_waterway', 'planet_osm_line', 'waterway', 'waterway IS NOT NULL');

-- indexes on functions
CREATE INDEX planet_osm_line_road_level_index ON planet_osm_line(mz_calculate_road_level(highway, railway, aeroway)) WHERE mz_calculate_road_level(highway, railway, aeroway) IS NOT NULL;

END $$;

导入的函数

--------------------------------------------------------------------------------
--               mz_does_index_exist()
--------------------------------------------------------------------------------

-- used to check whether an index exists before adding
CREATE OR REPLACE FUNCTION mz_does_index_exist(index_name text)
RETURNS BOOLEAN AS $$
BEGIN
    RETURN EXISTS (
        SELECT 1 FROM pg_class AS c
        INNER JOIN pg_namespace AS n ON n.oid = c.relnamespace
        WHERE c.relname = index_name
    );
END;
$$ LANGUAGE plpgsql STABLE;

--------------------------------------------------------------------------------
--               mz_does_trigger_exist()
--------------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION mz_does_trigger_exist(trigger_name text, table_name text)
RETURNS BOOLEAN AS $$
BEGIN
    RETURN EXISTS(
        SELECT 1 FROM pg_class AS c
        INNER JOIN pg_namespace AS n ON n.oid = c.relnamespace
        INNER JOIN pg_trigger AS t ON t.tgrelid = c.oid
        WHERE c.relname = table_name
          AND t.tgname = trigger_name
    );
END;
$$ LANGUAGE plpgsql STABLE;

--------------------------------------------------------------------------------
--               mz_create_index_if_not_exists()
--------------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION mz_create_index_if_not_exists(index_name text, table_name text, column_name text)
RETURNS VOID AS $$
BEGIN
    IF NOT mz_does_index_exist(index_name) THEN
        EXECUTE 'CREATE INDEX ' ||
            quote_ident(index_name) || ' ON ' || quote_ident(table_name) ||
            '(' || quote_ident(column_name) || ')';
    END IF;
END;
$$ LANGUAGE plpgsql VOLATILE;

--------------------------------------------------------------------------------
--               mz_create_partial_index_if_not_exists()
--------------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION mz_create_partial_index_if_not_exists(index_name text, table_name text, column_name text, where_clause text)
RETURNS VOID AS $$
BEGIN
    IF NOT mz_does_index_exist(index_name) THEN
        EXECUTE 'CREATE INDEX ' ||
            quote_ident(index_name) || ' ON ' || quote_ident(table_name) ||
            '(' || quote_ident(column_name) || ') WHERE ' || where_clause;
    END IF;
END;
$$ LANGUAGE plpgsql VOLATILE;

--------------------------------------------------------------------------------
--               mz_create_trigger_if_not_exists()
--------------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION mz_create_trigger_if_not_exists(
    trigger_name text, table_name text, function_name text)
RETURNS VOID AS $$
BEGIN
    IF NOT mz_does_trigger_exist(trigger_name, table_name) THEN
        EXECUTE 'CREATE TRIGGER ' || quote_ident(trigger_name) ||
          ' BEFORE INSERT OR UPDATE ON ' || quote_ident(table_name) ||
          ' FOR EACH ROW EXECUTE PROCEDURE ' || quote_ident(function_name) || '()';
    END IF;
END;
$$ LANGUAGE plpgsql VOLATILE;

--------------------------------------------------------------------------------
--               mz_does_column_exist()
--------------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION mz_does_column_exist(input_table_name text, input_column_name text)
RETURNS BOOLEAN AS $$
BEGIN
    RETURN EXISTS (
        SELECT 1 FROM information_schema.columns
        WHERE table_name=input_table_name AND column_name=input_column_name
    );
END;
$$ LANGUAGE plpgsql STABLE;

--------------------------------------------------------------------------------
--               mz_add_simplified_geometry_column()
--------------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION mz_add_simplified_geometry_column(
    table_name text, column_name text, existing_geom_column_name text, geom_type text, tolerance float, where_clause text default NULL)
RETURNS VOID AS $$
DECLARE v_where_clause TEXT DEFAULT '';
DECLARE v_index_where TEXT DEFAULT '';
BEGIN
    IF NOT mz_does_column_exist(table_name, column_name) THEN
        PERFORM AddGeometryColumn(table_name, column_name, 900913, geom_type, 2);
        IF where_clause IS NOT NULL THEN
            v_where_clause := ' AND ' || where_clause;
            v_index_where := ' WHERE ' || where_clause;
        END IF;
        EXECUTE 'UPDATE ' || quote_ident(table_name) ||
            ' SET ' || quote_ident(column_name) ||
            '=ST_SimplifyPreserveTopology(' || quote_ident(existing_geom_column_name) ||
            ', ' || tolerance || ')' ||
            ' WHERE ' || quote_ident(existing_geom_column_name) || ' IS NOT NULL' ||
            v_where_clause;
        EXECUTE 'CREATE INDEX ' || quote_ident(table_name || '_' || column_name) ||
            ' ON ' || quote_ident(table_name) || ' USING gist(' || quote_ident(column_name) || ')' ||
            v_index_where;
    END IF;
END;
$$ LANGUAGE plpgsql VOLATILE;

--------------------------------------------------------------------------------
--               mz_safe_convert_to_float()
--------------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION mz_safe_convert_to_float(v_input text)
RETURNS FLOAT AS $$
DECLARE v_float_value FLOAT DEFAULT NULL;
BEGIN
    BEGIN
        v_float_value := TO_NUMBER(
            REPLACE(REPLACE(v_input, ';', '.'), ',', '.'),
            '999999D99S') AS FLOAT;
    EXCEPTION WHEN OTHERS THEN
        RETURN NULL;
    END;
    RETURN v_float_value;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

--------------------------------------------------------------------------------
--               mz_normalize_id()
--------------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION mz_normalize_id(id bigint, geom Geometry)
RETURNS TEXT AS $$
BEGIN
    IF id < 0 THEN
        RETURN Substr(MD5(ST_AsBinary(geom)), 1, 10);
    ELSE
        RETURN id::text;
    END IF;
END;
$$ LANGUAGE plpgsql STABLE;

--------------------------------------------------------------------------------
--               mz_add_normalized_id()
--------------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION mz_add_normalized_id(
    table_name text, column_name text, geom_name text, prev_id_column_name text)
RETURNS VOID AS $$
BEGIN
    IF NOT mz_does_column_exist(table_name, column_name) THEN
        EXECUTE 'ALTER TABLE ' || quote_ident(table_name) ||
                ' ADD COLUMN ' || quote_ident(column_name) ||
                ' TEXT';
        EXECUTE 'UPDATE ' || quote_ident(table_name) ||
                ' SET ' || quote_ident(column_name) ||
                ' = mz_normalize_id(' || quote_ident(prev_id_column_name) ||
                ', ' || quote_ident(geom_name) || ')' ||
                ' WHERE ' || quote_ident(prev_id_column_name) || ' IS NOT NULL';
    END IF;
END;
$$ LANGUAGE plpgsql VOLATILE;

--------------------------------------------------------------------------------
--               mz_add_area_column()
--------------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION mz_add_area_column(table_name text, column_name text, geom_name text)
RETURNS VOID AS $$
BEGIN
    IF NOT mz_does_column_exist(table_name, column_name) THEN
        EXECUTE 'ALTER TABLE ' || quote_ident(table_name) ||
                ' ADD COLUMN ' || quote_ident(column_name) ||
                ' REAL';
        EXECUTE 'UPDATE ' || quote_ident(table_name) || ' SET ' || quote_ident(column_name) ||
                ' = ST_Area(' || quote_ident(geom_name) || ') ' ||
                'WHERE ' || quote_ident(geom_name) || ' IS NOT NULL';
    END IF;
END;
$$ LANGUAGE plpgsql VOLATILE;

--------------------------------------------------------------------------------
--               mz_calculate_is_landuse()
--------------------------------------------------------------------------------
-- functions to encapsulate logic for calculating new columns

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;

--------------------------------------------------------------------------------
--               mz_calculate_poi_level()
--------------------------------------------------------------------------------

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;

--------------------------------------------------------------------------------
--               mz_calculate_road_level()
--------------------------------------------------------------------------------

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', 'living_street')
                OR railway_val IN ('tram', 'light_rail', 'narrow_gauge', 'monorail')) THEN 15
             ELSE NULL END
    );
END;
$$ LANGUAGE plpgsql IMMUTABLE;

--------------------------------------------------------------------------------
--               mz_calculate_road_sort_key()
--------------------------------------------------------------------------------

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;

--------------------------------------------------------------------------------
--               mz_calculate_is_water()
--------------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION mz_calculate_is_water(
    waterway_val text, natural_val text, landuse_val text)
RETURNS BOOLEAN AS $$
BEGIN
    RETURN (
        waterway_val IN ('riverbank', 'dock')
     OR natural_val IN ('water')
     OR landuse_val IN ('basin', 'reservoir')
    );
END;
$$ LANGUAGE plpgsql IMMUTABLE;

--------------------------------------------------------------------------------
--               mz_calculate_is_building_or_part()
--------------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION mz_calculate_is_building_or_part(
    building_val text, buildingpart_val text)
RETURNS BOOLEAN AS $$
BEGIN
    RETURN (building_val IS NOT NULL OR buildingpart_val IS NOT NULL);
END;
$$ LANGUAGE plpgsql IMMUTABLE;


-- functions to temporarily enable and disable triggers
-- prevents them from firing while executing mass updates

--------------------------------------------------------------------------------
--               mz_tables_with_triggers()
--------------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION mz_tables_with_triggers()
RETURNS TEXT[] AS $$
BEGIN
    RETURN ARRAY['planet_osm_polygon', 'planet_osm_line', 'planet_osm_point', 'water_polygons'];
END;
$$ LANGUAGE plpgsql IMMUTABLE;

--------------------------------------------------------------------------------
--               mz_disable_triggers()
--------------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION mz_disable_triggers()
RETURNS VOID AS $$
DECLARE table_name TEXT;
BEGIN
    FOREACH table_name IN ARRAY mz_tables_with_triggers()
    LOOP
        EXECUTE 'ALTER TABLE ' || quote_ident(table_name) ||
                ' DISABLE TRIGGER USER';
    END LOOP;
END;
$$ LANGUAGE plpgsql VOLATILE;

--------------------------------------------------------------------------------
--               mz_enable_triggers()
--------------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION mz_enable_triggers()
RETURNS VOID AS $$
DECLARE table_name TEXT;
BEGIN
    FOREACH table_name IN ARRAY mz_tables_with_triggers()
    LOOP
        EXECUTE 'ALTER TABLE ' || quote_ident(table_name) ||
                ' ENABLE TRIGGER USER';
    END LOOP;
END;
$$ LANGUAGE plpgsql VOLATILE;

导入的触发器

--------------------------------------------------------------------------------
--               mz_trigger_function_landuse()
--------------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION mz_trigger_function_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;
        NEW.mz_centroid := ST_Centroid(NEW.way);
    ELSE
        NEW.mz_is_landuse := NULL;
        NEW.mz_centroid := NULL;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql VOLATILE;

DO $$
BEGIN

PERFORM mz_create_trigger_if_not_exists('mz_trigger_landuse', 'planet_osm_polygon', 'mz_trigger_function_landuse');

END $$;

拆分瓦片处理

split_to_tiles.sql

-- This file contains functions (see `mz_SplitIntoTiles()`) for splitting a
-- table of polygons into uniform tiles. It was useful in integrating the
-- ne_10m_land Natural Earth dataset, which, downloaded straight from the
-- source, stores all seven continents in one monolithic multipolygon. That
-- makes `st_intersects()`/`st_intersection()` take absurdly long, since they
-- can't take advantage of any spatial indexes (which *would* help if it were
-- split up into many smaller polygons), and thus clogs up tile queries, which
-- depend on both of those functions. Hence, we split it into 10km x 10km tiles
-- using `mz_SplitIntoTiles()`.

--------------------------------------------------------------------------------
--               mz_CreateGrid()
--------------------------------------------------------------------------------
-- A function that creates a table containing a grid of cells, taken from here:
-- http://gis.stackexchange.com/questions/16374/how-to-create-a-regular-polygon-grid-in-postgis

create or replace function mz_CreateGrid(
	numberX integer,
	numberY integer,
	xsize float8,
	ysize float8,
	x0 float8 default 0,
	y0 float8 default 0,
	out "row" integer,
	out col integer,
	out the_geom geometry
)
returns setof record as
$$
	select
		rowInd + 1 as row,
		colInd + 1 as col,
		st_Translate(cell, colInd * xsize + x0, rowInd * ysize + y0) as the_geom
	from
		generate_series(0, numberY - 1) as rowInd,
		generate_series(0, numberX - 1) as colInd,
		(select (format('POLYGON((0 0, 0 %s, %s %s, %s 0,0 0))', ysize, xsize, ysize, xsize))::geometry as cell) as foo;
$$ language sql immutable strict;

--------------------------------------------------------------------------------
--               mz_SplitIntoTiles()
--------------------------------------------------------------------------------
-- Split the polygons in a table called `table_name` into uniformly sized tiles
-- in a table called `${table_name}_tiles`.

create or replace function mz_SplitIntoTiles(
	table_name text,
	tile_size_meters integer,
	geom_column_name text default 'the_geom'
)
returns void as
$$
	declare
		grid_table_name text := table_name || '_grid';
		table_bbox box2d;
		num_tiles_x integer;
		num_tiles_y integer;
	begin
		execute format('select st_extent(%s) from %s', geom_column_name, table_name) into table_bbox;
		num_tiles_x = ceiling(
			(st_xmax(table_bbox) - st_xmin(table_bbox)) / (tile_size_meters :: float)
		);
		num_tiles_y = ceiling(
			(st_ymax(table_bbox) - st_ymin(table_bbox)) / (tile_size_meters :: float)
		);

		-- Create a table containing a grid with cells of length/width
		-- `tile_size_meters`, covering the entire extent of `table_name`.
		execute format(
			'create table %s as
			select *
			from MZ_CreateGrid(%s, %s, %s, %s, %s, %s);',
			grid_table_name, num_tiles_x, num_tiles_y,
			tile_size_meters, tile_size_meters,
			st_xmin(table_bbox), st_ymin(table_bbox)
		);
		perform UpdateGeometrySRID(grid_table_name, 'the_geom', 900913);
		execute format('create index %s_index on %1$s using gist(the_geom)', grid_table_name);

		execute format('create sequence %1$s_ids;', table_name);

		-- Intersect the gridded cells with the polygons in `table_name`,
		-- storing the now-tiled polygons in `${table_name}_tiles`. Assign each
		-- a unique `gid`.
		execute format(
			'create table %1$s_tiles as
			select
				nextval(''%1$s_ids'')::int as gid,
				st_intersection(%1$s.%3$s, %2$s.the_geom) as the_geom
			from %1$s
			join %2$s
			on (
				st_isvalid(%1$s.%3$s) and
				st_intersects(%1$s.%3$s, %2$s.the_geom)
			);',
			table_name, grid_table_name, geom_column_name
		);

		execute 'drop table ' || grid_table_name;
	end
$$ language plpgsql;