Visualize BigQuery GEOGRAPHY polygons with Data Studio

Stopwatch image

This tutorial is intended for people who want to visualize GEOGRAPHY data from BigQuery using Data Studio. To complete this tutorial, you'll need a BigQuery billing project. You don't need to know how to write SQL, and you can use the public data set.

Goal

In this tutorial, you'll create a report showing the affordability of rental properties in Washington state. You'll use a Google Map to visualize GEOGRAPHY data coming from a public BigQuery data set.

Example of Google Map showing BigQuery GEOGRAPHY polygons representing rent affordability in Washington state.

Before you begin

If you don't already have a BigQuery billing project set up, you can sign up for free here.

1 Create a new blank report

  1. Sign in to Data Studio.
  2. In the top left, click CreateCreate, then select Report.
  3. In the Add data to report panel, choose BigQuery.

Add BigQuery data to a report.

  1. On the left nav, choose CUSTOM QUERY.
  2. Select or enter your billing project ID.
  3. Under Enter Custom Query, paste the following SQL query:
select
    ct.state_fips_code,
    ct.county_fips_code,
    c.county_name,
    ct.tract_ce,
    ct.geo_id,
    ct.tract_name,
    ct.lsad_name,
    ct.internal_point_lat,
    ct.internal_point_lon,
    ct.internal_point_geo,
    ct.tract_geom,
    acs.total_pop,
    acs.households,
    acs.male_pop,
    acs.female_pop,
    acs.median_age,
    acs.median_income,
    acs.income_per_capita,
    acs.gini_index,
    acs.owner_occupied_housing_units_median_value,
    acs.median_rent,
    acs.percent_income_spent_on_rent,
from `bigquery-public-data.geo_census_tracts.census_tracts_washington` ct
left join `bigquery-public-data.geo_us_boundaries.counties` c
    on (ct.state_fips_code || ct.county_fips_code) = c.geo_id
left join `bigquery-public-data.census_bureau_acs.censustract_2018_5yr` acs
    on ct.geo_id = acs.geo_id
This query retrieves data from BigQuery Public Datasets about U.S. census tracts in Washington state. A census tract is a relatively small subdivision of a county. The query joins the geospatial data about the census tract boundaries with census statistics, such as population, age, income, and housing costs.

Click Add to add this data to the report.

2Add a Google Map to the report

  1. Delete the table on the report page.
  2. On the top left, click Add a chart.
  3. Under Google Maps, click Filled map.

3Configure the map

Configuring the Google Map.The map won't display yet. You'll need to add the field that uniquely identifies each location first.
 
  1. On the right, under Location, click "Invalid dimension," then choose geo_id.
    1. This field uniquely identifies each census tract.
  2. Under Geospatial field, click Add metric, then choose tract_geom.
    1. This field contains the BigQuery GEOGRAPHY data that defines the polygons we want to display.

The map should look like this:

Map with missing polygons.

 

Why is the map is missing polygons?
 
A Google Map in Data Studio can plot up to 100,000 points (polygon vertices), but the tract_geom column contains 911,364 points. To reduce the number of points, you can add a filter to focus on a specific area.
Adding a drop-down filter for county_name.
  1. In the toolbar, click Add a control.
  2. Select Drop-down list.
  3. Set the Control field to county_name, and, for Default selection, enter King.

Now you should see all polygons for King County, which contains Seattle:

Map with polygons.

4Style the map

The map's default color metric is Record Count. Let’s choose a more interesting metric.

On the right, under Color metric, choose percent_income_spent_on_rent.

5Interact with the map

To use the map, in the top right, click View.

In View mode, you can zoom and pan around and notice census tracts where people spend nearly half their income on rent, such as the University District in Seattle:

6Change the tooltip

As you mouse over the map, you'll notice that the tooltip shows the “geo_id”, which isn’t particularly meaningful:

Tooltip using geo_id.

Complete fields configuration for the map.You can provide viewers with a better tooltip by changing the Tooltip dimension.

  1. In the top right, click Edit.
  2. Select the map.
  3. On the right, under Tooltip, choose lsad_name.
    1. This field contains the human-readable census tract name:

7Add more style to the map

You can customize the appearance of the map in the STYLE tab. For example, you could increase the fill opacity to 80% and change the color gradient from blue to red.

Completed map example.

Congratulations!

You've created a Google Map in Data Studio that visualizes BigQuery GEOGRAPHY data.


Appendix: Visualize points

Data Studio supports visualizing polygons. If you have a GEOGRAPHY field that contains points and you want to visualize them in Data Studio,

  1. Return the coordinates in the format lat,lng (e.g., 47.4073238,-120.5757999). For example, if you have a GEOGRAPHY field point_geom_field, you can use the following SQL expression:
    ST_Y(point_geom_field) || "," || ST_X(point_geom_field)
  2. In Data Studio, set this field’s data type to Geo → Latitude, Longitude.
  3. Choose Bubble map and choose this field as the Location.
Was this helpful?
How can we improve it?