Simon Willison’s Weblog

New call queue ready to test. Also geography.

7th March 2021

Originally posted to my internal blog at VaccinateCA

I just shipped the first working preview version of the new /api/requestCall API [#54]—the API that our caller app uses to get the next location that the caller should be contacting (and lock it so that other users don’t call it at the same time).

I made a 1m45s video demonstrating the new API and showing how you can use the admin tools to both queue up calls and check that the call queue logic is correctly claiming calls that you are working on:

Video here: https://user-images.githubusercontent.com/9599/110281717-150c1c00-7f92-11eb-886f-c4d1a20f99fd.mp4

Shorter version of that video: you can add locations to the “Call Requests” queue using the dropdown action menu at the top of https://vaccinateca-preview.herokuapp.com/admin/core/location/

You can then look at the queue at https://vaccinateca-preview.herokuapp.com/admin/core/callrequest/

Then you can request calls from that queue at https://vaccinateca-preview.herokuapp.com/api/requestCall/debug (make sure you have a fresh JWT by logging out and in of the admin)—every call you request should show your name as having claimed it in https://vaccinateca-preview.herokuapp.com/admin/core/callrequest/

You can also use the admin action tools on https://vaccinateca-preview.herokuapp.com/admin/core/callrequest/ to clear any claims should you run out of test items in the queue

Geography: the counties of Oregon

I also wrote scripts to import 263 locations that might have the vaccine in the state of Oregon—original data sourced from VaccineFinder.

In doing so I ran into an interesting problem: the VaccineFinder data is very high quality, but the one field it was missing was county: each location has an addreess, a city, a zip code and a latitude and longitude, but no indication of which county it’s in.

Counties are a pretty important part of our data model. How could I best derive the counties for each of these locations?

Here’s the issue. I ended up going for the most fun of all of the routes: comparing the latitude/longitude points with geographic polygons representing the different counties.

Thankfully I had most of the pieces for this already. A few years ago I figured out how to build a location-to-timezone API using shapefiles and the SpatiaLite extension for SQLite. The US Census offer a high quality shapefile package for US counties at https://www.census.gov/geographies/mapping-files/time-series/geo/carto-boundary-file.html

So I built a Datasette instance that can turn latitudes and longitudes into counties. You can see how I built it in my simonw/us-counties-datasette repository, but here’s the API itself:

https://us-counties.datasette.io/counties/county_for_latitude_longitude_with_map?longitude=-122.676968&latitude=45.266289

Add .json to the path to get that back as JSON.

I wrote a ./manage.py backfill_location_counties script (code here) which loops through every location we have that is missing a county, calls that API and updates the record with the corresponding county.

This raised another problem: we need County records in our database for the counties of Oregon. And while we’re at it we should populate the counties for all of the other states as well.

Finding a comprehensive list of US counties that included their full FIPS codes was surprisingly difficult. Then I realized I already had one: the US Census shapefile I was using had all of the data we needed.

I wrote a SQL view to transform the shapefile data into a more convenient CSV format with just the data we needed and wrote a ./manage.py import_counties script (here) to import that CSV data directly into our database.

Which almost completely worked... except it found some counties that didn’t correspond to states in our States table. You guessed it... Puerto Rico, Guam, American Samoa and a few other places were being disenfranchised again.

I’ve filed an issue to fix that in the future!

Next up: test, then start making calls

I need help to test the new API: I don’t have deep enough understanding of how the caller app works to know if I’ve messed up something important. Once we’ve had a few people exercise it for a while I think we’re ready to point a copy of the caller app at it and start making some calls through it!