functions.sql

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_is_building_or_part(
    building_val text, buildingpart_val text)
RETURNS BOOLEAN AS $$
BEGIN
    -- there are 12,000 uses of building=no, so we ought to take that into
    -- account when figuring out if something is a building or not. also,
    -- returning "kind=no" is a bit weird.
    RETURN ((building_val IS NOT NULL AND building_val <> 'no') OR
            (buildingpart_val IS NOT NULL AND buildingpart_val <> 'no'));
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- mz_rel_get_tag returns the tag value associated with a given
-- key, or NULL if the tag is not set in the array.
--
-- tags in the planet_osm_rels table are stored in a flat array
-- rather than in an hstore variable. so this function makes it
-- easier to extract values from that array as if it were
-- associative.
--
CREATE OR REPLACE FUNCTION mz_rel_get_tag(
  tags text[],
  k text)
RETURNS text AS $$
DECLARE
  lo CONSTANT integer := array_lower(tags, 1);
  hi CONSTANT integer := array_upper(tags, 1);
BEGIN
  -- if tags is null, then we're not going to find any values
  -- in it, so just short-circuit and return NULL.
  IF tags IS NULL THEN
    RETURN NULL;
  END IF;
  -- tags is an array of key-value pairs inline, so to get the
  -- keys only we want each odd offset.
  FOR i IN 0 .. ((hi - lo - 1) / 2)
  LOOP
    IF tags[2 * i + lo] = k THEN
      RETURN tags[2 * i + lo + 1];
    END IF;
  END LOOP;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- mz_first_dedup returns the lexicographically first non-NULL
-- entry in an array of text.
--
-- this is used to get a single value from the array of perhaps
-- many relations that a way can be a member of. we're not
-- interested in NULL values, so those can be discarded. it
-- remains to be seen if lexicographic ordering is any good for
-- network names, but in the limited testing i've done so far,
-- it seems to do okay.
--
CREATE OR REPLACE FUNCTION mz_first_dedup(
  arr text[])
RETURNS text AS $$
DECLARE
  rv text;
BEGIN
  SELECT DISTINCT y.x INTO rv FROM (SELECT unnest(arr) as x) y
    WHERE y.x IS NOT NULL LIMIT 1;
  RETURN rv;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- mz_modify_network returns an hstore of {route, network, ref}
-- where the network has been modified according to the
-- modifier tag, if there is any.
CREATE OR REPLACE FUNCTION mz_modify_network(
  tags hstore)
RETURNS hstore AS $$
DECLARE
  network text := tags->'network';
  modifier text := tags->'modifier';
BEGIN
  RETURN
    tags ||
    hstore('network',
      CASE WHEN
        network LIKE 'US:%' AND
        modifier IN ('Business', 'Spur', 'Truck', 'Alternate', 'Bypass',
                     'Connector', 'Historic', 'Toll', 'Scenic') AND
        POSITION(modifier IN network) = 0
      THEN
        network || ':' || modifier
      ELSE
        network
      END
    );
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- mz_get_rel_networks returns a list of triples of route type,
-- network and ref tags, or NULL, for a given way ID.
--
-- it does this by joining onto the relations slim table, so it
-- won't work if you dropped the slim tables, or didn't use slim
-- mode in osm2pgsql.
--
CREATE OR REPLACE FUNCTION mz_get_rel_networks(
  way_id bigint)
RETURNS text[] AS $$
SELECT
  array_agg(unnested)
FROM (
  SELECT
    unnest(tags) AS unnested
  FROM (
    SELECT
      mz_modify_network(hstore(tags))->ARRAY['route','network','ref','osmc:symbol'] AS tags
    FROM
      planet_osm_rels
    WHERE
      parts && ARRAY[way_id] AND
      parts[way_off+1:rel_off] && ARRAY[way_id] AND
      hstore(tags) ? 'route' AND
      (hstore(tags) ? 'network' OR
       hstore(tags) ? 'ref') AND
      hstore(tags) ? 'osmc:symbol'
    ) inner1
  ) inner2;
$$ LANGUAGE sql STABLE;

-- adds the prefix onto every key in an hstore value
CREATE OR REPLACE FUNCTION mz_hstore_add_prefix(
  tags hstore,
  prefix text)
RETURNS hstore AS $$
DECLARE
  new_tags hstore;
BEGIN
  SELECT hstore(array_agg(prefix || key), array_agg(value))
    INTO STRICT new_tags
    FROM each(tags);
  RETURN new_tags;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- OSM tags are often structured as a list separated by ':'s.
-- to preserve some kind of ordering information, we want to
-- insert an element in this "list" after the first element.
-- i.e: mz_insert_one_level('foo:bar', 'x') -> 'foo:x:bar'.
CREATE OR REPLACE FUNCTION mz_insert_one_level(
  tag text,
  str text)
RETURNS text AS $$
DECLARE
  arr text[] = string_to_array(tag, ':');
  fst text   = arr[1];
  len int    = array_upper(arr, 1);
  rst text[] = arr[2:len];
BEGIN
  RETURN array_to_string(ARRAY[fst, str] || rst, ':');
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- adds an infix value into every key in an hstore value after
-- the first element. so 'foo:bar=>bat' with infix 'x' becomes
-- 'foo:x:bar=>bat'.
CREATE OR REPLACE FUNCTION mz_hstore_add_infix(
  tags hstore,
  infix text)
RETURNS hstore AS $$
DECLARE
  new_tags hstore;
BEGIN
  SELECT hstore(array_agg(mz_insert_one_level(key, infix)), array_agg(value))
    INTO STRICT new_tags
    FROM each(tags);
  RETURN new_tags;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- returns the (possibly fractional) zoom at which the given way
