Schema: wetlab

Outline

The schema wetlab contains tables for different laboratory analysis methods and results. The default soil properties included in the table are the physico-chemical quantities that are collected as part of the European Statistical Office (EUROSTAT) Land Use and Coverage Area frame Survey (LUCAS). The LUCAS sampling is structured in 5 different modules of which 3 are reflected in the AI4SH schema wetlab. The remaining 2 LUCAS modules are covered by the AI4SH schemas macrofauna and edna, and samples, as outlined below.

LUCAS module 1 Physico-chemical quantitative properties and analysis methods include:

The translation between the LUCAS (ISO) methods and the USDA methods as coded in the Open Soil Spectral Library (OSSL) are given below - NOTE ALL NEEDS CONFIRMATION.

Additional methods can be added by following the same principles of identifying the quantity, ISO-method and reporting units - and combining these into the unique quantcode. The table below illustrates how to define soil nitrogen species analysed from dried samples (ISO 14255:1998).

All properties that are to be laboratory analysed must be listed in the table labanalysismethod. The default methods to apply are the 13 physico-chemical methods listed in LUCAS module 1 (above).

The table methodtransfer is a support table for linking the ISO coded methods as defined by LUCAS to any other coding system. By default the USDA coding system as expressed in the Open Soil Spectral Library (OSSL) should be added. But also other, country specific, standards can be entered.

The sample to analyse is always the mixed sample from each sample event, where either only the topsoil (0-20 cm) or both the topsoil and subsoil (20-50 cm) are analysed. If both are analysed, two metadata records (table labanalysismeta) must be created for the same sampleevent. The metadata should also include the name and contact of the (certified/centralised) laboratory that analysed the sampleevent. The schema thus also contains a table for registering the laboratories used by AI4SH (laboratory).

The actual laboratory results, for quantities and units as defined in the table labanalysismethod, are registered in the table labanalysisresults. As the analysis actually done varies for each pilot/site/sample point, the table is built up for registering single analysis results against the analysismeta (linked using the common field labanalysisid).

DBML

// Use DBML to define your database structure
// Docs: https://dbml.dbdiagram.io/docs
// Tool: https://dbdiagram.io/d

Project project_name {
  database_type: 'PostgreSQL'
  Note: 'AI4SH schema for wet laboratory data'
}

Table users.user {
  userid SERIAL
}

Table samples.sample_event {
  sampleid SERIAL
}

Table labanalysismethod {
  quantity TEXT [pk]
  isocode TEXT [pk]
  unit TEXT  
  lucasmodule CHAR(1)
  quantcode TEXT
  default Boolean
}
// default is True for the LUCAS module 1 13 physico-chemical properties, and false for all other quantities.
// The quantcode is a combination of "quantity"."isocode"."unit"

TABLE labanalysismeta {
  laboratorieid INTEGER [pk]
  sampleid INTEGER [pk]
  topsoil Boolean [pk]
  analysisdate date
  userid INTEGER
  labanalysisid SERIAL
}
// topsoil (true) represents 0-20 cm, subsoil (false) represents 20-50 cm.

TABLE labanalysisresults {
  labanalysisid INTEGER
  quantcode TEXT
  value REAL
}

Table laboratory {
  labname TEXT [pk]
  labadress TEXT [pk]
  labcountry char(2)
  laburl TEXT
  labcontact TEXT
  laboratorieid SERIAL
}

Table methodtransfer {
  quantcode TEXT [pk]
  coountry TEXT [pk]
  countyrcode TEXT
  info TEXT
  gain REAL [DEFAULT: 1]
  offset REAL [DEFAULT: 0]
}

Ref: "public"."laboratory"."laboratorieid" - "public"."labanalysismeta"."laboratorieid"

Ref: "samples"."sample_event"."sampleid" - "public"."labanalysismeta"."sampleid"

Ref: "users"."user"."userid" - "public"."labanalysismeta"."userid"

Ref: "public"."labanalysismeta"."labanalysisid" - "public"."labanalysisresults"."labanalysisid"

Ref: "public"."labanalysismethod"."quantcode" - "public"."methodtransfer"."quantcode"

Ref: "public"."labanalysismethod"."quantcode" - "public"."labanalysisresults"."quantcode"

Figure