Difference between revisions of "Queries to be implemented in the future"

From phenoscape
(Strategy #1)
(Query)
Line 26: Line 26:
 
===Query===
 
===Query===
 
<sql>
 
<sql>
SELECT DISTINCT
+
SELECT
taxon_node.uid AS taxon,
+
phenotype_node.uid AS phenotype,  
phenotype_node.uid AS phenotype,
+
taxon_node.uid AS taxon,  
anatomy_node.uid AS anatomy,
 
 
quality_node.uid AS quality,
 
quality_node.uid AS quality,
slim_node.uid AS slim
+
anatomy_node.uid AS entity
FROM
+
FROM  
link AS inheres_link, link AS is_a_link, link AS subset_link,
+
link AS inheres_in_link, link AS search_link, link AS is_a_link,  
 
link AS exhibits_link,
 
link AS exhibits_link,
node AS phenotype_node, node AS anatomy_node, node AS inheres_pred_node,
+
node AS taxon_node, node AS exhibits_pred_node, node AS phenotype_node,  
node AS is_a_pred_node, node AS quality_node,
+
node AS search_pred_node, node AS search_node,  
node AS slim_node, node AS subset_pred_node,
+
node AS inheres_in_pred_node, node AS anatomy_node,  
node AS taxon_node, node AS exhibits_pred_node
+
node AS is_a_pred_node, node AS quality_node
 
WHERE
 
WHERE
 
exhibits_pred_node.uid = 'PHENOSCAPE:exhibits' AND
 
exhibits_pred_node.uid = 'PHENOSCAPE:exhibits' AND
subset_pred_node.uid = 'oboInOwl:inSubset' AND
 
 
is_a_pred_node.uid = 'OBO_REL:is_a' AND
 
is_a_pred_node.uid = 'OBO_REL:is_a' AND
is_a_link.is_inferred = 'f' AND
+
inheres_in_pred_node.uid = 'OBO_REL:inheres_in' AND
inheres_pred_node.uid = 'OBO_REL:inheres_in' AND
+
search_pred_node.uid = 'OBO_REL:inheres_in' AND
anatomy_node.uid = 'TAO:0000108' AND
+
search_node.uid = 'TAO:0000108' AND
slim_node.uid IN ('attribute_slim', 'value_slim') AND
+
inheres_in_link.is_inferred = 'f' AND
exhibits_link.node_id = taxon_node.node_id AND
+
exhibits_link.node_id IS NOT NULL AND
exhibits_link.predicate_id = exhibits_pred_node.node_id AND
+
search_link.node_id = phenotype_node.node_id AND
exhibits_link.object_id = phenotype_node.node_id AND
+
search_link.predicate_id = search_pred_node.node_id AND
subset_link.node_id = quality_node.node_id AND
+
search_link.object_id = search_node.node_id AND
subset_link.predicate_id = subset_pred_node.node_id AND
+
inheres_in_link.node_id = phenotype_node.node_id AND
subset_link.object_id = slim_node.node_id AND
+
inheres_in_link.predicate_id = inheres_in_pred_node.node_id AND
 +
inheres_in_link.object_id = anatomy_node.node_id AND
 
is_a_link.node_id = phenotype_node.node_id AND
 
is_a_link.node_id = phenotype_node.node_id AND
 
is_a_link.predicate_id = is_a_pred_node.node_id AND
 
is_a_link.predicate_id = is_a_pred_node.node_id AND
 
is_a_link.object_id = quality_node.node_id AND
 
is_a_link.object_id = quality_node.node_id AND
inheres_link.node_id = phenotype_node.node_id AND
+
exhibits_link.node_id = taxon_node.node_id AND
inheres_link.predicate_id = inheres_pred_node.node_id AND
+
exhibits_link.predicate_id = exhibits_pred_node.node_id AND
inheres_link.object_id = anatomy_node.node_id;
+
exhibits_link.object_id = phenotype_node.node_id;
 
</sql>
 
</sql>
  

Revision as of 17:28, 12 February 2009

This page discusses the requirements of the various data access modules of the Phenoscape application to be demonstrated at the ASIH workshop, and documents the performance of the various querying strategies being tested for these modules.

Anatomy data services

Given a specific anatomical entity A, the requirements of the anatomy data service module are as follows:

  1. Identify ALL unique phenotypes {P} that are composed of the entity A or any of its subtypes.
  2. For each identified phenotype P which is of the form inheres_in(Q, E)
    1. Identify the quality Q.
      1. In addition, identify the character C which subsumes Q. This requires navigating the PATO hierarchy recursively in some cases
    2. Identify the entity E. E may be the same as A, or any valid subtype of A.
    3. Identify the set of taxa {T} or genotypes {GT} that exhibit the phenotype P.
      1. For a genotype GT, identify the gene G that encodes it.

The result should be a list in the format shown below

<javascript> <Search-Anatomical-Entity> <Phenotype> <Entity> <Quality> <Character> <List of taxa>OR<List of genes> </javascript>


Strategy #1

This strategy is the simplest way to try and get all the necessary information related to the anatomical entity being searched for. Below is the actual query which attempts to do this. This query returns the unique phenotypes associated with the anatomical entity, and the qualities and specific anatomical entities associated with the phenotype, as well as the list of taxa that exhibit each of these phenotypes in one fell table join (uh, swoop).

Query

<sql> SELECT phenotype_node.uid AS phenotype, taxon_node.uid AS taxon, quality_node.uid AS quality, anatomy_node.uid AS entity FROM link AS inheres_in_link, link AS search_link, link AS is_a_link, link AS exhibits_link, node AS taxon_node, node AS exhibits_pred_node, node AS phenotype_node, node AS search_pred_node, node AS search_node, node AS inheres_in_pred_node, node AS anatomy_node, node AS is_a_pred_node, node AS quality_node WHERE exhibits_pred_node.uid = 'PHENOSCAPE:exhibits' AND is_a_pred_node.uid = 'OBO_REL:is_a' AND inheres_in_pred_node.uid = 'OBO_REL:inheres_in' AND search_pred_node.uid = 'OBO_REL:inheres_in' AND search_node.uid = 'TAO:0000108' AND inheres_in_link.is_inferred = 'f' AND exhibits_link.node_id IS NOT NULL AND search_link.node_id = phenotype_node.node_id AND search_link.predicate_id = search_pred_node.node_id AND search_link.object_id = search_node.node_id AND inheres_in_link.node_id = phenotype_node.node_id AND inheres_in_link.predicate_id = inheres_in_pred_node.node_id AND inheres_in_link.object_id = anatomy_node.node_id AND is_a_link.node_id = phenotype_node.node_id AND is_a_link.predicate_id = is_a_pred_node.node_id AND is_a_link.object_id = quality_node.node_id AND exhibits_link.node_id = taxon_node.node_id AND exhibits_link.predicate_id = exhibits_pred_node.node_id AND exhibits_link.object_id = phenotype_node.node_id; </sql>

Query Execution Plan

Execution Details

  • Rows returned: 2070
  • Time: 0.6 ~ 17 s

Discussion

Strategy #2

Strategy #3

Strategy #4

Strategy #5