Using geospatial analytics to plot a hurricane's path

---

This tutorial introduces you to geospatial analytics. Geospatial analytics let you easily analyze and visualize geospatial data in BigQuery.

Objectives

In this tutorial, you:

• Use a geospatial analytics function to convert latitude and longitude columns into geographical points
• Run a query that plots the path of a hurricane
• Visualize your results in BigQuery Geo Viz

Costs

BigQuery is a paid product and you will incur BigQuery usage in this tutorial. BigQuery offers some resources free of charge up to a specific limit. For more information, see BigQuery free operations and free tier.

Before you begin

Before you begin this tutorial, use the Google Cloud console to create or select a project.

1. Sign in to your Google Cloud account. If you're new to Google Cloud, create an account to evaluate how our products perform in real-world scenarios. New customers also get $300 in free credits to run, test, and deploy workloads.

2. In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

   Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.

   Go to project selector

3. In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

   Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.

   Go to project selector

4. BigQuery is automatically enabled in new projects. To activate BigQuery in an existing project, go to

   Enable the BigQuery API.

   Enable the API

5. Optional: Enable billing for the project. If you don't want to enable billing or provide a credit card, the steps in this document still work. BigQuery provides you a sandbox to perform the steps. For more information, see Enable the BigQuery sandbox.
   Note: If your project has a billing account and you want to use the BigQuery sandbox, then disable billing for your project.

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 editor text 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 results pane.

   

Visualize the query results in Geo Viz

Next, you 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 account dialog, click your Google Account.

   

4. In the access dialog, click Allow to 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 ID field.

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 Style section provides a list of visual styles for customization. Certain properties apply only to certain types of data. For example, circleRadius affects only points.

Supported style properties include:

• fillColor — The fill color of a polygon or point. For example, "linear" or "interval" functions can be used to map numeric values to a color gradient.
• fillOpacity — The fill opacity of a polygon or point. Values must be in the range zero — one where 0 = transparent and 1 = opaque.
• strokeColor — The stroke or outline color of a polygon or line.
• strokeOpacity — The stroke or outline opacity of polygon or line. Values must be in the range zero — one where 0 = transparent and 1 = opaque.
• strokeWeight — The stroke or outline width in pixels of a polygon or line.
• circleRadius — The radius of the circle representing a point in meters. For example, a "linear" function can be used to map numeric values to point sizes to create a scatterplot style.

Each style may be given either a global value (applied to every result) or a data-driven value (applied in different ways depending on data in each result row). For data-driven values, the following are used to determine the result:

• function — A function used to compute a style value from a field's values.
• identity — The data value of each field is used as the styling value.
• categorical — The data values of each field listed in the domain are mapped one to one with corresponding styles in the range.
• interval — Data values of each field are rounded down to the nearest value in the domain and are then styled with the corresponding style in the range.
• linear — Data values of each field are interpolated linearly across values in the domain and are then styled with a blend of the corresponding styles in the range.
• field — The specified field in the data is used as the input to the styling function.
• domain — An ordered list of sample input values from a field. Sample inputs (domain) are paired with sample outputs (range) based on the given function and are used to infer style values for all inputs (even those not listed in the domain). Values in the domain must have the same type (text, number, and so on) as the values of the field you are visualizing.
• range — A list of sample output values for the style rule. Values in the range must have the same type (color or number) as the style property you are controlling. For example, the range of the fillColor property should contain only colors.

To format your map:

1. Click Add styles in step two or click step 3 Style.

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

3. In the fillColor panel:

   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 Hex box. 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 Value field, 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 circleRadius panel:

   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.

Clean up

To avoid incurring charges to your Google Cloud account for the resources used in this tutorial, either delete the project that contains the resources, or keep the project and delete the individual resources.

• You can delete the project you created.
• Or you can keep the project for future use.

To delete the project:

Caution: Deleting a project has the following effects:

• Everything in the project is deleted. If you used an existing project for the tasks in this document, when you delete it, you also delete any other work you've done in the project.
• Custom project IDs are lost. When you created this project, you might have created a custom project ID that you want to use in the future. To preserve the URLs that use the project ID, such as an appspot.com URL, delete selected resources inside the project instead of deleting the whole project.

If you plan to explore multiple architectures, tutorials, or quickstarts, reusing projects can help you avoid exceeding project quota limits.

1. In the Google Cloud console, go to the Manage resources page.

   Go to Manage resources

2. In the project list, select the project that you want to delete, and then click Delete.
3. In the dialog, type the project ID, and then click Shut down to delete the project.

What's next

• To learn more about how to visualize options for geospatial analytics, see Visualizing geospatial data.
• To work with geospatial data, see Working with geospatial data.
• To learn more about the geography functions you can use in geospatial analytics, see Geography functions in GoogleSQL.