Uitlezen Covid-cijfers uit Highcharts grafieken op RIVM site

In dit topic leer je hoe je met Invantive SQL (inclusief gratis versies) de Covid-grafieken op de RIVM-site kunt laden in het no code platform Knack voor verdere verwerking. De volgende cijfers worden afgeleid uit de grafieken:

  • Aantal overledenen op peildatum na alle correcties t/m laatste rapportagedatum.
  • Aantal ziekenhuisopnames op peildatum na alle correcties t/m laatste rapportagedatum.
  • Aantal GGD-positieve tests op peildatum na alle correcties t/m laatste rapportagedatum.
  • En van elk een cumulatief aantal vanaf begin t/m peildatum.

Op de URL https://www.rivm.nl/coronavirus-covid-19/grafieken vind je een groot aantal grafieken die wekelijks op dinsdag bijgewerkt worden. De datasets zijn momenteel niet gemakkelijk geautomatiseerd uit te lezen, maar wel handmatig te downloaden via de “CSV downloaden” optie.

In deze grafiek vallen de cijfers uit elkaar in voorheen gerapporteerde totalen per dag (auberginekleur) en mutaties over het eerder gerapporteerde totaal (oranje). De mutaties over het verleden hebben meestal betrekking op het recente verleden, maar soms ook over langere tijd geleden.

De cijfers zitten in de HTML van de webpagina verwerkt binnen een grote <script> tag met daarin JavaScript. De data staat hier in als JSON, maar dan verpakt als een grote JavaScript tekst:
image
De cijfers zijn dus eerst in een doosje gedaan als een grote JavaScript tekst en daarna in een HTML-doosje.

Het ophalen van de gegevens gaat met de volgende query:

--
-- Extract highchart source data from RIVM. Note that the actual data 
-- is JSON encapsulated into JavaScript text into a <script> tag.
-- There are 7 tables on the page, each with a different numeric code.
--
create or replace table rivmgraphsourcedata@inmemorystorage
as
select jte.*
from   
  ( select substr(contents_char, instr(CONTENTS_CHAR, '"easychart":')) c
    from   HTTPDOWNLOAD@DataDictionary
           ( 'https://www.rivm.nl/coronavirus-covid-19/grafieken' )
  ) idta
join   jsontable
       ( 'data.[*]'
         passing '{"data": [{' || substr(c, 1, instr(c, ',"navbar":{"title":"COVID-19') - 1) || '}]}'
         columns csv422881    varchar2 path 'easychart.422881-0-field_par_chart.csv'
         ,       config422881 varchar2 path 'easychart.422881-0-field_par_chart.config'
         ,       csv462501    varchar2 path 'easychart.462501-0-field_par_chart.csv'
         ,       config462501 varchar2 path 'easychart.462501-0-field_par_chart.config'
         ,       csv422191    varchar2 path 'easychart.422191-0-field_par_chart.csv'
         ,       config422191 varchar2 path 'easychart.422191-0-field_par_chart.config'
         ,       csv462471    varchar2 path 'easychart.462471-0-field_par_chart.csv'
         ,       config462471 varchar2 path 'easychart.462471-0-field_par_chart.config'
         ,       csv424961    varchar2 path 'easychart.424961-0-field_par_chart.csv'
         ,       config424961 varchar2 path 'easychart.424961-0-field_par_chart.config'
         ,       csv462441    varchar2 path 'easychart.462441-0-field_par_chart.csv'
         ,       config462441 varchar2 path 'easychart.462441-0-field_par_chart.config'
         ,       csv425291    varchar2 path 'easychart.425291-0-field_par_chart.csv'
         ,       config425291 varchar2 path 'easychart.425291-0-field_par_chart.config'
       )
       jte

Merk op dat na het ophalen van de HTML via HTTPDOWNLOAD er geen regexp_replace of htmltable gebruikt wordt, maar een efficiënt gokje om op een tekst te zoeken. De resulterende tekst wordt vervolgens binnen de jsontable omgezet in een array binnen een data-element zodat er een “repeater” is. De verschillende tabellen staan namelijk naast elkaar in plaats van onder elkaar.

In de volgende stap zet je deze naast elkaar structuur om in een verticale lijst van gegevens per tabel horende bij een specifiek grafiek (“transponeren”):

--
-- Transpose the graph data into one row per graph.
-- We assume that the number is a unique identifying code
-- for the graph.
--
create or replace table rivmgraphsourcedatatrans@inmemorystorage
as
select '422881' code
,      csv422881 csv
,      config422881 config
from   rivmgraphsourcedata@inmemorystorage
union all
select '462501'
,      csv462501 csv
,      config462501 config
from   rivmgraphsourcedata@inmemorystorage
union all
select '422191'
,      csv422191 csv
,      config422191 config
from   rivmgraphsourcedata@inmemorystorage
union all
select '462471'
,      csv462471 csv
,      config462471 config
from   rivmgraphsourcedata@inmemorystorage
union all
select '424961'
,      csv424961 csv
,      config424961 config
from   rivmgraphsourcedata@inmemorystorage
union all
select '462441'
,      csv462441 csv
,      config462441 config
from   rivmgraphsourcedata@inmemorystorage
union all
select '425291'
,      csv425291 csv
,      config425291 config
from   rivmgraphsourcedata@inmemorystorage

Dit transponeren kan ook met een SQL pivot, maar bij deze aanpak voel ik me comfortabeler.

