Analyze COVID-19 Data with SQL and SQLite

In our first module, we'll get an overview of the workshop, and dive right into a discussion of SQL and basic SQL queries using the real-time COVID-19 data. We'll not only interrogate the databases, but we'll use other tools to display the results in both tabular and graphic form.

At the end of this lecture you’ll learn how to issue basic SQL commands like the SELECT statement with appropriate SQL clauses like WHERE, IN, ORDER BY, LIMIT, and others.

At the end of this lecture you’ll know how to perform in-query calculations with SQL statements like division, multiplication, addition, and subtraction.

At the end of this lecture you’ll know how to advance your knowledge of SQL using aggregate statements like SUM, MIN, MAX, AVG, and GROUP BY.

We are going to integrate everything we learned with basic SQL, calculations, and aggregate functions, and apply it to countywide COVID-19 data.

In this short video, I want to show you how to connect QGIS to SQLite to display spatial information. If you are like me, you'll probably spend hours playing around with this capability before the next workshop.

So, here are a couple of questions you may want to explore:

1. How different was the spatial distribution of deaths in Maryland, New York, and Virginia during April? How about July?

2. Try to link the map using the states table, and visualize the number of deaths in the United States. The following query will get you on your way:

    DROP TABLE IF EXISTS spatialtable;

    CREATE TABLE spatialtable AS

    SELECT state, sum(positiveIncrease) AS positive,sum(negativeIncrease) as negative,

     sum(deathIncrease) AS  deaths, geometry 

    FROM daily, state

    WHERE stusps = daily.state

    GROUP BY geometry, state;

    SELECT recovergeometrycolumn('spatialtable','geometry',4269,'MULTIPOLYGON');

And, you can start to issue other filters in the WHERE clause, like AND dt = '2020-12-25' to visualize the deaths, cases, or hospitalizations on Christmas day.

Above all, have fun with this, and see how far you can take it.

Today, we'll expand our knowledge of SQL and begin linking multiple tables on-the-fly to discover the power for relationships in data analysis. To do this, we'll like the real-time COVID-19 data with other database tables to assess COVID-19 activity with statewide demographics. We will also explore the powerful capabilities of SQL for updating and modifying database tables.

At the end of this lecture, you’ll learn how to link multiple database tables together with primary and secondary identifiers using SQL constructs of JOIN and alter data and add records in database tables using ALTER and UPDATE.

We’re going to explore a hot topic a lot of people have strong opinions on: are mask mandates effective.  This can be a very sensitive topic, especially for those who’ve been directly effected by COVID-19.  So, we are going to be as respectful as we can of the subject, and focus on ways a data analyst might perform and initial assessment of the problem.   While we might not come to a conclusion to this very complex question, we’ll see how pocessing the data with SQL can help us identify other areas of research to explore. 

If you are using Esri products like ArcGIS Pro or ArcMap, you may be feeling a little left out. In this short video, I'm going to show you a little workaround to get ArcGIS to read the spatial tables directly from SQLite. I'll also explain why we have to do this.

Check out the .pdf file to get the SQL text to paste in.

Over the next two hours, you will work alongside me to explore the spatial capabilities of SQLite with SpatiaLite. We'll eventually incorporate COVID-19 data, but initially, we will look at just a few spatial capabilities. As you follow along, feel free to pause the videos and try the SQL code for yourself.

In this module, we'll build on our basic and intermediary work to include spatial considerations in database analysis with SQL. This will include basic visualization of maps (making the results of our queries spatial) for states and counties. The transition to counties will allow us to see the COVID-19 trends on a finer scale as well as integrate multiple tables together, providing an even richer set of analyses.

In this lecture we'll create a time-series analysis for COVID-19 data throughout the United States.

In this lecture we’ll create a time-series analysis for COVID-19 data throughout the United States.