Tableau & Snowflake: COVID-19 by County Quadrants & Maps fixing Utah Counties

Last week I published a blog article showcasing how to create a COVID-19 quadrant scatter plot by county and turn the quadrants into a map by county https://bigdatadave.com/2020/11/22/tableau-snowflake-covid-19-by-county-quadrants-maps/. As you can see I had a problem with the big hole in Utah. “Utah is reporting county data somewhat differently than many other states. The larger-population counties are reporting confirmed cases and deaths at the county level. However, the smaller counties are banded together into county groups. This is in an effort to protect identities of individuals.” I just had to fix this as it was ruining my beautiful map.

Hole By Design
This is by design and I could easily see this when executing queries scoped to Utah against the JHU data set.

What are These County Groups?
The best summary of the issue with some mappings I found was here in GitHub: https://github.com/CSSEGISandData/COVID-19/issues/3066.

The best mapping of counties to these administrative zones I found was here: https://ualhd.org/

Allocating Cases to Populations
Now that we have this information of how these special Administrative zones map to Counties we can allocate cases to the actual counties. The best way I could come up with is to use country population / administrative area population. Let’s create a table with these mappings and county populations to join back to the base JHU data.

Now a CTE can be used to pull out the special Utah counties and allocate the cases to these base counties of the special administrative zones. These are the key lines to the SQL besides the joins that does the allocation.
(SUM(UTAHSQL.POPULATION) OVER (PARTITION BY UTAHSQL.AREA_NAME))::INTEGER AS AREA_POPULATION,
(SUM(JHU.CASES) * (SUM(UTAHSQL.POPULATION) / AREA_POPULATION))::INTEGER AS COUNTY_CASES_ALLOCATED_BY_POPULATION,

Making the Fix in Tableau
Now we can update our Tableau Workbook with these changes to make a map with Utah filled in with these allocations. See last week’s blog post for detailed instructions on building out the map in Tableau.

Conclusion
Building this data set and visualizations was a rewarding analytical experience. Adding in the additional data to allocate cases to the County level in Utah was a fantastic data challenge. I am happy to share my fix with you. Happy (Utah County level COVID) Querying!

Appendix of Tableau Workbook, SQL and Tableau Public
Tableau Public: https://public.tableau.com/views/COVID-19QuadrantMaps/CountyCasesvsDeathRatesMapUtah?:language=en&:display_count=y&publish=yes&:origin=viz_share_link

Tableau Workbook on GitHub: https://github.com/BigDataDave1/COVIDQuadrant/blob/main/COVID-19%20Quadrant%20Maps%20Utah.twb

Population Allocation SQL on GitHub: https://github.com/BigDataDave1/COVIDQuadrant/blob/main/covid_quradrant_utah_allocation.sql

CTE on GitHub: https://github.com/BigDataDave1/COVIDQuadrant/blob/main/covid_quadrant_map_cte_utah.sql

CTE for Tableau in GitHub: https://github.com/BigDataDave1/COVIDQuadrant/blob/main/covid_quadrant_map_tableau_cte_utah.sql

Originally published at http://bigdatadave.com on November 30, 2020.