Using BigQuery to build population maps for a vaccination app
Recently we published a map to track the COVID-19 vaccination rollout in the US–learn how this Cloud Native Spatial App was developed using CARTO & Google Cloud
Last week we published a spatial visualization to track the progress of the COVID-19 vaccination rollout in the US. It was very well received and many have asked us how this application was developed. In this follow-up post we are going to take a deep-dive into the creation process covering all of the different components used across the CARTO platform and beyond.
The main tools we used for this analysis were:
- BigQuery: for analysis and data processing
- GitHub actions to extract and push the data to BigQuery
- CARTO Spatial Extension for BigQuery for the creation of random points and the TileSets
- CARTO for React to build the application and deploy it to Firebase.
Let's get started!
Sourcing the data
It is important to highlight that this is not personal data. We don't know which individuals are vaccinated or not all we know is the percentage of vaccinated population by each county and state. From this data we can extrapolate the vaccine progress and create a simulation of individuals for the visualization.
Two main data sources were used as inputs into our analytical approach:
- Data from CDC on vaccination rollout numbers for each US state and county
- Socio Demographic Population Data from the 2018 US Census available on BigQuery public-data project or as part of CARTO Data Observatory commercial datasets.
As noted in last week's post vaccination data from the CDC is utilized at both the state and county levels in our analysis since coverage of data at the county level appears artificially low. In order for us to visualize this data within the app it first needs to be loaded into BigQuery. This data ingestion phase is achieved using a combination of GitHub actions which can be represented by the diagram below.
The script for sourcing the data runs every 2 hours automatically and checks if data has changed or not. If it has changed then it uploads the new data to BigQuery and starts the processing to recreate the visualization.
Socio demographic population data
Alongside the data from the CDC we have also utilized socio-demographic population data from the 2018 American Community Survey (an ongoing survey that provides vital information on a yearly basis about the US and its people) available for enrichment from our Data Observatory. This data set provides total population counts across the United States expressed at the census block level.
We don't need to extract this data. The beauty of modern cloud data warehouse technology is that the data is already loaded in BigQuery by CARTO so you can just join to it.
Analyzing the data
Since the aim of the application is to visualize the progress of vaccination rollout at an individual level within each block group we therefore need to calculate this data based upon the two input data sets.
We achieved this following a two-stage process. The first involves creating a synthetic dataset of the US population by generating a number of random points within each block group equal to its population.
To do that we pull on the Spatial Extension we have for BigQuery composed of a set of functions that can be run right inside BigQuery. The spatial function that we use for this task is ST_GENERATEPOINTS. The resulting dataset contains approximately 330M points each of them representing a US inhabitant.
The BigQuery Spatial Extension works by extending BigQuery with further spatial capabilities coded as UDFs functions that can be run directly for example:
The second stage consists of assigning a vaccination status attribute to each of these points based on the vaccination percentage at the county level as provided by the CDC. This is essentially a dasymetric mapping technique where the CDC dataset at the county level is downscaled to block group level based on the distribution of the population.
Here is the SQL of the process detailed above:
326 million records in a table as seen in BigQuery's new User Interface with the CARTO Chrome Extension
Creating the tilesets
Visualizing 330 Million points on a map is not an easy task. Doing so effectively is even harder. Tilesets are the way to go when you are working with large data volumes and CARTO provides a great way to generate tilesets directly from BigQuery. In this case we are using the CREATE_SIMPLE_TILESET function since we do not want to aggregate the data we want to show every single dot on the map separately.
On the GitHub repository you can find the full query and it goes like this:
There is a lot going on in this query and it utilizes many capabilities of our BigQuery Tiler. A few of them include:
- We are using different sources based on the zoom level of the map. State data at higher zoom levels county data at the lower levels. This way we can optimize performance and provide different visualizations at different zoom levels.
- You can see we are telling the tiler to drop_fraction_as_needed and we are setting a tile_feature_order. These 2 options provide a way to remove points to ensure the tiles on the tileset do not exceed a maximum size of 512kb. By doing so we ensure the map looks great at any zoom level.
- If you look at this other tileset we generate you can see that in order to have valid statistics at any zoom level we create a secondary layer just for the data at a way lower resolution. This is key to ensure the widgets on the visualization display absolute values for the entire dataset not just what is visible at a particular zoom level.
When all of this is done and with some scripting every 2 hours the source data gets updated the synthetic dataset is recreated and finally the 2 tilesets are regenerated.
Aggregation tileset not visible which is used for powering widgets in the app.
Visualization Layer with the dots.
Building the app
We announced CARTO for React last month as the best way to develop Location Intelligence (LI) apps using the CARTO platform React and deck.gl. By using the Create React App template we can dive straight into the creation of this app quickly and easily.
The deck.gl visualization is nested within the CARTO for React app and a number of innovative components have been brought together to create this application.
Check out the GitHub project for the app there are instructions on how to create a similar app on the README.
One key part is the cartography for the visualization. We are using a pink and blue color following this great visualization by Ryan McCullough. In our case we make use of CARTO for deck.gl and therefore the styling of the map is done as follows. Checkout the colorCategory to specify the color based on the "vaccinated" property.
Bringing it all together
Beyond the COVID-19 related insights this spatial application provides it also demonstrates the relative ease of cloud-native spatial app development.
The components of the solution are integrated in such a way that it gives both developers and data scientists the functional capabilities to be able to ingest spatial data carry out sophisticated analytical routines and also develop functionally rich user applications to visualize spatial data at scale.
Want to start using the CARTO Spatial Extension?