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.
SELECT JHU.FIPS, JHU.PROVINCE_STATE, JHU.COUNTY, JHU.DATE, GEOSQL.COUNTY_POPULATION,
SUM(JHU.CASES) AS COUNTY_CASES,
DIV0(COUNTY_CASES,GEOSQL.COUNTY_POPULATION) AS CASES_PER_CAPITA_COUNTY,
CASES_PER_CAPITA_COUNTY * 100000 AS CASES_PER_100K
FROM STARSCHEMA_COVID19.PUBLIC.JHU_COVID_19 JHU
LEFT JOIN (
SELECT LEFT(CBG,5) AS FIPS, SUM(GEO.TOTAL_POPULATION) AS COUNTY_POPULATION
FROM SAFEGRAPH_SAFEGRAPH_SHARE.PUBLIC.US_POPULATION_BY_SEX_GEO GEO GROUP BY FIPS) GEOSQL
ON GEOSQL.FIPS = JHU.FIPS
WHERE JHU.COUNTRY_REGION = 'United States' AND JHU.DATE = TO_DATE('2020-11-21') AND JHU.CASE_TYPE IN('Confirmed')
AND JHU.PROVINCE_STATE = 'Utah'
GROUP BY JHU.PROVINCE_STATE, JHU.COUNTY, JHU.FIPS, GEOSQL.COUNTY_POPULATION, JHU.DATE, JHU.CASE_TYPE
ORDER BY JHU.FIPS;

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.
County Name | JHU UID Code |
Bear River | 84070015 |
Central Utah | 84070016 |
Southeast Utah | 84070017 |
Southwest Utah | 84070018 |
TriCounty | 84070019 |
Weber-Morgan | 84070020 |
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.
/*--------- Utah FIPS Mapping Allocation by Population ---------------------------------------------------------
County Name JHU UID Code
Bear River 84070015
Central Utah 84070016
Southeast Utah 84070017
Southwest Utah 84070018
TriCounty 84070019
Weber-Morgan 84070020
--https://github.com/CSSEGISandData/COVID-19/issues/3066
Southeast Utah, Utah:
Carbon County, Emery County, and Grand County.
49007, 49015, 49019
Weber-Morgan, Utah:
Weber County and Morgan County.
49057, 49029
Central Utah, Utah:
Juab County, Millard County, Piute County, Sanpete County, Sevier County, and Wayne County.
49023, 49027, 49031, 49039, 49041, 49055
TriCounty, Utah:
Uintah County, Duchesne County, and Daggett County.
49047, 49013, 49009
Bear River, Utah:
Box Elder County, Cache(?), Rich(?).
49003, (?)49005, (?)49033
Southwest Utah, Utah:
Washington County, Iron County, Kane County, Beaver County, and Garfield County
49053, 49021, 49025, 49001, 49017
--------------------------------------------------------------------------------------------------------------*/
--CREATE TABLE & INSERT VALUES ABOVE
CREATE OR REPLACE TABLE STARSCHEMA_COVID19_QA.PUBLIC.TBL_UTAH_FIPS (
AREA_NAME varchar(50),
STATE varchar(50),
FIPS integer,
COUNTY_NAME varchar(50),
POPULATION integer
);
--INSERT MAPPING VALUES
--https://www.utah-demographics.com/counties_by_population
INSERT INTO STARSCHEMA_COVID19_QA.PUBLIC.TBL_UTAH_FIPS VALUES
('Southwest Utah','Utah',49001,'Beaver',6710),
('Bear River','Utah',49003,'Box Elder',56046),
('Bear River','Utah',49005,'Cache',128289),
('Southeast Utah','Utah',49007,'Carbon',20463),
('TriCounty','Utah',49009,'Daggett',950),
('TriCounty','Utah',49013,'Duchesne',19938),
('Southeast Utah','Utah',49015,'Emery',10012),
('Southwest Utah','Utah',49017,'Garfield',5051),
('Southeast Utah','Utah',49019,'Grand',9754),
('Southwest Utah','Utah',49021,'Iron',54839),
('Central Utah','Utah',49023,'Juab',12017),
('Southwest Utah','Utah',49025,'Kane',7886),
('Central Utah','Utah',49027,'Millard',13188),
('Weber-Morgan','Utah',49029,'Morgan',12124),
('Central Utah','Utah',49031,'Piute',1479),
('Bear River','Utah',49033,'Rich',2483),
('Central Utah','Utah',49039,'Sanpete',30939),
('Central Utah','Utah',49041,'Sevier',21620),
('TriCounty','Utah',49047,'Uintah',35734),
('Southwest Utah','Utah',49053,'Washington',177556),
('Central Utah','Utah',49055,'Wayne',2711),
('Weber-Morgan','Utah',49057,'Weber',260213);
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,
--MAKE JOIN AND ALLOCATION SQL IF NULL FOR UTAH
WITH
counties AS (
SELECT JHU.FIPS, JHU.PROVINCE_STATE, JHU.COUNTY, JHU.DATE, GEOSQL.COUNTY_POPULATION,
SUM(JHU.CASES) AS COUNTY_CASES,
DIV0(COUNTY_CASES,GEOSQL.COUNTY_POPULATION) AS CASES_PER_CAPITA_COUNTY,
CASES_PER_CAPITA_COUNTY * 100000 AS CASES_PER_100K
FROM STARSCHEMA_COVID19.PUBLIC.JHU_COVID_19 JHU
LEFT JOIN (
SELECT LEFT(CBG,5) AS FIPS, SUM(GEO.TOTAL_POPULATION) AS COUNTY_POPULATION
FROM SAFEGRAPH_SAFEGRAPH_SHARE.PUBLIC.US_POPULATION_BY_SEX_GEO GEO GROUP BY FIPS) GEOSQL
ON GEOSQL.FIPS = JHU.FIPS
WHERE JHU.COUNTRY_REGION = 'United States' AND JHU.DATE = TO_DATE('2020-11-21') AND JHU.CASE_TYPE IN('Confirmed')
AND JHU.PROVINCE_STATE = 'Utah' AND JHU.FIPS IS NOT NULL
GROUP BY JHU.PROVINCE_STATE, JHU.COUNTY, JHU.FIPS, GEOSQL.COUNTY_POPULATION, JHU.DATE, JHU.CASE_TYPE
ORDER BY JHU.FIPS),
utahCounties AS (
SELECT FIPS, PROVINCE_STATE, COUNTY_NAME, DATE, COUNTY_POPULATION, COUNTY_CASES_ALLOCATED_BY_POPULATION,
CASES_PER_CAPITA_COUNTY, CASES_PER_100K FROM
(SELECT UTAHSQL.FIPS, JHU.PROVINCE_STATE, JHU.COUNTY, UTAHSQL.COUNTY_NAME, JHU.DATE, UTAHSQL.POPULATION AS COUNTY_POPULATION,
(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,
DIV0(COUNTY_CASES_ALLOCATED_BY_POPULATION,UTAHSQL.POPULATION) AS CASES_PER_CAPITA_COUNTY,
CASES_PER_CAPITA_COUNTY * 100000 AS CASES_PER_100K
FROM STARSCHEMA_COVID19.PUBLIC.JHU_COVID_19 JHU
LEFT JOIN (
SELECT AREA_NAME, STATE, FIPS, COUNTY_NAME, POPULATION
FROM STARSCHEMA_COVID19_QA.PUBLIC.TBL_UTAH_FIPS) UTAHSQL
ON JHU.PROVINCE_STATE = UTAHSQL.STATE AND JHU.COUNTY = UTAHSQL.AREA_NAME
WHERE JHU.COUNTRY_REGION = 'United States' AND JHU.DATE = TO_DATE('2020-11-21') AND JHU.CASE_TYPE IN('Confirmed')
AND JHU.PROVINCE_STATE = 'Utah' AND JHU.FIPS IS NULL
GROUP BY JHU.PROVINCE_STATE, JHU.COUNTY, UTAHSQL.COUNTY_NAME, UTAHSQL.FIPS, UTAHSQL.POPULATION, JHU.DATE, JHU.CASE_TYPE, UTAHSQL.AREA_NAME
ORDER BY UTAHSQL.FIPS))
SELECT * FROM counties
UNION
SELECT * FROM utahCounties;

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.

--County Data with Populations, Deaths and Cases and Utah County Areas Allocated by Population for Tableau TWB
WITH
cases AS (
WITH counties AS (
SELECT JHU.FIPS, JHU.PROVINCE_STATE, JHU.COUNTY, JHU.DATE, GEOSQL.COUNTY_POPULATION,
SUM(JHU.CASES) AS COUNTY_CASES,
DIV0(COUNTY_CASES,GEOSQL.COUNTY_POPULATION) AS CASES_PER_CAPITA_COUNTY,
CASES_PER_CAPITA_COUNTY * 100000 AS CASES_PER_100K
FROM STARSCHEMA_COVID19.PUBLIC.JHU_COVID_19 JHU
LEFT JOIN (
SELECT LEFT(CBG,5) AS FIPS, SUM(GEO.TOTAL_POPULATION) AS COUNTY_POPULATION
FROM SAFEGRAPH_SAFEGRAPH_SHARE.PUBLIC.US_POPULATION_BY_SEX_GEO GEO GROUP BY FIPS) GEOSQL
ON GEOSQL.FIPS = JHU.FIPS
WHERE JHU.COUNTRY_REGION = 'United States' AND JHU.DATE = <Parameters.Date to Report> AND JHU.CASE_TYPE IN('Confirmed')
AND JHU.FIPS IS NOT NULL
GROUP BY JHU.PROVINCE_STATE, JHU.COUNTY, JHU.FIPS, GEOSQL.COUNTY_POPULATION, JHU.DATE, JHU.CASE_TYPE
ORDER BY JHU.FIPS),
utahCounties AS (
SELECT FIPS, PROVINCE_STATE, COUNTY_NAME, DATE, COUNTY_POPULATION, COUNTY_CASES_ALLOCATED_BY_POPULATION,
CASES_PER_CAPITA_COUNTY, CASES_PER_100K FROM
(SELECT UTAHSQL.FIPS, JHU.PROVINCE_STATE, JHU.COUNTY, UTAHSQL.COUNTY_NAME, JHU.DATE, UTAHSQL.POPULATION AS COUNTY_POPULATION,
(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,
DIV0(COUNTY_CASES_ALLOCATED_BY_POPULATION,UTAHSQL.POPULATION) AS CASES_PER_CAPITA_COUNTY,
CASES_PER_CAPITA_COUNTY * 100000 AS CASES_PER_100K
FROM STARSCHEMA_COVID19.PUBLIC.JHU_COVID_19 JHU
LEFT JOIN (
SELECT AREA_NAME, STATE, FIPS, COUNTY_NAME, POPULATION
FROM STARSCHEMA_COVID19_QA.PUBLIC.TBL_UTAH_FIPS) UTAHSQL
ON JHU.PROVINCE_STATE = UTAHSQL.STATE AND JHU.COUNTY = UTAHSQL.AREA_NAME
WHERE JHU.COUNTRY_REGION = 'United States' AND JHU.DATE = <Parameters.Date to Report> AND JHU.CASE_TYPE IN('Confirmed')
AND JHU.PROVINCE_STATE = 'Utah' AND JHU.FIPS IS NULL
GROUP BY JHU.PROVINCE_STATE, JHU.COUNTY, UTAHSQL.COUNTY_NAME, UTAHSQL.FIPS, UTAHSQL.POPULATION, JHU.DATE, JHU.CASE_TYPE, UTAHSQL.AREA_NAME
ORDER BY UTAHSQL.FIPS))
SELECT * FROM counties
UNION
SELECT * FROM utahCounties
),
deaths AS(
WITH countiesDeaths AS (
SELECT JHU.FIPS, JHU.PROVINCE_STATE, JHU.COUNTY, JHU.DATE, GEOSQL.COUNTY_POPULATION,
SUM(JHU.CASES) AS COUNTY_DEATHS,
DIV0(COUNTY_DEATHS,GEOSQL.COUNTY_POPULATION) AS DEATHS_PER_CAPITA_COUNTY,
DEATHS_PER_CAPITA_COUNTY * 100000 AS DEATHS_PER_100K
FROM STARSCHEMA_COVID19.PUBLIC.JHU_COVID_19 JHU
LEFT JOIN (
SELECT LEFT(CBG,5) AS FIPS, SUM(GEO.TOTAL_POPULATION) AS COUNTY_POPULATION
FROM SAFEGRAPH_SAFEGRAPH_SHARE.PUBLIC.US_POPULATION_BY_SEX_GEO GEO GROUP BY FIPS) GEOSQL
ON GEOSQL.FIPS = JHU.FIPS
WHERE JHU.COUNTRY_REGION = 'United States' AND JHU.DATE = <Parameters.Date to Report> AND JHU.CASE_TYPE IN('Deaths')
AND JHU.FIPS IS NOT NULL
GROUP BY JHU.PROVINCE_STATE, JHU.COUNTY, JHU.FIPS, GEOSQL.COUNTY_POPULATION, JHU.DATE, JHU.CASE_TYPE
ORDER BY JHU.FIPS),
utahCountiesDeaths AS (
SELECT FIPS, PROVINCE_STATE, COUNTY_NAME, DATE, COUNTY_POPULATION, COUNTY_DEATHS_ALLOCATED_BY_POPULATION,
DEATHS_PER_CAPITA_COUNTY, DEATHS_PER_100K FROM
(SELECT UTAHSQL.FIPS, JHU.PROVINCE_STATE, JHU.COUNTY, UTAHSQL.COUNTY_NAME, JHU.DATE, UTAHSQL.POPULATION AS COUNTY_POPULATION,
(SUM(UTAHSQL.POPULATION) OVER (PARTITION BY UTAHSQL.AREA_NAME))::INTEGER AS AREA_POPULATION,
(SUM(JHU.CASES) * (SUM(UTAHSQL.POPULATION) / AREA_POPULATION))::INTEGER AS COUNTY_DEATHS_ALLOCATED_BY_POPULATION,
DIV0(COUNTY_DEATHS_ALLOCATED_BY_POPULATION,UTAHSQL.POPULATION) AS DEATHS_PER_CAPITA_COUNTY,
DEATHS_PER_CAPITA_COUNTY * 100000 AS DEATHS_PER_100K
FROM STARSCHEMA_COVID19.PUBLIC.JHU_COVID_19 JHU
LEFT JOIN (
SELECT AREA_NAME, STATE, FIPS, COUNTY_NAME, POPULATION
FROM STARSCHEMA_COVID19_QA.PUBLIC.TBL_UTAH_FIPS) UTAHSQL
ON JHU.PROVINCE_STATE = UTAHSQL.STATE AND JHU.COUNTY = UTAHSQL.AREA_NAME
WHERE JHU.COUNTRY_REGION = 'United States' AND JHU.DATE = <Parameters.Date to Report> AND JHU.CASE_TYPE IN('Deaths')
AND JHU.PROVINCE_STATE = 'Utah' AND JHU.FIPS IS NULL
GROUP BY JHU.PROVINCE_STATE, JHU.COUNTY, UTAHSQL.COUNTY_NAME, UTAHSQL.FIPS, UTAHSQL.POPULATION, JHU.DATE, JHU.CASE_TYPE, UTAHSQL.AREA_NAME
ORDER BY UTAHSQL.FIPS))
SELECT * FROM countiesDeaths
UNION
SELECT * FROM utahCountiesDeaths
)
SELECT
cases.*,
deaths.COUNTY_DEATHS,
deaths.DEATHS_PER_CAPITA_COUNTY,
deaths.DEATHS_PER_100K
FROM cases
LEFT JOIN deaths on cases.FIPS = deaths.FIPS
WHERE cases.COUNTY <> 'unassigned' AND cases.FIPS IS NOT NULL
AND cases.PROVINCE_STATE IN ('Alabama','Alaska','Arizona','Arkansas','California'
,'Colorado','Connecticut','Delaware','District of Columbia','Florida','Georgia','Hawaii'
,'Idaho','Illinois','Indiana','Iowa','Kansas','Kentucky','Louisiana','Maine','Maryland'
,'Massachusetts','Michigan','Minnesota','Mississippi','Missouri','Montana','Nebraska'
,'Nevada','New Hampshire','New Jersey','New Mexico','New York','North Carolina'
,'North Dakota','Ohio','Oklahoma','Oregon','Pennsylvania','Rhode Island','South Carolina'
,'South Dakota','Tennessee','Texas','Utah','Vermont','Virgin Islands','Virginia'
,'Washington','West Virginia','Wisconsin','Wyoming');
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