-- area will be one square pixel nominally on screen (assuming
-- that tiles are 256x256px at integer zooms). sadly, features
-- aren't always rectangular and axis-aligned, but this should
-- still give a reasonable approximation to the zoom that it
-- would be appropriate to show them.
CREATE OR REPLACE FUNCTION mz_one_pixel_zoom(
  way_area real)
RETURNS real AS $$
BEGIN
  RETURN
    -- can't take logarithm of zero, and some ways have
    -- incredibly tiny areas, down to even zero. also, by z16
    -- all features really should be visible, so we clamp the
    -- computation at the way area which would result in 16
    -- being returned.
    CASE WHEN way_area < 5.704
           THEN 16.0
         ELSE (17.256-ln(way_area)/ln(4))
    END;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- for the purposes of sweeping for related public transit features,
-- "interesting" tags are ones which define relations which group public
-- transport features, or group features together in a "site". these are:
--   * public_transport = stop_area
--   * public_transport = stop_area_group
--   * type = stop_area
--   * type = stop_area_group
--   * type = site
--
CREATE OR REPLACE FUNCTION mz_is_interesting_transit_relation(
  tags hstore)
RETURNS BOOLEAN AS $$
BEGIN
  RETURN
    tags->'public_transport' IN ('stop_area', 'stop_area_group') OR
    tags->'type' IN ('stop_area', 'stop_area_group', 'site');
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- extract a name for a transit route relation. this can expand comma
-- separated lists and prefers to use the ref rather than the name.
CREATE OR REPLACE FUNCTION mz_transit_route_name(
  tags hstore)
RETURNS text AS $$
DECLARE
  route_name text;
BEGIN
  SELECT TRIM(BOTH FROM "name") INTO route_name
    FROM (
      SELECT UNNEST(string_to_array(COALESCE(
        -- prefer ref as it's less likely to contain the destination name
        tags->'ref',
        tags->'name'
      ), ',')) AS "name"
    ) refs_and_names
    LIMIT 1;
  RETURN route_name;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

