Using geospatial analytics to plot a hurricane's path

Explore the sample data

This tutorial uses a dataset available through the Google Cloud Public Dataset Program . A public dataset is any dataset that is stored in BigQuery and made available to the general public. The public datasets are datasets that BigQuery hosts for you to access and integrate into your applications. Google pays for the storage of these datasets and provides public access to the data by using a project . You pay only for the queries that you perform on the data (the first 1 TB per month is free, subject to query pricing details ).

The Global Hurricane Tracks (IBTrACS) dataset

Global Hurricane Tracks (IBTrACS) dataset

The historical positions and intensities along the tracks of global tropical cyclones (TC) are provided by NOAA's International Best Track Archive for Climate Stewardship (IBTrACS). Tropical cyclones are known as hurricanes in the north Atlantic and northeast Pacific ocean basins, typhoons in the northwest Pacific ocean basin, cyclones in the north and south Indian Ocean basins, and tropical cyclones in the southwest Pacific ocean basin.

IBTrACS collects data about TCs reported by international monitoring centers who have a responsibility to forecast and report on TCs (and also includes some important historical datasets). IBTrACS includes data from 9 different countries. Historically, the data describing these systems has included best estimates of their track and intensity (hence the term, best track).

You can start exploring this data in the Google Cloud console by viewing the details of the hurricanes table:

Go to hurricanes schema

Query the path of hurricane Maria in 2017

In this section of the tutorial, you run a GoogleSQL query that finds the path of hurricane Maria in the 2017 season. To plot the hurricane's path, you query the hurricane's location at different points in time.

Query details

The following GoogleSQL query is used to find the path of hurricane Maria.

 SELECT 
  
 ST_GeogPoint 
 ( 
 longitude 
 , 
  
 latitude 
 ) 
  
 AS 
  
 point 
 , 
  
 name 
 , 
  
 iso_time 
 , 
  
 dist2land 
 , 
  
 usa_wind 
 , 
  
 usa_pressure 
 , 
  
 usa_sshs 
 , 
  
 ( 
 usa_r34_ne 
  
 + 
  
 usa_r34_nw 
  
 + 
  
 usa_r34_se 
  
 + 
  
 usa_r34_sw 
 ) 
 / 
 4 
  
 AS 
  
 radius_34kt 
 , 
  
 ( 
 usa_r50_ne 
  
 + 
  
 usa_r50_nw 
  
 + 
  
 usa_r50_se 
  
 + 
  
 usa_r50_sw 
 ) 
 / 
 4 
  
 AS 
  
 radius_50kt 
 FROM 
  
 `bigquery-public-data.noaa_hurricanes.hurricanes` 
 WHERE 
  
 name 
  
 LIKE 
  
 '%MARIA%' 
  
 AND 
  
 season 
  
 = 
  
 '2017' 
  
 AND 
  
 ST_DWithin 
 ( 
 ST_GeogFromText 
 ( 
 'POLYGON((-179 26, -179 48, -10 48, -10 26, -100 -10.1, -179 26))' 
 ), 
  
 ST_GeogPoint 
 ( 
 longitude 
 , 
  
 latitude 
 ), 
  
 10 
 ) 
 ORDER 
  
 BY 
  
 iso_time 
  
 ASC 

The query clauses do the following:

• SELECT ST_GeogPoint(longitude, latitude) AS point, name, iso_time, dist2land, usa_wind, usa_pressure, usa_sshs, (usa_r34_ne + usa_r34_nw + usa_r34_se + usa_r34_sw)/4 AS radius_34kt, (usa_r50_ne + usa_r50_nw + usa_r50_se + usa_r50_sw)/4 AS radius_50kt

  The SELECT clause selects all the storm's weather data and uses the ST_GeogPoint function to convert the values in the latitude and longitude columns to GEOGRAPHY types (points).

• FROM bigquery-public-data.noaa_hurricanes.hurricanes

  The FROM clause specifies the table being queried: hurricanes .

• WHERE name LIKE '%MARIA%' AND season = '2017' AND ST_DWithin(ST_GeogFromText('POLYGON((-179 26, -179 48, -10 48, -10 26, -100 -10.1, -179 26))'), ST_GeogPoint(longitude, latitude), 10)

  The WHERE clause filters the data to just the points in the Atlantic corresponding to hurricane Maria in the 2017 hurricane season.

• ORDER BY iso_time ASC

  The ORDER BY clause orders the points to form a chronological storm path.

Run the query

To run the query by using the Google Cloud console:

1. Go to the BigQuery page in the Google Cloud console.

   Go to the BigQuery page

