Not too long after I started working at the Center for Geospatial Science and Technology (CGST), the world was turned upside down due to the Covid-19 pandemic. The Center’s director, Danielle Bram, started working on an ArcGIS Online (AGOL) web map that would display the daily confirmed cases and incidence rate per 100,000 across the cities of Los Angeles County. I was asked if I would be able to do the same for Orange County. I should note that Danielle did all of the work importing and setting up the data in the web map (as shown in the image below). I was tasked primarily with gathering the daily data for Orange County and submitting a feature class (housed in a file geodatabase) for upload to the web map.
PLEASE NOTE: The data displayed in the screenshots for the following post was accurate as of 4-24-2020. The map and accompanying data were no longer updated past that date.
How was the work done?
Phase 1 – Data acquisition and cleaning
My daily process was fairly straightforward. The first step was to grab the daily case numbers (which were actually the total numbers for the previous day) from Orange County’s Covid-19 page. The page is no longer active but, thankfully, I had the foresight to save the page data to PDF files in case I needed to reference them later.
The data grab was accomplished by simply copying and pasting the city, population, and case data into a text file (see below). From there, I was able to fairly quickly reformat the text so that it would import nicely into a spreadsheet. Basically, I just needed to get rid of the embedded HTML and since I already had the city names and population values in preformatted spreadsheets, all I needed to transfer from the text file was the case count.
The next step was to copy and paste the case values into the Cases column in the spreadsheet for the day that was being updated (in this case, 4-24-2020).
I set the spreadsheet up with formulas for the incidence rate, difference from previous day, and percent change from previous day so once the case values were added, all of this information would automatically update. Here are the formulas that were used:
Incidence Rate
Difference from Previous Day
Percent Change from Previous Day
Phase 2 – Spatial and tabular data join
Once the spreadsheets were updated, they were then imported into Esri ArcMap and joined to an Orange County cities feature class (with the city field as the primary key).
Finally, the last step was to export the daily updated layer to a feature class and submit it for upload to the live AGOL web map. The web map screenshot shown at the beginning of this writing contains data from the last daily update (4-24-2020) which is the same data that was used throughout this writing. The map can still be viewed here.
Special thanks to Danielle Bram for having me work on this project. I had a lot of fun with it and I hope you enjoyed reading about it!