Base

Overview

The Overture base theme includes additional features desired for rendering a complete basemap that are not yet associated with the global entity reference system (GERS), nor have they been through a rigorous schema definition process. Instead, we assign just a subtype and class to the feature and pass relevant attributes through in the source_tags attribute. Most of the features in the base theme come from OpenStreetMap via the Daylight Map Distribution.

Feature types

The base theme has five feature types.

• infrastructure - Human-made structures (bridges, towers, airports, etc.) from OpenStreetMap
• land - Natural land features from OpenStreetMap
• land_cover - Derived from European Space Agency (ESA) satellite imagery
• land_use - Human-determined land use features from OpenStreetMap (residential, education, etc).
• water - Water features (both natural and human-made) from OpenStreetMap. Includes the ocean as derived from the OSM Coastline Tool.

Theme concepts

OpenStreetMap Tags to Overture Properties

The subtype and class of an infrastructure, land, land_use, or water type feature in the base theme is determined by the original feature's tags on OpenStreetMap.

Below is the logic that is currently used to convert from OSM tags into Overture schema.

tip

Have a suggestion or improvement to the logic? Please start a discussion on Github

How do I interpret this query?

Each WHEN line in the CASE statement is a condition that defines both the subtype and the class of a feature. For example:

WHEN element_at(tags,'highway') = 'bus_stop' THEN ROW('transit', 'bus_stop')

Here, element_at(tags, 'highway') is accessing the value of the highway tag in OSM. So, when highway=bus_stop is present, then the statement returns ROW(subtype, class), in this case, subtype=transit and class=bus_stop.

Since this particular statement is at the top of the list, it will take priority over other tags on the feature. If the same feature was also tagged as an airport gate, those tags would be ignored.

Infrastructure

