biosample_biological_group

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

epigenome_id

uuid

2147483647

√

null

epigenome.epigenome_id

Implied Constraint

R

biological_group

text

2147483647

√

null

Relationships

Close relationships within degrees of separation

View Definition

WITH classified AS (
         SELECT metadata.epigenome_id,
                CASE lower(metadata.material)
                    WHEN 'cell line'::text THEN
                    CASE
                        WHEN (metadata.term = ANY (ARRAY['K562'::text, 'MEL'::text])) THEN 'blood and myeloid'::text
                        WHEN (metadata.term = 'SK-N-SH'::text) THEN 'brain and nervous system'::text
                        WHEN (metadata.term = ANY (ARRAY['Caco-2'::text, 'HCT116'::text])) THEN 'intestine'::text
                        WHEN (metadata.term = 'HEK293'::text) THEN 'kidney'::text
                        WHEN (metadata.term = ANY (ARRAY['A549'::text, 'PC-9'::text])) THEN 'lung'::text
                        WHEN (metadata.term = ANY (ARRAY['CH12.LX'::text, 'DND-41'::text, 'DOHH2'::text, 'GM06990'::text, 'GM12878'::text, 'KMS-11'::text, 'KOPT-K1'::text, 'Karpas-422'::text, 'Loucy'::text, 'MM.1S'::text, 'NCI-H929'::text, 'OCI-LY1'::text, 'OCI-LY3'::text, 'OCI-LY7'::text, 'SU-DHL-6'::text])) THEN 'lymphoid'::text
                        WHEN (metadata.term = 'MCF-7'::text) THEN 'mammary gland'::text
                        WHEN (metadata.term = ANY (ARRAY['A673'::text, 'AG04450'::text, 'BJ'::text, 'GM23248'::text, 'IMR-90'::text, 'SK-N-MC'::text])) THEN 'mesenchymal and fibroblast'::text
                        WHEN (metadata.term = 'Panc1'::text) THEN 'pancreas'::text
                        WHEN (metadata.term = ANY (ARRAY['ES-Bruce4'::text, 'ES-E14'::text, 'ES-I3'::text, 'GM23338'::text, 'H1'::text, 'H7'::text, 'H9'::text, 'HUES48'::text, 'HUES6'::text, 'HUES64'::text, 'NT2/D1'::text, 'iPS DF 19.11'::text, 'iPS DF 6.9'::text, 'iPS-18a'::text, 'iPS-20b'::text])) THEN 'pluripotent cells and models'::text
                        WHEN (metadata.term = 'PC-3'::text) THEN 'prostate gland'::text
                        WHEN (metadata.term = 'ACC112'::text) THEN 'salivary gland'::text
                        WHEN (metadata.term = 'HeLa-S3'::text) THEN 'uterus'::text
                        ELSE NULL::text
                    END
                    WHEN 'primary cell'::text THEN
                    CASE
                        WHEN (metadata.term = ANY (ARRAY['CD14-positive monocyte'::text, 'bone marrow macrophage'::text, 'common myeloid progenitor, CD34-positive'::text, 'mononuclear cell'::text, 'neutrophil'::text])) THEN 'blood and myeloid'::text
                        WHEN (metadata.term = 'astrocyte'::text) THEN 'brain and nervous system'::text
                        WHEN (metadata.term = 'kidney epithelial cell'::text) THEN 'kidney'::text
                        WHEN (metadata.term = 'bronchial epithelial cell'::text) THEN 'lung'::text
                        WHEN (metadata.term = ANY (ARRAY['B cell'::text, 'T-cell'::text, 'natural killer cell'::text])) THEN 'lymphoid'::text
                        WHEN (metadata.term = 'mammary epithelial cell'::text) THEN 'mammary gland'::text
                        WHEN (metadata.term = ANY (ARRAY['embryonic fibroblast'::text, 'fibroblast of dermis'::text, 'fibroblast of lung'::text, 'fibroblast of mammary gland'::text, 'foreskin fibroblast'::text, 'osteoblast'::text])) THEN 'mesenchymal and fibroblast'::text
                        WHEN (metadata.term = 'skeletal muscle myoblast'::text) THEN 'musculature'::text
                        WHEN (metadata.term = 'keratinocyte'::text) THEN 'skin'::text
                        WHEN (metadata.term = 'endothelial cell of umbilical vein'::text) THEN 'vasculature'::text
                        ELSE NULL::text
                    END
                    WHEN 'primary cell culture'::text THEN
                    CASE
                        WHEN (metadata.term = ANY (ARRAY['neural cell'::text, 'neural progenitor cell'::text, 'neuron'::text, 'neuronal stem cell'::text])) THEN 'brain and nervous system'::text
                        WHEN (metadata.term = ANY (ARRAY['ectodermal cell'::text, 'endodermal cell'::text, 'mesendoderm'::text, 'mesodermal cell'::text])) THEN 'embryo'::text
                        WHEN (metadata.term = 'cardiac muscle cell'::text) THEN 'heart'::text
                        WHEN (metadata.term = 'hepatocyte'::text) THEN 'liver'::text
                        WHEN (metadata.term = 'mesenchymal stem cell'::text) THEN 'mesenchymal and fibroblast'::text
                        WHEN (metadata.term = ANY (ARRAY['myotube'::text, 'smooth muscle cell'::text])) THEN 'musculature'::text
                        WHEN (metadata.term = 'trophoblast cell'::text) THEN 'placenta'::text
                        ELSE NULL::text
                    END
                    WHEN 'primary tissue'::text THEN
                    CASE
                        WHEN (metadata.term = ANY (ARRAY['adipose tissue'::text, 'brown adipose tissue'::text, 'subcutaneous adipose tissue'::text])) THEN 'adipose tissue'::text
                        WHEN (metadata.term = 'adrenal gland'::text) THEN 'adrenal gland'::text
                        WHEN (metadata.term = 'bone marrow'::text) THEN 'blood and myeloid'::text
                        WHEN (metadata.term = ANY (ARRAY['brain'::text, 'cerebellum'::text, 'cortex'::text, 'cortical plate'::text, 'forebrain'::text, 'frontal cortex'::text, 'hindbrain'::text, 'hypothalamus'::text, 'midbrain'::text, 'neural tube'::text, 'olfactory bulb'::text, 'tibial nerve'::text])) THEN 'brain and nervous system'::text
                        WHEN (metadata.term = ANY (ARRAY['embryo'::text, 'embryonic facial prominence'::text, 'limb'::text])) THEN 'embryo'::text
                        WHEN (metadata.term = ANY (ARRAY['esophagus muscularis mucosa'::text, 'esophagus squamous epithelium'::text])) THEN 'esophagus'::text
                        WHEN (metadata.term = 'gill filament'::text) THEN 'gill'::text
                        WHEN (metadata.term = ANY (ARRAY['gonad'::text, 'immature gonad'::text, 'testis'::text])) THEN 'gonad'::text
                        WHEN (metadata.term = ANY (ARRAY['heart'::text, 'heart left ventricle'::text, 'right atrium auricular region'::text])) THEN 'heart'::text
                        WHEN (metadata.term = ANY (ARRAY['Peyer''s patch'::text, 'ileum'::text, 'intestine'::text, 'large intestine'::text, 'sigmoid colon'::text, 'small intestine'::text, 'transverse colon'::text])) THEN 'intestine'::text
                        WHEN (metadata.term = ANY (ARRAY['kidney'::text, 'mesonephros'::text])) THEN 'kidney'::text
                        WHEN (metadata.term = ANY (ARRAY['liver'::text, 'lobe of liver'::text, 'right lobe of liver'::text])) THEN 'liver'::text
                        WHEN (metadata.term = ANY (ARRAY['left lung lobe'::text, 'lung'::text, 'upper lobe of left lung'::text])) THEN 'lung'::text
                        WHEN (metadata.term = 'breast epithelium'::text) THEN 'mammary gland'::text
                        WHEN (metadata.term = ANY (ARRAY['fast muscle tissue'::text, 'gastrocnemius medialis'::text, 'gluteus medius'::text, 'hindlimb muscle'::text, 'longissimus dorsi muscle'::text, 'muscle of leg'::text, 'muscle of trunk'::text, 'psoas muscle'::text, 'skeletal muscle tissue'::text])) THEN 'musculature'::text
                        WHEN (metadata.term = 'body of pancreas'::text) THEN 'pancreas'::text
                        WHEN (metadata.term = 'placenta'::text) THEN 'placenta'::text
                        WHEN (metadata.term = 'prostate gland'::text) THEN 'prostate gland'::text
                        WHEN (metadata.term = ANY (ARRAY['skin of back'::text, 'skin of body'::text])) THEN 'skin'::text
                        WHEN (metadata.term = 'spleen'::text) THEN 'spleen'::text
                        WHEN (metadata.term = ANY (ARRAY['gastroesophageal sphincter'::text, 'stomach'::text])) THEN 'stomach'::text
                        WHEN (metadata.term = 'thymus'::text) THEN 'thymus'::text
                        WHEN (metadata.term = 'thyroid gland'::text) THEN 'thyroid gland'::text
                        WHEN (metadata.term = 'trachea'::text) THEN 'trachea'::text
                        WHEN (metadata.term = 'uterus'::text) THEN 'uterus'::text
                        WHEN (metadata.term = 'vagina'::text) THEN 'vagina'::text
                        WHEN (metadata.term = ANY (ARRAY['ascending aorta'::text, 'thoracic aorta'::text, 'tibial artery'::text])) THEN 'vasculature'::text
                        ELSE NULL::text
                    END
                    ELSE NULL::text
                END AS biological_group
           FROM curated_experiment_metadata metadata
        )
 SELECT classified.epigenome_id,
        CASE
            WHEN (count(DISTINCT classified.biological_group) = 1) THEN min(classified.biological_group)
            ELSE NULL::text
        END AS biological_group
   FROM classified
  GROUP BY classified.epigenome_id;

Possibly Referenced Tables/Views

curated_experiment_metadata