Displaying BigQuery results on Google Maps using Data Studio

Data Studio has a Google Maps layer

The Google Mobility reports provide information on the extent to which visits to retail and recreation establishments has fallen due to COVID-19 related concerns and public health advice. But those reports give you the data as PDFs or a CSV.

What if you want a map that looks like this?

How visits to retail and recreation establishments vary across the USA. Green dots indicate counties where visits have fallen a lot and red dots indicate where visits are about the same.

SQL Query

The Mobility data is in BigQuery as a public dataset, so go to https://console.cloud.google.com/BigQuery and run the following query:

WITH agg AS (
SELECT 
  MAX(date) AS latest_date
FROM `bigquery-public-data.covid19_google_mobility.mobility_report`
),
latest_data AS (
  SELECT
    country_region_code, sub_region_1, sub_region_2, retail_and_recreation_percent_change_from_baseline
  FROM `bigquery-public-data.covid19_google_mobility.mobility_report`, agg
  WHERE date = agg.latest_date AND country_region_code = 'US' 
  AND retail_and_recreation_percent_change_from_baseline IS NOT NULL
),
with_state_fips AS (
  SELECT 
    state_fips_code AS sfc,
    TRIM(REPLACE(sub_region_2, 'County', '')) AS trimmed_county_name,
    retail_and_recreation_percent_change_from_baseline
  FROM latest_data
  JOIN `bigquery-public-data`.utility_us.us_states_area
  ON sub_region_1 = state_name
)
SELECT 
    CONCAT(ST_Y(ST_CENTROID(county_geom)),',',ST_X(ST_CENTROID(county_geom))) AS marker,
    (100 + retail_and_recreation_percent_change_from_baseline) AS mobility
  FROM with_state_fips
  JOIN `bigquery-public-data`.utility_us.us_county_area
  ON trimmed_county_name = county_name AND sfc = state_fips_code

The query above consists of these parts:

1. Find the latest date for which the mobility report is available
2. Pull latest data of every state/county in the US.
3. Use the state name (e.g., WA) to get the states FIPS code (e.g., 53) and massage the county name (e.g. King County) to get rid of the word County.
4. Join against a public dataset of county information to get the centroid of each county.

The resulting data looks like this:

View in Data Studio

In the BigQuery web console, click the button to “Explore Data” and choose Data Studio.

Select the Google Maps layer (see figure below)

Change the Bubble location to “marker” and edit the type to be Geo > Latitude, Longitude.

Remove the Bubble size — we want all dots to be the same size.

Change the Bubble color field to be mobility.

Then, switch over to the Style tab. Increase the number of bubbles to 5000. Then, change the colors so that max=Red, medium=yellow, and min=green as shown below.

You will now have a dashboard that looks like the one at the beginning of this article. Let’s now build a dashboard that also shows the number of cases.

Adding in confirmed cases

Use this query to join the confirmed case counts from the New York Times (it’s also public dataset in BigQuery):

CREATE TEMPORARY FUNCTION trim_county(sub_region_2 STRING) 
AS (
TRIM(REPLACE(sub_region_2, 'County', ''))
);
WITH agg AS (
SELECT 
  MAX(date) AS latest_date
FROM `bigquery-public-data.covid19_google_mobility.mobility_report`
),
latest_data AS (
  SELECT
    country_region_code, 
    sub_region_1, 
    county AS trimmed_county_name,
    retail_and_recreation_percent_change_from_baseline,
    confirmed_cases
  FROM `bigquery-public-data`.covid19_google_mobility.mobility_report, agg
  JOIN `bigquery-public-data`.covid19_nyt.us_counties AS nyt
  ON sub_region_1 = state_name AND trim_county(sub_region_2) = county AND mobility_report.date = nyt.date
  WHERE nyt.date = agg.latest_date AND country_region_code = 'US' 
  AND retail_and_recreation_percent_change_from_baseline IS NOT NULL
),
with_state_fips AS (
  SELECT 
    state_fips_code AS sfc,
    trimmed_county_name,
    retail_and_recreation_percent_change_from_baseline,
    confirmed_cases
  FROM latest_data
  JOIN `bigquery-public-data`.utility_us.us_states_area
  ON sub_region_1 = state_name
)
SELECT 
    CONCAT(ST_Y(ST_CENTROID(county_geom)),',',ST_X(ST_CENTROID(county_geom))) AS marker,
    (100 + retail_and_recreation_percent_change_from_baseline) AS mobility,
    LOG(confirmed_cases) AS log_cases
  FROM with_state_fips
  JOIN `bigquery-public-data`.utility_us.us_county_area
  ON trimmed_county_name = county_name AND sfc = state_fips_code

Set up Data Studio to show bubble sizes based on log_cases:

The result will look something like this:

Note the differences in behavior and outcomes between Colorado and Texas …

Sharing

You can share this Data Studio dashboard just like you would share any Data Studio dashboard. Currently (thanks to Felipe Hoffa for the info), embedding a map is not possible.

Want to see a step-by-step tutorial? Check out this video from Felipe and Yufeng G:

Enjoy!