DO $$
BEGIN

  -- suppress notice message from drop type cascade
  -- we re-add the type immediately afterwards
  SET LOCAL client_min_messages=warning;

  IF EXISTS (SELECT 1 FROM pg_type WHERE typname = 'mz_transit_routes_rettype') THEN
    DROP TYPE mz_transit_routes_rettype CASCADE;
  END IF;

  CREATE TYPE mz_transit_routes_rettype AS (
    -- the "top-most" site or public_transport relation found while exploring
    -- the graph of objects around the station.
    root_relation_id BIGINT,

    -- the "score" of the station. this takes the number of rail routes, the
    -- sum of the number of subway and light rail routes, and the sum of the
    -- number of tram and railway routes. if it is an interchange (i.e: both
    -- rail and subway / light_rail routes) then doubling both those numbers.
    -- these then get maxed out at 9, and turned into a 3-digit number.
    score SMALLINT,

    -- the list of distinct refs (or names if refs aren't present) on each
    -- type of route.
    train_routes      text[],
    subway_routes     text[],
    light_rail_routes text[],
    tram_routes       text[],
    railway_routes    text[]
  );
END$$;

-- examines a point or polygon (by OSM ID) and tries to determine if it is
-- related to a wider set of public transport features, including rail,
-- light rail, subway and tram routes. information about these is returned
-- in the returned type.
--
CREATE OR REPLACE FUNCTION mz_calculate_transit_routes_and_score(
  -- the station OSM ID from the point table, or NULL
  IN station_point_osm_id BIGINT,

  -- the station OSM ID from the polygon table, or NULL. may be negative to
  -- indicate a relation.
  IN station_polygon_osm_id BIGINT,

  -- the returned data - see the type definition for details.
  OUT retval mz_transit_routes_rettype)
AS $$
DECLARE
  -- the IDs of a "seed" set of relations which directly include any
  -- station node, way or relation passed as input.
  seed_relations bigint[];

  -- the geometry of the passed in OSM object, used to limit the
  -- station_and_stops query
  origin_point geometry;

  -- IDs of nodes which are tagged as stations or stops and are
  -- members of the stop area relations. these will contain the
  -- original `station_node_id`, but probably many others as well.
  stations_and_stops bigint[];

  -- IDs of ways which contain any of the stations and stops.
  -- these are included because sometimes the stop node isn't
  -- directly included in the route relation, only the way
  -- representing the track itself. this also contains the original
  -- `station_way_id` in case it's directly included in any routes.
  stations_and_lines bigint[];

  -- all the relation IDs as an array for intersecting with the
  -- "parts" array in planet_osm_rels.
  all_rel_ids bigint[];

  -- all the IDs of everything to intersect with the "parts" of
  -- planet_osm_rels when looking for route relations.
  all_interesting_ids bigint[];

  -- the IDs of all the route relations found
  all_routes bigint[];

  -- number of each type of route.
  num_train_routes smallint;
  num_subway_routes smallint;
  num_light_rail_routes smallint;
  num_tram_routes smallint;
  num_railway_routes smallint;

  -- a 'bonus' is given to the score of stations which are interchanges
  -- between mainline railway and subway or light rail.
  bonus smallint;

BEGIN
  -- find any "interesting" relations which contain the given station
  -- features as members.
  seed_relations := ARRAY(
    SELECT id
    FROM planet_osm_rels
    WHERE
      (ARRAY[station_point_osm_id, station_polygon_osm_id,
             -station_polygon_osm_id] && parts) AND
      (ARRAY[station_point_osm_id] && parts[1:way_off] OR
       ARRAY[station_polygon_osm_id] && parts[way_off+1:rel_off] OR
       ARRAY[-station_polygon_osm_id] && parts[rel_off+1:array_upper(parts,1)]) AND
      mz_is_interesting_transit_relation(hstore(tags))
    UNION
    -- manually include the station multipolygon relation so that we can
    -- sweep down its members too.
    SELECT -station_polygon_osm_id AS id WHERE station_polygon_osm_id < 0
    );

  -- this complex query does two recursive sweeps of the relations
  -- table starting from a seed set of relations which are or contain
  -- the original station.
  --
  -- the first sweep goes "upwards" from relations to "parent" relations. if
  -- a relation R1 is a member of relation R2, then R2 will be included in
  -- this sweep as long as it has "interesting" tags, as defined by the
  -- function mz_is_interesting_transit_relation.
  --
  -- the second sweep goes "downwards" from relations to "child" relations.
  -- if a relation R1 has a member R2 which is also a relation, then R2 will
  -- be included in this sweep as long as it also has "interesting" tags.
  --
  all_rel_ids := ARRAY(
    WITH RECURSIVE upward_search(level,path,id,parts,rel_off,cycle) AS (
        SELECT 0,ARRAY[id],id,parts,rel_off,false
        FROM planet_osm_rels WHERE id = ANY(seed_relations)
      UNION
        SELECT
          level + 1,
          path || r.id,
          r.id,
          r.parts,
          r.rel_off,
          r.id = ANY(path)
        FROM
          planet_osm_rels r JOIN upward_search s
        ON
          ARRAY[s.id] && r.parts
        WHERE
          ARRAY[s.id] && r.parts[r.rel_off+1:array_upper(r.parts,1)] AND
          mz_is_interesting_transit_relation(hstore(r.tags)) AND
          NOT cycle
      )
    SELECT id FROM upward_search ORDER BY level DESC);

  IF array_upper(all_rel_ids, 1) > 0 THEN
    retval.root_relation_id := all_rel_ids[1];
  ELSE
    retval.root_relation_id := NULL;
  END IF;

  all_rel_ids := ARRAY(
    WITH RECURSIVE downward_search(path,id,parts,rel_off,cycle) AS (
        SELECT ARRAY[id],id,parts,rel_off,false
        FROM planet_osm_rels WHERE id = ANY(all_rel_ids)
      UNION
        SELECT
          path || r.id,
          r.id,
          r.parts,
          r.rel_off,
          r.id = ANY(path)
        FROM
          planet_osm_rels r JOIN downward_search s
        ON
          r.id = ANY(s.parts[s.rel_off+1:array_upper(s.parts,1)])
        WHERE
          mz_is_interesting_transit_relation(hstore(r.tags)) AND
          NOT cycle
    ) SELECT id FROM downward_search WHERE NOT cycle);

  -- get the geometry for the passed-in OSM object. we'll use this to
  -- limit the stations and stops collected in the next step
  origin_point := COALESCE(
    (SELECT way FROM planet_osm_point WHERE osm_id = station_point_osm_id),
    (SELECT ST_PointOnSurface(way) FROM planet_osm_polygon WHERE osm_id = station_polygon_osm_id)
  );

  -- collect all the interesting nodes - this includes the station node (if
  -- any) and any nodes which are members of found relations which have
  -- public transport tags indicating that they're stations or stops.
  stations_and_stops := ARRAY(
    SELECT DISTINCT id
    FROM
      (SELECT UNNEST(parts[1:way_off]) AS id
       FROM planet_osm_rels
       WHERE id = ANY(all_rel_ids)) n
    JOIN planet_osm_point p
    ON n.id = p.osm_id
    WHERE
      (p.tags->'railway' IN ('station', 'stop', 'tram_stop') OR
       p.tags->'public_transport' IN ('stop', 'stop_position', 'tram_stop')) AND
      ST_DWithin(p.way, origin_point, 1000)
    -- re-include the station node, if there was one.
    UNION
    SELECT station_point_osm_id AS id
    WHERE station_point_osm_id IS NOT NULL);

  -- collect any physical railway which includes any of the above
  -- nodes.
  stations_and_lines := ARRAY(
    SELECT DISTINCT w.id
    FROM planet_osm_ways w
    WHERE w.nodes && stations_and_stops
    AND hstore(w.tags)->'railway' IN ('subway', 'light_rail', 'tram', 'rail')
    -- manually re-include the original station way, in case it's
    -- not part of a public_transport=stop_area relation.
    UNION SELECT station_polygon_osm_id AS id
    WHERE station_polygon_osm_id > 0
  );

  -- collect all IDs together in one array to intersect with the parts arrays
  -- of route relations which may include them.
  all_interesting_ids = stations_and_stops || stations_and_lines || all_rel_ids;

  all_routes := ARRAY(
    SELECT DISTINCT id
    FROM planet_osm_rels
    WHERE
      parts && all_interesting_ids AND
      (parts[1:way_off] && stations_and_stops OR
       parts[way_off+1:rel_off] && stations_and_lines OR
       parts[rel_off+1:array_upper(parts,1)] && all_rel_ids) AND
      hstore(tags)->'type' = 'route' AND
      hstore(tags)->'route' IN ('subway', 'light_rail', 'tram', 'train',
        'railway'));

  -- extract the route ref/name for routes
  retval.train_routes := ARRAY(
    SELECT DISTINCT mz_transit_route_name(hstore(tags)) FROM planet_osm_rels
    WHERE id = ANY(all_routes) AND hstore(tags)->'route' = 'train');

  retval.subway_routes := ARRAY(
    SELECT DISTINCT mz_transit_route_name(hstore(tags)) FROM planet_osm_rels
    WHERE id = ANY(all_routes) AND hstore(tags)->'route' = 'subway');

  retval.light_rail_routes := ARRAY(
    SELECT DISTINCT mz_transit_route_name(hstore(tags)) FROM planet_osm_rels
    WHERE id = ANY(all_routes) AND hstore(tags)->'route' = 'light_rail');

  retval.tram_routes := ARRAY(
    SELECT DISTINCT mz_transit_route_name(hstore(tags)) FROM planet_osm_rels
    WHERE id = ANY(all_routes) AND hstore(tags)->'route' = 'tram');

  retval.railway_routes := ARRAY(
    SELECT DISTINCT mz_transit_route_name(hstore(tags)) FROM planet_osm_rels
    WHERE id = ANY(all_routes) AND hstore(tags)->'route' = 'railway');

  -- calculate a score between 0 and 999 for stations.
  num_train_routes := COALESCE(array_upper(retval.train_routes, 1), 0);
  num_subway_routes := COALESCE(array_upper(retval.subway_routes, 1), 0);
  num_light_rail_routes := COALESCE(array_upper(retval.light_rail_routes, 1), 0);
  num_tram_routes := COALESCE(array_upper(retval.tram_routes, 1), 0);
  num_railway_routes := COALESCE(array_upper(retval.railway_routes, 1), 0);

  -- if a station is an interchange between mainline rail and subway or light
  -- rail, then give it a "bonus" boost of importance.
  IF num_train_routes > 0 AND
    (num_subway_routes + num_light_rail_routes) > 0 THEN
    bonus := 2;
  ELSE
    bonus := 1;
  END IF;

  retval.score :=
    100 * LEAST(9, bonus * num_train_routes) +
    10 * LEAST(9, bonus * (num_subway_routes + num_light_rail_routes)) +
    LEAST(9, num_tram_routes + num_railway_routes);
END;
$$ LANGUAGE plpgsql STABLE;

-- returns true if the text is numeric and can be cast
-- to a float without error.
CREATE OR REPLACE FUNCTION mz_is_numeric(
  t text)
RETURNS BOOLEAN AS $$
BEGIN
  RETURN t ~ '^([0-9]+[.]?[0-9]*|[.][0-9]+)$';
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- Calculate the height of a building by looking at either the
-- height tag, if one is set explicitly, or by calculating the
-- approximate height from the number of levels, if that is
-- set.
CREATE OR REPLACE FUNCTION mz_building_height(
  height text, levels text)
RETURNS REAL AS $$
BEGIN
  RETURN CASE
    -- if height is present, and can be parsed as a
    -- float, then we can filter right here.
    WHEN mz_is_numeric(height) THEN
      height::float

    -- looks like we assume each level is 3m, plus
    -- 2 overall.
    WHEN mz_is_numeric("levels") THEN
      (GREATEST(levels::float, 1) * 3 + 2)

    -- if height is present, but not numeric, then
    -- we have no idea what it could be, and we must
    -- assume it could be very large.
    WHEN "height" IS NOT NULL OR "levels" IS NOT NULL THEN
      1.0e10
    ELSE NULL END;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- Update the wof_neighbourhood table so that the `is_visible` field
-- is correct as of the given date `d`. The function returns the set
-- of WOF IDs which have been updated, and the locations of which
-- should be expired.
CREATE OR REPLACE FUNCTION wof_update_visible_ids(
  d DATE)
RETURNS SETOF BIGINT AS $$
UPDATE wof_neighbourhood a
  SET is_visible = b.new_visible
  FROM (
    SELECT wof_id, inception < d AND cessation >= d AS new_visible
    FROM wof_neighbourhood
  ) b
  WHERE
    a.wof_id = b.wof_id AND
    b.new_visible <> a.is_visible
  RETURNING a.wof_id;
$$ LANGUAGE sql VOLATILE;

-- returns TRUE if the given way ID (osm_id) is part of a bus route relation,
-- or NULL otherwise.
CREATE OR REPLACE FUNCTION mz_calculate_is_bus_route(osm_id BIGINT)
RETURNS BOOLEAN AS $$
BEGIN
  IF EXISTS(
    SELECT 1 FROM planet_osm_rels
    WHERE
      parts && ARRAY[osm_id] AND
      parts[way_off+1:rel_off] && ARRAY[osm_id] AND
      hstore(tags)->'type' = 'route' AND
      hstore(tags)->'route' IN ('bus', 'trolleybus')) THEN
    RETURN TRUE;
  END IF;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql STABLE;

CREATE OR REPLACE FUNCTION mz_is_path_major_route_relation(tags hstore)
RETURNS BOOLEAN AS $$
BEGIN
    RETURN (
      tags->'type' = 'route' AND
      tags->'route' IN ('hiking', 'foot', 'bicycle') AND
      tags->'network' IN ('iwn','nwn','rwn','lwn','icn','ncn','rcn','lcn')
    );
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- Looks up whether the given osm_id is a member of any hiking routes
-- and, if so, returns the network designation of the most important
-- (highest in hierarchy) of the networks.
--
CREATE OR REPLACE FUNCTION mz_hiking_network(osm_id bigint)
RETURNS text AS $$
DECLARE
  networks text[] := ARRAY(
    SELECT hstore(tags)->'network'
    FROM planet_osm_rels
    WHERE parts && ARRAY[osm_id]
      AND parts[way_off+1:rel_off] && ARRAY[osm_id]
      AND mz_is_path_major_route_relation(hstore(tags)));
BEGIN
  RETURN CASE
    WHEN networks && ARRAY['iwn'] THEN 'iwn'
    WHEN networks && ARRAY['nwn'] THEN 'nwn'
    WHEN networks && ARRAY['rwn'] THEN 'rwn'
    WHEN networks && ARRAY['lwn'] THEN 'lwn'
  END;
END;
$$ LANGUAGE plpgsql STABLE;

CREATE OR REPLACE FUNCTION mz_cycling_network_(way_tags hstore, osm_id bigint)
RETURNS text AS $$
DECLARE
  networks text[] := ARRAY(
    SELECT hstore(tags)->'network'
    FROM planet_osm_rels
    WHERE parts && ARRAY[osm_id]
      AND parts[way_off+1:rel_off] && ARRAY[osm_id]
      AND mz_is_path_major_route_relation(hstore(tags)));
BEGIN
  RETURN CASE
    WHEN networks && ARRAY['icn'] THEN 'icn'
    WHEN networks && ARRAY['ncn'] THEN 'ncn'
    WHEN way_tags->'ncn'='yes' OR way_tags ? 'ncn_ref' THEN 'ncn'
    WHEN networks && ARRAY['rcn'] THEN 'rcn'
    WHEN way_tags->'rcn'='yes' OR way_tags ? 'rcn_ref' THEN 'rcn'
    WHEN networks && ARRAY['lcn'] THEN 'lcn'
    WHEN way_tags->'lcn'='yes' OR way_tags ? 'lcn_ref' THEN 'lcn'
  END;
END;
$$ LANGUAGE plpgsql STABLE;

CREATE OR REPLACE FUNCTION mz_cycling_network(way_tags hstore, osm_id bigint)
RETURNS text AS $$
BEGIN
  RETURN CASE
    WHEN way_tags->'icn' = 'yes' OR way_tags ? 'icn_ref' THEN 'icn'
    ELSE mz_cycling_network_(way_tags, osm_id)
  END;
END;
$$ LANGUAGE plpgsql STABLE;

-- returns TRUE if the given way ID (osm_id) is part of a path route relation,
-- or NULL otherwise.
-- This function is meant to be called for something that we already know is a path.
-- Please ensure input is already a path, or output will be undefined.
--
-- Note that the dummy argument is just there to make the "shape" of the
-- arguments the same as with Python, where we need to pass in the relation
-- information rather than query it from `planet_osm_rels`.
CREATE OR REPLACE FUNCTION mz_calculate_path_major_route(osm_id BIGINT, dummy INTEGER)
RETURNS SMALLINT AS $$
BEGIN
  RETURN (
    SELECT
        MIN(
            CASE WHEN hstore(tags)->'network' IN ('icn', 'ncn') THEN 8
                 WHEN hstore(tags)->'network' IN ('iwn', 'nwn') THEN 9
                 WHEN hstore(tags)->'network' IN ('rcn') THEN 11
                 WHEN hstore(tags)->'network' IN ('rwn') THEN 11
                 WHEN hstore(tags)->'network' IN ('lcn') THEN 12
                 WHEN hstore(tags)->'network' IN ('lwn') THEN 12
            ELSE NULL
            END
        )
        AS p
     FROM planet_osm_rels
    WHERE
      parts && ARRAY[osm_id] AND
      parts[way_off+1:rel_off] && ARRAY[osm_id] AND
      mz_is_path_major_route_relation(hstore(tags))
  );
END;
$$ LANGUAGE plpgsql STABLE;

-- Returns a floating point number in meters for the given unit input text,
-- which must be of the form of a number (in which case it's assumed it's
-- meters), or a number followed by a unit (m, ft, '). The distance is
-- converted into meters and returned, or NULL is returned if the input
-- could not be understood.
--
CREATE OR REPLACE FUNCTION mz_to_float_meters(txt text)
RETURNS REAL AS $$
DECLARE
  decimal_matches text[] :=
    regexp_matches(txt, '([0-9]+(\.[0-9]*)?) *(mi|km|m|nmi|ft)');
  imperial_matches text[] :=
    regexp_matches(txt, E'([0-9]+(\\.[0-9]*)?)\x27 *(([0-9]+)")?');
  numeric_matches text[] :=
    regexp_matches(txt, '([0-9]+(\.[0-9]*)?)');
BEGIN
  RETURN CASE
    WHEN decimal_matches IS NOT NULL THEN
      CASE
        WHEN decimal_matches[3] = 'mi'  THEN 1609.3440 * decimal_matches[1]::real
        WHEN decimal_matches[3] = 'km'  THEN 1000.0000 * decimal_matches[1]::real
        WHEN decimal_matches[3] = 'm'   THEN    1.0000 * decimal_matches[1]::real
        WHEN decimal_matches[3] = 'nmi' THEN 1852.0000 * decimal_matches[1]::real
        WHEN decimal_matches[3] = 'ft'  THEN    0.3048 * decimal_matches[1]::real
      END
    WHEN imperial_matches IS NOT NULL THEN
      CASE WHEN imperial_matches[4] IS NULL THEN
        0.0254 * (imperial_matches[1]::real * 12)
      ELSE
        0.0254 * (imperial_matches[1]::real * 12 + imperial_matches[4]::real)
      END
    WHEN numeric_matches IS NOT NULL THEN
      numeric_matches[1]::real
    END;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

CREATE OR REPLACE FUNCTION mz_to_json_null_safe(val anyelement)
RETURNS JSON AS $$
DECLARE
BEGIN
  RETURN CASE WHEN val IS NULL THEN 'null' ELSE to_json(val) END;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

CREATE OR REPLACE FUNCTION mz_building_kind_detail(val TEXT)
RETURNS TEXT AS $$
DECLARE
BEGIN
  RETURN CASE WHEN val IN (
                  'bangunan',
                  'building',
                  'other',
                  'rumah',
                  'Rumah',
                  'Rumah Masyarakat',
                  'rumah_penduduk',
                  'true',
                  'trullo',
                  'yes'
              ) THEN NULL
              WHEN val IN (
                  'abandoned',
                  'administrative',
                  'agricultural',
                  'airport',
                  'allotment_house',
                  'apartments',
                  'arbour',
                  'bank',
                  'barn',
                  'basilica',
                  'beach_hut',
                  'bell_tower',
                  'boathouse',
                  'brewery',
                  'bridge',
                  'bungalow',
                  'bunker',
                  'cabin',
                  'carport',
                  'castle',
                  'cathedral',
                  'chapel',
                  'chimney',
                  'church',
                  'civic',
                  'clinic',
                  'clubhouse',
                  'collapsed',
                  'college',
                  'commercial',
                  'construction',
                  'container',
                  'convent',
                  'cowshed',
                  'dam',
                  'damaged',
                  'depot',
                  'destroyed',
                  'detached',
                  'disused',
                  'dormitory',
                  'duplex',
                  'factory',
                  'farm',
                  'farm_auxiliary',
                  'fire_station',
                  'garage',
                  'garages',
                  'gazebo',
                  'ger',
                  'glasshouse',
                  'government',
                  'grandstand',
                  'greenhouse',
                  'hangar',
                  'healthcare',
                  'hermitage',
                  'hospital',
                  'hotel',
                  'house',
                  'houseboat',
                  'hut',
                  'industrial',
                  'kindergarten',
                  'kiosk',
                  'library',
                  'mall',
                  'manor',
                  'manufacture',
                  'mobile_home',
                  'monastery',
                  'mortuary',
                  'mosque',
                  'museum',
                  'office',
                  'outbuilding',
                  'parking',
                  'pavilion',
                  'power',
                  'prison',
                  'proposed',
                  'pub',
                  'public',
                  'residential',
                  'restaurant',
                  'retail',
                  'roof',
                  'ruin',
                  'ruins',
                  'school',
                  'semidetached_house',
                  'service',
                  'shed',
                  'shelter',
                  'shop',
                  'shrine',
                  'silo',
                  'slurry_tank',
                  'stable',
                  'stadium',
                  'static_caravan',
                  'storage',
                  'storage_tank',
                  'store',
                  'substation',
                  'summer_cottage',
                  'summer_house',
                  'supermarket',
                  'synagogue',
                  'tank',
                  'temple',
                  'terrace',
                  'tower',
                  'train_station',
                  'transformer_tower',
                  'transportation',
                  'university',
                  'utility',
                  'veranda',
                  'warehouse',
                  'wayside_shrine',
                  'works'
              ) THEN val
              WHEN val = 'barne' THEN 'barn'
              WHEN val = 'commercial;residential' THEN 'mixed_use'
              WHEN val = 'constructie' THEN 'construction'
              WHEN val = 'dwelling_house' THEN 'house'
              WHEN val = 'education' THEN 'school'
              WHEN val = 'greenhouse_horticulture' THEN 'greenhouse'
              WHEN val IN ('apartment', 'flat') THEN 'apartments'
              WHEN val IN ('houses', 'residences', 'residence', 'perumahan permukiman', 'residentiel1') THEN 'residential'
              WHEN val IN ('semi_detached', 'semi-detached', 'semi') THEN 'semidetached_house'
              WHEN val = 'offices' THEN 'office'
              WHEN val = 'prefab_container' THEN 'container'
              WHEN val = 'public_building' THEN 'public'
              WHEN val = 'railway_station' THEN 'train_station'
              WHEN val = 'roof=permanent' THEN 'roof'
              WHEN val = 'stables' THEN 'stable'
              WHEN val = 'static caravan' THEN 'static_caravan'
              WHEN val = 'station' THEN 'transportation'
              WHEN val = 'storage tank' THEN 'storage_tank'
              WHEN val = 'townhome' THEN 'terrace'
              ELSE NULL END;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

CREATE OR REPLACE FUNCTION mz_building_part_kind_detail(val TEXT)
RETURNS TEXT AS $$
DECLARE
BEGIN
  RETURN CASE WHEN val IN ('yes', 'part', 'church:part', 'default') THEN NULL
              WHEN val IN (
                'arch',
                'balcony',
                'base',
                'column',
                'door',
                'elevator',
                'entrance',
                'floor',
                'hall',
                'main',
                'passageway',
                'pillar',
                'porch',
                'ramp',
                'roof',
                'room',
                'steps',
                'stilobate',
                'tier',
                'tower',
                'verticalpassage',
                'wall',
                'window'
              ) THEN val
              WHEN val IN ('corridor', 'Corridor', 'vertical', 'verticalpassage') THEN 'verticalpassage'
              WHEN val IN ('stairs', 'stairway') THEN 'steps'
              ELSE NULL END;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

--- Identifies and returns the min_zoom for gates
--- given the highway level of gate location
-- NOTE: the dummy integer is just to make the "shape" of the arguments the same
-- between SQL and Python, since the latter needs to be passed ways which
-- SQL just looks up in the `planet_osm_ways` table.
CREATE OR REPLACE FUNCTION mz_get_min_zoom_highway_level_gate(val_osm_id BIGINT, dummy INTEGER)
RETURNS SMALLINT AS $$
BEGIN
  RETURN MAX(CASE
    WHEN highway IN ('motorway', 'trunk', 'primary', 'motorway_link',
                     'trunk_link', 'primary_link') THEN 14
    WHEN highway IN ('secondary', 'tertiary', 'secondary_link',
                     'tertiary_link') THEN 15
    WHEN highway IN ('residential', 'service', 'path', 'track', 'footway', 'unclassified') THEN 16
    ELSE 17
  END) AS highway_level FROM (
  SELECT
  p.osm_id,
  hstore(w.tags)->'highway' AS highway
  FROM planet_osm_point p
  JOIN planet_osm_ways w
  ON ARRAY[p.osm_id] && w.nodes
  WHERE
      p.osm_id = val_osm_id
    ) x
        GROUP BY osm_id;
END;
$$ LANGUAGE plpgsql STABLE;

-- calculates the building height given the footprint area and the tags, which
-- provide either the height or the number of levels to approximate the height.
CREATE OR REPLACE FUNCTION mz_calculate_building_volume(way_area REAL, tags hstore)
RETURNS REAL AS $$
BEGIN
  RETURN mz_building_height(tags->'height', tags->'building:levels') * way_area;
END
$$ LANGUAGE plpgsql IMMUTABLE;

-- removes the leading 'SH' in the label text.
-- this is used for removing that prefix from the refs of New Zealand highways
-- (perhaps SH = State Highway?) because we put the 'SH' as part of the network
-- and want to keep a numeric ref.
CREATE OR REPLACE FUNCTION trim_nz_sh(label TEXT)
RETURNS TEXT AS $$
BEGIN
  RETURN trim(leading 'SH' from label);
END
$$ LANGUAGE plpgsql IMMUTABLE;

-- returns a JSONB object containing __ne_min_zoom and __ne_max_zoom set to the
-- label min and max zoom of any matching row from the Natural Earth countries,
-- map units and states/provinces themes.
CREATE OR REPLACE FUNCTION tz_get_ne_min_max_zoom(wikidata_id TEXT, place_tag TEXT)
RETURNS JSONB AS $$
DECLARE
  min_zoom REAL;
  max_zoom REAL;
BEGIN
  IF wikidata_id IS NULL THEN
    RETURN '{}'::jsonb;
  END IF;

  -- if it's a country, only look it up in the iso and tlc tables
  IF place_tag='country' OR place_tag='unrecognized' THEN
      SELECT
        min_label, max_label INTO min_zoom, max_zoom
        FROM ne_10m_admin_0_countries_iso i
        WHERE i.wikidataid = wikidata_id;

      IF NOT FOUND THEN
        SELECT
            min_label, max_label INTO min_zoom, max_zoom
        FROM ne_10m_admin_0_countries_tlc t
        WHERE t.wikidataid = wikidata_id
        AND featurecla IN ('Admin-0 country', 'Admin-0 dependency');
      END IF;
  ELSE
    -- try states and provinces if it's not a country
    SELECT
        min_label, max_label INTO min_zoom, max_zoom
    FROM ne_10m_admin_1_states_provinces sp
    WHERE sp.wikidataid = wikidata_id;

    -- finally, try localities
    -- There is no concept of max_zoom for ne_10m_populated_places
    IF NOT FOUND THEN
        SELECT
            pp.min_zoom, NULL INTO min_zoom, max_zoom
        FROM ne_10m_populated_places pp
        WHERE pp.wikidataid = wikidata_id;
    END IF;
  END IF;
  -- return an empty JSONB rather than null, so that it can be safely
  -- concatenated with whatever other JSONB rather than needing a check for
  -- null.
  IF NOT FOUND THEN
    RETURN '{}'::jsonb;
  END IF;
  RETURN jsonb_build_object(
    '__ne_min_zoom', min_zoom,
    '__ne_max_zoom', max_zoom
  );
END;
$$ LANGUAGE plpgsql STABLE;

CREATE OR REPLACE FUNCTION tz_get_fclass_and_label_position(wikidata_id TEXT, place_tag TEXT)
RETURNS JSONB AS $$
DECLARE
fclass_iso_var TEXT;
fclass_tlc_var TEXT;
label_x_var REAL;
label_y_var REAL;
BEGIN
  IF wikidata_id IS NULL THEN
    RETURN '{}'::jsonb;
END IF;

  -- if it's a country, only look it up in the iso and tlc tables
  IF place_tag='country' OR place_tag='unrecognized' THEN
    SELECT
        i.fclass_iso, i.fclass_tlc, i.label_x, i.label_y INTO fclass_iso_var, fclass_tlc_var, label_x_var, label_y_var
    FROM ne_10m_admin_0_countries_iso i
    WHERE i.wikidataid = wikidata_id;

    IF NOT FOUND THEN
        SELECT
            t.fclass_iso, t.fclass_tlc, t.label_x, t.label_y INTO fclass_iso_var, fclass_tlc_var, label_x_var, label_y_var
        FROM ne_10m_admin_0_countries_tlc t
        WHERE t.wikidataid = wikidata_id
        AND featurecla IN ('Admin-0 country', 'Admin-0 dependency');
    END IF;

    IF NOT FOUND THEN
        RETURN '{}'::jsonb;
    END IF;
    RETURN jsonb_build_object(
        '__ne_fclass_iso', fclass_iso_var,
        '__ne_fclass_tlc', fclass_tlc_var,
        '__ne_label_x', label_x_var,
        '__ne_label_y', label_y_var
    );
  END IF;

  SELECT
    sp.fclass_iso, sp.fclass_tlc, sp.longitude, sp.latitude INTO fclass_iso_var, fclass_tlc_var, label_x_var, label_y_var
  FROM ne_10m_admin_1_states_provinces sp
  WHERE sp.wikidataid = wikidata_id;

  -- finally, try localities
  IF NOT FOUND THEN
      SELECT
        pp.fclass_iso, pp.fclass_tlc, pp.longitude, pp.latitude INTO fclass_iso_var, fclass_tlc_var, label_x_var, label_y_var
      FROM ne_10m_populated_places pp
      WHERE pp.wikidataid = wikidata_id;
  END IF;

  -- return an empty JSONB rather than null, so that it can be safely
  -- concatenated with whatever other JSONB rather than needing a check for
  -- null.
  IF NOT FOUND THEN
    RETURN '{}'::jsonb;
  END IF;
  RETURN jsonb_build_object(
        '__ne_fclass_iso', fclass_iso_var,
        '__ne_fclass_tlc', fclass_tlc_var,
        '__ne_label_x', label_x_var,
        '__ne_label_y', label_y_var
    );
END;
$$ LANGUAGE plpgsql STABLE;


-- returns a JSONB object containing __ne_pop_min and __ne_pop_max
CREATE OR REPLACE FUNCTION tz_get_ne_pop_min_max(wikidata_id TEXT)
RETURNS JSONB AS $$
DECLARE
  pop_min REAL;
  pop_max REAL;
BEGIN
  IF wikidata_id IS NULL THEN
    RETURN '{}'::jsonb;
  END IF;

  SELECT
    pp.pop_min, pp.pop_max INTO pop_min, pop_max
    FROM ne_10m_populated_places pp
    WHERE pp.wikidataid = wikidata_id;

  -- return an empty JSONB rather than null, so that it can be safely
  -- concatenated with whatever other JSONB rather than needing a check for
  -- null.
  IF NOT FOUND THEN
    RETURN '{}'::jsonb;
  END IF;
  RETURN jsonb_build_object(
    '__ne_pop_min', pop_min,
    '__ne_pop_max', pop_max
  );
END;
$$ LANGUAGE plpgsql STABLE;

-- return the min zoom for a node that looks like a service area.
CREATE OR REPLACE FUNCTION tz_looks_like_service_area(name TEXT)
RETURNS INTEGER AS $$
BEGIN
  IF name ILIKE '%service area' OR name ILIKE '%services' OR name ILIKE '%travel plaza' THEN
    RETURN 13;
  END IF;
  RETURN 17;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- return the min zoom for a node that looks like a rest area.
CREATE OR REPLACE FUNCTION tz_looks_like_rest_area(name TEXT)
RETURNS INTEGER AS $$
BEGIN
  IF name ILIKE '%rest area' THEN
    RETURN 13;
  END IF;
  RETURN 17;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- return the capacity of a parking lot, or estimate it from the area and building levels.
CREATE OR REPLACE FUNCTION tz_estimate_parking_capacity(capacity TEXT, parking TEXT, levels TEXT, way_area REAL)
RETURNS INTEGER AS $$
DECLARE
  levels_int INTEGER;
  spaces_per_level INTEGER;
BEGIN
  -- if the capacity is set to something between 0 and 99999, then use that.
  IF capacity ~ '^[0-9]{1,5}$' THEN
    RETURN capacity::integer;
  END IF;
  -- don't try to do this if way_area is abnormally large.
  -- (Epcot's parking lot is 647k m^2)
  IF way_area > 2000000 THEN
    RETURN NULL;
  END IF;
  -- otherwise, try to use the information we have to guess the capacity
  spaces_per_level := (way_area / 46.0)::integer;
  levels_int := CASE
    -- limit levels to an integer between 0 and 99
    WHEN levels ~ '^[0-9]{1,2}$' THEN levels::integer
    WHEN parking = 'multi-storey' THEN 2
    ELSE 1
  END;
  -- if we get a silly answer, don't set that - just return NULL to indicate
  -- that we're unsure.
  IF levels_int * spaces_per_level > 0 THEN
    RETURN levels_int * spaces_per_level;
  ELSE
    RETURN NULL;
  END IF;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- try to convert a string into an integer, returning null if that fails.
CREATE OR REPLACE FUNCTION tz_safe_int(t text)
RETURNS INTEGER AS $$
DECLARE
  val INTEGER DEFAULT NULL;
BEGIN
  BEGIN
    val := (t)::INTEGER;
  EXCEPTION WHEN OTHERS THEN
    RETURN NULL;
  END;
  RETURN val;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- get extra wikidata properties matched to an OSM feature on the wikidata ID
CREATE OR REPLACE FUNCTION extra_wikidata_properties(wikidata_id text)
RETURNS JSONB AS $$
DECLARE
  tags HSTORE DEFAULT NULL;
  ne_tags HSTORE DEFAULT NULL;
BEGIN
  IF wikidata_id IS NOT NULL THEN
    SELECT w.tags INTO tags FROM wikidata w WHERE w.id = wikidata_id;
    SELECT hstore(ARRAY[
      'featurecla', ne_pp.featurecla,
      'fclass_iso', ne_pp.fclass_iso,
      'fclass_ar', ne_pp.fclass_ar,
      'fclass_bd', ne_pp.fclass_bd,
      'fclass_br', ne_pp.fclass_br,
      'fclass_cn', ne_pp.fclass_cn,
      'fclass_de', ne_pp.fclass_de,
      'fclass_eg', ne_pp.fclass_eg,
      'fclass_es', ne_pp.fclass_es,
      'fclass_fr', ne_pp.fclass_fr,
      'fclass_gb', ne_pp.fclass_gb,
      'fclass_gr', ne_pp.fclass_gr,
      'fclass_id', ne_pp.fclass_id,
      'fclass_il', ne_pp.fclass_il,
      'fclass_in', ne_pp.fclass_in,
      'fclass_it', ne_pp.fclass_it,
      'fclass_jp', ne_pp.fclass_jp,
      'fclass_ko', ne_pp.fclass_ko,
      'fclass_ma', ne_pp.fclass_ma,
      'fclass_nl', ne_pp.fclass_nl,
      'fclass_np', ne_pp.fclass_np,
      'fclass_pk', ne_pp.fclass_pk,
      'fclass_pl', ne_pp.fclass_pl,
      'fclass_ps', ne_pp.fclass_ps,
      'fclass_pt', ne_pp.fclass_pt,
      'fclass_ru', ne_pp.fclass_ru,
      'fclass_sa', ne_pp.fclass_sa,
      'fclass_se', ne_pp.fclass_se,
      'fclass_tr', ne_pp.fclass_tr,
      'fclass_tw', ne_pp.fclass_tw,
      'fclass_us', ne_pp.fclass_us,
      'fclass_vn', ne_pp.fclass_vn
    ]) INTO ne_tags FROM ne_10m_populated_places ne_pp
    WHERE ne_pp.wikidataid = wikidata_id;
  END IF;
  RETURN to_jsonb(coalesce(tags, ''::hstore) || coalesce(ne_tags, ''::hstore));
END;
$$ LANGUAGE plpgsql STABLE;