Open-source tools for local Covid-19 surveillance

29 Jul 2020

This week TPP has provided new open-source tools to help with regional responses to Covid-19 surveillance. These tools should help CCGs, working with local Public Health teams, to have accurate, near real-time surveillance across their region.


These tools are based on the daily, complete, open data extract from TPP SystmOne (the Strategic Reporting Extract). This extract is used across almost all regions in England to support ongoing risk stratification and service evaluation activities. However, it also forms a very powerful resource for infectious disease surveillance.

TPP has now provided open-source SQL scripts, making it possible for standard analyses on Covid-19 symptoms and testing data to be run on these extracts on a daily basis. The scripts can be fully customised to reflect key local priorities, based on the regional prevalence of specific long-term conditions. For example, the analyses can track positive Covid-19 test results, as well as instances of fever, persistent cough, and anosmia. The scripts also allow for analytic breakdown by ethnic category, gender, and age band.


The tools also enable detailed geographic breakdown to help identify potential target areas via geolocation and heat maps. This approach has already proved vital for specific regional responses, informing targeted advice campaigns in high-risk areas via SMS messaging, for example. It is hoped that similar analyses can play an important role in ongoing surveillance activities over the coming months and into the winter season.


There are a significant number of Covid-19 codes that can be recorded in Electronic Health Records on SystmOne. These codes were initially introduced by TPP in early February, in response to the emerging pandemic. The number of codes available has increased over recent months, with several emergency SNOMED CT releases. There has also been a rapid increase in code usage. There are three main routes for this coding - direct input from clinical staff members, 111 telephone triage, and laboratory messaging for Pillar 2 test results.

The tools are accessible through SystmOne notices for CCG and Trust Reporting units.