search

CAVaccineInventory / vial

The Django application powering calltheshots.us
https://vial.calltheshots.us
MIT License
13 stars 1 forks source link

Partner request for API: appointment websites, vaccine types, appointment availability and structured hours information #705

Closed simonw closed 3 years ago

simonw commented 3 years ago
simonw commented 3 years ago

Vaccine types and appointment availability will come from #650.

Appointment websites is a bit harder, because most of those are for things like pharmacy chains and we've not been keeping our "provider" information robust as we scaled beyond California. We may well be able to pull this together based on concordance identifiers for walgreens: etc though.

High quality structured hours information may be available from specific trusted source location providers.

simonw commented 3 years ago

select count(*) from location where provider_id is not null returns 27933 - more than I expected.

https://vial.calltheshots.us/dashboard/?sql=select+provider.name%2C+count%28%2A%29+from+location+join+provider+on+location.provider_id+%3D+provider.id+group+by+provider.id+order+by+count%28%2A%29+desc%3ACDDhxBBt0RPMY1I_hz_wBsabdkxDCjrztAGiZN61Z7c

name    count
Walgreens   6673
Walmart 4603
CVS 3849
None / Unknown / Unimportant    3318
Kroger  1911
Rite-Aid    1690
Publix  1178
Safeway 662
Sam's Pharmacy  568
Rite-Aid Pharmacy   541
County  440
Health Mart 435
Kaiser Permanente   337
Winn-Dixie  215
Costco Pharmacy 126
Von's   119
The Medicine Shoppe 109
Sav-on Pharmacy 98
Ralph's Pharmacy    81

Do those look right though?

select count(*) from concordance_identifier where authority = 'walgreens' returns 9015 (against 6673 for the provider)

select count(*) from concordance_identifier where authority = 'walmart' returns 4679 (against 4603 for the provider, pretty close)

select count(*) from concordance_identifier where authority = 'cvs' returns 13934 (against 3849, so a lot missing there)

simonw commented 3 years ago

Two ways we could do this then:

I like option 1 the best.

Provider is one of our version-tracked tables, so I can tell how often they are updated.

select count(*) from reversion_version where content_type_id = 24 returns 38909 - so we have updated providers a lot!

https://vial.calltheshots.us/dashboard/?sql=select+reversion_version.%2A%2C+reversion_revision.%2A+from+reversion_version+join+reversion_revision+on+reversion_version.revision_id+%3D+reversion_revision.id+where+content_type_id+%3D+24+order+by+date_created+desc%3A_vyWE9gFIqEB47IR8WvQxekVsuakvChoUtRztdfe7WM shows them all.

But not many of those were manual edits: https://vial.calltheshots.us/dashboard/?sql=select+%28select+username+from+auth_user+where+auth_user.id+%3D+user_id%29%2C+count%28%2A%29+as+n+from+%28select+reversion_version.%2A%2C+reversion_revision.%2A+from+reversion_version+join+reversion_revision+on+reversion_version.revision_id+%3D+reversion_revision.id+where+content_type_id+%3D+24+order+by+date_created+desc%29+as+results+group+by+%22user_id%22+order+by+n+desc%3A6BiGaXQWJYKhMk_B5mYxy8RnfkzqpID4bnkQW9BNVgQ

username    n
    38749
cho...  84
swil... 30
lau...  15
mle...  12
ale...  10
kjc...  3
iso...  2
aar...  2
yco...  1
lis...  1
simonw commented 3 years ago

https://vial.calltheshots.us/dashboard/?sql=select%20%22comment%22%2C%20count%28%2A%29%20as%20n%20from%20%28select%20reversion_version.%2A%2C%20reversion_revision.%2A%20from%20reversion_version%20join%20reversion_revision%20on%20reversion_version.revision_id%20%3D%20reversion_revision.id%20where%20content_type_id%20%3D%2024%20and%20user_id%20is%20null%20order%20by%20date_created%20desc%29%20as%20results%20group%20by%20%22comment%22%20order%20by%20n%20desc%3Ay41JTjxSbd4hwNU1wkMRO6qa6TRCCNpZs38NER8i3EQ shows that 38628 of those provider edits had a comment of /api/importLocations called with API key 5:c39e2f3c...

simonw commented 3 years ago

I've now assigned providers to locations that were missing them for the following authorities:

Next step: expose the vaccination information for those providers in the APIv0 output.

simonw commented 3 years ago

I'm going to expose it like this:

{
    "name": "Name",
    "provider": {
        "name": "CVS",
        "vaccine_info_url": "..."
     }
}

So the only field from Provider that I will be exposing is the vaccine_info_url one.

simonw commented 3 years ago

Demo: https://vial-staging.calltheshots.us/api/searchLocations?format=v0preview&size=50