CASE
    -- Railways / Subways
    WHEN element_at(tags,'railway') IN ('station','halt') THEN CASE

        -- Railway Specific
        WHEN element_at(tags,'station') <> 'subway' AND element_at(tags, 'subway') <> 'yes' THEN ROW('transit', 'railway_station')
        
        -- Multimodal stations
        ELSE ROW('transit', 'railway_' || element_at(tags,'railway'))
    END

    -- Ferry Terminals
    WHEN element_at(tags,'amenity') = 'ferry_terminal' OR (
            element_at(tags,'public_transport') = 'stop_position' AND element_at(tags,'ferry') = 'yes' 
        ) THEN ROW('transit','ferry_terminal')

    -- Transit Stops
    WHEN element_at(tags,'highway') = 'bus_stop' THEN ROW('transit', 'bus_stop')
    WHEN element_at(tags,'route') = 'bus' THEN ROW('transit', 'bus_route')
    WHEN element_at(tags,'amenity') = 'bus_station' THEN ROW('transit', 'bus_station')
    WHEN element_at(tags,'public_transport') IN ('stop_position', 'platform') THEN ROW('transit', element_at(tags,'public_transport'))

    -- Parking
    WHEN element_at(tags,'amenity') IN (
        'parking',
        'parking_space',
        'bicycle_parking'
    ) THEN ROW('transit', element_at(tags,'amenity'))

    -- Aerialways (Linestrings)
    WHEN ST_GEOMETRYTYPE(ST_GeomFromBinary(geometry)) = 'ST_LineString' AND element_at(tags,'aerialway') IN (
        'cable_car',
        'chair_lift',
        'drag_lift',
        'gondola',
        'mixed_lift',
        't-bar'
    ) THEN ROW('aerialway', element_at(tags,'aerialway'))
    
    -- Pylons are points
    WHEN ST_GEOMETRYTYPE(ST_GeomFromBinary(geometry)) = 'ST_Point' AND element_at(tags,'aerialway') = 'pylon' THEN ROW('aerialway', 'pylon')

    -- Stations are nodes/ways
    WHEN element_at(tags,'aerialway') = 'station' THEN ROW('aerialway', 'aerialway_station')

    -- Airports (Polygons)
    WHEN ST_GEOMETRYTYPE(ST_GeomFromBinary(geometry)) IN ('ST_Polygon', 'ST_MultiPolygon') AND element_at(tags,'aeroway') IN (
        'airstrip', 
        'helipad',
        'heliport'
    ) THEN ROW('airport', element_at(tags,'aeroway'))

    -- Airports (LineStrings)
    WHEN ST_GEOMETRYTYPE(ST_GeomFromBinary(geometry)) = 'ST_LineString' AND element_at(tags, 'aeroway') IN (
        'runway',
        'taxiway'
    ) THEN ROW('airport', element_at(tags,'aeroway'))

    -- Airports (Points)
    WHEN ST_GEOMETRYTYPE(ST_GeomFromBinary(geometry)) = 'ST_Point' AND element_at(tags,'aeroway') IN (
        'airstrip',
        'helipad'
    ) THEN ROW('airport', element_at(tags,'aeroway'))

    WHEN ST_GEOMETRYTYPE(ST_GeomFromBinary(geometry)) = 'ST_Point' AND element_at(tags,'aeroway') = 'gate'
        THEN ROW('airport', 'airport_gate')

    WHEN element_at(tags,'aeroway') = 'aerodrome' THEN CASE
        WHEN element_at(tags,'aerodrome:type') = 'military' OR element_at(tags,'landuse') = 'military' OR element_at(tags,'military') IN (
            'airfield'
        ) THEN ROW('airport','military_airport')
        WHEN element_at(tags,'access') IN ('emergency', 'no', 'permissive', 'private')
            OR element_at(tags,'aerodrome:type') = 'private' THEN ROW('airport','private_airport')
        WHEN lower(element_at(tags,'name')) LIKE '%international%' OR element_at(tags,'aerodrome:type') = 'international'
            OR element_at(tags,'aerodrome') = 'international' THEN ROW('airport','international_airport')
        WHEN lower(element_at(tags,'name')) LIKE '%regional%' OR element_at(tags,'aerodrome:type') = 'regional'
            THEN ROW('airport','regional_airport')
        WHEN lower(element_at(tags,'name')) LIKE '%municipal%' THEN ROW('airport','municipal_airport')
        WHEN lower(element_at(tags,'name')) LIKE '%seaplane%' THEN ROW('airport','seaplane_airport')
        WHEN lower(element_at(tags,'name')) LIKE '%heli%' THEN ROW('airport','heliport')
        ELSE ROW('airport','airport')
    END

    -- Bridges
    WHEN element_at(tags,'bridge') IN (
        'aqueduct',
        'boardwalk',
        'cantilever',
        'covered',
        'movable',
        'trestle',
        'viaduct'
    ) THEN ROW('bridge', element_at(tags,'bridge'))
    WHEN element_at(tags,'bridge:support') IS NOT NULL THEN
        ROW('bridge', 'bridge_support')

    -- Communication
    WHEN element_at(tags,'communication:mobile_phone') <> 'no' THEN ROW('communication','mobile_phone_tower')
    WHEN element_at(tags,'communication') IN ('line','pole') THEN ROW('communication','communication_' || element_at(tags,'communication'))
    WHEN element_at(tags,'tower:type') = 'communication' THEN ROW('communication','communication_tower')

    -- Pedestrian
    WHEN element_at(tags,'highway') IS NULL AND element_at(tags,'footway') IN ('crossing') AND 
        ST_GEOMETRYTYPE(ST_GeomFromBinary(geometry)) IN ('ST_Polygon','ST_MultiPolygon') THEN ROW('pedestrian','pedestrian_crossing')
    WHEN element_at(tags,'tourism') IN ('information', 'viewpoint') THEN ROW('pedestrian', element_at(tags,'tourism'))
    WHEN element_at(tags,'amenity') IN (
        'atm',
        'bench',
        'picnic_table',
        'post_box',
        'toilets',
        'vending_machine'
    ) THEN ROW('pedestrian', element_at(tags,'amenity'))

    -- Manholes
    WHEN element_at(tags,'manhole') IN ('drain', 'sewer') THEN ROW('manhole', element_at(tags,'manhole'))
    WHEN element_at(tags,'manhole') IS NOT NULL THEN ROW('manhole','manhole')

    -- Power
    WHEN element_at(tags,'power') IN (
        'cable_distribution',
        'cable',
        'catenary_mast',
        'connection',
        'generator',
        'heliostat',
        'insulator',
        'minor_line',
        'plant',
        'portal',
        'sub_station',
        'substation',
        'switch',
        'terminal',
        'transformer'
    ) THEN ROW('power', element_at(tags,'power'))

    WHEN element_at(tags,'power') IN ('line', 'pole', 'tower') THEN ROW('power','power_' || element_at(tags,'power'))

    -- Recreation
    WHEN element_at(tags,'tourism') = ('camp_site') AND ST_GEOMETRYTYPE(ST_GeomFromBinary(geometry)) = 'ST_Point' THEN ROW('recreation','camp_site')

    -- Towers
    WHEN element_at(tags,'tower:type') IN (
        'bell_tower',
        'cooling',
        'defensive',
        'diving',
        'hose',
        'lighting',
        'lightning_protection',
        'minaret',
        'monitoring',
        'observation',
        'radar',
        'siren',
        'watchtower'
    ) THEN ROW('tower', element_at(tags,'tower:type'))

    -- Utility / human made made containers
    WHEN element_at(tags,'man_made') IN (
        'pipeline',
        'silo',
        'storage_tank', 
        'utility_pole',
        'water_tower'
    ) THEN ROW('utility', element_at(tags,'man_made'))

    -- Waste Management
    WHEN element_at(tags,'amenity') IN(
        'recycling',
        'waste_basket',
        'waste_disposal'
    ) THEN ROW('waste_management',element_at(tags,'amenity'))

    --Water
    WHEN element_at(tags,'man_made') IN ('dam') THEN ROW('water',element_at(tags,'man_made'))
    WHEN element_at(tags,'waterway') IN ('dam','weir') THEN ROW('water', element_at(tags,'waterway'))
    WHEN element_at(tags,'amenity') = ('drinking_water') AND
        (element_at(tags,'drinking_water') IS NULL OR element_at(tags,'drinking_water') <> 'no') AND
        (element_at(tags,'access') IS NULL OR element_at(tags,'access') <> 'private')
        THEN ROW('water', 'drinking_water')


    -- Standalone piers
    WHEN element_at(tags,'man_made') IN ('pier') THEN ROW('pier','pier')


    -- Barrier tags are often secondary on other features, so put them last.
    -- Barrier tags that are not allowed on points:
    WHEN ST_GEOMETRYTYPE(ST_GeomFromBinary(geometry)) <> 'ST_Point' AND element_at(tags,'barrier') IN (
        'cable_barrier',
        'city_wall',
        'chain',
        'ditch',
        'fence',
        'guard_rail',
        'handrail',
        'hedge',
        'jersey_barrier',
        'kerb',
        'retaining_wall',
        'wall'
    ) THEN ROW('barrier', element_at(tags,'barrier'))

    -- Points allowed on these types of barriers:
    WHEN element_at(tags,'barrier') IN (
        'block',
        'bollard',
        'border_control',
        'bump_gate',
        'bus_trap',
        'cattle_grid',
        'cycle_barrier',
        'chain',
        'entrance',
        'full-height_turnstile',
        'gate',
        'hampshire_gate',
        'height_restrictor',
        'jersey_barrier',
        'kerb',
        'kissing_gate',
        'lift_gate',
        'planter',
        'sally_port',
        'stile',
        'swing_gate',
        'toll_booth'
    ) THEN ROW('barrier', element_at(tags,'barrier'))
    WHEN element_at(tags,'man_made') IN ('cutline') THEN ROW('barrier','cutline')

    -- If there remains a barrier tag but it's not in the above list:
    WHEN element_at(tags,'barrier') IS NOT NULL THEN ROW('barrier','barrier')

    -- Lower priority generic `bridge` tags
    WHEN element_at(tags,'man_made') = 'bridge' THEN ROW('bridge','bridge')
    WHEN element_at(tags,'bridge') = 'yes' THEN ROW('bridge','bridge')
