BioAtlas Technical Details

-- Find FDA-approved drugs targeting genes with strong 
-- genetic evidence for Alzheimer's disease
SELECT DISTINCT
    d.drug_name,
    d.max_phase,
    g.hgnc_symbol,
    dg.association_score,
    dt.action_type
FROM drug d
JOIN drug_target dt ON d.molecule_chembl_id = dt.molecule_chembl_id
JOIN gene g ON dt.ensembl_gene_id = g.ensembl_gene_id
JOIN disease_gene dg ON g.ensembl_gene_id = dg.ensembl_gene_id
JOIN disease dis ON dg.mondo_id = dis.mondo_id
WHERE dis.disease_label ILIKE '%Alzheimer%'
  AND d.fda_approved_us = TRUE
  AND dg.association_score > 0.5
ORDER BY dg.association_score DESC;

-- What pathways does Paclitaxel activate across cell lines?
SELECT 
    tc.cell_line_name,
    ta.feature_id as pathway,
    AVG(ta.score) as mean_activity,
    COUNT(*) as n_measurements
FROM tahoe_context tc
JOIN tahoe_activity ta ON tc.context_id = ta.context_id
WHERE tc.drug_name = 'Paclitaxel'
  AND ta.provider = 'PROGENy'
  AND ta.feature_type = 'pathway'
GROUP BY tc.cell_line_name, ta.feature_id
HAVING ABS(AVG(ta.score)) > 2.0
ORDER BY ABS(AVG(ta.score)) DESC;

-- Find drugs targeting genes with strong colocalization 
-- evidence for inflammatory bowel disease
SELECT DISTINCT
    d.drug_name,
    g.hgnc_symbol,
    c.h4 as colocalization_prob,
    dt.action_type
FROM coloc_bayesian c
JOIN gene g ON c."rightStudyLocusId" LIKE '%' || g.ensembl_gene_id || '%'
JOIN drug_target dt ON g.ensembl_gene_id = dt.ensembl_gene_id
JOIN drug d ON dt.molecule_chembl_id = d.molecule_chembl_id
WHERE c."leftStudyLocusId" LIKE '%inflammatory_bowel%'
  AND c.h4 > 0.9
  AND d.max_phase >= 2
LIMIT 20;