Count number of buildings near green areas (600 meters) for Berlin, London and Paris

Snowflake_OvertureMaps_Buildings.sql

WITH division_ids AS (
    SELECT
        id,
        CASE
            WHEN country = 'DE' THEN 'Berlin'
            WHEN country = 'FR' THEN 'Paris'
            ELSE 'London'
        END as city_name
    FROM
        OVERTURE_MAPS__DIVISIONS.CARTO.DIVISION
    WHERE
        (country = 'DE' AND subtype = 'region' AND JSON_EXTRACT_PATH_TEXT(names, 'primary') = 'Berlin')
        OR (country = 'FR' AND subtype = 'county' AND JSON_EXTRACT_PATH_TEXT(names, 'primary') = 'Paris')
        OR (country = 'GB' AND subtype = 'county' AND JSON_EXTRACT_PATH_TEXT(names, 'primary') IN (
            'Barking and Dagenham',
            'Barnet',
            'Bexley',
            'Brent',
            'Bromley',
            'Camden',
            'City of London',
            'Croydon',
            'Ealing',
            'Enfield',
            'Greenwich',
            'Hackney',
            'Hammersmith and Fulham',
            'Haringey',
            'Harrow',
            'Havering',
            'Hillingdon',
            'Hounslow',
            'Islington',
            'Kensington and Chelsea',
            'Kingston upon Thames',
            'Lambeth',
            'Lewisham',
            'Merton',
            'Newham',
            'Redbridge',
            'Richmond upon Thames',
            'Southwark',
            'Sutton',
            'Tower Hamlets',
            'Waltham Forest',
            'Wandsworth',
            'Westminster'
        ))
),
division_areas AS (
    SELECT
        division_ids.city_name,
        ST_Union_Agg(division_areas.geometry) as geometry
    FROM
        division_ids
    JOIN
        OVERTURE_MAPS__DIVISIONS.CARTO.DIVISION_AREA division_areas
        ON division_areas.division_id = division_ids.id
    GROUP BY 1
),
greenery AS (
    SELECT
        division_areas.city_name, land.geometry
    FROM
        division_areas
    JOIN
        OVERTURE_MAPS__BASE.CARTO.LAND land
        ON ST_INTERSECTS(division_areas.geometry, land.geometry)
    WHERE subtype IN ('forest', 'grass', 'shrub')
    UNION
    SELECT
        division_areas.city_name, land.geometry
    FROM
        division_areas
    JOIN
        OVERTURE_MAPS__BASE.CARTO.LAND_COVER land
        ON ST_INTERSECTS(division_areas.geometry, land.geometry)
    WHERE subtype IN ('forest', 'grass', 'shrub')
    UNION
    SELECT
        division_areas.city_name, land.geometry
    FROM
        division_areas
    JOIN
        OVERTURE_MAPS__BASE.CARTO.LAND_USE land 
        ON ST_INTERSECTS(division_areas.geometry, land.geometry)
    WHERE subtype IN ('grass', 'park')
),
buildings AS (
    SELECT
        division_areas.city_name, buildings.id, buildings.geometry
    FROM
        division_areas
    JOIN
        OVERTURE_MAPS__BUILDINGS.CARTO.BUILDING buildings 
        ON ST_INTERSECTS(division_areas.geometry, buildings.geometry)
)
SELECT
    buildings.city_name,
    COUNT(DISTINCT buildings.id) AS total_buildings,
    COUNT(DISTINCT CASE WHEN greenery.geometry IS NOT NULL THEN buildings.id END) AS buildings_near_green_areas,
    ROUND(100 * buildings_near_green_areas / total_buildings, 2) AS buildings_near_green_areas_ratio
FROM
    buildings
LEFT JOIN
    greenery
    ON buildings.city_name = greenery.city_name
    AND ST_DWITHIN(buildings.geometry, greenery.geometry, 600)
GROUP BY 1;