> 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 Descriptions ## Overview Descriptions in SNOMED CT are human readable terms that are either fully-specified names or synonyms for SNOMED concepts. In some query strategies that use SNOMED CT relationships to extensionally select subsets of concepts of interest, it is helpful to be able to look up the fully-specified names (FSN) for these concepts. However the NLP API **concept** table does not contain the full set of FSNs available in SNOMED CT, only those present in the source reports that had been processed by the NLP API. emtelligent provides an add-on table, **sct_description** table. This table contains the **fully-specified names** for **all** currently **active** concepts from the indicated release. If you have added the **sct_relationship** table as explained in [here](/nlp/database/schema/extensions/snomed-hierarchies), you may also want to add the **sct_description** table. This section explains how to use the **sct_description** in conjunction with the **sct_relationship** table to enable hierarchical concept searches. The following shows rows from the **sct_description** table. | conceptid | term | | ---------------- | --------------------------------------------- | | 100000000 | BITTER-3 (product) | | 10000006 | Radiating chest pain (finding) | | 1000001000004108 | Mismatch repair endonuclease PMS2 (substance) | | 1000004 | Sprain (morphologic abnormality) | | 100001001 | BIZOLIN 200 (product) | ## Adding SNOMED descriptions to Output Database This section explains how to add the **sct_description** table to the Output Database. ### MySQL For MySQL server, run the following command: ```bash zcat sct_description_{country}_{version}_mysql.zip | mysql -u {dbuser} -p {emtellipro-db-name} Enter password: {dbpwd} ``` ### PostgreSQL To add the SNOMED description table: 1. Download the zip file `sct_description_{country}_{version}_psql.zip`. You can download this file from the [downloads](https://legacy-docs.emtelligent.com/emtellipro/downloads.html) page. 2. Then, run the following command ```bash zcat sct_description_{country}_{version}_psql.zip | 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_description** table. 1. Download the zip file `sct_description_{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_description_v2-22.csv @~ auto_compress=true; ``` 1. Execute the `.sql` file to create and populate the `sct_description` 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_descriptiion` 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_description\_{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_description` 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. This will add a table called `sct_description` 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. Click on *Advanced* (in the left-hand panel) to reveal the advanced data type settings for flat-file imports. The defaults must be changed as follows: > > * For *Column 0* (containing `conceptId` values), set *DataType* to `eight-byte signed integer` > > * For *Column 1* ( containing `term` values), set *OutputColumnWidth* to `256` > > 3. In the *Choose a Destination* dialog, set the *Destination* to `SQL Server Native Client 11.0` > > 4. Click to Next to advance through the dialogs presented, and finally click the **Finish** button to begin the import. ## Using sct_description table The following ER diagram illustrates the relationship between the **sct_relationship** table and the **sct_description** table. ![Entity-Relation diagram showing sct\_relationship table in relation to sct\_description table](/_fern-img/c47345bd547c1064769eff59385c68c1b8202baf0e09ade80c0b75c23e32983f.webp) In the ER diagram, notice that: * In the **sct_description** table, **conceptid** column stores the Snomed concept ID as **number**, and the **term** column contains the fully-specified name for the concept. * The **conceptid** column is a primary key field, but does not have foreign key constraints. However, it can be used as the join constraint for joining the **sct_relationship** and **sct_description** tables. Because the **sct_description table** contains all Snomed concept descriptions, it is used when a concept description cannot be found in the **concept** table. This is the case when there are no occurrences of the concept in your report (and therefore the concept was not extracted by the NLP API and added to the **concept** table). However, in some data analysis tasks, it is convenient to be able to access all concept descriptions in Snomed regardless. For example, when: * You have generated a list of target concepts to search for by using the `Is a (attribute)| 116680003` relationship to find all children concepts, and you want to review their fully-specified names. * Using other Snomed attribute relationships e.g. `Procedure site - Direct (attribute) | 405813007` to retrieve more information about the concept. These types of searches typically return target concepts not present in your reports. The following section provides sample queries for these uses. > **Note** > > It is essential to understand how to use the hierarchical concept search query pattern before attempting the following queries (see [Using SNOMED CT Concept Hierarchies](/nlp/database/schema/extensions/snomed-hierarchies)) ### Query: Find concept descriptions for children of Myocardial Infarction The following query searches performs a hierarchical concept search for `Myocardial Infarct (disorder) | 22298006` and all its children concepts. This query approach explained in [here](/nlp/database/schema/extensions/snomed-hierarchies). After retrieving the set of children concept IDs, we look up the concept description using the **sct_description** table by joining on the **conceptid** column as shown. #### PostgreSQL ```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 ) SELECT sd.* from sctids JOIN sct_description sd ON sctids.sourceId = sd.conceptid LIMIT 5; ``` | conceptid | term | | ---------- | ----------------------------------------------------------------------------------------------------------- | | 1163440003 | Postoperative acute myocardial infarction (disorder) | | 1204151009 | Acute inferior non-ST segment elevation myocardial infarction of right ventricle (disorder) | | 1204152002 | Acute inferior non-ST segment elevation myocardial infarction (disorder) | | 1204154001 | Acute anterior non-ST segment elevation myocardial infarction with right ventricular involvement (disorder) | | 1204155000 | Acute anterior non-ST segment elevation myocardial infarction (disorder) | ### Query: Find procedure sites Some Snomed CT concepts are post-coordinated concepts such as `Arthrotomy with open meniscus repair (procedure) | 80486001` where the location of a procedure and the procedure are represented by a single concept ID. Snomed CT represents defining characteristics using as **attribute relationships**. In this example, SNOMED CT distinguishes `Arthrotomy with open meniscus repair` from other types of arthrotomies by using the `procedure site` relationship attribute to associate `arthrotomy` (surgical incisions into joints) with `knee joint structure` and `knee meniscus structure`. SNOMED CT defines a large number of relationship attributes, each of which is relevant for a subset of concepts within the ontology (i.e. each type of attribute relationship is applicable for a specific domain or range of concepts). An explanation of the [logical model](https://confluence.ihtsdotools.org/display/DOCSTART/5.+SNOMED+CT+Logical+Model) and the [list of relationship attributes and their ranges](https://confluence.ihtsdotools.org/display/DOCSTART/6.+SNOMED+CT+Concept+Model) is found in the [SNOMED CT Starter Guide](https://confluence.ihtsdotools.org/display/DOCSTART/SNOMED+CT+Starter+Guide). You can use this feature of the SNOMED ontology to retrieve the body structures associated with procedures. The query below shows how to do this using the Output Database. The approach is as follows: 1. First, find all procedure concepts by performing a hierarchical search for `Procedure (procedure) | 71388002`. 2. Perform a left join with the **sct_relationship** table to find all relationships for procedure concepts that are of type `Procedure site - Direct (attribute) | 405813007`. 3. For the `Procedure site - Direct (attribute) | 405813007` relationship instances found, get the body site concept IDs from the **destinationid** column from the corresponding row (of the **sct_relationship** table). 4. Finally, look up the **destinationid** in the **sct_description** table to retrieve the fully-specified name of the body sites. This query only returns procedures where the procedure is positively asserted **and** the procedure site ID defined (i.e. is not NULL). #### PostgreSQL ```sql WITH RECURSIVE subtypeids (sourceId) AS ( -- This recursive CTE returns rows containing subtype SCTIDs for Procedure (procedure), a top-level concept SELECT r.sourceId FROM sct_relationship r WHERE r.destinationId = '71388002' -- concept for Procedure (procedure) 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 ( -- add parent concept to get the final set of ids to find SELECT '71388002' as sourceId UNION ALL SELECT DISTINCT sourceId FROM subtypeids ), procedures AS ( -- Get procedure entity info and look up procedure site attributes SELECT DISTINCT ON (fe.id) fe.id AS feid, -- Required (possible for multiple procedure sites per procedure) c.concept_id, c.description, c.ontology, sc.sourceId AS sourceid, fe.polarity, fe.uncertainty, fe.section_name, first_value(sd.conceptid) -- Select first procedure site if more than one OVER (PARTITION BY fe.id) AS site_concept_id, first_value(sd.term) OVER (PARTITION BY c.concept_id) AS site_concept FROM foundentity fe JOIN foundentityconcept fec ON fe.id = fec.found_entity_id JOIN concept c ON fec.concept_id = c.concept_id AND fec.concept_ontology = c.ontology AND c.ontology = 'snomed' JOIN sctids sc ON sc.sourceId = c.concept_id -- Get site, using Procedure site - Direct (attribute) relationship LEFT JOIN sct_relationship sr ON sc.sourceId = sr.sourceid AND sr.typeid = '405813007' -- Lookup site concept id to find its fully-specified name LEFT JOIN sct_description sd ON sr.destinationid = sd.conceptid ) SELECT proc.concept_id, proc.description AS concept, fet.type_name AS entity_type, fe.text AS term, proc.site_concept_id AS site_concept_id, proc.site_concept AS site_concept, sl.text AS sentence FROM procedures proc JOIN foundentity fe ON proc.feid = fe.id JOIN foundentitylocation fel ON proc.feid = 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 sentencelocationspan sls ON sl.id = sls.sentence_location_id JOIN foundentitytype fet ON fet.found_entity_id = proc.feid AND fet.ontology = 'snomed' JOIN document d ON d.id = l.document_id WHERE proc.polarity = 'asserted' AND proc.site_concept_id IS NOT NULL -- !! Only return procedures where site is defined ORDER BY feid, 1, 2, 3, 4, 5, 6, 7; ``` | concept_id | concept | entity_type | term | site_concept_id | site_concept | sentence | | ---------- | --------------------------------------------------------------- | ----------- | --------------------------------- | --------------- | -------------------------------------------------------------------------------------------------------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | 75732000 | Vitrectomy (procedure) | procedure | Vitrectomy | 47538007 | Vitreous body structure (body structure) | Vitrectomy, right eye. | | 75732000 | Vitrectomy (procedure) | procedure | vitrectomy procedure | 47538007 | Vitreous body structure (body structure) | The patient was prepared and draped in the usual manner for a vitrectomy procedure under local anesthesia. | | 120085004 | Eye incision (procedure) | procedure | MVR incisions | 81745001 | Structure of eye proper (body structure) | MVR incisions were made 4 mm posterior to the limbus in the \*\*\* and \*\*\* o'clock meridians following which the infusion apparatus was positioned in the \*\*\* o'clock site and secured with a 5-0 Vicryl suture. | | 75732000 | Vitrectomy (procedure) | procedure | vitrectomy | 47538007 | Vitreous body structure (body structure) | Then, under indirect ophthalmoscopic control, the vitrector was introduced through the \*\*\* o'clock site and a complete vitrectomy was performed. | | 120085004 | Eye incision (procedure) | procedure | MVR incisions | 81745001 | Structure of eye proper (body structure) | The MVR incisions were closed with 7-0 Vicryl. | | 76009000 | Esophagogastroduodenoscopy (procedure) | procedure | upper endoscopy | 32849002 | Stomach structure (body structure) | Otherwise, normal upper endoscopy to the transverse duodenum. | | 419394006 | Computed tomography of abdomen and pelvis (procedure) | procedure | CT of the abdomen and pelvis | 818981001 | Structure of pelvic cross-sectional segment of trunk (body structure) | COMPARISON: Comparison is made to CT of the abdomen and pelvis from yesterday. | | 416733000 | Supine plain X-ray of abdomen (procedure) | procedure | supine views of the abdomen | 818983003 | Structure of abdominopelvic cavity and/or content of abdominopelvic cavity and/or anterior abdominal wall (body structure) | TECHNIQUE: 2 supine views of the abdomen. | | 83985009 | Resection of stomach with gastrojejunal anastomosis (procedure) | procedure | Billroth II anastomosis | 119212007 | Stomach part (body structure) | Probable Billroth II anastomosis. | | 116241004 | Pancreaticoduodenectomy (procedure) | procedure | Whipple's pancreaticoduodenectomy | 28231008 | Structure of head of pancreas (body structure) | Status post Whipple's pancreaticoduodenectomy. | It should be noted that it is possible for a concept to have multiple instances of relationships of the same type. The following diagram below shows the Snomed CT diagrammatic notation for `Arthrotomy with open meniscus repair (procedure)`. Notice that this concept has multiple attribute groups each with a procedure site attribute (`Knee meniscus structure (body structure)` and `Knee joint structure (body structure)` respectively). For simplicity we return the first procedure site (with no particular guarantee as to which procedure site concept is returned). This is done using the `first_value` window function, and `SELECT DISTINCT ON` keyword. ![Snomed CT diagram showing defining relationships for Arthrotomy with open meniscus repair (procedure)](/_fern-img/9d80c0b52736e35f7ac051f9816441700c53ffcb8f88daa989989c707b226004.webp) ## References * [Using SNOMED CT Concept Hierarchies](/nlp/database/schema/extensions/snomed-hierarchies) * SNOMED CT Starter Guide * [SNOMED CT Logical Model](https://confluence.ihtsdotools.org/display/DOCSTART/5.+SNOMED+CT+Logical+Model). This page explains what Descriptions and Relationships are in the SNOMED CT nomenclature. * [SNOMED CT Concept Model](https://confluence.ihtsdotools.org/display/DOCSTART/6.+SNOMED+CT+Concept+Model). This page lists all relationship types and concept subsets that they are associated with. * [Snomed CT Diagramming Guideline](https://confluence.ihtsdotools.org/display/DOCDIAG/Diagramming+Guideline).