Code to create a coverage map of how many London Underground stations are within a 10 min walk of any given point.

London_undeground_walk.sql

SELECT
  osm_id,
  geometry,
  -- Here we are going to extract the station name from the all_tags array:
  (
  SELECT
    value
  FROM
    UNNEST(all_tags)
  WHERE
    key = 'name' ) AS name,
FROM
  bigquery-public-data.geo_openstreetmap.planet_features
WHERE
  ('public_transport', 'station') IN ( -- Select only stations
  SELECT
    (key, value)
  FROM
    UNNEST(all_tags))
  AND ('network', 'London Underground') IN ( -- Limit the stations to underground, not overground.
  SELECT
    (key, value)
  FROM
    UNNEST(all_tags))
  AND ('fare_zone','1') IN ( -- Select only stations in Zone 1
  SELECT
    (key,value)
  FROM
    UNNEST(all_tags));
    
    
    
    DECLARE coverage_area GEOGRAPHY;

-- First union all of the isochrones into a single multipolygon
-- to fill with H3 cells.
SET
  coverage_area = (
  SELECT
    ST_UNION_AGG(geom)
  FROM
    `10_min_walk_isochrones`);
    
CREATE OR REPLACE TABLE
  `station_coverage_h3_grid_12` AS(
  SELECT
    h3,
    COUNT(station.osm_id) AS station_count, -- Count the number of isochrones
  FROM
    -- Fill the total coverage area with a H3 index, resolution 13 (~40m2)
    UNNEST(`carto-un`.carto.H3_POLYFILL(coverage_area,
        13)) h3
  JOIN
    `station_coverage_h3_grid_12` AS station
  ON
    -- Join the H3 grid and isochrones where they intersect to enable the count above.
    ST_Intersects(`carto-un`.carto.H3_BOUNDARY(h3),
      station.geom)
  GROUP BY
    h3 -- This ensures that the number of isochrones are counted per H3 cell