The Public Health Environmental Surveillance Open Data Model (PHES-ODM, or ODM). A data model, dictionary and support tools for environmental surveillance.
License: Creative Commons Attribution Share Alike 4.0 International
pooled samples can be handled in the current model with the addition of a field for the parent sample. all samples that went into the one sample would be labeled with the same ID.
Create a template file that is flat and wide, and easy for labs to enter data into, but also easy to etl.
Add naming convention description for measurement category, Measurement Units and and measurement Type
What is a sound recommended wastewater surveillance sampling protocol that adequately addresses issues of sample collection, handling, storage, archiving, etc.... and can be adopted broadly.
Site metadata should have optional start date and end date fields (which would allow multiple lines for the same site) in case sampling characteristics change over time (e.g., sampleType or assayMethod). This allows linkage between site data and sample data to indicate that different observations correspond to different sampling methods.
rename measurmentType to MeasureAggregation
Field names here don't always follow database conventions: https://github.com/Big-Life-Lab/covid-19-wastewater/blob/main/metadata.md
E.g. see https://launchbylunch.com/posts/2014/Feb/16/sql-naming-conventions/
Specifically we could:
This is trivial, I know (feel free to ignore+close). But wanted to note anyway! :)
Should we move from an .MD document to a table or object?
We can generate the md from a table or object and then have better alignment with the ERD and SQL docs.
assayIDDefault vs assayID
@DougManuel was musing in the meeting that assayIDDefault and assayID should be the same.
The terminology that is currently being used for the sample collection method is not very clear to our research group. There seems to be a distinction between grab, continuous, discrete and integrated. Are the latter 3 all different composite samples, but what is there exact difference (any reference)? We are used to the terms grab sample, composite sample - time proportional, and composite sample - flow proportional.
grab sample: Sample was a simple grab samplecontFlowProp: Continuous flow proportionalcontConstant: Continuous constant time proportional?contOther: Continuous otherdiscTimeProp: Discrete time proportionaldiscTimeProp24hq1h: Discrete time proportional 24-hour composite, every 1 hr (make this more general)discTimeProp24hq4h: Discrete time proportional 24-hour composite, every 4 hrdiscTimeProp24hq6h: Discrete time proportional 24-hour composite, every 6 hrdiscFlowProp: Discrete flow proportionaldiscVolumeProp: Discrete volume proportionaldiscOther: Discrete otherintegratedOther: Integrated otheras per the comment of Vince add some extra aggregate measurements to the value
geomean range and single value.
Currently the measurements that are taken on different dates do not fit into the suggested "wide" format of the table.
They do fit into the long format, but that is less intuitive.
as an example NML records some information about the state of the sample when it arives, then typically will process the sample the next day.
Add ability to record a binary measurement
Like covid detected vs not detected, like in the dipstick or pregnancy style tests that are under development.
suggest adding a field
isDetected to the Measurement table, and LOQ and LOD from the assayMethod table would help quantify its level for a given test.
Create a working group to help development.
Include people from:
Add some WWTP parameters. pH, COD, Temp of ww
Method is 1 to many with TestResult
Got data today containing calibration curves do we store them and how?
Can this format assist with inter lab verification.
IE can it work based on sub-samples or a model matrix across different labs.
We should use either
sampleID
or sample.ID and ID
and
sampleNotes
and
sample.Notes
how do we record standard WWTP parameters, like
two options
1 in the measurements table change genregion to be measurementCategory and add some options to the possibilities like this.
covid-unspecified (default)covid-N1covid-N2covid-N3covid-Ecovid-RdRpcovid-N1N2avgww-param-flowww-param-tssww-param-BodLOD should be added to the data model.
this goes in AssayMethod.
Note that if you would update the assayMethod.version field (because of and improvement made to the assay and thus leading to a new version), all measurements that were performed in the past will also be linked to the updated version of the assay method. And thus they will loose their history. In fact the same applies to AssayMethod.date, Lab.updateDate
Moreover, because the AssayMethod table does not have a name field, it is currently imposible to see that two versions of the same assay method exist.
Inhibition should be added to the data model.
this either goes in AssayMethod or Measurement.
LOQ should be added to the data model
this should be added to the AssayMethod table
methodCollection: method used to collect the data.
discTimeProp: Discrete time proportional
discTimeProp24hq1h: Discrete time proportional 24-hour composite, every 1 hr
discTimeProp24hq4h: Discrete time proportional 24-hour composite, every 4 hr
discTimeProp24hq6h: Discrete time proportional 24-hour composite, every 6 hr
The trigger time will vary. For our hospital work it was was every 15 minutes. I'm not sure what is going on with NWT, as they are handling that. Also, the total integration time varies, in NWT is can be 48, to 72 hours. So there needs to be some plasticity here.
Create a form for data provider intake.
This form should :
What is the best approach to report variant levels?
how long till the sample was cooled ?
I noticed that the options for Site.type (where the sample was taken) and Sample.type (what kind of sample is taken) are rather limited in the current version. Also, both fields are currently very dependant on each other.
Following an internal discussion, I would suggest to break down Site.type based on how far the site is removed from the source. As also proposed in this report that was recently published as by the UK governement.
1. Near-source
2. Major sewer pipeline
3. Intermediate sewer infrastructure
4. Wastewater treatment plant
5. Environment
A more complete classification for the Sample.type could look somewhat like this
From a data analysis an statistical point of view, we would greatly benefit from having the concentration measurements of all the replicates rather than a summary statistic (e.g. mean, std dev). This "issue" is a formal request to have replicates measurements as the minimal data set. Thanks!
The ‘notes’ field in the ‘Site’ data group can also be added to each of the other data groups to capture any additional ‘site-sample specific’ information.
Vince
when talking with the UW lab they get a 250ml sample from the plant but they then take 40ml as part of the extraction procedure.
Break assay method up to:
working on the transformation script and I realized that the same lab that measures Viral copies per ml may also measure temperature of the sample. the defaultAssay, can't apply to temperature of sample.
So we would need another table, linking lab with measurement type, might look like this
LabDefaults
labIDmeasureCatmeasureUnitmeasureTypedefaultAssayReceived this comment
The amount of sample loaded in qPCR plates might vary from lab to lab and so reporting only PMMoV Ct value when working with sludge will not allow quick comparison between studies (one would need to look into individual methods and adjust all data). If comparison of pepper level in sludge is something of interest, I would suggest reporting both the PMMoV Ct value and PMMoV copies/X amount of sludge to allow for direct comparison.
I need to think about if we are currently able to do this.
Line level case data, could be added to the data model with an additional table.
caseID case-id of the reporter.
reporterID
postalCode_3_6
age
sex
onsetDate
repordedDate
recoveryDate
DeathDate
Technically sample and sample results are "one or zero" to many, as samples might never be analyzed, or at least not yet.
I wonder if we should rotate the CovidPublicHealthData this would allow for different reporters of various information like hospital admition numbers and cases counts:
new proposed table would be
publicHealthID: (Primary key) Unique identifier for the table.
ReporterID ID of the reporter who gave this data
PolygonID: Links with the Polygon table (foreign key).
date: Date of covid-19 measure.
dateType: Type of date used.
episodeDate : Episode date is usually just the earliest of a list of dates available as not every case has every dateonsetDate: Earliest that symptoms were reported for this casereportedDate: Date that the numbers were reported publiclypublicHealthMeasuretype: Type of date used.
confirmed : Number of confirmed cases.active:Number of active cases.tests:Number Number of tests.positiveTests: Number of positive tests.percentPositivityRate Percent positivity rate.hospitalCensus Hospital census or the number of people admitted with covid-19.hospitalAdmit Hospital admissions or patients newly admitted to hospital.publicHealthMeasureValue: numeric
notes: notes
Move PMMV and CrA up to MeasurementCat
this allows us to capture the normalizations that can be done.
Sewer Trucks and Airplanes, need to be able to be fit in this model
Currently, all data access permisions are part of the reporter. This means that whenever a single reporter wants to share both data that is (i) visible to all (ii) and data that has access limitations, multiple instances of the same reporter will need to be created in the database. Which does not seem to be very efficient. Would it therefore not be better to have all the allowAcces fields on the level of the measurement (or sample)?
while often a site will use the same lab, lab is technically, one to many with testresults. As different labs can analyses the same "sub-sample".
measureID is used for multiple measures (i.e. gene regions) performed at the same time. However, we also need a unique id for long table. Add UUID for long table unique ID.
Use cases:
Use a new measureID when a lab repeats analyses to confirm or perform QA.
Use UUID only for long tables. UUID is not needed when using a wide table format.
Create UUID as a sequential created from measureID. e.g. measureID = 100,
then UUID = 100.1, 100.2 ....
creating a template for Chand at NML.
He was telling me that PMMV is not a unit, so we might need to change that. These are the headers we found in his header, where headers are of the form
measureCat_measureUnit_measureType
Measurement.covidN1_cycles_singleton_1Measurement.covidN1_cycles_singleton_2Measurement.covidN2_cycles_singleton_1Measurement.covidN2_cycles_singleton_2Measurement.mhv_cycles_singleton_1Measurement.mhv_cycles_singleton_2Measurement.PMMV_cycles_singlton_1Measurement.PMMV_cycles_singlton_2Measurement._notesMeasurement.covidN1_ml_singleton_1Measurement.covidN1_ml_singleton_2Measurement.PPMV_ml_singleton_1Measurement.PPMV_ml_singleton_2The ones of the machine are in units of cycles then once he normalizes by a standard curve they are in copies per ml.
So in the end the issue is move ppmv out of measureUnit and into measureCat
After talking to Statscan about the data the data they have I wonder if a seperate table is needed for handling .....
Surrogate Recovery should be added to the data model.
this either goes in AssayMethod or Measurement.
Create a generic ETL tool that transforms forms any data from the generic wide structure to the generic long format.
I have been told the CDC already did a lot of this data model work, we need to find a contact to discuss with them.
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
Django
The Web framework for perfectionists with deadlines.
Laravel
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
Microsoft
Open source projects and samples from Microsoft.
Google
Google ❤️ Open Source for everyone.
Alibaba
Alibaba Open Source for everyone
D3
Data-Driven Documents codes.
Tencent
China tencent open source team.