NOAA Global Surface Summary of the Day Weather Data

This public dataset was created by the National Oceanic and Atmospheric Administration (NOAA) and includes global data obtained from the USAF Climatology Center. This dataset covers GSOD data between 1929 and 2016, collected from over 9000 stations.

You can start exploring this data in the BigQuery console:

Go to NOAA GSOD Dataset

Sample queries

Here are some examples of SQL queries you can run on this data in BigQuery.

The warmest day in 2015 in the US by state

This query joins the gsod table for 2015 with station data to provide a station name for each record. To get other years simply swap in the right gsod table (eg, gsod1980, gsod2000).

SELECT
  max,
  (max-32)*5/9 celsius,
  mo,
  da,
  state,
  stn,
  name
FROM (
  SELECT
    max,
    mo,
    da,
    state,
    stn,
    name,
    ROW_NUMBER() OVER(PARTITION BY state ORDER BY max DESC) rn
  FROM
    [bigquery-public-data:noaa_gsod.gsod2015] a
  JOIN
    [bigquery-public-data:noaa_gsod.stations] b
  ON
    a.stn=b.usaf
    AND a.wban=b.wban
  WHERE
    state IS NOT NULL
    AND max<1000
    AND country='US' )
WHERE
  rn=1
ORDER BY
  max DESC

Only the top ten results are shown here:

Warmest day since 1929 in the US by state

This query expands on the previous by finding the warmest day in each state since 1929.

SELECT
  max,
  (max-32)*5/9 celsius,
  year,
  mo,
  da,
  state,
  stn,
  name
FROM (
  SELECT
    max,
    year,
    mo,
    da,
    state,
    stn,
    name,
    ROW_NUMBER() OVER(PARTITION BY state ORDER BY max DESC) rn
  FROM (
    SELECT
      max,
      year,
      mo,
      da,
      stn,
      wban
    FROM
      TABLE_QUERY([bigquery-public-data:noaa_gsod], 'table_id CONTAINS "gsod"')) a
  JOIN
    [bigquery-public-data:noaa_gsod.stations] b
  ON
    a.stn=b.usaf
    AND a.wban=b.wban
  WHERE
    state IS NOT NULL
    AND max<1000
    AND country='US' )
WHERE
  rn=1
ORDER BY
  YEAR DESC

Only the top ten results are shown here:

Top 10 places that received the most snow days in 2015

This query returns the stations that record the most days of snow precipitation.

SELECT
  c as days,
  name,
  country
FROM (
  SELECT
    stn,
    wban,
    COUNT(*) c
  FROM
    [bigquery-public-data:noaa_gsod.gsod2015]
  WHERE
    snow_ice_pellets='1'
  GROUP BY
    1,
    2
  ORDER BY
    c DESC
  LIMIT
    20 ) a
LEFT JOIN
  [bigquery-public-data:noaa_gsod.stations] b
ON
  a.stn=b.usaf
  AND a.wban=b.wban
WHERE
  name IS NOT NULL

Only the top ten results are shown here:

About the data

Dataset Source: NOAA

Category: Weather

Use: This dataset is publicly available for anyone to use under the following terms provided by the Dataset Source — http://www.data.gov/privacy-policy#data_policy — and is provided "AS IS" without any warranty, express or implied, from Google. Google disclaims all liability for any damages, direct or indirect, resulting from the use of the dataset.

Update Frequency: daily

View in BigQuery: Go to NOAA GSOD dataset