END

Land

CASE
    -- Desert
    WHEN element_at(tags,'natural') IN ('desert') THEN ROW('desert', element_at(tags,'natural'))

    -- Wetland
    WHEN element_at(tags,'natural') IN ('wetland') THEN ROW('wetland', element_at(tags,'natural'))

    -- Glacier
    WHEN element_at(tags,'natural') IN ('glacier') THEN ROW('glacier', element_at(tags,'natural'))

    -- Rock
    WHEN element_at(tags,'natural') IN (
        'bare_rock',
        'rock',
        'scree',
        'shingle',
        'stone'
    ) THEN ROW('rock', element_at(tags,'natural'))

    -- Sand
    WHEN element_at(tags,'natural') IN ('beach', 'dune', 'sand') THEN ROW('sand', element_at(tags,'natural'))

    -- Grass
    WHEN element_at(tags,'natural') IN (
        'fell',
        'grass',
        'grassland',
        'meadow',
        'tundra'
    ) THEN ROW('grass', element_at(tags,'natural'))
    WHEN element_at(tags,'landcover') IN ('grass') THEN ROW ('grass', element_at(tags,'landcover'))

    -- Shrub / Scrub
    WHEN element_at(tags,'natural') IN (
        'heath',
        'shrub',
        'shrubbery',
        'scrub'
    ) THEN ROW('shrub',element_at(tags,'natural'))
    WHEN element_at(tags,'landcover') IN ('scrub') THEN ROW('shrub', element_at(tags,'landcover'))

    -- Reefs
    WHEN element_at(tags,'natural') IN ('reef') THEN ROW('reef', element_at(tags,'natural'))

    -- Forest
    WHEN element_at(tags,'natural') IN ('forest', 'wood') THEN ROW('forest', element_at(tags,'natural'))
    WHEN element_at(tags,'landcover') IN ('trees') THEN ROW('forest', 'forest')
    WHEN element_at(tags,'landuse') IN ('forest') THEN ROW('forest','forest')

    -- Single trees / tree rows
    WHEN element_at(tags,'natural') IN ('tree') THEN ROW('tree','tree')
    WHEN element_at(tags,'natural') IN ('tree_row') THEN ROW('tree','tree_row')

    -- Physical Subtype
    WHEN element_at(tags,'natural') IN(
        'cave_entrance',
        'cliff',
        'hill',
        'mountain_range',
        'peak',
        'peninsula',
        'ridge',
        'saddle',
        'valley'
    ) THEN ROW('physical', element_at(tags,'natural'))

    -- Volcanoes
    WHEN element_at(tags,'natural') = 'volcano' THEN IF(
        element_at(tags,'type') = 'extinct' OR element_at(tags,'volcano:status') = 'extinct',
        ROW ('physical','peak'),
        ROW('physical','volcano')
    )

    -- Archipelagos, Islands & Islets
    WHEN element_at(tags,'place') IN (
        'archipelago',
        'island',
        'islet'
    ) THEN ROW('land',element_at(tags,'place'))

    -- Look at surface tag now
    WHEN element_at(tags,'surface') IN ('grass') THEN ROW('grass','grass')

    ELSE ROW(NULL, NULL)
