SQL to generate Voronoi Polygons to determine National Park catchment areas.

national_park_voronoi.sql

/* 
Data sources for National Park boundaries:
- England: https://environment.data.gov.uk/DefraDataDownload/?mapService=NE/NationalParksEngland&Mode=spatial
- Scotland: https://spatialdata.gov.scot/geonetwork/srv/eng/catalog.search#/home
- Wales: https://datamap.gov.wales/layers/inspire-nrw:NRW_NATIONAL_PARK
*/ 

WITH
  park_info AS(
    -- Select required information about each National Park from a merged dataset
  SELECT
    INITCAP(name) AS park_name,
    ST_CENTROID(geom) AS geom,
    ST_AREA(geom) AS area,
  FROM
    `project.dataset.uk_national_parks`),

  uk_boundary AS (
    -- Select the UK boundary from the EU NUTS dataset
  SELECT
    geom
  FROM
    `project.dataset.eurostat_nuts_2021`
  WHERE
    NAME_LATN = 'United Kingdom'),

  geom_array AS (
    -- Gather the centroids of the National Parks into an array (required by ST_VORONOIPOLYGONS below)
  SELECT
    ARRAY (
    SELECT
      p.geom
    FROM
      park_info p
    JOIN
      uk_boundary
    ON
      ST_CONTAINS(uk_boundary.geom,
        p.geom) ) AS array_parks ),

  voronoi_array AS (
    -- Create an array of Voronoi polygons
  SELECT
    `carto-un`.carto.ST_VORONOIPOLYGONS(geom_array.array_parks,
      NULL) AS nested_voronoi
  FROM
    geom_array ),

  voronoi_polygons AS (
    -- Unnest the Voronoi polygon array and trim it to the UK boundary
  SELECT
    ST_INTERSECTION(uk_boundary.geom,
      unnested_voronoi) AS geom
  FROM
    voronoi_array,
    UNNEST(nested_voronoi) AS unnested_voronoi,
    uk_boundary )
    
  -- Create the final table ready to plot on a map.
SELECT
  pi.park_name,
  pi.desig_date,
  pi.area AS park_area,
  ST_AREA(vp.geom) AS catchment_area,
  vp.geom
FROM
  voronoi_polygons vp
JOIN
  park_info pi
ON
  ST_INTERSECTS (pi.geom,
    vp.geom)