SQL Practical Guide

Search

SNOMED CT Document Library

    Loading...

Page tree

  

Requirement

The SNOMED CT subtype polyhierarchy is an important resource which allows retrieval of all the concepts that are subtypes of a specified concept. The full hierarchy is represented by subtype (is a) relationships between each concept and its proximal supertypes. Therefore, to determine is whether a concept is a subtype of another concept a chain of these subtypes must be followed.  It is possible to follow these chain in a relational database environment but the queries to achieve this are complex and often perform poorly.

Solution

Fortunately, there is a well-understood way to simplify and speed up the testing process. This requires the creation of a resource known as a transitive closure table. A transitive closure table includes direct relationships between every concept and all of it subtypes and supertypes. This makes it possible to test whether a concept is a subtype of another concept looking for a single row in that table. Similarly it makes it easy to access all the subtypes (or supertypes) of concept with a query on a single table.

Creating a Transitive Closure File

SNOMED International provides a Perl script that reads the snapshot relationships file. By processing this file, it rapidly generates the transitive closure of all the subtype relationships and then saves this as a file. The result a two column file (subtypeId and supertypeId) containing more than 6.5 million rows. This transitive closure file can then be read into a database table in the same way as the release files.

Using a Transitive Closure File

By using the transitive closure table very simple queries can:

  • Test is concept A is a subtype of concept B
  • List all supertypes of concept A
  • List all subtypes of concept A

Computing Proximal Primitive Supertypes

The transitive closure can also be used to determine which supertype concept or concepts are essential to the definition of a concept. These concepts are known as the proximal primitive supertypes of a concept. Proximal primitive supertypes can be computed as follows.

  1. Use the transitive closure table to get the set of all concepts that are supertypes of concept A
    • Call this set: Set-B
  2. Create a subset of Set-B containing only those concepts with definitionStatusId= 900000000000074008 | Primitive|  
    • Call this set: Set-C

  3. Create a subset of Set-C containing only those concepts that have no subtypes that are also in Set-C1

    • The resulting set represents the primitive supertypes of concept A.


Footnotes
Ref Notes
1 Concepts in Set-C that also have subtypes in Set-C are primitive supertypes of concept A but they are not proximal primitive supertypes because these subtype(s) are more specific concepts that are also primitive supertypes of concept A.

Feedback
  • No labels