More direct access to ESR database

[simeonmiteff]

Hi @vizowl

I did some poking around the arcgis.com dashboard linked from the MOH webste and found that it is pulling some tables that appear to be obtained from, or maintained by https://www.esr.cri.nz/. This gels with the intel @gizmoguy gathered earlier in the week (I suspect he looked at the author names in the spreadsheet metadata and tied that back to ESR).

I worked out that the right query string dumps these tables in JSON format:

• Daily_ESR_Update_Cases
• Daily_ESR_Update_DHB

Perhaps using these would be better than working with the spreadsheets?

Regards,
Simeon.

[vizowl]

Hi @simeonmiteff

I have been playing with both the ESR data and the spreadsheets - on Friday the Ministry missed 4 cases from their update - so I filled them in from the ESR data - but today the Ministry's update came through before the ESR one. So it seems that neither one is better - also I am relying on the overseas/nz/unknown case origin classifications which are not in the ESR data ...

[simeonmiteff]

Some more inferences:

The authoritative database is presumably "ESR EpiSurv", which looks to be a SharePoint system.

Here is the form that case information is being captured with: https://surv.esr.cri.nz/episurv/CaseReportForms/Field_Coronavirus_Mar2020.pdf

I believe that partially overlapping subsets of the fields seen in that form get exported from SharePoint to (at least) two different spreadsheets:

• One that is sent to MOH where it is published on the website, along with someone copying new case rows from the sheet into a CMS for the HTML tables (while their exercising creativity by subtly changing the format almost every day).

• One that is uploaded to arcgis.com to drive the geospatial dashboard.

I imagine that cases imported between the times that the two spreadsheets are exported is the cause of your two missing cases.

[gizmoguy]

I've also been looking into this EpiSurv system, you can find it's details here (however the system itself requires a username/password to gain access):

https://surv.esr.cri.nz/episurv/index.php

I do note that the media outlet thebfd.co.nz claims to be getting data from a direct extract from this system, so possibly it's available to people outside the medical field as well:

https://thebfd.co.nz/blog/2020/04/04/covid-19-update-4-april-2020/

[vizowl]

My understanding is you are correct - everything (that the Ministry holds) sits in EpiSurv and the releases are just extracts from it.

I think the bfd is just mirroring exactly what is on the Ministry of Health update page e.g. https://www.health.govt.nz/our-work/diseases-and-conditions/covid-19-novel-coronavirus/covid-19-current-situation/covid-19-current-cases#lab

[vizowl]

Here's a more useful screenshot with the EpiSurv extract details

[gizmoguy]

Ah you are right, they are just copy and pasting from the dumps on health.govt.nz.

Interestingly when I was doing sleuthing on google thebfd was showing up for my search term but not health.govt.nz.

I guess the elusive true source of this data is out of reach for us non-medical folks.

[gizmoguy]

Okay so I've found another data source out of ESR:

https://nzcoviddashboard.esr.cri.nz/

This one seems to use a streaming API based around rstudio's shiny-server. I am not an expert in this kind of streaming API but if you find someone who is it might be a helpful data source. I have not analysed if this is any more up to date than the other sources of data.

[neon-ninja]

Here's my script to extract a CSV of cases by DHB over time from that ESR shiny app - https://github.com/UoA-eResearch/nz-covid19-data-auto/blob/master/fetch_ESR.py