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

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

A python client for accessing ChEMBL web services

Motivation The CheMBL Web Services provide simple reliable programmatic access to the data stored in ChEMBL database. RESTful API approaches are quite easy to master in most languages but still require writing a few lines of code. Additionally, it can be a challenging task to write a nontrivial application using REST without any examples. These factors were the motivation for us to write a small client library for accessing web services from Python. Why Python? We choose this language because Python has become extremely popular (and still growing in use) in scientific applications; there are several Open Source chemical toolkits available in this language, and so the wealth of ChEMBL resources and functionality of those toolkits can be easily combined. Moreover, Python is a very web-friendly language and we wanted to show how easy complex resource acquisition can be expressed in Python. Reinventing the wheel? There are already some libraries providing access to ChEMBL d

LSH-based similarity search in MongoDB is faster than postgres cartridge.

TL;DR: In his excellent blog post , Matt Swain described the implementation of compound similarity searches in MongoDB . Unfortunately, Matt's approach had suboptimal ( polynomial ) time complexity with respect to decreasing similarity thresholds, which renders unsuitable for production environments. In this article, we improve on the method by enhancing it with Locality Sensitive Hashing algorithm, which significantly reduces query time and outperforms RDKit PostgreSQL cartridge . myChEMBL 21 - NoSQL edition    Given that NoSQL technologies applied to computational chemistry and cheminformatics are gaining traction and popularity, we decided to include a taster in future myChEMBL releases. Two especially appealing technologies are Neo4j and MongoDB . The former is a graph database and the latter is a BSON document storage. We would like to provide IPython notebook -based tutorials explaining how to use this software to deal with common cheminformatics p

Multi-task neural network on ChEMBL with PyTorch 1.0 and RDKit

  Update: KNIME protocol with the model available thanks to Greg Landrum. Update: New code to train the model and ONNX exported trained models available in github . The use and application of multi-task neural networks is growing rapidly in cheminformatics and drug discovery. Examples can be found in the following publications: - Deep Learning as an Opportunity in VirtualScreening - Massively Multitask Networks for Drug Discovery - Beyond the hype: deep neural networks outperform established methods using a ChEMBL bioactivity benchmark set But what is a multi-task neural network? In short, it's a kind of neural network architecture that can optimise multiple classification/regression problems at the same time while taking advantage of their shared description. This blogpost gives a great overview of their architecture. All networks in references above implement the hard parameter sharing approach. So, having a set of activities relating targets and molecules we can tra

Using ChEMBL activity comments

We’re sometimes asked what the ‘activity_comments’ in the ChEMBL database mean. In this Blog post, we’ll use aspirin as an example to explain some of the more common activity comments. First, let’s review the bioactivity data included in ChEMBL. We extract bioactivity data directly from   seven core medicinal chemistry journals . Some common activity types, such as IC50s, are standardised  to allow broad comparisons across assays; the standardised data can be found in the  standard_value ,  standard_relation  and  standard_units  fields. Original data is retained in the database downloads in the  value ,  relation  and  units  fields. However, we extract all data from a publication including non-numerical bioactivity and ADME data. In these cases, the activity comments may be populated during the ChEMBL extraction-curation process  in order to capture the author's  overall  conclusions . Similarly, for deposited datasets and subsets of other databases (e.g. DrugMatrix, PubChem), th