Some of them have this:

    {
      "id": "rec01tcOLdRjMfCnZ",
      "name": "ZELZAH PHARMACY",
      "provider": {
        "name": "None / Unknown / Unimportant",
        "vaccine_info_url": null
      },

https://vial-staging.calltheshots.us/api/searchLocations?provider=None%20/%20Unknown%20/%20Unimportant&size=0 shows 3244 with that value on staging and https://vial.calltheshots.us/api/searchLocations?provider=None%20/%20Unknown%20/%20Unimportant&size=0 shows 2730 with that value in production.

A map of those in production confirms that they are almost all in CA: https://vial.calltheshots.us/api/searchLocations?provider=None%20/%20Unknown%20/%20Unimportant&format=map&all=1

I'm going to update all of those locations to have provider of null instead, then I'm going to mark that provider as obsolete - since provider can be null which means the same thing.

simonw commented 3 years ago

I archived the list of location IDs that currently have None / Unknown / Unimportant in this gist: https://gist.github.com/simonw/023d476fc8b825183551c43ec4153214

simonw commented 3 years ago

Ran this:

url = "https://vial.calltheshots.us/api/searchLocations?provider=None%20/%20Unknown%20/%20Unimportant&all=1&format=ids"
ids = httpx.get(url, headers={"Authorization": "Bearer {}".format(api_key)}).json()
chunks = list(make_chunks(ids, 100))
for chunk in tqdm(chunks):
    httpx.post("https://vial.calltheshots.us/api/updateLocations", timeout=20, json={
      "update": {
        id: {
          "provider_null": True
        } for id in chunk
      },
      "revision_comment": "Issue #707"
    }, headers={"Authorization": "Bearer {}".format(api_key)})

https://vial.calltheshots.us/api/searchLocations?provider=None%20/%20Unknown%20/%20Unimportant&size=0 now returns 0

simonw commented 3 years ago

Renamed https://vial.calltheshots.us/admin/core/provider/5/change/ to have OBSOLETE in the name.

simonw commented 3 years ago

One of the locations in https://api.vaccinatethestates.com/v0/locations.json now looks like this:

{
    "id": "rec07ksibBimtTgNA",
    "name": "WALGREENS #2810",
    "provider": {
        "name": "Walgreens",
        "provider_type": "Pharmacy",
        "vaccine_info_url": "https://www.walgreens.com/findcare/vaccination/covid-19"
    },
    "state": "CA",
    "latitude": 36.93495,
    "longitude": -121.77202,
    "location_type": "Pharmacy",
    "phone_number": "831-768-0183",
    "full_address": "1810 FREEDOM BLVD, Freedom, CA 95019",
    "city": null,
    "county": "Santa Cruz",
    "zip_code": null,
    "hours": {
        "unstructured": "Monday - Sunday: Open 24 hours\nMeal break from 1:30 PM - 2 PM everyday ",
        "structured": [
            {
                "day": "monday",
                "opens": "00:00",
                "closes": "23:59"
            },
            {
                "day": "tuesday",
                "opens": "00:00",
                "closes": "23:59"
            },
            {
                "day": "wednesday",
                "opens": "00:00",
                "closes": "23:59"
            },
            {
                "day": "thursday",
                "opens": "00:00",
                "closes": "23:59"
            },
            {
                "day": "friday",
                "opens": "00:00",
                "closes": "23:59"
            },
            {
                "day": "saturday",
                "opens": "00:00",
                "closes": "23:59"
            },
            {
                "day": "sunday",
                "opens": "00:00",
                "closes": "23:59"
            }
        ]
    },
    "website": null,
    "vaccines_offered": [
        "Pfizer"
    ],
    "concordances": [
        "google_places:ChIJxd3pxlYajoARRxz1FkiAacg",
        "getmyvax_org:62e6da23-df70-40d6-b655-b5643cfe74c5",
        "placekey:223@5vh-dx4-dd9",
        "walgreens:2810",
        "vaccinespotter_org:2736889",
        "vaccinefinder_org:450773d2-cf63-4ddd-a632-45809b45f28b",
        "_tag_provider:walgreens",
        "us_carbon_health:499e577b-78ec-475f-8652-dd0833601f3a",
        "us_carbon_health:da1c2de2-1aab-420e-a366-a178b4020052",
        "placekey:223-223@5vh-dx4-dd9"
    ],
    "last_verified_by_vts": "2021-05-13T16:21:27.244660+00:00",
    "vts_url": "https://www.vaccinatethestates.com/?lng=-121.77202&lat=36.93495#rec07ksibBimtTgNA"
}
simonw commented 3 years ago

I'm going to expose these two fields next: https://github.com/CAVaccineInventory/vial/blob/1a79b1e6cb574bf34ede69b3f7af7e0e18f22e4f/vaccinate/core/models.py#L314-L319

simonw commented 3 years ago

https://vial-staging.calltheshots.us/location/ltbpt is an example of this:

{
    "id": "ltbpt",
    "name": "Walgreen Drug Store",
    "provider": null,
    "state": "NY",
    "latitude": 40.5824,
    "longitude": -73.82907,
    "location_type": "Unknown",
    "phone_number": null,
    "full_address": "10640 ROCKAWAY BEACH BOULEVARD\nROCKAWAY PARK, NY 11694",
    "city": "ROCKAWAY PARK",
    "county": "Queens",
    "zip_code": "11694",
    "hours": {
        "unstructured": null,
        "structured": null
    },
    "website": "https://www.walgreens.com/findcare/vaccination/covid-19",
    "vaccines_offered": null,
    "accepts_appointments": true,
    "accepts_walkins": false,
    "concordances": [
        "vaccinespotter_org:2713291",
        "us_giscorps_vaccine_providers:82c5f284-453d-4cfe-84d8-e0548ac397c1"
    ],
    "last_verified_by_vts": null,
    "vts_url": "https://www.vaccinatethestates.com/?lng=-73.82907&lat=40.58240#ltbpt"
}

Note that accepts_appointments and accepts_walkins can currently be true or false or null for "we don't know".

simonw commented 3 years ago

This is all now live in production, see https://api.vaccinatethestates.com/v0/locations.json

simonw commented 3 years ago

Summary of the changes: