Visualizing Geographic Data in PAD

There are several options to visualize geographic data in BigQuery. We’ll cover two options below - Looker Studio and BigQuery Geo Viz - but you can also read about more advanced options in Google’s documentation here.

(SketchPAD) Looker Studio:

Looker Studio is Google’s data visualization program. You can visualize many different data types, including geography, with several different mapping types. Looker Studio supports the following built-in geographic data types:

• Continents
• Subcontinents
• Countries
• Country subdivision first level (in the US, this refers to states)
• Country subdivision second level (in the US, this refers to counties)
• Designated market areas
• City
• Postal code
• Latitude and longitude

Note that some of these geographic types only work for specific mapping chart types in Looker Studio, and there are also formatting requirements for some of the data types. See here for a detailed breakdown from Google. We recommend using numeric codes (FIPS codes) where possible instead of strings. 

FAQ on data visualizations in Looker Studio:

• Q: How do I combine data types? (E.g., I want to see both state and county borders)
  A: You can create a calculated field (or have a field in your data source) that concatenates those and then set that field to the most detailed geo-type you have in your data (e.g., if you have a field that concatenates state and county, mark it as a country subdivision second level). We recommend using FIPS codes wherever possible instead of names.  
  
  
• Q: My data is ending up all over the world and in strange places. What do I do to fix this?
  A: See the info above about combining data types. If you input county information without state information, typically, Google will interpret the county information in strange ways. We recommend always combining data types for the country subdivision second level. If you have this issue with the country subdivision first level, try encoding the country name in the field (e.g., US-AL for Alabama instead of just AL). If you are having this issue for other data types, check that your formatting matches Google’s required formatting linked above. 
  
  
• Q: I want to use a custom .shp file (shapefile) for my data / I want to use a geography type that isn’t built into Looker Studio, like congressional districts. What do I do?
  A: You can do this by setting up BigQuery geography polygons for your data and visualizing them in the Google Map chart option in Looker Studio (note that this only allows you to do a choropleth map). Some common geography types are available in the BigQuery public data project or Google Analytics Hub, and we recommend checking those out before investing a lot of effort into building out the BQ data yourself. See Google's documentation here to create data with the required Geography type for mapping. Note that Google primarily supports GeoJSON file types for direct upload to Google, which is typically not the format a .shp file is in (meaning it will require conversion to GeoJSON or a different approach to be uploaded directly).
  
  Once your data exists, Google has a detailed walkthrough here of how to convert BQ Geography data into a Looker Studio map.

BigQuery Geo Viz

BigQuery Geo Viz is a geovisualizing platform separate from Looker Studio. It runs off of the Google Maps API and allows you to create a map based on a SQL query. Some formatting options are also available to adjust how your map shows up. We recommend using Geo Viz for quick visuals that don’t necessitate an entire dashboard in Looker Studio and don’t need to be shared widely.

To create a Geo Viz, your data must be in BigQuery and formatted in the Geography type (see Google’s documentation here on that data type). You can access the Geo Viz platform here. Once you authorize it to access your project, you can start writing queries to display as visuals. You can see an example of creating a Ge oViz from Google here.

Remember that for other people to access your Geo Viz, they will also need permission to run queries in your project. 

Geo-data

BigQuery public datasets are a treasure trove of publicly available data. In particular, the US Census Bureau provides tons of population and demographic data at a variety of geographical levels. 

Each geographical unit is assigned a FIPS ID by the US Census Bureau. Beware that if you are joining between two data sources, this ID may have different names. This field is called geo_id in Census Bureau data but can be called xxxxx_fips in other datasets (where xx is the geographical unit, e.g., county_fips or state_fips). 

• Be careful that you are joining two tables describing the same geographical level (state with state, county with county, Congressional District with Congressional District). Otherwise, the IDs will not match, and you won’t have accurate data.
• For more information on GEOIDs and FIPS codes, check out the guide and reference sheet directly from the US Census Bureau website.

Mapping in Looker Studio and Geo Viz

For more information about mapping features in Looker Studio and Geo Viz, you can take a look at our help article here. 

Have more questions? Contact us at help@techallies.org