2. Enter the following GoogleSQL query in the Query editortext area.

    SELECT 
     
    ST_GeogPoint 
    ( 
    longitude 
    , 
     
    latitude 
    ) 
     
    AS 
     
    point 
    , 
     
    name 
    , 
     
    iso_time 
    , 
     
    dist2land 
    , 
     
    usa_wind 
    , 
     
    usa_pressure 
    , 
     
    usa_sshs 
    , 
     
    ( 
    usa_r34_ne 
     
    + 
     
    usa_r34_nw 
     
    + 
     
    usa_r34_se 
     
    + 
     
    usa_r34_sw 
    ) 
    / 
    4 
     
    AS 
     
    radius_34kt 
    , 
     
    ( 
    usa_r50_ne 
     
    + 
     
    usa_r50_nw 
     
    + 
     
    usa_r50_se 
     
    + 
     
    usa_r50_sw 
    ) 
    / 
    4 
     
    AS 
     
    radius_50kt 
    FROM 
     
    `bigquery-public-data.noaa_hurricanes.hurricanes` 
    WHERE 
     
    name 
     
    LIKE 
     
    '%MARIA%' 
     
    AND 
     
    season 
     
    = 
     
    '2017' 
     
    AND 
     
    ST_DWithin 
    ( 
    ST_GeogFromText 
    ( 
    'POLYGON((-179 26, -179 48, -10 48, -10 26, -100 -10.1, -179 26))' 
    ), 
     
    ST_GeogPoint 
    ( 
    longitude 
    , 
     
    latitude 
    ), 
     
    10 
    ) 
    ORDER 
     
    BY 
     
    iso_time 
     
    ASC 
   

3. Click Run.

   The query takes a moment to complete. After the query runs, your results appear in the Query resultspane.

   

Visualize the query results in BigQuery

To visualize your results in BigQuery, follow these steps:

1. To visualize your results in BigQuery, in the Query resultspane, click Visualization.

2. For Data column, select usa_wind .

   A map appears with points for the hurricane's location over time, styled by a color gradient for wind speed.

3. Optional: To adjust the visibility of the points, set Minto 0 and select a different color gradient from the Colorlist.

Visualize the query results in Geo Viz

You can also visualize your results using BigQuery Geo Viz — A web tool for visualization of geospatial data in BigQuery using Google Maps APIs.

Launch Geo Viz and authenticate

Before using Geo Viz, you must authenticate and grant access to data in BigQuery.

To set up Geo Viz:

1. Open the Geo Viz web tool.

   Open the Geo Viz web tool

2. Under step one, Select data, click Authorize.

   

3. In the Choose an accountdialog, click your Google Account.

   

4. In the access dialog, click Allowto give Geo Viz access to your BigQuery data.

   

Run your query in Geo Viz

After you authenticate and grant access, the next step is to run the query in Geo Viz.

To run the query:

1. For step one, Select data, enter your project ID in the Project IDfield.

2. In the query window, enter the following GoogleSQL query.

    SELECT 
     
    ST_GeogPoint 
    ( 
    longitude 
    , 
     
    latitude 
    ) 
     
    AS 
     
    point 
    , 
     
    name 
    , 
     
    iso_time 
    , 
     
    dist2land 
    , 
     
    usa_wind 
    , 
     
    usa_pressure 
    , 
     
    usa_sshs 
    , 
     
    ( 
    usa_r34_ne 
     
    + 
     
    usa_r34_nw 
     
    + 
     
    usa_r34_se 
     
    + 
     
    usa_r34_sw 
    ) 
    / 
    4 
     
    AS 
     
    radius_34kt 
    , 
     
    ( 
    usa_r50_ne 
     
    + 
     
    usa_r50_nw 
     
    + 
     
    usa_r50_se 
     
    + 
     
    usa_r50_sw 
    ) 
    / 
    4 
     
    AS 
     
    radius_50kt 
    FROM 
     
    `bigquery-public-data.noaa_hurricanes.hurricanes` 
    WHERE 
     
    name 
     
    LIKE 
     
    '%MARIA%' 
     
    AND 
     
    season 
     
    = 
     
    '2017' 
     
    AND 
     
    ST_DWithin 
    ( 
    ST_GeogFromText 
    ( 
    'POLYGON((-179 26, -179 48, -10 48, -10 26, -100 -10.1, -179 26))' 
    ), 
     
    ST_GeogPoint 
    ( 
    longitude 
    , 
     
    latitude 
    ), 
     
    10 
    ) 
    ORDER 
     
    BY 
     
    iso_time 
     
    ASC 
   

3. Click Run.

4. When the query completes, click Show results. You can also click step two Data.

5. This moves you to step two. In step two, for Geometry column, choose point. This plots the points corresponding to hurricane Maria's path.

   

Format your visualization in Geo Viz

The Stylesection provides a list of visual styles for customization. For more information about style properties and values, see Format your visualization .

To format your map:

1. Click Add stylesin step two or click step 3 Style.

2. Change the color of your points. Click fillColor.

3. In the fillColorpanel:

   1. Click Data driven.
   2. For Function, choose linear.
   3. For Field, choose usa_wind .
   4. For Domain, enter 0 in the first box and 150 in the second.

   5. For Range, click the first box and enter #0006ff in the Hexbox. Click the second box and enter #ff0000 . This changes the color of the point based on the wind speed. Blue for lighter winds and red for stronger winds.

      

4. Examine your map. If you hold the pointer over on one of your points, the point's weather data is displayed.

   

5. Click fillOpacity.

6. In the Valuefield, enter .5.

   

7. Examine your map. The fill color of the points is now semi-transparent.

8. Change the size of the points based on the hurricane's radius. Click circleRadius.

9. In the circleRadiuspanel:

   1. Click Data driven.
   2. For Function, choose linear.
   3. For Field, choose radius_50kt .
   4. For Domain, enter 0 in the first box and 135 in the second.

   5. For Range, enter 5 in the first box and 135000 in the second.

      

10. Examine your map. The radius of each point now corresponds to the radius of the hurricane.

    

11. Close Geo Viz.