Once
ontologies are loaded and enabled, you can also make direct use of them in SQL queries. The syntax described in this topic helps you access the preferred vocabularies and wealth of meaning contained in your ontologies.
Ontology Usage in SQL
The following functions and annotations are available:
- Functions:
- IsSubClassOf(conceptX, conceptParent): Is X a subclass of Parent?
- IsInSubTree(conceptX, ConceptPath(..)): Is X in the subtree specified?
- ConceptPath([conceptHierarchy], conceptParent): Select this unique hierarchy path so that I can find X in it using "isInSubTree".
- table.findColumn([columnProperties]): Find a desired column by concept, conceptURI, name, propertyURI, obsolete name.
- Annotation:
- @concept=[CODE]: Used to reference ontology concepts in column metadata.
IsSubClassOf
Usage:
IsSubClassOf(conceptX, conceptParent)
- Returns true if conceptX is a direct or indirect subclass of conceptParent.
IsInSubtree
Usage:
IsInSubtree(conceptX, ConceptPath(conceptA,conceptB,conceptParent))
- Returns true if conceptX is contained in a subtree rooted at the unique path that contains .../conceptA/conceptB/conceptParent/.
- If there is no such unique path the method returns false.
- If there is such a unique path, but conceptX is not in this tree the method returns false.
ConceptPath
Usage:
ConceptPath(conceptA,conceptB,...,conceptParent)
- This method takes one or more arguments, and returns the unique hierarchy path that contains the provided concepts in sequence (no gaps). The return value comes from the ontology.hierarchy.path column.
- This method will return null if no such path is found, or if more than one path is found.
- Note that the hierarchy paths may not be user readable at all, and may be more likely to change than the concept codes which are very stable. So this usage is preferable to trying to use hierarchy paths directly.
Performance note: It is faster to ask if conceptX belongs to a subtree ending in conceptParent than it is to ask whether conceptX is a subclass of conceptParent.
For performance we store all possible paths in the “subclass” hierarchy to create a pure tree, rather than a graph of subclass relations. This makes it much easier to answer questions like select all rows containing a ‘cancer finding’. This schema means that internally we are really querying the relationship between paths, not directly querying concepts. Therefore ConceptIsSubClass() is more complicated to answer than ConceptIsInSubtree().
@concept
The @concept annotation can be used to override the metadata of the column with a concept annotation.
Usage:
SELECT 'Something' as "Finding" @concept=C3367 FROM T WHERE ...
table.findColumn
To find a column in a given table by concept, conceptURI, name, propertyuri, or obsolete name
, use findColumn on your table:
table.findColumn([columnProperties])
For example, if you've annotated a column with the concept coded "ONT:123", use the following to return the column with that concept:
SELECT
MyTable.findColumn(@concept='ONT:123')
FROM Project.MyStudy.study.MyTable
Examples
In these examples, we use a fictional ontology nicknamed "ONT". A value like "ONT:123" might be one of the codes in the ontology meaning "Pharma", for example. All SQL values are just string literals of the concept code, including a readable name in a comment in these examples is for clarity.
Here, "ONT:123" (Pharma) appears in the hierarchy tree once as a root term; "ONT:382" (Ibuprofen, for example) appears twice in the hierarchy 'below' Pharma, and has a further child: "ONT:350" (Oral form ibuprofen). Other codes are omitted for readability:
ONT:123 (Pharma) / biologic product / Analgesic / Anti-inflammatory preparations / Non-steroidal anti-inflammatory agent / ONT:382 (Ibuprofen) / ONT:350 (Oral form ibuprofen)
ONT:123 (Pharma) / biologic product / Analgesic / Non-opioid analgesics / Non-steroidal anti-inflammatory agent / ONT:382 (Ibuprofen) / ONT:350 (Oral form ibuprofen)
The two expressions below are not semantically the same, but return the same result. The second version is preferred because it ensures that there is only one path being evaluated and might be faster.
IsSubClassOf('ONT:382' /* Ibuprofen */, 'ONT:123' /* Pharma */)
IsInSubtree('ONT:382' /* Ibuprofen */, ConceptPath('ONT:123' /* Pharma */)The next two expressions do not return the same result. The first works as expected:
IsSubClassOf('ONT:350' /* Oral form ibuprofen */, 'ONT:382' /* Ibuprofen */)
IsInSubtree('ONT:350' /* Oral form ibuprofen */, ConceptPath('ONT:382' /* Ibuprofen */)Since there is not a unique concept path containing 'ONT:382' (Ibuprofen), the value of ConceptPath('ONT:382' /* Ibuprofen */) is NULL in the second row. Instead, the following expression would work as expected, clarifying which "branch" of the path to use:
IsInSubtree('ONT:350' /* Oral form ibuprofen */, ConceptPath('ONT:322' /* Non-opioid analgesics */, 'ONT:164' /* Non-steroidal anti-inflammatory agent */, 'ONT:382' /* Ibuprofen */)Related Topics
