You can now interactively query the Storm Prediction Center (SPC)’s quality-controlled reports of severe weather (tornadoes, hail, and wind) in Google BigQuery (first 1 TB per month of processing is free). This is one of the NOAA open datasets and is provided through an ongoing public-private collaboration called the NOAA Big Data Project.

Here’s an example of what you can do with this data. What is the windiest place in the United States? You could define “windiest” in many ways, but one reasonable way is to define it as the county that has the most number of severe wind events in any given year.

Let’s write a quick SQL query to do that … go to the BigQuery web console and type in:

#standardsql

WITH

windreports AS (

SELECT

CONCAT(county, ", ", state) AS county,

speed AS windspeed,

EXTRACT(year

FROM

timestamp) AS year

FROM

`bigquery-public-data.noaa_spc.wind_reports`)

SELECT

county,

year,

MAX(windspeed) AS maxwinds,

COUNT(windspeed) AS numevents

FROM

windreports

GROUP BY

year,

county

ORDER BY

numevents DESC

When I ran the above query, I got:

For three years running, the windiest county in the USA is Tooele County, Utah

It’s windy there (62 days of severe wind in 2016!), but the 75 mph maximum wind speed is not that bad. Well, fast enough that a headwind will keep your car at a standstill, but you know what I mean …

Change the query to rank by maxwinds instead, and we get:

Ranking by the wind speed, though, we get an interesting mix

Now, it seems that things are more interesting, with a mix of hurricane places (TX, LA), mountain states (MT), and the prairies (KS, SD).

Happy querying.