Displaying BigQuery results on Google Maps using Data Studio

Data Studio has a Google Maps layer

Lak Lakshmanan
May 8, 2020 · 4 min read

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:

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):

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!

Google Cloud - Community

Google Cloud community articles and blogs

Google Cloud - Community

A collection of technical articles and blogs published or curated by Google Cloud Developer Advocates. The views expressed are those of the authors and don't necessarily reflect those of Google.

Lak Lakshmanan

Written by

Data Analytics & AI @ Google Cloud

Google Cloud - Community

A collection of technical articles and blogs published or curated by Google Cloud Developer Advocates. The views expressed are those of the authors and don't necessarily reflect those of Google.

Medium is an open platform where 170 million readers come to find insightful and dynamic thinking. Here, expert and undiscovered voices alike dive into the heart of any topic and bring new ideas to the surface. Learn more

Follow the writers, publications, and topics that matter to you, and you’ll see them on your homepage and in your inbox. Explore

If you have a story to tell, knowledge to share, or a perspective to offer — welcome home. It’s easy and free to post your thinking on any topic. Write on Medium

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store