Query Free Covid-19 Test Slots (Netherlands)

Wiki (en)
, , ,
1

The Dutch government provides a large quantity of tests every calendar day to detect Corona outbreaks as early as possible. However, due to high demand it can be hard to manually find a free test slot. New slots are made available in batches to avoid a very long planning horizon.

Currently the coronatest.nl site has no “search nearby location“ function. Inspired by sessions on R at Dataharvest Digital 2020, this article will illustrate how to query the coronatest site for free slots, cache them and possibly publish them to a website. However, instead of using R, this sample will use Invantive SQL.

Please use this code within the usage conditions posed by the site. The site will automatically throw a 429 (too many requests) when you overuse the site. By intent no sample is given how to circumvent such a limitation.

The sample can be extended by searching on travel distance from the current location.

Instructions

Execute the following steps to query for free Covid-19 test slots across a range of postal codes:

  • Start Invantive Query Tool.
  • Log on to Invantive Query Tool using the sample Dummy database.
  • Log on to coronatest.nl using Google Chrome.
  • Proceed to the form for a reservation:

image
image716×706 23.9 KB

  • Press F12 to display the developer tools.
  • Extract the token granted to access coronatest.nl from the headers as shown:

image

  • Place the token in the SQL statement.
  • Run the SQL statement.
  • The results resemble:

image
image692×293 13.6 KB

  • Please note that multiple postal codes can result the same free slot.

The Invantive SQL statement to search for free slots on https://user-api.coronatest.nl/ is:

select pcgen.value
,      res.*
from   range
       ( 10 /* Number of consecutive codes to test. */
       , 1000 /* Start postal code. */
       )@datadictionary pcgen
join   httpdownload
       ( url => 'https://user-api.coronatest.nl/afspraken/reserveren'
       , acceptMimeType => 'application/json'
       , method => 'POST'
       , headers => 'authorization=Bearer nI---w'
       , diskCache => true
       , textPayload => '{"postcodeCijfers":"' || pcgen.value || '","vervoersmiddel":"AUTO"}'
       , cacheKey => to_char(pcgen.value || '-' || to_char(sysdate, 'YYYYMMDD'))
       , minimumDurationMs => 2000
       )@DataDictionary htp
join   jsontable 
       ( '[*]'
         passing htp.content_clob
         columns StartDatum datetime path 'begindatum'
         ,       EindDatum datetime path 'einddatum'
         ,       AfspraakID int64 path 'afspraakId'
         ,       LocatieID int64 path 'locatie.id'
         ,       LocatieStraat varchar2 path 'locatie.straat'
         ,       LocatieHuisnummer int32 path 'locatie.huisnummer'
         ,       LocatieHuisnummerToevoeging varchar2 path 'locatie.huisnummerToevoeging'
         ,       LocatiePostcode varchar2 path 'locatie.postcode'
         ,       LocatiePlaats varchar2 path 'locatie.plaats'
         ,       LocatieBereikbaarheid varchar2 path 'locatie.bereikbaarheid'
       ) res
--
-- Find first 10 available slots.
--
limit 10