Phenotypes

Phenotypic records can be stored in Breedbase as measurements to existing plot, plant, or tissue sample entries in a field trial. Once stored, these phenotypic records power all phenotypic analyses on Breedbase. Prior to storing phenotypes into Breedbase, a researcher can collect phenotypes using a simple Excel or CSV spreadsheet, using the Android Fieldbook App (http://www.wheatgenetics.org/research/software/22-android-field-book), or using a BrAPI enabled application. When using an Excel spreadsheet to collect phenotypes, the researcher should first used Breedbase to download a template for data collection; this template ensures that the observation variables being measured and the plot, plant, or tissue sample observation units already exist in the database. As described in the previous section, observation variables must exist in an ontology in Breedbase. And as described in the previous section, plot, plant, or tissue sample entries must exist as part of a field trial in Breedbase. When using the Fieldbook App, the researcher should use Breedbase to download templates to load into Fieldbook; the templates consist of the field trial layout being observed and the observation variables being measured. When using a BrAPI enabled application, such as HIDAP (https://research.cip.cgiar.org/gtdms/hidap/) the system allows users to directly add phenotypic records into Breedbase, while simultaneously using BrAPI to retrieve field trial and observation units information from Breedbase.

Each phenotypic record is stored as an entry in the phenotype table with its own nd_experiment entry. The nd_experiment entry has the type name ‘phenotyping_experiment’ from the ‘experiment_type’ controlled vocabulary; this entry is linked to the stock entry of the plot, plant, or tissue sample that was observed and to the project entry for the field trial in which the phenotype was measured, using the nd_experiment_stock and nd_experiment_project linking tables, respectively.

For performance reasons, a PostgreSQL materialized view is used when querying for phenotypes in Breedbase; the materialized view is updated whenever new field trials and phenotypic records are added to Breedbase. The materialized view has all observation units, whether they are plots, plants, or tissue samples, listed in rows with columns containing all field trial related information and a column named ‘observations’ containing all phenotypic records for the observation unit; the ‘observations’ column is a PostgreSQL JSONb field structured as an array of objects, where each object contains, the observation variable name, the phenotypic value, the timestamp when the phenotype was collected, the timestamp when the phenotype was stored in Breedbase, and the person responsible. PostgreSQL can directly query keys from arrays of objects in JSON or JSONb formatted fields, allowing fast querying of millions of phenotypic records. Almost all phenotyping related queries utilize the materialized view for very fast data retrieval.