Skip to main content

Some Queries For Data Retrieval From StARlite

It is a public holiday in the UK today (we call these bank holidays, for reasons that seem obscure nowadays). The weather is traditionally bad on such days, and today is no exception, at least the remainder of the week, when we return to work, will be fine and sunny.

We will run a further StARlite schema and query walkthrough webinar shortly, but in the meantime here are some skeleton sql queries, that perform a set of related queries retrieving compounds/bioactivities for a given target. In this case the target is human PDE4A (for which the tid is 3), and human PDE5A (for which the tid is 276). We will walk through getting these unique target identifiers (or tids) on another occasion, but suffice it to say, that this is easy, especially programmatically, using blastp.

Firstly, retrieving a set of potent inhibitors of human PDE4A or PDE5A. There are a number of parameters one needs to set to actually do this (the end-point, the affinity cutoff, etc. Specifically here we have selected high confidence assay to target assignments (the a2t.confidence=7 bit), and where the potency is better than 1000nM for an IC50 measurement. This is a pretty generic query, and piping in the target tid to this covers a surprisingly frequent use the the data.

select  act.molregno, act.activity_type, act.relation as operator, act.standard_value, act.standard_units, 
   td.pref_name, td.organism, 
   a.description as assay_description, 
   docs.journal, docs.year, docs.volume, docs.first_page, docs.pubmed_id, cr.compound_key
from  target_dictionary td, 
   assay2target a2t,    
   assays a, 
   activities act, 
   docs, 
   compound_records cr
where  td.tid in (3,276)
and  td.tid = a2t.tid
and  a2t.confidence = 7
and  a2t.assay_id = a.assay_id
and  a2t.assay_id = act.assay_id
and  act.doc_id = docs.doc_id
and  act.record_id = cr.record_id
and  act.activity_type = 'IC50'
and  act.relation in( '=', '<')
and  act.standard_units = 'nM'
and  act.standard_value <=1000
and  a.assay_type = 'B';

Here is a modified form to retrieve just the compound identifiers (molregno)

select  distinct act.molregno
from  target_dictionary td, 
  assay2target a2t,    
  assays a,  
  activities act
where  td.tid in (3,276)
and  td.tid = a2t.tid
and  a2t.confidence = 7
and  a2t.assay_id = a.assay_id
and  a2t.assay_id = act.assay_id
and  act.activity_type = 'IC50'
and  act.relation in( '=', '<')
and  act.standard_units = 'nM'
and  act.standard_value <=1000
and  a.assay_type = 'B';

Also a common requirement is to get the associated molecule structures from the database - here the syntax is for an sdf format output and the query does not rely on any fancy chemical cartridge manipulation (since we store the molfiles in a clob called molfile in the COMPOUNDS table). The query here simply retrieves the structures, and not the associated bioactivity data. The goofy looking concatenations (||) and newlines (chr(10)) just make sure that a validly formatted sdf file emerges at the end.

select  c.molfile || chr(10) || '> ' ||chr(10)|| c.molregno||chr(10)||chr(10)||'$$$$'||chr(10)
from  compounds c, 
  (select distinct act.molregno
  from  target_dictionary td, 
    assay2target a2t,    
    assays a, 
   activities act
 where  td.tid in (3,276)
 and  td.tid = a2t.tid
 and  a2t.confidence = 7
 and  a2t.assay_id = a.assay_id
 and  a2t.assay_id = act.assay_id
 and  act.activity_type = 'IC50'
 and  act.relation in( '=', '<')
 and  act.standard_units = 'nM'
 and  act.standard_value <=1000
 and  a.assay_type = 'B') t1
where  t1.molregno = c.molregno;

Comments

Popular posts from this blog

ChEMBL 34 is out!

We are delighted to announce the release of ChEMBL 34, which includes a full update to drug and clinical candidate drug data. This version of the database, prepared on 28/03/2024 contains:         2,431,025 compounds (of which 2,409,270 have mol files)         3,106,257 compound records (non-unique compounds)         20,772,701 activities         1,644,390 assays         15,598 targets         89,892 documents Data can be downloaded from the ChEMBL FTP site:  https://ftp.ebi.ac.uk/pub/databases/chembl/ChEMBLdb/releases/chembl_34/ Please see ChEMBL_34 release notes for full details of all changes in this release:  https://ftp.ebi.ac.uk/pub/databases/chembl/ChEMBLdb/releases/chembl_34/chembl_34_release_notes.txt New Data Sources European Medicines Agency (src_id = 66): European Medicines Agency's data correspond to EMA drugs prior to 20 January 2023 (excluding vaccines). 71 out of the 882 newly added EMA drugs are only authorised by EMA, rather than from other regulatory bodies e.g.

New SureChEMBL announcement

(Generated with DALL-E 3 ∙ 30 October 2023 at 1:48 pm) We have some very exciting news to report: the new SureChEMBL is now available! Hooray! What is SureChEMBL, you may ask. Good question! In our portfolio of chemical biology services, alongside our established database of bioactivity data for drug-like molecules ChEMBL , our dictionary of annotated small molecule entities ChEBI , and our compound cross-referencing system UniChem , we also deliver a database of annotated patents! Almost 10 years ago , EMBL-EBI acquired the SureChem system of chemically annotated patents and made this freely accessible in the public domain as SureChEMBL. Since then, our team has continued to maintain and deliver SureChEMBL. However, this has become increasingly challenging due to the complexities of the underlying codebase. We were awarded a Wellcome Trust grant in 2021 to completely overhaul SureChEMBL, with a new UI, backend infrastructure, and new f

Accessing SureChEMBL data in bulk

It is the peak of the summer (at least in this hemisphere) and many of our readers/users will be on holiday, perhaps on an island enjoying the sea. Luckily, for the rest of us there is still the 'sea' of SureChEMBL data that awaits to be enjoyed and explored for hidden 'treasures' (let me know if I pushed this analogy too far). See here and  here for a reminder of SureChEMBL is and what it does.  This wealth of (big) data can be accessed via the SureChEMBL interface , where users can submit quite sophisticated and granular queries by combining: i) Lucene fields against full-text and bibliographic metadata and ii) advanced structure query features against the annotated compound corpus. Examples of such queries will be the topic of a future post. Once the search results are back, users can browse through and export the chemistry from the patent(s) of interest. In addition to this functionality, we've been receiving user requests for  local (behind the