Getting started with BigQuery GIS

This tutorial introduces you to BigQuery GIS. BigQuery GIS lets you analyze and visualize geospatial data in BigQuery.

Objectives

In this tutorial, you:

• Use a BigQuery GIS function to convert latitude and longitude columns into geographical points
• Run a query that finds all the Citi Bike stations with more than 30 bikes available for rental
• Visualize your results in BigQuery Geo Viz

Costs

This tutorial uses billable components of Google Cloud, including:

• BigQuery

You incur charges for:

• Querying data in the BigQuery public datasets.
  • The first 1 TB is free each month.
  • If you are using flat-rate pricing, query costs are included in the monthly flat-rate price.

Before you begin

1. Sign in to your Google Account.

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

2. In the Cloud Console, on the project selector page, select or create a 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 the project selector page

3. Make sure that billing is enabled for your Google Cloud project. Learn how to confirm billing is enabled for your project.

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

   Enable the API

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 via 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 NYC Citi Bike Trips dataset

NYC Citi Bike Trips

Citi Bike is the nation's largest bike share program, with 10,000 bikes and 600 stations across Manhattan, Brooklyn, Queens, and Jersey City. This dataset includes Citi Bike trips since Citi Bike launched in September 2013 and is updated daily. The data is processed by Citi Bike to remove trips that are taken by staff to service and inspect the system and any trips below 60 seconds in length, which are considered false starts.

You can start exploring this data in the BigQuery console by viewing the details of the citibike_stations table:

Go to citibike_stations schema

Three columns in this table are relevant to this tutorial:

• bike_stations.longitude: the longitude of a station. The values are valid WGS 84 longitudes in decimal degrees format.
• bike_stations.latitude: the latitude of a station. The values are valid WGS 84 latitudes in decimal degrees format.
• num_bikes_available: the number of bikes available for rental.

Query the bike stations with more than 30 bikes available

In this section of the tutorial, you run a standard SQL query that finds all the Citi Bike stations in New York City with more than 30 bikes available to rent.

Query details

The following standard SQL query is used to find the Citi Bike stations with more than 30 bikes.

SELECT
  ST_GeogPoint(longitude, latitude)  AS WKT,
  num_bikes_available
FROM
  `bigquery-public-data.new_york.citibike_stations`
WHERE num_bikes_available > 30

The query clauses do the following:

• SELECT ST_GeogPoint(longitude, latitude) AS WKT, num_bikes_available

  The SELECT clause selects the num_bikes_available column and uses the ST_GeogPoint function to convert the values in the latitude and longitude columns to GEOGRAPHY types (points).

• FROM `bigquery-public-data.new_york.citibike_stations`

  The FROM clause specifies the table being queried: citibike_stations.

• WHERE num_bikes_available > 30

  The WHERE clause filters the values in the num_bikes_available column to just those stations with more than 30 bikes.

Run the query

To run the query by using the Cloud Console:

1. Go to the BigQuery page in the Cloud Console.

   Go to the BigQuery page

2. Enter the following standard SQL query in the Query editor text area.

   -- Finds Citi Bike stations with > 30 bikes
   SELECT
     ST_GeogPoint(longitude, latitude)  AS WKT,
     num_bikes_available
   FROM
     `bigquery-public-data.new_york.citibike_stations`
   WHERE num_bikes_available > 30
   

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 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 a standard SQL query on BigQuery GIS data

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 standard SQL query.

   -- Finds Citi Bike stations with > 30 bikes
   SELECT
     ST_GeogPoint(longitude, latitude)  AS WKT,
     num_bikes_available
   FROM
     `bigquery-public-data.new_york.citibike_stations`
   WHERE num_bikes_available > 30
   

3. Click Run.

4. When the query completes, click See results. You can also click step two Define columns.

   

5. This moves you to step two. In step two, for Geometry column, choose WKT. This plots the points corresponding to the bike stations on your map.

   

Format your visualization

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 0 to 1, 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 0 to 1, 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 pixels. For example, a "linear" function can be used to map numeric values to point sizes to create a scatterplot style.

Each style can 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 Value field, enter #0000FF, the HTML color code for blue.

   

4. Examine your map. If you hover on one of your points, the value 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 number of bikes available. Click circleRadius.

9. In the circleRadius panel:

   1. Click Data driven.
   2. For Function, choose linear.
   3. For Field, choose num_bikes_available.
   4. For Domain, enter 30 in the first box and 60 in the second.

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

      

10. Examine your map. The radius of each circle now corresponds to the number of bikes available at that location.

    

11. Close Geo Viz.

Cleaning up

To avoid incurring charges to your Google Cloud Platform account for the resources used in this tutorial:

• 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 this tutorial, 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 tutorials and quickstarts, reusing projects can help you avoid exceeding project quota limits.

1. In the Cloud Console, go to the Manage resources page.

   Go to the Manage resources page

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 visualization options for BigQuery GIS, see Visualizing BigQuery GIS data.
• To learn more about working with BigQuery GIS data, see Working with BigQuery GIS data.
• For a tutorial on using BigQuery GIS, see Using BigQuery GIS to plot a hurricane's path.
• For documentation on standard SQL functions in BigQuery GIS, see Geography functions in standard SQL.