maps/backend/initdb/02_tile_functions.sql

-- Zoom-dependent tile functions for Martin.
-- These apply geometry simplification and feature filtering so that
-- low-zoom tiles contain fewer, simpler features.

--------------------------------------------------------------------------------
-- planet_osm_polygon
--------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION tile_polygon(z integer, x integer, y integer)
RETURNS bytea AS $$
DECLARE
  bounds geometry;
  tolerance double precision;
  result bytea;
BEGIN
  bounds := ST_TileEnvelope(z, x, y);

  -- Simplification tolerance scales with zoom: lower zoom = more aggressive
  tolerance := CASE
    WHEN z >= 13 THEN 0        -- full detail
    WHEN z >= 11 THEN 1        -- light simplification
    WHEN z >= 9  THEN 10       -- moderate
    ELSE 50                     -- heavy
  END;

  WITH features AS (
    SELECT
      osm_id,
      name,
      building,
      landuse,
      "natural",
      amenity,
      leisure,
      water,
      waterway,
      CASE
        WHEN tolerance = 0 THEN ST_AsMVTGeom(way, bounds)
        ELSE ST_AsMVTGeom(
          ST_Simplify(way, tolerance, true),
          bounds
        )
      END AS geom
    FROM planet_osm_polygon
    WHERE way && bounds
      -- Feature filtering by zoom level:
      AND CASE
        -- zoom 8-9: only water, large forests/parks
        WHEN z < 10 THEN
          "natural" IN ('water', 'wood') OR landuse IN ('forest', 'reservoir')
        -- zoom 10-11: add more landuse
        WHEN z < 12 THEN
          "natural" IN ('water', 'wood', 'wetland', 'heath', 'scrub')
          OR landuse IN ('forest', 'reservoir', 'residential', 'industrial',
                         'commercial', 'farmland', 'meadow', 'grass')
          OR leisure IN ('park', 'nature_reserve', 'garden')
        -- zoom 12: add buildings (large only)
        WHEN z < 13 THEN
          "natural" IS NOT NULL OR landuse IS NOT NULL OR leisure IS NOT NULL
          OR water IS NOT NULL
          OR (building IS NOT NULL AND ST_Area(way) > 5000)
        -- zoom 13+: everything
        ELSE true
      END
  )
  SELECT ST_AsMVT(features, 'planet_osm_polygon') INTO result FROM features;

  RETURN result;
END;
$$ LANGUAGE plpgsql STABLE PARALLEL SAFE;

--------------------------------------------------------------------------------
-- planet_osm_line
--------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION tile_line(z integer, x integer, y integer)
RETURNS bytea AS $$
DECLARE
  bounds geometry;
  tolerance double precision;
  result bytea;
BEGIN
  bounds := ST_TileEnvelope(z, x, y);

  tolerance := CASE
    WHEN z >= 13 THEN 0
    WHEN z >= 11 THEN 5
    ELSE 20
  END;

  WITH features AS (
    SELECT
      osm_id,
      name,
      highway,
      railway,
      waterway,
      CASE
        WHEN tolerance = 0 THEN ST_AsMVTGeom(way, bounds)
        ELSE ST_AsMVTGeom(
          ST_Simplify(way, tolerance, true),
          bounds
        )
      END AS geom
    FROM planet_osm_line
    WHERE way && bounds
      AND CASE
        -- zoom 10-11: only major roads and rivers
        WHEN z < 12 THEN
          highway IN ('motorway', 'trunk', 'primary', 'secondary')
          OR railway IN ('rail')
          OR waterway IN ('river', 'canal')
        -- zoom 12: add tertiary roads
        WHEN z < 13 THEN
          highway IN ('motorway', 'trunk', 'primary', 'secondary',
                      'tertiary', 'motorway_link', 'trunk_link')
          OR railway IS NOT NULL
          OR waterway IS NOT NULL
        -- zoom 13+: everything
        ELSE true
      END
  )
  SELECT ST_AsMVT(features, 'planet_osm_line') INTO result FROM features;

  RETURN result;
END;
$$ LANGUAGE plpgsql STABLE PARALLEL SAFE;

--------------------------------------------------------------------------------
-- planet_osm_point
--------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION tile_point(z integer, x integer, y integer)
RETURNS bytea AS $$
DECLARE
  bounds geometry;
  result bytea;
BEGIN
  bounds := ST_TileEnvelope(z, x, y);

  WITH features AS (
    SELECT
      osm_id,
      name,
      place,
      amenity,
      shop,
      tourism,
      "natural",
      ST_AsMVTGeom(way, bounds) AS geom
    FROM planet_osm_point
    WHERE way && bounds
      AND CASE
        -- zoom 12: only cities/towns/villages and major POIs
        WHEN z < 13 THEN
          place IN ('city', 'town', 'village')
        -- zoom 13: add hamlets and named POIs
        WHEN z < 14 THEN
          place IS NOT NULL
          OR (name IS NOT NULL AND (amenity IS NOT NULL OR tourism IS NOT NULL))
        -- zoom 14+: everything
        ELSE true
      END
  )
  SELECT ST_AsMVT(features, 'planet_osm_point') INTO result FROM features;

  RETURN result;
END;
$$ LANGUAGE plpgsql STABLE PARALLEL SAFE;

--------------------------------------------------------------------------------
-- planet_osm_roads
--------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION tile_roads(z integer, x integer, y integer)
RETURNS bytea AS $$
DECLARE
  bounds geometry;
  tolerance double precision;
  result bytea;
BEGIN
  bounds := ST_TileEnvelope(z, x, y);

  tolerance := CASE
    WHEN z >= 10 THEN 0
    WHEN z >= 8  THEN 10
    ELSE 50
  END;

  WITH features AS (
    SELECT
      osm_id,
      name,
      highway,
      ref,
      CASE
        WHEN tolerance = 0 THEN ST_AsMVTGeom(way, bounds)
        ELSE ST_AsMVTGeom(
          ST_Simplify(way, tolerance, true),
          bounds
        )
      END AS geom
    FROM planet_osm_roads
    WHERE way && bounds
      AND CASE
        -- zoom 6-7: motorways only
        WHEN z < 8 THEN
          highway IN ('motorway', 'trunk')
        -- zoom 8-9: add primary roads
        WHEN z < 10 THEN
          highway IN ('motorway', 'trunk', 'primary')
        -- zoom 10+: everything in the roads table
        ELSE true
      END
  )
  SELECT ST_AsMVT(features, 'planet_osm_roads') INTO result FROM features;

  RETURN result;
END;
$$ LANGUAGE plpgsql STABLE PARALLEL SAFE;