In de volgende stap zoek je de titel op binnen de kolom config met de instellingen van de grafiek. Daarna split je met jsontable de JSON-data in csv op in drie velden.

Aangezien de kolomkoppen als eerste rij in csv staan, slaan we de rijen over waar de x-as geen waarde heeft. Dat zijn toevallig de kolomkoppen.

De datums zijn in geschreven woord volgens een Nederlandse schrijfwijze. Er is voor gekozen om met een (2n+1)-aire replace operator deze te herschrijven naar een tekst die betrouwbaar door een to_date omgezet kan worden in een datum:

create or replace table rivmgraphdata@inmemorystorage
as
select src.code
       label 'Unique graph code'
,      cfg.title
       label 'Title'
,      to_date
       ( replace
         ( dta.xaxisdatelabel
         , ' jan', '-01-2020'
         , ' feb', '-02-2020'
         , ' mrt', '-03-2020'
         , ' apr', '-04-2020'
         , ' mei', '-05-2020'
         , ' jun', '-06-2020'
         , ' jul', '-07-2020'
         , ' aug', '-08-2020'
         , ' sep', '-09-2020'
         , ' okt', '-10-2020'
         , ' nov', '-11-2020'
         , ' dec', '-12-2020'
         )
       , 'DD-MM-YYYY'
       )
       xaxisdate
       label 'Date'
,      coalesce(cast(dta.corr_cases_c as number), 0)
       corr_cases
       label 'Correction'
,      coalesce(cast(dta.total_cases_c as number), 0)
       total_cases_excl_corr
       label 'Total cases excl correction'
,      coalesce(cast(dta.corr_cases_c as number), 0)
       + coalesce(cast(dta.total_cases_c as number), 0)
       total_cases_incl_corr
       label 'Total cases incl correction'
from   rivmgraphsourcedatatrans@inmemorystorage src
join   jsontable
       ( 'data.[*]'
         passing '{"data": [' || src.config || ']}'
         columns title varchar2 path 'title.text'
       )
       cfg
join   jsontable
       ( '[*]'
         passing '' || src.csv || ''
         columns xaxisdatelabel     varchar2 path '[0]'
         ,       corr_cases_c       varchar2 path '[1]'
         ,       total_cases_c      varchar2 path '[2]'
       )
       dta
--
-- Remove header row. It is the only row
-- with no value for X-axis.
--
on     dta.xaxisdatelabel is not null
where  src.code in
       ( '462501' -- Overledenen per dag vanaf 27 februari 2020
       , '462471' -- In ziekenhuis opgenomen patienten vanaf 27 februari 2020
       , '462441' -- GGD meldingen positief geteste personen per dag vanaf 27 februari 2020
       )

In de vorige stap waren er maar drie tabellen doorgelaten door de where op src.code.

De drie tabellen worden weer naast elkaar gezet en krijgen alvast de namen van de kolommen waar de waardes in Knack in moeten komen:

create or replace table rivmgraphdatawithoutcumulatives@Inmemorystorage
as
select c462501.xaxisdate
       Datum
,      c462501.total_cases_incl_corr
       Overleden_nieuw_GGD_
       label 'Overledenen'
,      c462471.total_cases_incl_corr
       Ziekenhuisopnames_nieuw_GGD_
       label 'Opnames'
,      c462441.total_cases_incl_corr
       Positief_getest_nieuw_GGD_
       label 'GGD positief getest'
from   rivmgraphdata@inmemorystorage c462501
join   rivmgraphdata@inmemorystorage c462471
on     c462471.code = '462471'
and    c462471.xaxisdate = c462501.xaxisdate
join   rivmgraphdata@inmemorystorage c462441
on     c462441.code = '462441'
and    c462441.xaxisdate = c462501.xaxisdate
where  c462501.code = '462501'

In Knack wil je naast de dagstanden na correctie ook cumulatieve totalen hebben vanaf begin van de metingen tot/met de peildatum. Knack biedt hiervoor geen ondersteuning. Met een cartesisch product tussen de dagstanden en een join op kleiner dan/gelijk wordt per datum een cumulatief totaal van alle cijfers toegevoegd als volgt:

--
-- Cartesian product, takes over 2 seconds.
--
create or replace table rivmgraphdatawithcumulatives@Inmemorystorage
as
select curr.Datum
,      curr.Overleden_nieuw_GGD_
,      curr.Ziekenhuisopnames_nieuw_GGD_
,      curr.Positief_getest_nieuw_GGD_
,      sum(prev.Overleden_nieuw_GGD_)
       Overleden_GGD_
,      sum(prev.Ziekenhuisopnames_nieuw_GGD_)
       Ziekenhuisopnames_GGD_
,      sum(prev.Positief_getest_nieuw_GGD_)
       Positief_getest_GGD_
from   rivmgraphdatawithoutcumulatives@Inmemorystorage curr
left
outer
join   rivmgraphdatawithoutcumulatives@Inmemorystorage prev
on     prev.datum <= curr.datum
group
by     curr.Datum
,      curr.Overleden_nieuw_GGD_
,      curr.Ziekenhuisopnames_nieuw_GGD_
,      curr.Positief_getest_nieuw_GGD_

De gegevens vergelijk je vervolgens met de huidige cijfers in Knack en worden waar nodig bijgesteld:

synchronize Dagelijkse_update@knk
from        rivmgraphdatawithcumulatives@Inmemorystorage
with        insert or update
identified
by          Datum