NOAA Global Historical Climatology Network 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

NOAA’s Global Historical Climatology Network (GHCN) is an integrated database of climate summaries from land surface stations across the globe that have been subjected to a common suite of quality assurance reviews. Two GHCN datasets are available in BigQuery, the GHCN-D (daily) and the GHCN-M (monthly). The data included in the GHCN datasets are obtained from more than 20 sources, including some data from every year since 1763.

For a complete description of data variables available in this dataset, see NOAA’s readme.txt.

You can start exploring the GHCN-D and GHCN-M in the BigQuery console.

Go to NOAA GHCN Dataset (daily)

Go to NOAA GHCN Dataset (monthly)

Sample queries

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

Find weather stations close to a specific location

In this case, we are searching for stations close to Chicago (latitude=41.88, longitude=-87.63).

SELECT
  id,
  name,
  state,
  latitude,
  longitude
FROM
  [bigquery-public-data:ghcn_d.ghcnd_stations]
WHERE
  latitude > 41.7
  AND latitude < 42
  AND longitude > -87.7
  AND longitude < -87.5

Only the top ten results are shown here:

weather stations in the vicinity

Daily rainfall amounts at specific station

Here, we are obtaining rainfall (in mm) for all days in 2015 from a weather station in Chicago whose id is provided in the query (the station corresponds to O’Hare airport).

SELECT
  wx.date,
  wx.value/10.0 AS prcp
FROM
  [bigquery-public-data:ghcn_d.ghcnd_2015] AS wx
WHERE
  id = 'USW00094846'
  AND qflag IS NULL
  AND element = 'PRCP'
ORDER BY wx.date

Only the top ten results are shown here:

daily rainfall at specific station

Weather for the past two weeks

Pulling daily min/max temperature (in Celsius) and rainfall (in mm) for the past 14 days. Change year from 2016 if necessary in the table name.

SELECT
  date,
  MAX(prcp) AS prcp,
  MAX(tmin) AS tmin,
  MAX(tmax) AS tmax
FROM (
  SELECT
    STRING(wx.date) AS date,
    IF (wx.element = 'PRCP', wx.value/10, NULL) AS prcp,
    IF (wx.element = 'TMIN', wx.value/10, NULL) AS tmin,
    IF (wx.element = 'TMAX', wx.value/10, NULL) AS tmax
  FROM
    [bigquery-public-data:ghcn_d.ghcnd_2016] AS wx
  WHERE
    id = 'USW00094846'
    AND qflag IS NULL
    AND value IS NOT NULL
    AND DATEDIFF(CURRENT_DATE(), date) < 15 )
GROUP BY
  date
ORDER BY
  date ASC

Only the top ten results are shown here:

weather past two weeks

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, monthly

View in BigQuery: Go to NOAA GHCN Dataset (daily) Go to NOAA GHCN Dataset (monthly)

Monitor your resources on the go

Get the Google Cloud Console app to help you manage your projects.

Send feedback about...