wriglz

WITH
  NN AS (
  SELECT
    a.osm_id,
    -- Select the closest ID
    ARRAY_AGG(STRUCT(b.osm_id,
        ST_DISTANCE(a.geom, b.geom) AS dist)
    ORDER BY
      ST_DISTANCE(a.geom, b.geom)
    LIMIT

wriglz

SELECT
  osm_id, 
  ST_CENTROID(geometry) AS geom,
  (SELECT value FROM UNNEST(all_tags) WHERE key = 'name' ) AS name,
FROM
  bigquery-public-data.geo_openstreetmap.planet_features
WHERE
  ('man_made', 'lighthouse') IN ( 
    SELECT
      (key, value)

wriglz

CREATE OR REPLACE TABLE
  `dataset.north_south_divide`
CLUSTER BY
  h3 AS(
  WITH
    england_grid AS (
    SELECT
      geoid AS h3,
      population
    FROM

wriglz

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

wriglz

CREATE OR REPLACE FUNCTION
  demo.ST_LineLocatePoint(line GEOGRAPHY,
    point GEOGRAPHY)
  RETURNS FLOAT64 AS (ST_DISTANCE(ST_STARTPOINT(line),ST_CLOSESTPOINT(line, point) )/ST_LENGTH(line) );

wriglz

DECLARE
  bbox GEOGRAPHY;
SET
  bbox = (
  SELECT
    geom
  FROM
    `global_boundaries_dataset`
  WHERE
    NAME_EN = 'United Kingdom');

wriglz

WITH
  hurricane_geometry AS (
  SELECT
    * EXCEPT (longitude, latitude), -- Select everything except lat & lon
    ST_GEOGPOINT(longitude, latitude) AS geom, -- As we create a geometry here
    MAX(usa_wind) OVER (PARTITION BY sid) AS max_wind_speed -- Calculate the max wind speed per storm
  FROM
    `bigquery-public-data.noaa_hurricanes.hurricanes`
  WHERE
    basin = 'NA' -- Select only storms in the North American basin

wriglz

SELECT
  osm_id,
  feature_type,
  osm_timestamp,
  geometry,
  -- Here we are going to extract a couple of attributes from the all_tags array:
  (SELECT value FROM UNNEST(all_tags) WHERE key = 'name' ) AS name,
  (SELECT value FROM UNNEST(all_tags) WHERE key = 'addr:city') AS city
FROM
  bigquery-public-data.geo_openstreetmap.planet_features

wriglz

-- Data source: https://data-rnli.opendata.arcgis.com/

-- Remove outliers which are beyond the reach of the maximum lifeboat range:
SELECT
  rescue.*
FROM
  `RNLI.RNLI_Returns_of_Service` rescue
LEFT JOIN
  `RNLI.RNLI_Lifeboat_Station_Locations` station
ON

wriglz

-- Generate a random number within a specified <min> <max> range

SELECT 
   CAST(<min> + RAND() * (<max> - <min>) AS INT) AS random_number