Longest hurricanes

This recipe is mostly to demostrate how to construct proper lines from a set of coordinates and timestamps, as I often see attempts to use array_agg without ordering points by timestamps, which might result in points connected in random order.

Let’s use NOAA database to find longest hurricanes using BigQuery GIS

SELECT 
  season, number, basin, any_value(name) name,
  round(st_length(st_makeline(
    array_agg(st_geogpoint(longitude, latitude) order by iso_time)))
    / 1000) dist_km
FROM `bigquery-public-data.noaa_hurricanes.hurricanes`
group by 1, 2, 3
order by dist_km desc
limit 100

The key here is creating a line corresponding to hurricane path. For this we collect all the coordinates of the hurricane ordered by their time stamp:

array_agg(st_geogpoint(longitude, latitude) order by iso_time)

From this we can make the line with st_makeline, and then measure its length with st_length.

The results look reasonable: first hurricane is some unnamed one from 1899 season, and second result is 2000 Alberto. So it works.

We can also visualize them in BigQuery GeoViz: