Using SNOMED CT Concept Hierarchies

Introduction

The goal of this section of documentation is to provide concrete examples of how to use the SNOMED CT ontology to best achieve your goals with the Output Database queries. To this end we will:

• Provide a brief overview of SNOMED focusing on those topics that are relevant for data retrieval from Output Databases
• Show how to extend the Output Database by loading relationship definitions from the SNOMED ontology
• Provide several queries to show how to use SNOMED relationship definitions to find related SNOMED concept IDs to use in a query.

The sample SQL queries provided in this document were developed for PostgreSQL. However, Microsoft SQL Server or MySQL-compatible queries can be downloaded from links provided in the References section.

Licensing & Distribution

Before using SNOMED CT, please check your SNOMED CT licensing eligibility.

SNOMED CT is licensed for use free-of-charge in SNOMED member countries which includes (but is not limited to) Canada, USA, and the UK. If you are using and/or deploying SNOMED CT in a non-Member country/territory, you are required to apply for a license through the Member Licensing & Distribution Service (MLDS) on an annual basis.

SNOMED CT Overview

SNOMED CT (Systematized Nomenclature of Medicine - Clinical Terms) is a very large medical ontology, maintained by SNOMED International (https://www.snomed.org), the trading name of the IHTSDO (International Health Terminology Standards Development Organization). SNOMED CT is comprised of 3 different components:

• Concepts
• Descriptions
• Relationships

SNOMED CT codes the words used in medicine into concepts and descriptions. In the context of SNOMED CT, the term concept refers to the unique identifier that is assigned to a clinical idea. A concept is mapped to a set of descriptions, which gives the human-readable term or fully-specified name of the concept. Descriptions also provide information about synonyms and translations. Relationships represent an association between two concepts. While not required, using relationships can significantly simplify and enhance extraction of data from an Output Database. We will go into more detail about SNOMED relationships and how they are codified in the next section.

In addition to these components, SNOMED CT also contains additional information in the form of reference sets. These are used to represent subsets of components. A reference set may refer to:

• An ordered list of components
• A set of associations between components
• A mapping between SNOMED CT concepts and another system of codes, classifications, or knowledge resources.
• A language or dialect of language (e.g. British English (e.g. oesophagus) vs. American English (esophagus)

For the purposes of this documentation, we will focus solely on the use of the Relationships component.

SNOMED Relationships

In SNOMED CT, relationships represent an association between two concepts. Every concept is logically defined relative to other concepts via relationships, and hence the definition of a concept is represented in SNOMED as a hierarchy of related concepts.

There are 2 major classes of relationships:

• ‘Is A’ relationships
• Attribute relationships

In this document, we will only discuss ‘Is A’ relationships. The key points to note about relationships are that:

1. Every concept has at least one ‘Is A’ relationship
2. A relationship is specified only when the relationship is known to be true.

The ‘Is A’ relationship is the simplest to understand and the most relevant for querying the NLP API data because by finding all concepts that are related to a target concept via ‘Is A’ relationships, we can expand the query to return a wider result set for exploration or analysis. The ‘Is A’ relationship defines an association between a Subtype (or child) and Supertype (or parent) concept. Consider the concept ‘Myocardial Infarction’; when you search for this concept using the SNOMED CT Browser, you will see that it has 12 child concepts, and some of these in turn, have their own child concepts. In this case Myocardial Infarction is the supertype related to the 12 subtypes by the |Is a| defining relationship.

The following should be noted about the hierarchy of concepts in SNOMED:

• The hierarchy can be nested and be multiple levels deep. For example the subtype concept ‘Acute myocardial infarction’ itself has 23 child concepts.
• When |Is a| relationships are illustrated as directional arrows: the subtype is the source and supertype concept is the destination of the arrows.

The following diagram is an illustration of the partial concept hierarchy for Fever (finding), showing only select concepts from the first level of the hierarchy. Notice the direction of the arrows. Subtypes are source concepts and supertypes are the destination concepts. This detail is important when querying for concept IDs using relationships.

Adding SNOMED relationships to Output Database

SNOMED International allows for significant, country-based extensions to SNOMED CT, which are maintained by medical standards organizations in those countries - e.g. in the US, they are maintained by the National Library of Medicine, part of the NIH.

SNOMED distributes its ontology as a set of files and these files can be loaded into a relational database management system (e.g. MySQL, MS-SQL, PostgreSQL) to allow querying of SNOMED. emtelligent has loaded SNOMED into both PostgreSQL, MySQL and MS-SQL, and exported the relationships table as a compressed .sql file and made them available for download. The files are named as follows:

• sct_relationship{country}_{version}_mysql.zip
• sct_relationship{country}_{version}_psql.zip
• sct_relationship{country}_{version}_ms_sql.zip
• sct_relationship{country}_{version}_snowflake.zip

Note: The above {country} in the filename denotes whether this is a SNOMED US or international release, and the {version} denotes the SNOMED CT release that the relationships table was exported from.

Follow the instructions to import the emtelligent-provided sct_relationship data into your Output Database.

Using emtelligent’s distribution

MySQL

For MySQL server, run the following command:

zcat sct_relationship_{country}_{version}_mysql.gz | mysql -u {dbuser} -p {emtellipro-db-name}
Enter password: {dbpwd}

PostgreSQL

For PostgreSQL server, run the following command:

zcat sct_relationship_{country}_{version}_psql.gz | psql -U {dbuser} -W -d {emtellipro-db-name}
Enter password: {dbpwd}

Snowflake

For Snowflake, loading the relationship definitions into a table in the Output Database is a two-step process consisting of:

• Uploading the provided csv file to an internal stage.
• Executing the provided sql file that creates and populates the sct_relationship table.

To add the SNOMED relationship table:

1. Download the zip file sct_relationship_{country}_{version}_snowflake.zip.

2. Extract the downloaded zip file.

3. Extract the downloaded zip file. You will find 2 files:

   • a .csv file containing the data in csv-delimited format
   • a .sql file

4. In the directory containing the extracted files, start a SnowSQL client session.

   There are several ways to do this. If using a config file to store connection details, please refer to the SnowSQL documentation.

   To start a SnowSQL session, you can also directly invoke the snowsql command-line client as follows, subsituting the placeholder arguments with the correct values for your instance:

snowsql -a <account.url> -u <user> -d <db_name> -s <schema> -r <role> -w <warehouse>

1. Upload the file to Snowflake. Note the following:

   • Please do not alter the name of .csv file as it is referenced by the companion .sql file.
   • Please upload the file to the root of the current user’s personal stage in Snowflake. The COPY TO statement in the .sql file expects to find the csv file there. If you upload the csv to a location other than the current user’s personal stage (signified by @~), you must modify the .sql file with the preferred location.

   To upload the csv file to the root of the current user’s personal stage:

PUT file://./sct_relationship_v2-22.csv @~ auto_compress=true;

1. Execute the .sql file to create and populate the sct_relationship table in a Output Database.

   • Connect to an existing Output Database in Snowflake. Note: If one does not exist, you will have to create a new database in Snowflake and initialize it using the Output Database Client create-db command. For more information about this command, see Preparing the database for storing NLP API output in the Output Database Client documentation.
   • In snowsql, at the command prompt execute the provided .sql file. For example:

!load sct_relationship_insert.sql; 

This script contains SQL commands that creates a table called sct_relationship with the required column definitions, and copies rows from the csv file into it.

Microsoft SQL Server

For Microsoft SQL Server, loading the relationship data into the Output Database is a two-step process: creating the table and then importing the data from tab-delimited text file. You will need to do the following:

1. Download the zip file sct_relationship\_{country}\_{version}\_ms_sql.zip.

2. Extract the downloaded zip file. You will find 2 files:

   • a .tsv file containing the data in tab-delimited format
   • a .sql file that contains the table definition for the relationship table

3. Execute the .sql file to add the sct_relationship table to an existing the Output Database. For example, you can do this using sqlcmd from the command prompt or by opening the file in the SQL Server Management Studio. Select the target database and then execute the CREATE TABLE statement. This will add a table called sct_relationship to your database with the required column definitions.

4. Use the SQL Server Management Studio, to import data from the .tsv file as follows:

   1. In the Object Explorer, right-click on the destination database. In the right-click menu, click Task > Import Data.

   2. In the Choose a Data Source dialog, select Flat File Source.

      1. Browse and find the .tsv file.
      2. Change the Header row delimiter character to {LF}.
      3. In the Choose a Data Source dialog click Advanced (in the left-hand panel) to reveal the advanced data type settings for flat-file imports.
      4. Then click the Suggest Types button. Click OK button to dismiss the Suggest Types dialog.
      5. Review the suggested types for each column shown in the right-hand panel of the Advanced view. Ensure that the data types are set as follows:
         • Select 8-byte signed integer (DT_I8) for these columns: id, moduleId, sourceId, destinationId, typeId, modifierId, characteristicTypeIdid
         • Select 1-byte signed integer (DT_I1) for these columns: active, relationshipGroup
         • Select Date (DT_DATE) for these columns: effectiveTime

   3. In the Choose a Destination dialog:

      1. Set the Destination to SQL Server Native Client 11.0
      2. Set the Database to the target Output Database.

   4. Click to Next to advance through the dialogs presented, and finally click the Finish button to begin the import.

sqlcmd -U ** -P **** -d sct_test -i /<path>/sct_relationship_usedition_mar2022_ms_sql/sct_relationship_ms_sql.sql
sqlcmd -U ** -P **** -d sct_test -Q "BULK INSERT sct_relationship FROM '/<path>/sct_relationship_usedition_mar2022_ms_sql/sct_relationship.tsv'
WITH ( FIELDTERMINATOR='\t', KEEPNULLS, FIRSTROW=2 )"

Using IHTSDO files

If you are using a specific edition or version of the SNOMED CT, you can download the release files directly from IDHTSO: the organization that maintains and publishes the SNOMED-CT ontology. For example, you can download the International edition or the US edition.

You will then need to load these into a database using open source database loading scripts available on Github.

The Relationship Table Schema

SNOMED-CT Relationships are stored in the relationship file, which when loaded into a database, creates a table with the following schema. The columns that we will be using most often in the Output Database queries are bolded.

The descriptions for these columns/fields are:

• id - a unique identifier for the relationship.

• effectiveTime -the datetime when the relationship entered usage

• active - either a 1 to denote ‘active’ or a 0 to denote inactive/no longer in use

• moduleId - only contains a few different values:

  • 900000000000012004 - SNOMED CT model component module - these are relationships related to concepts pertaining SNOMED itself
  • 900000000000207008 - SNOMED CT core module - these are the relationships for the SNOMED-CT core clinical concepts
  • 731000124108 - US National Library of Medicine maintained module - these are the relationships between concepts added by the US NIH’s National Library of Medicine

• sourceId - The sourceId is a SCTID (snomed concept id) and it identifies the concept being defined by the relationship. In the case of a hierarchical |Is a| relationship, the sourceid refers to the subtype or child concept.

• destinationId - The destinationId is a SCTID that represents the value of the defining relationship. In the case of a hierarchical |Is a| relationship, the destinationid refers to the supertype/parent concept. The destinationid may refer to a concept in the same extension module as the source concept, or any module on which this extension module depends.

• relationshipGroup - The relationshipGroup is an integer that indicates whether or not the relationship is grouped together with other relationships associated with the same source concept. Any relationships with the same sourceId and relationshipGroup integer (other than zero) are considered to belong to the same relationship group. A relationshipGroup of zero (i.e. “0”) indicates that the relationship was not grouped with other relationships by the terminology author. This is a complex topic, and is better described here.

• typeId- This is a SCTID for the type of relationship that there is between the concepts. In the July 2020 International release, there are 122 different relationship types. Examples of relationship types would be: due to, associated with, is a, finding site, interprets, or Has product characteristic. In most cases, the typeId will refer to an international concept. However, additional attribute concepts may be added in an extension if there is a legitimate clinical need. For the purposes of this documentation, we will only concern ourselves with the |Is a| relationship type. The is a relationship is denoted as |Is a| in SNOMED nomenclature, and its concept id is 116680003.

• characteristicTypeId - This has two values in the international release; it specifies whether this relationship was stated by the author or was inferred by a description logic classifier. The two values are:

  • 900000000000011006 - Inferred relationship (core metadata concept)
  • 900000000000227009 - Additional relationship (core metadata concept)

• modifierId - The modifierId attribute is a SCTID which specifies the type of description logic that applies to the given relationship - existential restriction (i.e. ‘there exists some’) or universal restriction (i.e. ‘there exists only’). All relationships in the International Edition use the value |Existential restriction modifier|. This value is also recommended for extension relationships, as the value |Universal restriction modifier| can significantly affect the speed of classification.

The following queries illustrate how to query the sct_relationship table.

Query: Find concept IDs for immediate supertypes

This query finds the immediate supertype or parent concepts of ‘acute myocardial infarction (disorder)’ with which it has a |Is A| relationship. The Supertype concept is a more general concept and hence extends the query to return more hits.

Because we want the supertype concepts, we select the destinationid field.

The key points to note about this query are:

1. We test the sourceid field for SCTID 22298006, ‘Myocardial Infarction (disorder)’.
2. We search only for relationships that are ‘Is A’ relationships by specifying the typeId as 116680003 - the Concept ID for Is a (attribute). Important: As SNOMED identifies many types of relationships between concepts, this constraint is required.
3. We ensure that no inactive or superceded entries are returned.

SELECT
r.destinationid
FROM sct_relationship r
WHERE
r.sourceid = '57054005'                         -- conceptId for 'Acute Myocardial Infarction'
AND r.typeId= '116680003'                     -- relationship is a 'is a' relationship
AND r.active=1;                              -- relationship is active

Query: Find concept IDs for immediate subtypes

This query returns the immediate subtypes of Myocardial Infarction (disorder). We do this by retrieving the sourceid values of relationships where the destinationid is the concept of interest. By doing so we will usually find a large number of more specific child concepts.

In the WHERE condition we filter for rows where destinationid is the SCTID for Myocardial Infarction (22298006).

SELECT
   r.sourceid as conceptID
FROM sct_relationship r
WHERE
   r.destinationid = '22298006'           -- Snomed concept id (SCTID)  for 'Myocardial Infarction (disorder)'
   AND r.typeId = '116680003'             -- relationship is a 'is a' relationship
   AND r.active = 1;                    -- relationship is active

Notice that there are only 15 immediate children concept IDs.

The sct_relationship table does not store the fully-specified name of the concept just their IDs. In the next section, we will show how to use the SNOMED CT sct_relationship table in conjunction with the Output Database tables in queries.

Query: Find all subtypes using recursion

The previous query returns 12 subtype concepts for Myocardial Infarction. However, we know that this concept has many more subtype concepts, as it subsumes child concepts like ‘Acute Myocardial Infarction’ which itself has 23 children.

The following query finds all the subtypes concepts for Myocardial Infarction by using a recursive CTE (common table expression) to find all child concepts in the hierarchy. The logical constraint on the INNER JOIN statement shown below finds children concepts and terminates when no more |Is A| relationships are found for the target concept.

WITH recursive sctids (sourceId) AS (
SELECT      r1.sourceId       -- anchor query (provides starting point for recursion)
FROM        sct_relationship r1
WHERE       r1.destinationId = '22298006'                  -- concept for myocardial infarction
   AND         r1.typeId='116680003'                       -- relationship is a 'is a' relationship
   AND         r1.active = 1                               -- relationship is active
UNION ALL
SELECT      r2.sourceId      -- recursive query
FROM        sct_relationship r2
INNER JOIN  sctids ON r2.destinationId = sctids.sourceId  -- termination condition
WHERE       r2.active = 1                                   -- relationship is active
   AND         r2.typeId='116680003'                       -- relationship is a 'is a' relationship
)
SELECT DISTINCT * from sctids;

Notice that many more concept IDs are returned.

Querying NLP API using SNOMED relationships

Before we can use both tables in a query, we need to understand how these tables relate to each other. The following ER diagram illustrates the relationship between the NLP API’s concept table and the SNOMED CT sct_relationship table.

Notice that the sct_relationship is not related to any Output Database tables via foreign keys. However, the values in the sourceid and destinationid columns contain SNOMED concept IDs which correspond to the concept_id in the NLP API’s concept table.

Query: Find subtypes and their fully-specified name

The sct_relationship table does not store the fully-specified name (FSN), only concept IDs. This query shows how to display the FSN for those concepts in the Output Database. Note that the NLP API concept table contains only those concepts for entities that were extracted during processing, which is a subset of the concepts in the SNOMED ontology. Hence, in the results for this query we do expect to find many null values for those concepts not present in the dataset. Regardless, this query is useful because it shows the occurrence of subtype concepts in the data.

In this query, we will find the subtypes or child concepts of ‘Myocardial Infarction (disorder)’, and display their FSN by using a left outer join.

WITH recursive sctids (sourceId) AS (
   SELECT r.sourceId
         FROM sct_relationship r
         WHERE r.destinationId = '22298006'         -- concept for 'myocardial infarction (disorder)'
         AND r.typeId = '116680003'               -- relationship is a 'is a' relationship
         AND r.active = 1                             -- relationship is active
         UNION ALL
   SELECT r.sourceid
         FROM sct_relationship r
               INNER JOIN sctids
               ON r.destinationId = sctids.sourceid
         WHERE r.active = 1                             -- relationship is active
         AND r.typeid = '116680003'                     -- relationship is a 'is a' relationship
)
SELECT DISTINCT sctids.sourceId, c.concept_id, c.description
FROM sctids
         LEFT OUTER JOIN concept c
         ON c.concept_id = sourceId AND c.ontology = 'snomed';

In the query results, we find in our dataset that several subtypes of ‘Myocardial Infarction (disorder) were mapped to recognized entities. The next query shows how to perform a query to retrieve these entities and their attributes.

WITH RECURSIVE subtypeids (sourceId) AS (
      -- This recursive CTE returns rows containing subtype SCTIDs
      SELECT r.sourceId
                  FROM        sct_relationship r
                  WHERE       r.destinationId = '22298006'                   -- concept for 'myocardial infarction (disorder)'
                        AND         r.typeId='116680003'                       -- relationship is a 'is a' relationship
                        AND         active = 1                               -- relationship is active
      UNION ALL
      SELECT r.sourceId
                  FROM        sct_relationship r
                  INNER JOIN  subtypeids ON r.destinationId = subtypeids.sourceid
                  WHERE       r.active = 1                                   -- relationship is active
                        AND         r.typeId='116680003'                         -- relationship is a 'is a' relationship)
),
sctids (sourceId) AS (
      SELECT '22298006' as sourceId -- add parent concept to get the final set of ids to find
      UNION ALL
      SELECT DISTINCT sourceId FROM subtypeids -- IMPORTANT: distinct is required
),
patient_ers AS ( -- find the experiencer relation ids where patient  is implicit or explicitly mentioned
      SELECT er.id AS expid, e.type_ as mention, c.description as experiencer
                  FROM experiencerrelation er
                        JOIN entity e
                        ON er.experiencer_id = e.id -- NOTE: find experience(r) entities
                        JOIN foundentity fe
                        ON fe.id = e.entity_id
                        JOIN foundentityconcept fec
                        ON fe.id = fec.found_entity_id
                        JOIN concept c
                        ON c.concept_id = fec.concept_id AND c.ontology = fec.concept_ontology
                  WHERE c.ontology = 'snomed'
                  AND e.type_ = 'found'          -- IMPORTANT: this condition is required.
                  AND c.concept_id = '116154003' -- SCTID of 'Patient (person)'
      UNION
      SELECT er.id AS expid, e.type_ as mention, ae.value as experiencer
                  FROM entity e
                        JOIN experiencerrelation er
                        ON er.experiencer_id = e.id -- NOTE: experience(r) entities
                        JOIN assumedentity ae
                        ON ae.id = e.entity_id
                  WHERE e.type_ = 'assumed' AND ae.value = 'patient'
)
SELECT --count(*)
      e.id, fe.polarity, fe.uncertainty, c.description as fully_specified_name, fet.type_name,
      patient_ers.experiencer as experiencer,
      patient_ers.mention as mention,
      fe.text as term,
      sl.text as sentence
FROM patient_ers   -- Using subset of experiencer relations referring to patient (implicit/explicit), find those
                     -- patients who experience 'myocardial infarction (disorder)' or its subtypes
         JOIN experiencerrelation er ON patient_ers.expid = er.id
         JOIN entity e ON er.experienced_id = e.id -- NOTE: experience(d) entities
         JOIN foundentity fe ON fe.id = e.entity_id
         JOIN foundentityconcept fec ON fe.id = fec.found_entity_id
         JOIN concept c ON c.concept_id = fec.concept_id AND c.ontology = fec.concept_ontology
         JOIN foundentitylocation fel ON fe.id=fel.found_entity_id
         JOIN location l ON fel.location_id=l.id AND (l.type_='sentence')
         JOIN sentencelocation sl ON sl.id=l.location_id  AND (l.type_='sentence')
         JOIN foundentitytype fet ON fet.found_entity_id=fe.id AND (fet.ontology LIKE 'snomed')
         JOIN document d ON d.id=l.document_id
WHERE c.ontology = 'snomed' -- IMPORTANT: this condition is required
      AND c.concept_id IN ( SELECT * FROM sctids)
      AND fe.polarity = 'asserted'
ORDER BY e.id;

References

• Snomed CT Technical Implementation Guide, Technical Overview.
• Snomed CT Relationship table SQL download.
• SNOMED CT Files International Edition. The NLP API uses the International Edition.
• SNOMED CT Files US Edition.
• IHTDSO SNOMED Database Loader.