SQL utilising window functions to explore NOAA Global Hurricane Tracks

window_functions.sql

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
    AND name != 'NOT_NAMED'), -- Select only named storms
  dist_between_points AS (
  SELECT
    sid, -- Storm identifier
    name,
    season,
    iso_time,
    max_wind_speed,
    -- Calculate the distance between each point in the track and the one preceedding it in time.
    ST_DISTANCE(geom, LAG(geom, 1) OVER (PARTITION BY sid ORDER BY iso_time ASC))/1000 AS dist,
    geom
  FROM
    hurricane_geometry)
SELECT
  sid,
  name,
  season,
  max_wind_speed,
  -- Calculate the cumulative distance from the start of the parition to the current row.
  SUM(dist) OVER (PARTITION BY sid ORDER BY iso_time ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS cumulative_distance,
  -- Calculate the total distance by summing across the whole partition.
  SUM(dist) OVER (PARTITION BY sid) AS total_dist,
  geom
FROM
  dist_between_points)