This tutorial is intended for people who want to visualize GEOGRAPHY
data from BigQuery using Looker 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 dataset.
Goal
In this tutorial, you'll create a report that shows the affordability of rental properties in Washington state. You'll use a Google Map to visualize GEOGRAPHY
data coming from a public BigQuery dataset.
Before you begin
Create a new blank report
- Sign in to Looker Studio.
- Click Create and then select Report.
- In the Add data to report panel, choose BigQuery.
- In the left navigation, choose CUSTOM QUERY.
- Select or enter your billing project ID.
- 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
Click Add to add this data to the report.
Add a Google Map to the report
- Delete the table on the report page.
- Click Add a chart.
- In the Google Maps section, click Filled map.
Configure the map
- In the Location section, click Invalid dimension, and then choose geo_id.
- This field uniquely identifies each census tract.
- In the Geospatial field section, click Add metric, and then choose tract_geom.
- This field contains the BigQuery
GEOGRAPHY
data that defines the polygons that you want to display.
- This field contains the BigQuery
The map should look like this:
Add a county filter
- In the toolbar, click Add a control.
- Select Drop-down list.
- 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:
Style the map
The map's default color metric is Record Count. You can also choose a different metric.
In the Color metric section, choose percent_income_spent_on_rent.
Interact with the map
Depending on the options that you turned on, 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:
Change the tooltip
As you mouse over the map, you'll notice that the tooltip shows the geo_id
, which isn’t particularly meaningful in this context:
You can provide viewers with a more useful tooltip by changing the Tooltip dimension.
- In the top right, click Edit.
- Select the map.
- In the Tooltip section, choose lsad_name.
- This field contains the human-readable census tract name:
Add 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.
Congratulations!
You've created a Google Map in Looker Studio that visualizes BigQuery GEOGRAPHY
data.
Related resources
- Connect to Google BigQuery
- BigQuery Public Datasets (several datasets with geographic polygon data)
- Google Maps reference
- BigQuery geography functions