I was going to spend my weekend knitting, organizing my knitting stuff, and selling excess knitting stuff. (WOULD YOU LIKE SOME MACHINE KNITTING MAGAZINES FROM THE 1980s?)

However, I have finally gotten irritated at the way that San Diego County is(n’t) reporting its COVID-19 numbers. It used to be reported daily. I wasn’t entirely happy with the way that they broke down the data, but it was easy for me to go to their webpage and see the daily number of new cases. Now that we have decided that the pandemic is over, they announced that they were only going to update the numbers once a week. Apparently, they changed their minds, and now they are reporting on weekdays and each update shows the number of new cases since the previous update – so in order to get new cases per day, you need to know how many days since the latest update.

No problem, I thought, I will just dust off my old code for getting data from the County’s API. (Aside: Why does government love ArcGIS so much?) Turns out that the data served by the API is also updated on the same sporadic schedule (but at least the payload has dates).

Next up: Checking with the state. You would think that there was someone in the state of California who could put together a reasonable API. I think that I can get the data that I need, but it took a lot of digging to find it. The state’s data might also only be sporadic. I may have to monitor this over the next few days before I make a dedicated page that I can use to keep track of things.

The state’s data is not great either. I really want to see the case data broken down by age. I really, really want to know how much I should be annoyed that the new cases are coming from unvaccinated adults (most of whom should know better) vs. unvaccinated children (typically not their fault).

While there is a chance that they’d consider binning things by age, there is no chance that they’re going to break things down by vaccination status (especially since a lot of people are bad at interpreting numbers). That’s really what I want to know so that I can decide how much of a risk it is for me to go to Trader Joe’s and the yarn store. I’ve gotten so frustrated buying yarn online and being disappointed by the colors.

Fun fact about the state API: you send SQL to the endpoint, and it returns the result of your query. In order to prevent you from doing SQL injection the state merely wraps your entire query in SELECT * FROM () AS blah LIMIT 32001. The “blah” is not a placeholder that I am using here; it is really part of what is processed by the endpoint. There doesn’t seem to be anything that keeps me from doing some sort of gnarly self-join on the table, but fortunately, today’s task is simple enough that I won’t accidentally write a query that grinds their server to a halt. They’re running Postgres 9.6.19, which you can learn by sending SELECT VERSION() as your query instead of asking about public health data.