END

Land Use

CASE
    -- Piste types
    WHEN ST_GeometryType(ST_GeomFromBinary(geometry)) <> 'ST_Point' AND element_at(tags,'piste:type') IN (
        'playground'
    ) THEN ROW ('winter_sports', element_at(tags,'piste:type'))

    -- Polygons
    WHEN ST_GeometryType(ST_GeomFromBinary(geometry)) IN ('ST_Polygon', 'ST_MultiPolygon') THEN CASE

        -- Military Specific Landuses
        WHEN element_at(tags,'military') IN (
            'airfield',
            'barracks',
            'base',
            'danger_area',
            'naval_base',
            'nuclear_explosion_site',
            'obstacle_course',
            'range',
            'training_area',
            'trench'
        ) THEN ROW('military', element_at(tags,'military'))

        -- Other general military landuse
        WHEN (element_at(tags,'military') <> 'no' OR element_at(tags,'landuse') = 'military') AND element_at(tags,'aeroway') IS NULL THEN ROW('military', 'military')

        -- Residential
        WHEN element_at(tags,'landuse') IN ('residential', 'static_caravan', 'garages') THEN ROW('residential', element_at(tags,'landuse'))

        -- Entertainment
        WHEN element_at(tags,'tourism') IN (
            'zoo',
            'theme_park'
        ) THEN ROW('entertainment', element_at(tags,'tourism'))
        WHEN element_at(tags,'leisure') IN (
            'water_park'
        ) THEN ROW('entertainment', element_at(tags,'leisure'))

        -- Give National Parks top priority since it might have other tags.
        WHEN element_at(tags,'boundary') = 'national_park' THEN ROW('protected','national_park')

        -- Aboriginal Lands & Reservations
        WHEN element_at(tags, 'boundary') IN ('aboriginal_lands') OR (
            element_at(tags, 'boundary') = 'protected_area' AND element_at(tags, 'protect_class') = '24'
        ) THEN ROW('protected', 'aboriginal_land')

        -- Pedestrian land use, such as plazas
        WHEN element_at(tags, 'place') = 'square' THEN ROW('pedestrian', 'plaza')
        WHEN element_at(tags, 'highway') = 'pedestrian' THEN ROW('pedestrian', 'pedestrian')

        -- Is there is an official Protect Class Designation (wiki.openstreetmap.org/wiki/Key:protect_class)?
        WHEN element_at(tags, 'protect_class') = '1a' THEN ROW('protected', 'strict_nature_reserve')
        WHEN element_at(tags, 'protect_class') IN ('1b', '1') THEN ROW('protected', 'wilderness_area')
        WHEN element_at(tags, 'protect_class') = '2' THEN ROW('protected', 'national_park')
        WHEN element_at(tags, 'protect_class') = '3' THEN ROW('protected', 'natural_monument')
        WHEN element_at(tags, 'protect_class') = '4' THEN ROW('protected', 'species_management_area')
        WHEN element_at(tags, 'protect_class') = '5' THEN ROW('protected', 'protected_landscape_seascape')
        WHEN element_at(tags, 'protect_class') = '6' THEN ROW('protected', 'nature_reserve')

        WHEN element_at(tags, 'boundary') = 'protected_area' THEN CASE
            WHEN LOWER(element_at(tags, 'protection_title')) IN ('national forest', 'state forest')
                THEN ROW('protected', 'forest')
            WHEN LOWER(element_at(tags, 'protection_title')) IN ('national park', 'parque nacional', 'national_park')
                THEN ROW('protected', 'national_park')
            WHEN LOWER(element_at(tags, 'protection_title')) IN ('state park') THEN ROW('protected','state_park')
            WHEN LOWER(element_at(tags, 'protection_title')) IN (
                'wilderness area',
                'wilderness study area'
            ) THEN ROW('protected', 'wilderness_area')
            WHEN LOWER(element_at(tags, 'protection_title')) IN ('nature reserve', 'nature refuge', 'reserva nacional')
                THEN ROW('protected', 'nature_reserve')
            WHEN LOWER(element_at(tags, 'protection_title')) IN ('environmental use')
                THEN ROW('protected', 'environmental')
            WHEN element_at(tags,'leisure') IN ('nature_reserve')
                THEN ROW('protected', element_at(tags,'leisure'))
            WHEN element_at(tags,'landuse') IS NOT NULL
                THEN ROW('protected', 'protected')
        END

        WHEN element_at(tags,'leisure') IN ('nature_reserve') THEN ROW('protected','nature_reserve')

        -- National & State Parks
        WHEN LOWER(element_at(tags, 'protection_title')) = 'national park' THEN ROW('protected', 'national_park')
        WHEN LOWER(element_at(tags, 'protection_title')) = 'state park' THEN ROW('protected', 'state_park')
        WHEN element_at(tags, 'protected_area') = 'national_park' THEN ROW('protected', 'national_park')

        -- Golf
        WHEN element_at(tags,'golf') IN (
            'bunker',
            'driving_range',
            'fairway',
            'green',
            'lateral_water_hazard',
            'rough',
            'tee',
            'water_hazard'
        )
            THEN ROW('golf', element_at(tags,'golf'))
        WHEN element_at(tags,'leisure') IN (
            'golf_course'
        ) THEN ROW('golf','golf_course')

        -- Winter Sports
        WHEN element_at(tags,'landuse') IN ('winter_sports') THEN ROW('winter_sports','winter_sports')

        -- Horticulture
        WHEN element_at(tags,'landuse') IN (
            'allotments',
            'greenhouse_horticulture',
            'flowerbed',
            'plant_nursery',
            'orchard',
            'vineyard'
        ) THEN ROW('horticulture', element_at(tags,'landuse'))
        WHEN element_at(tags,'leisure') IN (
            'garden'
        ) THEN ROW('horticulture', element_at(tags,'leisure'))

        -- Aquaculture
        WHEN element_at(tags,'landuse') IN ('aquaculture') THEN ROW('aquaculture', 'aquaculture')

        -- Education / Schoolyards
        WHEN element_at(tags, 'amenity') IN (
            'college',
            'school',
            'university'
        ) THEN ROW('education', element_at(tags, 'amenity'))
        
        WHEN element_at(tags,'landuse') = 'education' THEN ROW('education', 'education')
        
        WHEN element_at(tags,'leisure') = 'schoolyard' THEN ROW('education', 'schoolyard')

        -- Medical
        WHEN element_at(tags, 'amenity') IN (
            'clinic',
            'doctors',
            'hospital'
        ) THEN ROW('medical', element_at(tags, 'amenity'))

        -- Park
        WHEN element_at(tags,'leisure') IN (
            'dog_park',
            'park'
        ) THEN ROW('park', element_at(tags,'leisure'))
        WHEN element_at(tags,'landuse') IN ('village_green') THEN ROW('park', element_at(tags,'landuse'))

        -- Agriculture
        WHEN element_at(tags,'landuse') IN ('animal_keeping', 'farmland', 'farmyard', 'meadow')
            THEN ROW('agriculture', element_at(tags,'landuse'))
        -- Meadows can also be tagged this way:
        WHEN element_at(tags,'meadow') IN ('agricultural', 'agriculture', 'pasture')
            THEN ROW('agriculture', 'meadow')

        -- Resource extraction
        WHEN element_at(tags,'landuse') IN (
            'logging',
            'peat_cutting',
            'quarry',
            'salt_pond'
        ) THEN ROW('resource_extraction', element_at(tags,'landuse'))

        -- Campgrounds
        WHEN element_at(tags,'tourism') = 'camp_site' AND element_at(tags, 'refugee') IS NULL
            THEN ROW('campground', 'camp_site')

        -- Cemetery
        WHEN element_at(tags, 'amenity') IN ('grave_yard') THEN ROW('cemetery', 'grave_yard')
        WHEN element_at(tags,'landuse') IN ('cemetery') THEN ROW('cemetery', 'cemetery')
        WHEN element_at(tags,'landuse') IN ('grave_yard') THEN ROW('cemetery','grave_yard')

        -- Religious
        WHEN element_at(tags,'landuse') IN ('religious') THEN ROW('religious', element_at(tags,'landuse'))

        -- Recreation
        WHEN element_at(tags,'leisure') IN (
            'beach_resort',
            'marina',
            'pitch',
            'playground',
            'recreation_ground',
            'stadium',
            'track'
        ) THEN ROW('recreation', element_at(tags,'leisure'))
        WHEN element_at(tags,'landuse') IN ('recreation_ground') THEN ROW('recreation',element_at(tags,'landuse'))

        -- Landfill
        WHEN element_at(tags,'landuse') IN ('landfill') THEN ROW('landfill', 'landfill')

        -- General "developed"
        WHEN element_at(tags,'landuse') IN (
            'brownfield',
            'commercial',
            'industrial',
            'institutional',
            'retail'
        ) THEN ROW('developed', element_at(tags,'landuse'))
        WHEN element_at(tags,'man_made') = 'works' THEN ROW('developed', 'works')

        -- Construction
        WHEN element_at(tags,'landuse') IN ('construction', 'greenfield') THEN ROW('construction',element_at(tags,'landuse'))

        -- Other managed / maintained
        WHEN element_at(tags,'natural') IS NULL AND element_at(tags,'landuse') IN (
            'grass'
        ) THEN ROW('managed', element_at(tags,'landuse'))

        -- Other Landuse
        WHEN element_at(tags,'landuse') IN ('highway', 'traffic_island') THEN ROW('transportation',element_at(tags,'landuse'))
        ELSE ROW(NULL,NULL)
    END
    
    -- Linestrings
    WHEN ST_GeometryType(ST_GeomFromBinary(geometry)) = 'ST_LineString' THEN CASE
        WHEN element_at(tags,'leisure') IN ('track') THEN ROW('recreation', element_at(tags,'leisure'))
        ELSE ROW(NULL,NULL)
    END

    -- No Points allowed in landuse
    ELSE ROW(NULL,NULL)
