> For clean Markdown of any page, append .md to the page URL. > For a complete documentation index, see https://docs.emtelligent.com/llms.txt. > For AI client integration (Claude Code, Cursor, etc.), connect to the MCP server at https://docs.emtelligent.com/_mcp/server. # 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](#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)](https://mlds.ihtsdotools.org/#/landing) 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](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](https://browser.ihtsdotools.org/?), you will see that it has [12 child concepts](https://browser.ihtsdotools.org/?perspective=full\&conceptId1=22298006\&edition=MAIN/2020-07-31\&release=\&languages=en), 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. ![subtypes concepts for myocardial infarcton (collapsed view)](/_fern-img/e36ae948667e6d1d5e8710faa3ab82bfb15d8a9cf0468e3b34f9d12115392a5a.webp) 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. ![partial hierarchy for fever (finding)](/_fern-files/emtelligent-prod.docs.buildwithfern.com/45c5f96981cd456d8c44c0c174a4cfe215f2febcbe59f7c9bf7299ff5f351024/assets/nlp/sct_is_a_relationship_hierarchy.png) ## 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](https://legacy-docs.emtelligent.com/emtellipro/downloads.html). 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: ```bash 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: ```bash 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](https://docs.snowflake.com/en/user-guide/snowsql.html). 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: ```text snowsql -a -u -d -s -r -w ``` 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: ```text 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: ```bash !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. > **Note** > > For MS SQL Server, the two step approach above was chosen because it works reliably on systems that have sufficient in-process memory for SQL Server Management Studio to load and run large .sql files. > > You can also use the sqlcmd to execute DDL SQL script and to `BULK INSERT` the data using the commands below. However, before running must first ensure that the database user has sufficient sql server permissions to perform a bulk insert and file permissions to read the requisite files: ```bash sqlcmd -U ** -P **** -d sct_test -i //sct_relationship_usedition_mar2022_ms_sql/sct_relationship_ms_sql.sql sqlcmd -U ** -P **** -d sct_test -Q "BULK INSERT sct_relationship FROM '//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](https://www.nlm.nih.gov/healthit/snomedct/international.html) or the [US edition](https://www.nlm.nih.gov/healthit/snomedct/us_edition.html). You will then need to load these into a database using open source database loading scripts available on [Github](https://github.com/IHTSDO/snomed-database-loader). ## 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. ![partial hierarchy for fever (finding)](/_fern-img/c3301c412545a4c45ba92bf75a894a980a4b6fa8203e7a495d1d9c2a3ce3f693.webp) 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](https://confluence.ihtsdotools.org/display/DOCEG/Relationship+Group). * **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. > **Warning** > > Concept hierarchies in SNOMED may contain duplicate concepts. The same concept may be referenced in multiple branches of a concept tree. To return a set of concept subtype ids (with no duplicates), you must use a recursive query and also SELECT DISTINCT to remove the repeated concepts. 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. #### SQL ```sql 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 ``` | destinationid | | ------------- | | 22298006 | | 413439005 | ### 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`). #### SQL ```sql 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 ``` | conceptid | | ------------------ | | 129574000 | | 16837681000119104 | | 1755008 | | 194802003 | | 194856005 | | 233843008 | | 314207007 | | 380001000004106 | | 394710008 | | 418044006 | | 42531007 | | 428196007 | | 57054005 | | 726499301000119105 | | 879955009 | 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. > **Note** > > Concept hierarchies in SNOMED may contain duplicate concepts. The same concept may be referenced in multiple branches of a concept tree. Hence, the DISTINCT keyword is used to removed duplicate concepts from the IDs returned by the recursive CTE. This is particularly important if you subsequently perform a table join with the output of the `sctids` recursive CTE given below. #### SQL ```sql 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; ``` #### Microsoft SQL Server ```sql WITH 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; ``` | count | | ----- | | 127 | 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. ![Entity-Relation diagram for sct\_relationship table & related Output Database tables](/_fern-img/86c1818d2d1435e6d5424a16cbdd5a0cd95b9e44606703ef42c3301fee6530fe.webp) 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. > **Warning** > > When performing an inner join between the **sct_relationship** table and the **concept** table, you must test for equality as follows `sct_relationship.sourceid = concept.concept_id AND concept.ontology = 'snomed`. The later constraint is required because `concept_id` values in the Output Database are not guaranteed to be unique across ontologies. ### 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. #### SQL ```sql 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'; ``` #### Microsoft SQL Server ```sql WITH 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'; ``` | sourceid | concept_id | description | | ----------------- | ---------- | ----------- | | 1163440003 | — | — | | 1204151009 | — | — | | 1204152002 | — | — | | 1204154001 | — | — | | 1204155000 | — | — | | 1204222000 | — | — | | 1208872002 | — | — | | 1208873007 | — | — | | 12238111000119106 | — | — | | 12238151000119107 | — | — | 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. #### SQL ```sql 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; ``` #### Microsoft SQL Server ```sql WITH RECURSIVE 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' ), 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 the parent concept 'Myocardial Infarction (disorder)' UNION ALL SELECT DISTINCT sourceId FROM subtypeids ) SELECT -- TOP 5 e.id, regexp_replace(d.filename, '.*/', '') as filename, fe.polarity, fe.uncertainty, c.description as fully_specified_name, fet.type_name, patient_ers.mention as mention, patient_ers.experiencer as experiencer, 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' AND c.concept_id IN ( SELECT * FROM sctids) AND fe.polarity = 'asserted' ORDER BY e.id; ``` | id | polarity | uncertainty | fully_specified_name | type_name | experiencer | mention | term | sentence | | ---- | -------- | ----------- | --------------------------------------------- | --------- | ----------- | ------- | ---------------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------- | | 3291 | asserted | certain | Old inferior myocardial infarction (disorder) | disorder | patient | assumed | Old inferior myocardial infarction | Old inferior myocardial infarction. | | 3313 | asserted | certain | Myocardial infarction (disorder) | disorder | patient | assumed | MI | Cocaine Induced MI | | 3439 | asserted | certain | Myocardial infarction (disorder) | disorder | patient | assumed | MI | Cocaine Induced MI | | 7001 | asserted | uncertain | Myocardial infarction (disorder) | disorder | patient | assumed | myocardial infarction | T wave inversion in the anterior and lateral limb leads with ST segment elevation in leads V1-V2 which suggest evolving myocardial infarction. | | 7046 | asserted | uncertain | Old myocardial infarction (disorder) | disorder | patient | assumed | old ischemia | Early repolarization may be from LVH or old ischemia. | ## References * Snomed CT Technical Implementation Guide, [Technical Overview](https://confluence.ihtsdotools.org/display/DOCTIG/3.+Technical+Overview). * Snomed CT Relationship table SQL [download](https://legacy-docs.emtelligent.com/emtellipro/downloads.html). * SNOMED CT Files [International Edition](https://www.nlm.nih.gov/healthit/snomedct/international.html). The NLP API uses the International Edition. * SNOMED CT Files [US Edition](https://www.nlm.nih.gov/healthit/snomedct/us_edition.html). * [IHTDSO SNOMED Database Loader](https://github.com/IHTSDO/snomed-database-loader).