curated_experiment_metadata

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

species_name

text

2147483647

√

null

epigenome_name

text

2147483647

√

null

name

text

2147483647

√

null

biosample_accession

text

2147483647

√

null

material

text

2147483647

√

null

term

text

2147483647

√

null

term_ontology

text

2147483647

√

null

organ_slims

_text

2147483647

√

null

system_slims

_text

2147483647

√

null

experiment_type

text

2147483647

√

null

experiment_target_type

text

2147483647

√

null

experiment_target

text

2147483647

√

null

sex

sex_type

2147483647

√

null

life_stage

text

2147483647

√

null

age

text

2147483647

√

null

age_unit

text

2147483647

√

null

data_source

text

2147483647

√

null

assembly_name

varchar

2147483647

√

null

experiment_id

uuid

2147483647

√

null

epigenome_id

uuid

2147483647

√

null

epigenome.epigenome_id

Implied Constraint

epigenome_description

text

2147483647

√

null

cell_slims

_text

2147483647

√

null

species_id

uuid

2147483647

√

null

species.species_id

Implied Constraint

assembly_id

uuid

2147483647

√

null

assembly.assembly_id

Implied Constraint

Relationships

Close relationships within degrees of separation

View Definition

SELECT DISTINCT experiment_metadata.species_name,
    experiment_metadata.epigenome_name,
    experiment_metadata.name,
    COALESCE(experiment_metadata.accession, experiment_metadata.biosample_accession) AS biosample_accession,
    initcap(replace((experiment_metadata.biomaterial_type)::text, '_'::text, ' '::text)) AS material,
        CASE
            WHEN (experiment_metadata.biosample_term = ANY (ARRAY['Thymus'::text, 'Uterus'::text])) THEN lower(experiment_metadata.biosample_term)
            ELSE experiment_metadata.biosample_term
        END AS term,
    concat(experiment_metadata.ontology_type, '_', experiment_metadata.curie) AS term_ontology,
    experiment_metadata.organ_slims,
    experiment_metadata.system_slims,
        CASE
            WHEN (experiment_metadata.experiment_type = 'atac_seq'::experiment_type) THEN 'ATAC-seq'::text
            WHEN (experiment_metadata.experiment_type = 'dnase_seq'::experiment_type) THEN 'DNase-seq'::text
            WHEN (experiment_metadata.experiment_type = 'chip_seq'::experiment_type) THEN 'ChIP-seq'::text
            ELSE NULL::text
        END AS experiment_type,
        CASE
            WHEN (experiment_metadata.experiment_target_type = 'atac_seq'::text) THEN 'open chromatin'::text
            WHEN (experiment_metadata.experiment_target_type = 'dnase_seq'::text) THEN 'open chromatin'::text
            WHEN (experiment_metadata.experiment_target_type = 'tf'::text) THEN 'transcription factor'::text
            WHEN (experiment_metadata.experiment_target_type = 'histone'::text) THEN 'histone'::text
            ELSE NULL::text
        END AS experiment_target_type,
        CASE
            WHEN ((experiment_metadata.experiment_target = 'atac_seq'::text) OR (experiment_metadata.experiment_target = 'dnase_seq'::text)) THEN 'open chromatin'::text
            ELSE experiment_metadata.experiment_target
        END AS experiment_target,
    experiment_metadata.sex,
    COALESCE(experiment_metadata.life_stage_term, 'unknown'::text) AS life_stage,
        CASE
            WHEN (experiment_metadata.thermal_time IS NOT NULL) THEN (round(experiment_metadata.thermal_time, 2))::text
            WHEN (lower(COALESCE(experiment_metadata.age_at_collection, experiment_metadata.age)) = upper(COALESCE(experiment_metadata.age_at_collection, experiment_metadata.age))) THEN (lower(COALESCE(experiment_metadata.age_at_collection, experiment_metadata.age)))::text
            WHEN (lower(COALESCE(experiment_metadata.age_at_collection, experiment_metadata.age)) <> upper(COALESCE(experiment_metadata.age_at_collection, experiment_metadata.age))) THEN concat(lower(COALESCE(experiment_metadata.age_at_collection, experiment_metadata.age)), '-', upper(COALESCE(experiment_metadata.age_at_collection, experiment_metadata.age)))
            ELSE 'unknown'::text
        END AS age,
        CASE
            WHEN (experiment_metadata.thermal_time IS NOT NULL) THEN 'thermal time'::text
            ELSE (COALESCE(experiment_metadata.age_at_collection_unit, experiment_metadata.age_unit))::text
        END AS age_unit,
    COALESCE(experiment_metadata.replicate_set_accession, experiment_metadata.replicate_accession) AS data_source,
    experiment_metadata.assembly_name,
    experiment_metadata.experiment_id,
    experiment_metadata.epigenome_id,
    experiment_metadata.epigenome_description,
    experiment_metadata.cell_slims,
    experiment_metadata.species_id,
    experiment_metadata.assembly_id
   FROM (experiment_metadata
     JOIN analysis_file ON (((experiment_metadata.experiment_id = analysis_file.experiment_id) AND (analysis_file.assembly_id = experiment_metadata.assembly_id))))
  WHERE ((analysis_file.handover = true) AND (experiment_metadata.replicate_set_type = 'signal'::text) AND (analysis_file.analysis_type = ANY (ARRAY['chip_seq_broad_peaks'::analysis_type, 'chip_seq_gapped_peaks'::analysis_type, 'chip_seq_narrow_peaks'::analysis_type, 'dnase_seq_peaks'::analysis_type, 'atac_seq_peaks'::analysis_type])) AND (experiment_metadata.biomaterial_type IS NOT NULL));

curated_experiment_metadata

Columns

Relationships

Close relationships within degrees of separation

View Definition

Possibly Referenced Tables/Views