END

Water

CASE
    -- Streams
    WHEN element_at(tags, 'waterway') IN ('stream') THEN ROW('stream', element_at(tags, 'waterway'))
    WHEN element_at(tags, 'water') IN ('stream') THEN ROW('stream', element_at(tags, 'water'))

    -- Rivers
    WHEN element_at(tags, 'waterway') IN ('river') THEN ROW('river', element_at(tags, 'waterway'))
    WHEN element_at(tags, 'water') IN ('river') THEN ROW('river', element_at(tags, 'water'))

    -- Canals
    WHEN element_at(tags, 'water') IN ('canal', 'ditch', 'moat') THEN ROW('canal', element_at(tags, 'water'))
    WHEN element_at(tags, 'waterway') IN ('canal', 'ditch') THEN ROW('canal', element_at(tags, 'waterway'))
    WHEN element_at(tags, 'water') IN ('drain') THEN ROW('canal', 'drain')
    WHEN element_at(tags, 'waterway') IN ('drain') THEN ROW('canal', 'drain')

    -- Ponds
    WHEN element_at(tags, 'water') IN ('fishpond', 'pond') THEN ROW('pond', element_at(tags, 'water'))
    WHEN element_at(tags, 'water') IN ('lake', 'reservoir')
            AND ST_GeometryType(ST_GeomFromBinary(geometry)) IN ('ST_Polygon', 'ST_MultiPolygon')
            AND ST_AREA(TO_SPHERICAL_GEOGRAPHY(ST_GeomFromBinary(geometry))) < 4000
            THEN ROW('pond','pond')

    -- Lakes
    WHEN element_at(tags, 'water') IN ('lake', 'oxbow','lagoon') THEN ROW('lake', element_at(tags, 'water'))

    -- Springs
    WHEN element_at(tags, 'natural') IN ('spring','hot_spring','geyser') THEN ROW('spring', element_at(tags, 'natural'))

    -- Tidal Channels / Fairways
    WHEN element_at(tags, 'waterway') IN ('tidal_channel', 'fairway') THEN ROW('water', element_at(tags, 'waterway'))

    -- Wastewater
    WHEN element_at(tags, 'water') IN ('wastewater') THEN ROW('water', 'wastewater')
    WHEN element_at(tags, 'reservoir_type') IN ('sewage') THEN ROW('wastewater', 'sewage')

    -- Reservoirs
    WHEN element_at(tags, 'water') IN ('reservoir', 'basin') THEN ROW('reservoir', element_at(tags, 'water'))
    WHEN element_at(tags, 'landuse') IN ('reservoir', 'basin') THEN
        CASE
            WHEN element_at(tags, 'basin') IN (
                'evaporation',
                'detention',
                'retention',
                'infiltration',
                'cooling'
            ) THEN ROW('reservoir', 'basin')
            WHEN element_at(tags, 'reservoir_type') IN ('water_storage') THEN ROW('reservoir', 'water_storage')
            ELSE ROW('reservoir', 'reservoir')
        END

    -- Physical
    WHEN element_at(tags, 'natural') IN ('bay','cape','shoal','strait') THEN ROW('physical', element_at(tags, 'natural'))
    WHEN element_at(tags, 'waterway') IN ('waterfall') THEN ROW('physical', element_at(tags, 'waterway'))

    -- Swimming Pools
    WHEN element_at(tags, 'leisure') = 'swimming_pool' AND (
        element_at(tags, 'location') IS NULL OR element_at(tags, 'location') IN ('roof','outdoor','overground','surface')
    ) THEN ROW('human_made', 'swimming_pool')

    -- Reflecting Pools
    WHEN element_at(tags, 'water') IN ('reflecting_pool') THEN ROW('human_made', 'reflecting_pool')

    -- Salt Ponds
    WHEN element_at(tags, 'landuse') IN ('salt_pond') THEN ROW('human_made', 'salt_pond')

    -- Fish pass
    WHEN element_at(tags, 'waterway') IN ('fish_pass') THEN ROW('human_made', 'fish_pass')

    -- Dock
    WHEN element_at(tags, 'waterway') = 'dock' AND element_at(tags, 'dock') <> 'drydock' THEN ROW('water', 'dock')

    -- Oceans / Seas
    WHEN element_at(tags, 'place') IN ('ocean','sea') THEN ROW('physical', element_at(tags, 'place'))

    -- Default "water"
    WHEN element_at(tags, 'natural') = 'water' THEN ('water', 'water')

    ELSE ROW(NULL,NULL)
END

Schema reference

• Explore the schema for the infrastructure feature type
• Explore the schema for the land feature type
• Explore the schema for the land_cover feature type
• Explore the schema for the land_use type
• Explore the schema for the water type