NOAA Global Surface Summary of the Day Weather Data

How to query public data sets using BigQuery

BigQuery is a fully managed data warehouse and analytics platform. Public datasets are available for you to analyze using SQL queries. You can access BigQuery public data sets using the web UI the command-line tool, or by making calls to the BigQuery REST API using a variety of client libraries such as Java, .NET, or Python.

To get started using a BigQuery public dataset, create or select a project. The first terabyte of data processed per month is free, so you can start querying public datasets without enabling billing. If you intend to go beyond the free tier, you should also enable billing.

  1. Sign in to your Google account.

    If you don't already have one, sign up for a new account.

  2. Select or create a Cloud Platform project.

    Go to the Manage resources page

  3. Enable billing for your project.

    Enable billing

  4. BigQuery is automatically enabled in new projects. To activate BigQuery in a pre-existing project, Enable the BigQuery API.

    Enable the API

Dataset overview

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 in 2015 in US

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:

warmest day since 1929 in US

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:

most snow days in 2015

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

Send feedback about...