NOAA International Comprehensive Ocean-Atmosphere Data Set (ICOADS)

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

The ICOADS dataset contains global marine data from ships (merchant, navy, research) and buoys, each capturing details according to the current weather or ocean conditions (wave height, sea temperature, wind speed, and so on). Each record contains the exact location of the observation which is great for visualizations. The historical depth of the data is quite comprehensive — There are records going back to 1662!

For more information, see the NOAA/NCAR website.

You can start exploring this data in the BigQuery console:

Go to NOAA ICOADS data

Sample queries

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

These samples use BigQuery’s support for standard SQL. Use the #standardSQL tag to let BigQuery know you want to use standard SQL. For more information about the #standardSQL prefix, see Setting a query prefix.

Where were nearby ships during the hours the Titanic sank?

The NOAA ICOADS data can be used to examine interesting points in history. The Titanic sank in the early morning hours of April 15, 1912. It hit an iceberg in the North Atlantic and sank near coordinates: 41.73, -49.95. This query retrieves the locations of ships reported near the site of the tragedy. Perhaps some of these ships were involved in the rescue effort.

Web UI

Open the following query in the Web UI

#standardSQL
SELECT
  1 AS num,
  callsign,
  country_code,
  timestamp,
  CONCAT(CAST(latitude AS STRING), ', ', CAST(longitude AS STRING)) AS latlong
FROM
  `bigquery-public-data.noaa_icoads.icoads_core_1662_2000`
WHERE
  timestamp > "1912-04-14 01:00:00 UTC"
  AND timestamp < "1912-04-14 17:00:00 UTC"
  AND latitude > 30
  AND longitude < -25
  AND longitude > -75

Command-line

bq query --use_legacy_sql=false '
SELECT
  1 AS num,
  callsign,
  country_code,
  timestamp,
  CONCAT(CAST(latitude AS STRING), ', ', CAST(longitude AS STRING)) AS latlong
FROM
  `bigquery-public-data.noaa_icoads.icoads_core_1662_2000`
WHERE
  timestamp > "1912-04-14 01:00:00 UTC"
  AND timestamp < "1912-04-14 17:00:00 UTC"
  AND latitude > 30
  AND longitude < -25
  AND longitude > -75'

You can use this query to create a report in Google Data Studio to visualize the query results. The results use a range around the relevant latitude and longitude. A zoom in Datastudio is used to look at each relevant location. The darker dots are multiple reports in a particular location. The red arrow indicates the Titanic's location.

Ships near the Titanic sinking

For a tutorial on using Google Data Studio with BigQuery, see Visualizing BigQuery Data Using Google Data Studio.

Where were ships going in our earliest records (1600s)?

This query retrieves some of the shipping lanes between 1661 and 1700. The ships analyzed are primarily Dutch (NL) and British (03).

Web UI

Open the following query in the Web UI

#standardSQL
SELECT
  1 AS num,
  callsign,
  country_code,
  timestamp,
  CONCAT(CAST(latitude AS STRING), ', ', CAST(longitude AS STRING)) AS latlong
FROM
  `bigquery-public-data.noaa_icoads.icoads_core_1662_2000`
WHERE
  year BETWEEN 1661
  AND 1700

Command-line

bq query --use_legacy_sql=false '
SELECT
  1 AS num,
  callsign,
  country_code,
  timestamp,
  CONCAT(CAST(latitude AS STRING), ', ', CAST(longitude AS STRING)) AS latlong
FROM
  `bigquery-public-data.noaa_icoads.icoads_core_1662_2000`
WHERE
  year BETWEEN 1661
  AND 1700'

Visualizing the query results in Data Studio produces a global chart with some clear shipping lanes.

Early shipping lanes

What are the top 10 fastest wind speeds ever recorded in the last 350 years of oceanic data?

This query joins the ICOADS tables to determine the 10 fastest wind speeds in meters per second.

Web UI

Open the following query in the Web UI

#standardSQL
SELECT
  wind_speed,
  callsign,
  timestamp,
  latitude,
  longitude
FROM
  `bigquery-public-data.noaa_icoads.icoads_core_*`
ORDER BY
  wind_speed DESC
LIMIT
  10

Command-line

bq query --use_legacy_sql=false '
SELECT
  wind_speed,
  callsign,
  timestamp,
  latitude,
  longitude
FROM
  `bigquery-public-data.noaa_icoads.icoads_core_*`
ORDER BY
  wind_speed DESC
LIMIT
  10'

Sample results are shown here:

+-------------+----------+--------------------------+-----------+-----------+
| wind_speed  | callsign | timestamp                | latitude  | longitude |
+-------------+----------+--------------------------+-----------+-----------+
| 55.6        | GOMF     | 1973-01-28 00:00:00 UTC  | 47.2      | -40.1     |
| 53.0        | ERES     | 1985-01-16 06:00:00 UTC  | 51.0      | -34.2     |
| 52.5        | C6SI4    | 2011-01-17 18:00:00 UTC  | 52.8      | 172.5     |
| 52.0        |          | 1955-01-05 12:00:00 UTC  | 50.0      | -28.3     |
| 51.4        | 62108    | 2000-02-28 21:00:00 UTC  | 53.5      | -19.5     |
| 50.9        | OZPF     | 1980-12-13 12:00:00 UTC  | 41.5      | -59.6     |
| 50.9        | ZGAD3    | 2010-02-09 00:00:00 UTC  | 51.8      | 173.5     |
| 50.9        | GBTT     | 1983-11-08 12:00:00 UTC  | 48.9      | -38.0     |
| 50.9        | DCGJYD   | 1993-01-17 05:00:00 UTC  | 56.0      | -42.3     |
| 50.4        | TFLE     | 1994-12-05 18:00:00 UTC  | 54.7      | -41.2     |
+-------------+----------+--------------------------+-----------+-----------+

The fastest observed wind speed was 55.6 m/s in 1973, right in the middle of the North Atlantic Ocean. That’s about 124 miles per hour!

About the data

Dataset Source: NOAA

Category: Meteorological, Climate, Transportation

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.

Citation: National Centers for Environmental Information/NESDIS/NOAA/U.S. Department of Commerce, Research Data Archive/Computational and Information Systems Laboratory/National Center for Atmospheric Research/University Corporation for Atmospheric Research, Earth System Research Laboratory/NOAA/U.S. Department of Commerce, Cooperative Institute for Research in Environmental Sciences/University of Colorado, National Oceanography Centre/Natural Environment Research Council/United Kingdom, Met Office/Ministry of Defence/United Kingdom, Deutscher Wetterdienst (German Meteorological Service)/Germany, Department of Atmospheric Science/University of Washington, and Center for Ocean-Atmospheric Prediction Studies/Florida State University. 2016, updated monthly. International Comprehensive Ocean-Atmosphere Data Set (ICOADS) Release 3, Individual Observations. Research Data Archive at the National Center for Atmospheric Research, Computational and Information Systems Laboratory: https://doi.org/10.5065/D6ZS2TR3. Accessed 01 04 2017.

Update Frequency: Semi-annual

View in BigQuery: Go to NOAA ICOADS data

Monitor your resources on the go

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

Send feedback about...