close

Вход

Забыли?

вход по аккаунту

?

SNOMED CT® SNOMED CT How To Guides - Ihtsdo

код для вставки
SNOMED CTВ® SNOMED CT How To Guides
July 2013 International Release
(GB English)
This document may have been updated since you received it.
The latest versions of IHTSDO documents are available online.
Latest PDF version: www.snomed.org/how.pdf
Latest web browsable version: www.snomed.org/how
Directory of available documents: www.snomed.org/doc
2002-2013 International Health Terminology Standards Development
Organisation CVR #: 30363434
2 | SNOMED CT SNOMED CT How To Guides July 2013
Contents
1 General How To Guides................................................................6
1.1 How to Understand SNOMED CT and its Value...........................................................6
1.1.1 Overview.........................................................................................................6
1.1.2 Before You Start..............................................................................................6
1.1.3 What is SNOMED CT?...................................................................................6
1.1.4 An Initial Overview..........................................................................................6
1.1.5 Next Steps......................................................................................................8
1.2 How to View the Content of SNOMED CT...................................................................8
1.2.1 Overview.........................................................................................................8
1.2.2 Before You Start..............................................................................................8
1.2.3 Browsing SNOMED CT clinical content..........................................................9
1.2.4 Next steps.....................................................................................................12
1.3 How to Find SNOMED CT Education and Guidance Materials..................................12
1.3.1 Overview.......................................................................................................12
1.3.2 Before You Start............................................................................................13
1.3.3 Overview of the main SNOMED CT documentation.....................................13
1.3.4 Related SNOMED CT Documentation and Guidance materials..................15
2 Clinical How To Guides..............................................................16
2.1 How to Look at SNOMED CT from a Clinical Perspective .........................................16
2.1.1 Overview.......................................................................................................16
2.1.2 Before You Start............................................................................................16
2.1.3 {main task group}..........................................................................................16
2.2 How to Evaluate SNOMED CT for an Intended Clinical Use......................................16
2.2.1 Overview.......................................................................................................16
2.2.2 Before You Start............................................................................................16
2.2.3 {main task group}..........................................................................................17
3 Technical How To Guides...........................................................18
3.1 How to Obtain SNOMED CT Release Files...............................................................18
3.1.1 Overview.......................................................................................................18
3.1.2 Before You Start............................................................................................18
3.1.3 Accessing Release Files..............................................................................19
3.2 How to Use SNOMED CT Release Files: ..................................................................22
3.2.1 Overview.......................................................................................................22
3.2.2 Before You Start............................................................................................22
3.2.3 Understanding how the data in the release files supports the many uses of SNOMED CT..22
3.2.4 Understanding the content of the SNOMED CT Release Files....................24
3.2.5 Using the content of the SNOMED CT Release Files..................................25
3.2.6 Summary and next steps..............................................................................27
3.3 How to Look at SNOMED CT from a Technical Perspective .....................................27
В© 2002-2013 International Health Terminology Standards Development Organisation CVR #: 30363434
Contents | 3
3.3.1 Overview.......................................................................................................27
3.3.2 Before You Start............................................................................................27
3.3.3 {main task group}..........................................................................................27
3.4 How to Evaluate SNOMED CT for a Technical Use Case..........................................27
3.4.1 Overview.......................................................................................................27
3.4.2 Before You Start............................................................................................28
3.4.3 {main task group}..........................................................................................28
3.5 How to Compare Changes Between Releases of SNOMED CT...............................28
3.5.1 Overview.......................................................................................................28
3.5.2 Before You Start............................................................................................28
3.5.3 {main task group}..........................................................................................28
3.6 How to Analyse Data Collected With Different Versions of SNOMED CT..................28
3.6.1 Overview.......................................................................................................28
3.6.2 Before You Start............................................................................................28
3.6.3 {main task group}..........................................................................................29
4 Extension Maintenance How To Guides...................................30
4.1 How to Create and Manage an Extension..................................................................30
4.1.1 Overview.......................................................................................................30
4.1.2 Before You Start............................................................................................30
4.1.3 How to Create an SCTID Generator from an Extension Identifier................30
4.2 How to Create a Simple Reference Set .....................................................................33
4.2.1 Overview.......................................................................................................33
4.2.2 Before You Start............................................................................................33
4.2.3 {main task group}..........................................................................................33
4.3 How to Represent Subsets of Concepts Using a Refset............................................33
4.3.1 Overview.......................................................................................................33
4.3.2 Before You Start............................................................................................33
4.3.3 {main task group}..........................................................................................34
4.4 How to Represent a Mapping Using a Refset (simple and complex maps)...............34
4.4.1 Overview.......................................................................................................34
4.4.2 Before You Start............................................................................................34
4.4.3 {main task group}..........................................................................................34
4.5 How to Represent Term Preferences Using a Refset (e.g. for languages and dialects)
........................................................................................................................................3 4
4.5.1 Overview.......................................................................................................34
4.5.2 Before You Start............................................................................................34
4.5.3 {main task group}..........................................................................................35
4.6 How to Update a Refset to Take Account of Changes Between Releases.................35
4.6.1 Overview.......................................................................................................35
4.6.2 Before You Start............................................................................................35
4.6.3 {main task group}..........................................................................................35
5 Reference Set Implementation How To Guides.......................36
5.1 How to Use a Simple Refset of Concepts..................................................................36
5.1.1 Overview.......................................................................................................36
5.1.2 Typographical and Syntax Conventions........................................................36
В© 2002-2013 International Health Terminology Standards Development Organisation CVR #: 30363434
4 | SNOMED CT SNOMED CT How To Guides July 2013
5.1.3 Typographical and Syntax Conventions........................................................36
5.1.4 Simple Reference Table Layout....................................................................36
5.1.5 How to load the Simple Refset Table into an SQL Database........................37
5.1.6 How to List the Available Reference Sets.....................................................38
5.1.7 How to Retrieve the Members of a Simple Reference Set...........................39
5.1.8 How to determine whether a concept is a member of a simple reference set.40
5.1.9 How to do set operations with simple reference sets...................................41
5.2 How to use a Language Refset..................................................................................46
5.2.1 Overview.......................................................................................................46
5.2.2 Before You Start............................................................................................46
5.2.3 Typographical and Syntax Conventions........................................................47
5.2.4 Language Refset Table Layout.....................................................................47
5.2.5 How to load the Language Refset Table into an SQL Database...................48
5.2.6 How to List the Available Language Reference Sets....................................50
5.2.7 Constants Used Throughout this Document.................................................50
5.2.8Howtofindthecorrecttermforaconceptgivenalanguagerefset,acceptabilityandtypeinaSNAPSHOTRelease.51
5.2.9 Related Links................................................................................................56
5.3 How to use a Simple Map Refset...............................................................................56
5.3.1 Overview.......................................................................................................56
5.3.2 Before You Start............................................................................................56
5.3.3 Simple Map Refset Table Layout..................................................................57
5.3.4 How to load the Simple Map Refset Table into an SQL Database................57
5.3.5 How to List the Available Simple Maps.........................................................59
5.3.6 How to Retrieve the Members of a Simple Map Reference Set...................59
5.3.7 How to Determine What a Given SCTID Maps To in a simple map..............60
5.3.8 How to determine which SCTIDs a target code maps to..............................61
5.4 How to use a Complex Map Refset............................................................................62
5.4.1 Overview.......................................................................................................62
5.4.2 Before You Start............................................................................................62
5.4.3 Complex Map Refset Table Layout...............................................................63
5.4.4 How to load the Complex Map Refset Table into an SQL Database............64
5.4.5 How to List the Available Complex Maps......................................................65
5.4.6 How to Extract the Contents of a Complex Map...........................................65
5.4.7 How to Extract the Map Rules for a Complex Map and Source SCTID........67
5.4.8 How to Determine all the SCTIDs associated with a given Map Target........70
6 Clinical Information How To Guides..........................................72
6.1HowtochooseanSNOMEDCTExtensionstrategy,shouldIusepostcoordinationoraddnewconceptstoanextension?.72
6.1.1 Implementation based on postcoordinated expressions .............................72
6.1.2 Implementation based on local concept extensions.....................................72
6.1.3 Features comparison....................................................................................72
6.2 How to Represent Clinical ideas Using SNOMED CT................................................74
6.2.1 Overview.......................................................................................................74
6.2.2 Before You Start............................................................................................74
6.2.3 {main task group}..........................................................................................74
6.3 How to Enter Data Represented Using SNOMED CT................................................74
6.3.1 Overview.......................................................................................................74
В© 2002-2013 International Health Terminology Standards Development Organisation CVR #: 30363434
Contents | 5
6.3.2 Before You Start............................................................................................74
6.3.3 {main task group}..........................................................................................74
6.4 How to Retrieve Data Represented Using SNOMED CT...........................................74
6.4.1 Overview.......................................................................................................74
6.4.2 Before You Start............................................................................................75
6.4.3 {main task group}..........................................................................................75
6.5 How to Communicate Data Represented Using SNOMED CT..................................75
6.5.1 Overview.......................................................................................................75
6.5.2 Before You Start............................................................................................75
6.5.3 {main task group}..........................................................................................75
7 Referencing and Commenting..................................................76
В© 2002-2013 International Health Terminology Standards Development Organisation CVR #: 30363434
6 | SNOMED CT SNOMED CT How To Guides July 2013
Chapter
1
1 General How To Guides
1.1 How to Understand SNOMED CT and its Value
1.1.1 Overview
This guide describes the steps you need to go through to gain a basic understanding of SNOMED CT,
its value and how it can be used.
1.1.2 Before You Start
1.1.2.1 Is This For You?
This guide is intended for people who have no previous knowledge of SNOMED CT, or who
would like to review their understanding of the basics of SNOMED CT, its value and how it can be used.
This guide is not intended for those already familiar with SNOMED CT.
Note that guidance on navigation in these documents is available for first time users.
1.1.3 What is SNOMED CT?
SNOMED CT is a comprehensive clinical terminology that provides clinical content and expressivity for
clinical documentation and reporting. SNOMED CT contains concepts for both human and non-human
medicine and some of the covered domains are:
•
•
•
Clinical findings, including disorders
Procedures, broadly defined as including all health related activities.
Observable entities which, when given a value, provide a specific finding or assertion about health
related information.
SNOMED CT can be used in health care software applications that focus on collection of clinical data,
linking to clinical knowledge bases, information retrieval, as well as data aggregation and exchange. The
content in SNOMED CT provides a standard for clinical information that can be used as a common
reference point for improving data analysis and information access and thereby results in improved patient
outcomes.
The terminology is comprised of concepts; descriptions, which contain terms that explains the concepts;
and relationships, which define the meaning of the concepts. [see SNOMED CT Overview]
1.1.4 An Initial Overview
SNOMED CT is not a simple coding system. It is a terminology system whose domain is clinical concepts.
It supports formally structured methods of representing clinical concepts. These formal representations
support multiple ways of using clinical concepts in varied applications and user groups. There are three
main approaches to understanding SNOMED CT.
•
CLINICAL: Leaning about the scope of the clinical content as well as the multiple ways it can be viewed
and expressed at the user interface level.
В© 2002-2013 International Health Terminology Standards Development Organisation CVR #: 30363434
General How To Guides | 7
•
TECHNICAL: Learning about the logical and technical structures used to represent the clinical content,
and how they can be used to support computable access to and use of the clinical content.
•
VALUE: Learning about the many ways in which SNOMED CT can be used to support patient care,
clinical practise, research, public health, etc.
Note that these three approaches are all inter-related, since the clinical content is expressed using the
formal technical structures, and the value propositions are based on a mix of both clinical content and
the logical, technical structures.
In the following we will discuss how to begin each approach. The following ”Next Steps” section will support
navigation to obtain more detailed information about each approach.
1.1.4.1 How to get an introduction to the clinical content of SNOMED CT
•
SNOMED CT is multi-axial: the clinical concepts are arranged in a number of distinct
hierarchical (tree-structured) axes, each representing a concept domain such as clinical findings,
observables, procedures, body structures, etc, There is no easy way to view or understand this content
by simply scanning (or printing out) the main hierarchies. The most practical way to view the clinical
content is with a browser.
•
As one starts to use the browser, some basic knowledge of the various ways that concepts are
represented, coded and classified in SNOMED CT will start to be helpful. For each of the (human
readable) terms displayed by the browser, additional contextual information is also available, including:
•
•
The kind of term: a term may be a fully specified name (FSN) , a preferred term (PT) or a synonym
(S) of a SNOMED CT concept. See the corresponding FAQ Questions about Terms and Descriptions
for further details.
•
The language (e.g. British English, French, etc.)
•
Which of the major hierarchies the term belongs to: clinical findings, observables, procedures, body
structures, etc,.
•
Its location in that hierarchy, i.e. its ”parent” or ”child” concepts in that hierarchy (i.e. Subtype
Relationships).
•
The ways that the concept (represented by the term) can be related to concepts in other hierarchies)
(i.e. ways that concepts from other hierarchies can define, qualify or refine its meaning).
This contextual information is specified formally by the logical and technical parts of SNOMED CT.
(See next section)
Further details are available the in the guide ”How to View the Content of SNOMED CT." Also the guide,
” How to Look at SNOMED CT from a Clinical Perspective” will walk through several actual examples
that will clarify the these topics.
1.1.4.2 How to get an introduction to the technical and structural content of SNOMED
CT.
The basic technical knowledge mentioned in the previous section is a good starting place. Additional
technical details, which support many more types of uses for SNOMED CT, are also specified, including:
•
a formal logical abstract model for SNOMED CT concepts which supports a variety technical
implementations
•
a technique to represent SNOMED CT concepts in an equivalent computable language form that can
be processed by description logic programming languages.
•
techniques to support different languages and dialects, as well as extensions to support specific
national and institutional needs
•
multiple techniques to implement SNOMED CT in software supporting clinical care, research, population
health, and many other types of uses.
•
ways to create new versions of SNOMED CT, as medical and clinical knowledge increases.
В© 2002-2013 International Health Terminology Standards Development Organisation CVR #: 30363434
8 | SNOMED CT SNOMED CT How To Guides July 2013
As more is learnt about the technical details of SNOMED CT, there is the realisation that these provide
not just a specific set of tools to support the many uses of SNOMED CT, but a formal information
architecture for the continued development of a clinical terminology.
1.1.4.3 How to get an introduction to the Value(s) of SNOMED CT.
SNOMED CT has many applications. In addition to the basic support of clinical practise,
documentation and research it is of interest a number of other use communities, including:
•
Bodies responsible for health care at national or regional Levels, participating as IHTSDO members
or potential members, or as authors of policy assisted/impacted by effective SNOMED CT
implementations
•
Health service provider organisations and Institutions, participating as users or potential users of
SNOMED CT to support use of integrated clinical information within their organisations and businesses;
as procurers of applications that support effective use of SNOMED CT; and/or integrators of applications
that use SNOMED CT.
•
Clinical Researchers and Epidemiologists, participating as designers and users of data-collection,
queries, aggregations and analyses that use SNOMED CT.
•
Authors and Publishers of Clinical Knowledge, Guidance and Decision Support, participating as users
of SNOMED CT to markup, tag or provided interfaces to their services and resources
A more detailed listing of these and other technical and clinical use cases is available at <tba>
1.1.5 Next Steps
Start exploring the other How To Guides.
Note that they are arranged in several topic areas:
•
The first 3 are the most basic: General (1), Clinical (2), andTechnical (3) How To guides.
•
The next group of How To guides are more detailed, specifying the uses of the technical architecture
of SNOMED CT to support different languages, specific national and institutional use cases, and
mapping to other terminologies. These are the Extension Maintenance (4) and Reference Set (5) How
To guides.
•
The final group are the Clinical Information (6) How To guides. These discuss the various ways to use
SNOMED CT in various Clinical Information Systems and applications. They cover creating new
concepts versus using combinations of existing concepts (pre- and post- coordination); representation
of clinical ideas with SNOMED CT; as well as data entry, retrieval, and communication using SNOMED
CT.
For other questions, explore the Frequently Asked Questions and explore other SNOMED CT International
Release documents.
1.2 How to View the Content of SNOMED CT
1.2.1 Overview
This guide describes the steps you need to go through to obtain and use a SNOMED CT browser.
1.2.2 Before You Start
1.2.2.1 Is This For You?
This guide is intended for those who need an introduction to the clinical content of SNOMED
CT. Since SNOMED CT is multi-axial, the clinical concepts are arranged in a number of distinct hierarchical
(tree-structured) axes, each representing a concept domain such as clinical findings, observables,
procedures, body structures, etc.. There is no easy way to view or understand this content by simply
В© 2002-2013 International Health Terminology Standards Development Organisation CVR #: 30363434
General How To Guides | 9
scanning (or printing out) the main hierarchies. However, the clinical content can be represented in
database structures which are viewable through a browser that can navigate the different hierarchies.
This is the most practical way to get an introduction to the clinical content of SNOMED CT
This guide is not intended for those who are already familiar with the clinical content of SNOMED CT
and/or those who are familiar with one or more SNOMED CT browsers.
1.2.2.2 Other preliminary tasks.
You must have a valid SNOMED CT license to access the SNOMED CT content (and
browser(s)). Contact the National Release Center of your member country for further details.
Note: A list of publically available SNOMED browsers is available from your National Release Center.
1.2.3 Browsing SNOMED CT clinical content
1.2.3.1 Searches - Introduction
•
•
Search arguments:
•
the browser should be able to search for matches by: words in any order, matching phrases, identical
terms, starts with, ends with, etc.
•
the search may have additional constraints: return only a simple unstructured list; a list ordered by
the basic SNOMED CT subtype hierarchies ; a list from only one of the basic hierarchies, etc. The
browser may have been extended to return other types of constraints. For example, the search
might be limited to a particular extension that has been defined for a given country or institution.
Search Results:
When one of the matching values is chosen, the browser may then have several display options. For
example, in addition to the text of the matching value, any combination of the following may or may
not be displayed :
•
the SNOMED CT Identifier (SctId): this is a unique abstract identifier for each of the matching values
•
the (one or more) matching clinical content hierarchies that contain matches to the search arguments
•
a detailed view: this may include the Fully Specified Name, the Preferred term, Synonyms and the
concepts referenced by the Fully Specified Name, Preferred Term or Synonym.
•
the formal SNOMED CT expression language form of the search result.
•
At this point, the browser may also support functionality that adds qualifiers and/or refinements to
modify the selected clinical concept. Various types of context (situational, temporal, subject relationship,
etc.) may also be added. The translation of these additions into a formal SNOMED CT expression is
another possible feature of the browser.
•
The browser may also support the export of the resulting SNOMED CT expression for use in other
documents or programs.
1.2.3.2 Search example
1.2.3.2.1 Search term = heart disease.
1.2.3.2.2 This yields a number of choices from the clinical
finding hierarchy:
•
404684003 Clinical finding:
•
93561011 heart disease (PT)
•
56123018 round heart disease (PT)
•
+625016 acute heart disease (PT)
•
1706015 heart valve disease (S)
В© 2002-2013 International Health Terminology Standards Development Organisation CVR #: 30363434
10 | SNOMED CT SNOMED CT How To Guides July 2013
•
•
+2534663012 ischemic heart disease (PT)
•
2534671011 chronic ischemic heart disease (PT)
•
299761018 ischemic heart disease - angina (PT)
•
8756018 myxedema heart disease
•
....
...
A note on the these browser extracts: All terms are preceded by their numeric SctId. If a term is preceded
by a "+", there are further terms descending from it, which may also displayed. Most terms are followed
by a PT (Preferred Term) or S (Synonym). Terms not followed by PT or S are actually FSN's (Fully
Specified Names) without their trailing "semantic tag."
1.2.3.2.3 If 2534663012 ischemic heart disease (PT) is chosen, the subtype hierarchy
of concepts then be displayed as follows:
Note that the full hierarchy starts with beginning of the Clinical Finding hierarchy
•
404684003
•
clinical finding
+56265001 heart disease
•
+128599005 structural disorder of heart
•
+414545008 ischaemic heart disease (disorder)
•
....
Note: "(disorder)" is the Semantic Tag
1.2.3.2.4 Descending the hierarchy from this point we have:
•
+414545008 ischaemic heart disease (disorder)
•
+413439005 acute ischaemic heart disease
•
+194828000 angina
•
•
•
+413838009 chronic ischaemic heart disease
82522008 ischaemic contracture of left ventricle syndrome
+22298006 myocardial infarction
•
•
+57054005 acute myocardial infarction
... other subtypes of myocardial infarction
1.2.3.2.5 We can then select 57054005 acute myocardial infarction.
At this point, the browser should be able to display many more details about the selected
concept, including the Fully Specified Name, Preferred Term, Synonyms, as well as the potential qualifiers,
contexts, and refinements, etc.
Thus we have:
•
Concept Status: current
•
•
Descriptions
• FSN: 795634013 acute myocardial infarction (disorder)
•
•
PT: 94884017 acute myocardial infarction
•
Syn: 1231678011 AMI - Acute myocardial infarction
Fully Defined as ...
•
116680003 is a 127337006 acute heart disease
В© 2002-2013 International Health Terminology Standards Development Organisation CVR #: 30363434
General How To Guides | 11
•
•
with +263502005 clinical course:
•
•
•
116680003 is a 22298006 myocardial infarction
+424124008 sudden onset AND/OR short duration
• 255212004 acute-on-chronic
• +5315009 sudden onset
• +424572001 clinical course with short duration
with Group
•
+116676008 associated morphology
•
•
363698007 finding site
•
•
+74281007 myocardium structure
Available Qualifiers
•
+246112005 severity
•
•
+272141005 severities
+246456000 episodicity
•
•
55470003 acute infarct
+288526004 episodicities
Codes
•
Original SNOMED Id : D3-15100
•
Read Code (Ctv3Id) : XE0Uhs
1.2.3.2.6 Examples of relationships between clinical concepts that may be
viewed/selected from the browser:
•
Selecting values for any of the following contexts:
•
finding context.
•
temporal context.
•
subject Relationship context.
•
procedure context.
These values are known as explicit context.
•
Refinements of meaning including:
•
•
•
•
•
•
•
•
•
•
•
•
due to
causative agent
after
finding site
laterality
associated morphology
severity
clinical course
episodicity
occurrence
finding method
finding informer
В© 2002-2013 International Health Terminology Standards Development Organisation CVR #: 30363434
12 | SNOMED CT SNOMED CT How To Guides July 2013
•
•
•
•
•
has interpretation
interprets
has definitional manifestation
pathological process
As any of the above Relationships are added to the chosen clinical concept, the browser may also
create the equivalent formal SNOMED CT expression .
An example:
417662000 | history of clinical finding in subject | : { 246090004 | associated finding | = 414545008 |
ischemic heart disease | , 408729009 | finding context | = 410515003 | known present | , 408731000
| temporal context | = 410513005 | past | , 408732007 | subject Relationship context | = 410604004 |
subject of record | }
•
All the previous examples have been "top down",
In other words we have navigated the basic clinical content hierarchies from a more general to a more
specific level of meaning. The browser may also support the reverse direction: from a more specific
to a more general meaning.
For example, the descendents from the Body Structure hierarchy include:
•
2819011013 anatomical or acquired body structure
•
496345019 body region structure
•
128263001 lower body structure
•
61685007 lower limb structure
•
32153003 left lower extremity structure
•
51636004 structure of left ankle
•
.....
It is clear that the browser should support navigation in the reverse direction, from 51636004 structure of
left ankle to 32153003 left lower extremity structure to 61685007 lower limb structure to 128263001 lower
body structure to 496345019 body region structure to 2819011013 anatomical or acquired body structure.
At any point in this upward progression, the browser should support navigation down another branch of
the Body Structure hierarchy.
1.2.4 Next steps
Review the titles of the other How To Guides and Frequently Asked Questions and select those most
relevant to your needs.
1.3 How to Find SNOMED CT Education and Guidance Materials
1.3.1 Overview
This guide describes the steps one needs to go through to locate and access SNOMED CT Education
and Guidance Materials that are relevant to your area of interest.
В© 2002-2013 International Health Terminology Standards Development Organisation CVR #: 30363434
General How To Guides | 13
1.3.2 Before You Start
1.3.2.1 Is This For You?
This guide will first list the main places where the various types of SNOMED CT education
and guidance materials are located. Since there are a number of ways to use SNOMED CT, and thus a
number of potential audiences for SNOMED CT, the guide will then suggest which types of materials are
associated with potential user types .
This guide is not intended for those already familiar with these materials.
1.3.2.2 Other Preliminaries
If this is your fist introduction to SNOMED CT, you can get a helpful overview by reviewing
the introductory How To Guides: How to Understand SNOMED CT and its Value and How to View the
Content of SNOMED CT.
If you are already familiar with some aspects of SNOMED CT, you may want to start with the Frequently
Asked Questions (FAQ's), the How To Guides, or the Glossary.
1.3.3 Overview of the main SNOMED CT documentation
•
•
•
SNOMED CT documentation. which itself contains links to
•
User Guide.
•
Editorial Guide.
•
Technical Implementation Guide.
•
Glossary
There are also two more specialised guides:
•
The Non-Human Refset Guide
•
The SNOMED CT Developer Toolkit Guide.
This page also contains some general overview documents on the benefits and the value proposition
of SNOMED CT.
Note: there is also a guide for users of a the previous SNOMED CT RF1 release.
1.3.3.2 Overview of the varied SNOMED CT user communities, and the documentation
and guidance materials appropriate to each group.
1.3.3.3 Clinical Users
There are three major subgroups of clinical users, each needing appropriate levels of
SNOMED CT documentation and guidance:
•
Healthcare professionals treating patients, or in the role of observers of the development of information
technology with an interest in its impact on health and social care.
In general, this group will be using computer applications or publications which use SNOMED CT in
various ways. They need the introductions available in the General How to Guides (1) and the Clinical
How To Guides (2), using the FAQ's as needed. For a more detailed introduction, they may also need
to explore the overview in the SNOMED CT User Guide.
•
Clinical Researchers and Epidemiologists
As designers and users of data-collection, queries, aggregations and analyses that use SNOMED CT,
this group will need a much deeper level of knowledge of the ways in which SNOMED CT can express
the clinical areas that they are studying. In general, this group will need to understand the relevant
sections of the Technical Implementation Guide: including the Implementation Guide, the Concept
В© 2002-2013 International Health Terminology Standards Development Organisation CVR #: 30363434
14 | SNOMED CT SNOMED CT How To Guides July 2013
Model Guide, the Terminology Services Guide, the Record Services Guide, and the Extensions Services
Guide.
•
Authors and Publishers of Clinical Knowledge, Guidance and Decision Support Rules
As users of SNOMED CT to markup, tag or provided interfaces to their services and resources, this
group needs a comprehensive knowledge of the ways in which SNOMED CT can be used to support
decision logic algorithms. In particular, they will need to understand in detail how the SNOMED CT
expression language can be used to express the SNOMED CT concept model, and how these formal
expressions can be used in description logic programming languages.
1.3.3.4 Technical Users
The main subgroups are:
•
Software architects and developers
Designers of software architectures (including health IT architectures) and applications developers
will need understanding of terminology releases and related specifications. This group will also need
knowledge of the technical aspects of SNOMED CT, especially the Structure and Content Guide, the
Concept Model Guide, the Terminology Services Guide and the Record Services Guide. The SNOMED
CT Developer Toolkit Guide will also be useful.
•
Extension Content Developers and Translators
This group needs detailed knowledge of the terminological structures and contents of SNOMED CT,
as well as an education in the tools used to create, extend, maintain and translate the SNOMED CT
terminology. Additional documentation is available on the IHTSDO development home page.
1.3.3.5 Classification Experts
This group includes users or potential users of SNOMED CT for data entry, reporting or
mapping to deliver required statistical and/or billing information.
In general, this group needs to know the content of the clinical information they are being asked to classify.
Although the SNOMED CT terms or expressions will usually be supplied by their application software
(e.g. pick lists), they should have access to a SNOMED CT browser if they need more detailed knowledge
about the classification(s) they are using.
Authors of classification software will also need to coordinate their work with the Authors and Publishers
of Clinical Knowledge, Guidance and Decision Support Rules (see Clinical Users, above).
1.3.3.6 Health Service Provider Organizations and Institutions
This community may use SNOMED CT in several ways :
•
•
•
to support use of integrated clinical information within their organisations and businesses
as procurers of applications that support effective use of SNOMED CT
as integrators of applications that use SNOMED CT
This group will need a general introduction, including the User Guide Overview and the value proposition.
More detailed Information about the various ways in which SNOMED CT can support this user community
can be found in the implementation section of the Technical Implementation Guide.
The requirements from the Governmental Policy group will also be important (see next).
1.3.3.7 Governmental Policy
Bodies Responsible for Health Care at National or Regional Levels will need several types
of SNOMED CT documentation and guidance including:
•
the User Guide Overview
•
the value proposition
•
the varied ways in which implementations of SNOMED CT can benefit all the user groups mentioned
in this document.
В© 2002-2013 International Health Terminology Standards Development Organisation CVR #: 30363434
General How To Guides | 15
•
input from the other user communities listed herein, including the general public.
1.3.3.8 General Public
The public may have an interest in the ways in which SNOMED CT may support personal
health care delivery (using SNOMED CT encoded records and services), and/or the ways in which the
development of information technology may have an impact on health care and social care.
For a generic formal introduction to SNOMED CT, the Overview in the User Guide is recommended.
The SNOMED CT value proposition also provides useful background information for the public.
1.3.3.9 Health IT Application Vendors
As developers and suppliers of products and services that use (or may in future use)
SNOMED CT, this group will need both a general introduction, including the User Guide Overview and
the value proposition.
The vendors will also want to communicate with the following user groups and understand the SNOMED
CT documentation and guidance materials that they are using
•
Governmental Policy
•
Health Service Provider Organisations and Institutions
•
Technical and Clinical users(see above)
1.3.4 Related SNOMED CT Documentation and Guidance materials.
•
SNOMED CTs Training Videos
•
2012 Implementation Showcase
•
The IHTSDO Education SIG home page (requires registration).
В© 2002-2013 International Health Terminology Standards Development Organisation CVR #: 30363434
16 | SNOMED CT SNOMED CT How To Guides July 2013
Chapter
2
2 Clinical How To Guides
2.1 How to Look at SNOMED CT from a Clinical Perspective
2.1.1 Overview
This guide describes the steps you need to go through to {task objective summary}.
2.1.2 Before You Start
2.1.2.1 Is This For You?
This guide is intended for people who {primary audience}.
This guide is not intended for {audiences not supported}.
2.1.2.2 {other preliminary tasks if any}
2.1.3 {main task group}
2.1.3.1 {main task step 1}
2.1.3.2 {main task step 2}
2.2 How to Evaluate SNOMED CT for an Intended Clinical Use
2.2.1 Overview
This guide describes the steps you need to go through to {task objective summary}.
2.2.2 Before You Start
2.2.2.1 Is This For You?
This guide is intended for people who {primary audience}.
This guide is not intended for {audiences not supported}.
В© 2002-2013 International Health Terminology Standards Development Organisation CVR #: 30363434
Clinical How To Guides | 17
2.2.2.2 {other preliminary tasks if any}
2.2.3 {main task group}
2.2.3.1 {main task step 1}
2.2.3.2 {main task step 2}
В© 2002-2013 International Health Terminology Standards Development Organisation CVR #: 30363434
18 | SNOMED CT SNOMED CT How To Guides July 2013
Chapter
3
3 Technical How To Guides
3.1 How to Obtain SNOMED CT Release Files
3.1.1 Overview
This guide describes the steps you need to go through to obtain SNOMED CT release files.
Note: SNOMED CT release files are tab-delimited text files intended to be imported into a database
or loaded by a software application. While these files hold the full content of SNOMED CT, they do
not allow you explore that content unless you have software to process it in an appropriate manner.
3.1.2 Before You Start
3.1.2.1 Is This For You?
This guide is intended for people who wish to obtain SNOMED CT content in a form that
can be imported in a database or software application.
This guide is not intended for non-technical users who are interested in browsing, exploring or evaluating
the clinical content of SNOMED CT.
3.1.2.2 Meeting Licensing Requirements
SNOMED CT release files are only available to IHTSDO Affiliate Licensee. The process of
becoming an IHTSDO Affiliate Licensee is straight-forward and simple requires you to register your
acceptance of the terms of the IHTSDO Affiliate License Agreement. The current approach to becoming
an IHTSDO affiliate depends on whether you are located in an IHTSDO Member country.
•
To obtain an IHTSDO Affiliate License:
•
•
In an IHTSDO Member country, please check the relevant page IHTSDO Member web pages for
advice on how to register.
In countries that are not IHTSDO Members, or in IHTSDO Member countries that do not provide
a national registration service, please use the SNOMED CT Affiliate License Service (SALSA).
Note: The IHTSDO does not make a charge for issuing Affiliate Licences but charges to apply for
specific types of use in Non-Member Territories. The IHTSDO does not make any charges for any
licenced uses of SNOMED CT in IHTSDO Member countries. However, IHTSDO Members can place
constraints on use in their country (for example, requiring the use of a National Extension).
3.1.2.3 Deciding Which Files You Need
When deciding which files you need to download and use there are several factors to consider.
•
Edition(s):
•
All installations require the International edition which is signified by the letters "INT" in the file
names.
В© 2002-2013 International Health Terminology Standards Development Organisation CVR #: 30363434
Technical How To Guides | 19
•
•
•
Depending on the requirements for use in a particular country, organisation or software application,
one or more Extension Editions may also be required. Extensions may be produced and distributed
by IHTSDO Member country and Affiliates.
Extensions file names contain a code representing the country or origin and may also contain a
seven digit namespace identifying the origin of the Extension.
Version:
•
•
•
The version is signified by a date-stamp in the form YYYYMMDD (e.g. 20120731).
This date-stamp is included in the name of the release file archive and individual files within the
archive.
Always download the latest release unless you have a specific reason to require an earlier release.
Note: The Affiliate Licence Agreement requires the version of SNOMED CT installed in software
within 180 days after the release or an update to the International edition.
•
Format:
•
•
The primary format used for SNOMED CT International Edition release files is referred to as RF2
(Release Format 2). This format is highly recommended as it includes important additional data
that is not supported by the earlier (RF1) format.
An earlier Release Format (RF1) continues to be supported for a transitional period to allow migration
of software and Extension Edition releases to the new format.
Notes:
1. RF1 files can be generated from the RF2 release files using a software utility and compatibility
pack which is available to IHTSDO affiliates from the same sources as the Release Files. This
conversion process retains the clinical content of SNOMED CT. However, key features of RF2
are not supported by RF1 and as a result, functionality related to versioning, mapping, subsets
and extensibility are lost in the conversion process.The significance of these losses will increase
as more use is made of the enhancements made possible by RF2.
2. During the transitional period some SNOMED CT Extensions may only be available in RF1
format. However, migration to RF2 is strongly encouraged as features specific to effective use
of Extension content are not fully supported in RF1.
•
Release Type:
•
•
•
A "Full" release contains every version of every component ever released in the specified Edition.
They provide full historical record and can be used to obtain views of the state of any component
at any point in time since its first release.
A "Snapshot" release contains one version of every component released up to the time of the
snapshot. The version of each component contained in a snapshot is the most recent version of
that component at the time of the snapshot.
A "Delta" release only contains component versions created, inactivated or changed since the
previous release. Adding a "Delta" release to the previous "Full" release of the previous Version
of the same Edition updates it to a "Full" release of the next Version.
Note: RF2 supports all three Release Types, but RF1 only supports a Snapshot release type.
3.1.3 Accessing Release Files
3.1.3.1 Obtaining International Release Files
When you have completed the Affiliate Licence process you will be provided with access to
SNOMED CT International Edition release files. In most cases, access is provided to an online service
from which the release files can be downloaded using a standard download protocol (e.g. FTP).
Note: Before you download these files you should consider which files you need to download.
В© 2002-2013 International Health Terminology Standards Development Organisation CVR #: 30363434
20 | SNOMED CT SNOMED CT How To Guides July 2013
3.1.3.1.1 Making Sense of an International Release File Archive
The Release Files are usually supplied in zip archive files. Each file will contain a set of files
related to one or more release file. These files are organised into folders. The images below illustrate the
way the files were arranged in the archive that represented the entire International Release in July 2012.
This archive contained both RF1 and RF2 files and in the case of RF2 also contained all three released
types "Full", "Snapshot" and "Delta".
Note: In future, separate archives may be available for some Release Formats and Release Types
(e.g. a much smaller archive just containing the "Delta" release in RF2 format would be sufficient to
update a current installed "Full" release).
Figure 1: Folders in the complete International Release archive
В© 2002-2013 International Health Terminology Standards Development Organisation CVR #: 30363434
Technical How To Guides | 21
Figure 2: Folders and files from the three RF2 Release Types
Figure 3: Folders and files from the RF1 Release
3.1.3.2 Obtaining Extension Release Files
To gain access to National Extensions provided by IHTSDO Members in particular countries
you with need to register with the National Release Center in that country. You may also be required to
accept specific conditions of use for each country.
Some IHTSDO affiliates may also provide access to Extensions that they have developed. There is no
general mechanism to support sharing of these Extensions, so arrangements for obtaining these files
depends on individual arrangements with the Extension provider.
Note: The file "doc_NamespaceIdentifierRegistry_Current-US_INT_[YYYYMMDD].pdf" (in the
"Documentation" sub-folder of the main International Release archive) contains a list of Members
and Affiliates who hold a namespace registration, which enables them to develop a SNOMED CT
Extension.
В© 2002-2013 International Health Terminology Standards Development Organisation CVR #: 30363434
22 | SNOMED CT SNOMED CT How To Guides July 2013
3.1.3.3 Using Release Files
Release files are tab delimited text files encoded using the Unicode character set encoded
in accordance with the UTF-8 standard. These files can be imported into a variety of structures including
relational database tables and other data structures designed to optimise access to the information the
files contain.
Various sections of the Technical Implementation Guide referenced below provide detailed specification
of the file formats. The guide on How to Use SNOMED CT Release Files provides a step by step walk
through of a simple initial approach to making use of these files.
3.2 How to Use SNOMED CT Release Files:
3.2.1 Overview
This guide describes the steps you need to go through to be able to use the content of the SNOMED CT
release files for a number of different applications including those concerned with: Clinical Records,
Knowledge Representation, Aggregation and Analysis, and Terminology Tools.
3.2.2 Before You Start
3.2.2.1 Is This For You?
This guide is intended for people who wish to use the content of SNOMED CT release files
in a database or software application.
This guide is not intended for non-technical users who are interested in browsing, exploring or evaluating
the clinical content of SNOMED CT.
It assumes that the user has already obtained the SNOMED CT Release Files
.
3.2.3 Understanding how the data in the release files supports the
many uses of SNOMED CT.
3.2.3.1 Overview:
The release files solve the problem of distributing the basic data needed to implement the
SNOMED Clinical erminology in various modes in a technology-neutral format.
There are two major, but inter-related, modes of representing the SNOMED CT terminology:
•
With a database which represents the poly-hierarchical axes of clinical concepts and the relationships
between the concepts in a way that many applications can be supported.
•
In an expression language form that can be processed by description logic programming languages.
Note that in many computing environments there will be interactions between the database representations
and the expression language representations of terminological information. This is especially true when
dealing with pre and postcoordinated forms of SNOMED CT concepts.
The release data files and associated release documentation must support both of these representation
modes. Also, it must be possible to move data between these two representation modes in a manner that
neither loses nor adds information.
A fundamental part of the solution is the use of a common information model which supports the following
requirements:
В© 2002-2013 International Health Terminology Standards Development Organisation CVR #: 30363434
Technical How To Guides | 23
•
Each SNOMED CT concept is represented by text Descriptions (terms) in three specific forms: as a
Preferred Term (unique within a supported language or dialect), as a Fully Specified Name (unique
for the given concept), and as a Synonym (not necessarily unique and may refer to multiple concepts).
•
The relationships (and various types of relationships) between the concepts in the same and different
axes need to be supported.
The following link opens a section showing the formal logical model for concepts, Descriptions and
Relationships.
There are several other important aspects of SNOMED CT that are supported by the basic logical model:
•
•
changes to the terminology:
•
New clinical concepts may be added as knowledge increases: and as each new concept is added,
the various term types (Preferred, Fully Specified Name, and Synonyms) and the various
relationships among the concepts are also added. Existing concepts (and terms and Relationships)
may also be retired.
•
The organisation responsible for changes to concepts, terms, Descriptions and Relationships and
also for creating new and varied collections of concepts, terms, and Descriptions called Reference
Sets (5) that extend the functionality of SNOMED CT is also documented. To support the change
management process, the time and type of such changes are also documented.
The formal metadata that specifies and extends the logical model
The metadata is also represented in terms of concepts, terms (Preferred, Fully Specified Name, and
Synonyms), and Relationships, using the same logical model that is used for the clinical data, but with
a different set of non-clinical, metadata concept hierarchies.
•
•
•
For example, the concepts and tems defining the relationship types refining and qualifying are
formally represented in the core metadata descending from the 900000000000442005 | core
metadata concept | of 900000000000449001 | characteristic type | that categorises the refining
and qualifying relationship types and subtypes.
• New subtypes of refining and qualifying Relationships can be added as needed.
The metadata functionality creates the flexibility to extend the basic logical model in an explicit way
to support new functionalities that are specific to a particular type of application or user group.
•
•
•
•
In particular, new named collections of concepts, terms, and Relationships developed by an
organisation (Refsets) are defined by metadata.
Refsets are also used to support language-specific editions of SNOMED CT.
Reference set file definitions can also be extended to support additional functionality such as
maps between SNOMED CT and other terminology systems.
The release files thus contain
•
•
•
•
the data for the clinical terminology of SNOMED CT ( in the Terminology file folder, whose file
names start with "sct")
the core metadata that is used to specify the logical model for the clinical data (also in the
Terminology file folder)
the foundation metadata that is used to specify the logical model for the extensions (also in the
Terminology file folders)
the data content of the reference sets (in the Refset file folder, whose file names start with "der"
indicating that they are derived from the "sct" files, and also that they may have additional content
added that is not present in the "sct" files) )
В© 2002-2013 International Health Terminology Standards Development Organisation CVR #: 30363434
24 | SNOMED CT SNOMED CT How To Guides July 2013
3.2.4 Understanding the content of the SNOMED CT Release Files
3.2.4.1 How to understand the structure of a release file .
Note: The examples in this section are taken from the July 31, 2012 SNOMED CT
International Release.
•
The names of the release files have a formal six part structure specified in the File Naming Conventions.
For example: sct2_Concept_Snapshot_INT_20120731.txt .
•
The file type is "sct2." The set of all applicable file types defines this as a "Terminology data file.
The "2" denotes that the file is further defined by the Release Format 2 ("RF2") specification.
•
The content type is "Concept." The concept types specification denotes this as a file of "SNOMED
CT"concepts.
•
The content subtype is "Snapshot".The concept subtypes specification defines a Snapshot release
as a release "containing only the most recent version of every component ever released (both
active and inactive components)."
The Country/Namespace is "INT." The country/namespace specification defines this as an
"International IHTSDO Release" and also that it belongs to the international Namespace.
The version date is "20120731". This specifies the official publication date of the release.
The extension element is "txt" which specifies the file format.
•
•
•
•
Using the above information one is able to read the first record in this file, which is:
id
effectiveTime
active
moduleId
definitionStatusId
These are the names of the fields in the subsequent records in the file. They are defined as follows:
Field
Data type
Immutable
Purpose
id
SCTID
Y
Uniquely identifies the
concept.
effectiveTime
Time
N
Specifies the inclusive
date at which the
component version's
state became the then
current valid state of the
component
active
Boolean
N
Specifies whether the
concept 's state was
active or inactive from
the nominal release date
specified by the
effectiveTime
moduleId
SCTID
N
Identifies the concept
version's module. Set to
a descendant of
|Module| within the
metadata hierarchy.
definitionStatusId
SCTID
N
Specifies if the concept
version is primitive or
fully defined. Set to a
child of | Definition
status | in the metadata
hierarchy.
В© 2002-2013 International Health Terminology Standards Development Organisation CVR #: 30363434
Technical How To Guides | 25
Note that the ID column contains SNOMED CT identifiers, (SCTID's), which, like the release file names
have a formal multi-part structure including the SCTID itself, a check digit, a partition identifier, and in
the case of extensions, two additional parts: a namespace identifier, and a partition identifier.
Each of the subsequent rows following rows each contain the data for the concept identified in the
first column:
•
id
effectiveTime
active
moduleId
definitionStatusId
100000000
20090731
0
900000000000207000 900000000000074000
10000006
20020131
1
900000000000207000 900000000000074000
1000004
20030131
0
900000000000207000 900000000000074000
...
...
...
...
...
The names and contents of the other release files can be discovered in the same way as that of the
above example using the formal specifications of the terminology release files and the reference set
release files.
Note: further details of the SNOMED CT Concept Model which are important to an undestanding of the
ways in which the release files can be used are discussed in the Concept Model Guide.
3.2.5 Using the content of the SNOMED CT Release Files
3.2.5.1 A database example: importing data from the release files to support an
application that will implement the uses of the SNOMED CT terminology.
Note: SNOMED CT releases can be used without creating a new application by obtaining a copy of the
IHTSDO Workbench and/or by using tools created by IHTSDO or one of the National Release Centers
and/or by one of several vendors licenced by IHTSDO. However, an import facility should also be available
in a suitably secured form to end-user organisations, to enable installation and maintenance of Extensions.
The three main types of release file datasets support the following views of the terminology:
Release Type
Description
Full
The files representing each type of component
contain every version of every component ever
released.
Snapshot
The files representing each type of component
contain one version of every component released
up to the time of the snapshot. The version of each
component contained in a snapshot is the most
recent version of that component at the time of the
snapshot.
Delta
The files representing each type of component
contain only component versions created since the
previous release. Each component version in a delta
release represents either a new component or a
change to an existing component.
For a terminology server application, these three support the corresponding user "views" of SNOMED
CT.
View
Description
Snapshot view
A snapshot view terminology service provides
access to the content of the current state of all the
components of the International Release and any
chosen Extension Releases.
В© 2002-2013 International Health Terminology Standards Development Organisation CVR #: 30363434
26 | SNOMED CT SNOMED CT How To Guides July 2013
Multi- snapshot view
A "multi- snapshot view "terminology service
provides access to:
* the content of the current state and content of all
components of the International release and any
chosen Extension Releases
* the content of one or more additional snapshot
views, each of which represents the state of all
components at a different fixed point in time
A "multi- snapshot view "terminology server may
provide access to delta views that report the
differences between two snapshot views. This is
limited to comparisons of specific points represented
by the available snapshot views.
Full view
A Full view terminology service provides access to:
* the complete content of the full International
release and any chosen Extension Releases
* the state and content of all components as they
were at any specified point in time
A full view terminology server should also provide
access to views that show the changes to
components between any two specified points in
time.
The formal structure of the release Teminology and Refset files mentioned above enables code to be
wrtten to both identify and read the files. This combined with formal structures of the logical model,
including those specified by the metadata, enables the creation of database structures to support the
SNOMED CT terminology. A basic example of this is provided in the Terminology Services Guide, using
the Open Source database MySql Community Edition to create a Full View Relational Representation of
SNOMED CT.
Example: after mapping the release file datatypes to MySql datatypes, the following MySql code defines
the Full View Concept table and imports the Concept file (see above) into a Full View Relational Database.
•
Create Full View Concept Table
•
CREATE TABLE `sct2_concept`
(`id` BIGINT NOT NULL DEFAULT 0,
`effectiveTime` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
`active` TINYINT NOT NULL DEFAULT 0,
`moduleId` BIGINT NOT NULL DEFAULT 0,
`definitionStatusId` BIGINT NOT NULL DEFAULT 0,
PRIMARY KEY (`id`,`effectiveTime`) )
ENGINE=MyISAM DEFAULT CHARSET=utf8;
•
Import release Concept file:
LOAD DATA LOCAL INFILE '[path]sct2_concept_[AdditionalInfo].txt'
INTO TABLE `sct2_concept`
В© 2002-2013 International Health Terminology Standards Development Organisation CVR #: 30363434
Technical How To Guides | 27
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;
The import of the other release files into a Full View relational database follows a similar pattern. The
release documentation provides examples for not only the Full View, but also the Snapshot and
Multi-Snapshot representations.
There is also documentation on the mandatory, recommended and optional terminology and refset release
file import options.
3.2.6 Summary and next steps
3.2.6.1 The basics of accessing the content of the release files, and importing that
content into a relational database have been discussed.
Note that there are other release file distribution forms, including the IHTSDO workbench internal format
and several proprietary application-based internal formats.
Once the release file data has been imported it can be used in several main types of applications including
those concerned with: Clinical Records, Knowledge Representation, Aggregation and Analysis, and
Terminology Tools. These will be discussed in the guide How to Look at SNOMED CT from a Technical
Perspective.
3.3 How to Look at SNOMED CT from a Technical Perspective
3.3.1 Overview
This guide describes the steps you need to go through to {task objective summary}.
3.3.2 Before You Start
3.3.2.1 Is This For You?
This guide is intended for people who {primary audience}.
This guide is not intended for {audiences not supported}.
3.3.2.2 {other preliminary tasks if any}
3.3.3 {main task group}
3.3.3.1 {main task step 1}
3.3.3.2 {main task step 2}
3.4 How to Evaluate SNOMED CT for a Technical Use Case
3.4.1 Overview
This guide describes the steps you need to go through to {task objective summary}.
В© 2002-2013 International Health Terminology Standards Development Organisation CVR #: 30363434
28 | SNOMED CT SNOMED CT How To Guides July 2013
3.4.2 Before You Start
3.4.2.1 Is This For You?
This guide is intended for people who {primary audience}.
This guide is not intended for {audiences not supported}.
3.4.2.2 {other preliminary tasks if any}
3.4.3 {main task group}
3.4.3.1 {main task step 1}
3.4.3.2 {main task step 2}
3.5 How to Compare Changes Between Releases of SNOMED CT
3.5.1 Overview
This guide describes the steps you need to go through to {task objective summary}.
3.5.2 Before You Start
3.5.2.1 Is This For You?
This guide is intended for people who {primary audience}.
This guide is not intended for {audiences not supported}.
3.5.2.2 {other preliminary tasks if any}
3.5.3 {main task group}
3.5.3.1 {main task step 1}
3.5.3.2 {main task step 2}
3.6 How to Analyse Data Collected With Different Versions of
SNOMED CT
3.6.1 Overview
This guide describes the steps you need to go through to {task objective summary}.
3.6.2 Before You Start
3.6.2.1 Is This For You?
This guide is intended for people who {primary audience}.
This guide is not intended for {audiences not supported}.
В© 2002-2013 International Health Terminology Standards Development Organisation CVR #: 30363434
Technical How To Guides | 29
3.6.2.2 {other preliminary tasks if any}
3.6.3 {main task group}
3.6.3.1 {main task step 1}
3.6.3.2 {main task step 2}
В© 2002-2013 International Health Terminology Standards Development Organisation CVR #: 30363434
30 | SNOMED CT SNOMED CT How To Guides July 2013
Chapter
4
4 Extension Maintenance How To Guides
4.1 How to Create and Manage an Extension
4.1.1 Overview
This guide describes the steps you need to go through to create and manage a SNOMED CT Extension.
4.1.2 Before You Start
4.1.2.1 Is This For You?
This guide is intended for people who need to extend the primary content of SNOMED CT
and need to implement the extension using the raw RF2 tables.
This guide is not intended for people who will not be creating new SNOMED CT content or reference
sets. It is also not intended for those who will be using existing tools or services such as the IHTSDO
workbench.
4.1.2.2 Obtaining an extension namespace identifier
This document assumes that the reader has already applied for and has received a SNOMED
CT Extension identifier. This identifier will be in the form of a seven digit number (e.g. 1000160). A list of
all official namespace identifiers can be found at the bottom of http://www.ihtsdo.org/develop/namespaces/
4.1.3 How to Create an SCTID Generator from an Extension Identifier
4.1.3.1 Introduction
SNOMED CT identifiers are composed of four components:
1.
2.
3.
4.
The item identifier - 1 to 8 digits
The namespace identifier - 7 digits
The partition identifier- 2 digits where "10" means Concept, "11"Description and "12"Relationship
The check digit - 1 digit
In order to manage your own namespace, you need to be able to:
1. Generate and manage unique item identifiers for each partition
2. Generate check Digits
Each of these tasks is described below.
4.1.3.1.1 How to Manage Unique Item Identifiers
The key to managing SNOMED CT identifiers is the ability to manage and generate unique
identifiers. The key to managing these identifiers is to guarantee uniqueness - that the same identifier
cannot be issued twice. There are many approaches to managing this and the approach outlined below
is only one possibility.
В© 2002-2013 International Health Terminology Standards Development Organisation CVR #: 30363434
Extension Maintenance How To Guides | 31
One approach to managing unique identifiers is to create three tables, one for each of the Concept,
Description and Relationship partitions. Each table uses the MySQL AUTO_INCREMENT function that
guarantees that a new identifier will ge generated for each row in the table:
CREATE TABLE `next_concept` (
`id` int(8) AUTO_INCREMENT,
`uuid` char(36),
PRIMARY KEY (`id`),
UNIQUE `uuid` (uuid)
);
CREATE TABLE `next_description` (
`id` int(8) AUTO_INCREMENT,
`uuid` char(36),
PRIMARY KEY (`id`),
UNIQUE `uuid` (uuid)
);
CREATE TABLE `next_relationship` (
`id` int(8) AUTO_INCREMENT,
`uuid` char(36),
PRIMARY KEY (`id`),
UNIQUE `uuid` (uuid)
);
4.1.3.1.2 How to Generate and Validate Checksums
SNOMED CT uses Verhoeff's Dihedral Group D5 Check Digit Algorithm. The following
MySQL function definition allows the creation and validation of SNOMED CT check digits:
DELIMITER //
CREATE FUNCTION checkdigit(number bigint, action tinyint)
/* Translated directly from http://www.stens.ca/kb/VerhoeffCheck */
/* number is the number to check or to create a checkdigit for. */
/* action: 0 = Check on validity (0 return is valid, anything else is not), 1=Create checknum */
RETURNS bigint
DETERMINISTIC
NO SQL
BEGIN
DECLARE c tinyint;
DECLARE len int;
DECLARE m tinyint;
DECLARE n varchar(255);
DECLARE i smallint;
/* Declare the Arrays */
DECLARE d char(100);
DECLARE p char(80);
DECLARE inv char(10);
SET
d='0123456789123406789523401789563401289567401239567859876043216598710432765982104387659321049876543210';
SET
p='01234567891576283094580379614289160435279453126870428657390127938064157046913258';
SET inv='0432156789';
/* Start Processing */
SET c = 0;
SET n = Reverse(number);
SET len = Length(n);
SET i=0;
WHILE i < len
DO
IF action = 1
THEN
В© 2002-2013 International Health Terminology Standards Development Organisation CVR #: 30363434
32 | SNOMED CT SNOMED CT How To Guides July 2013
SET m = CAST(substring(p,(((i+1)%8)*10)+ CAST(substring(n,i+1,1) AS SIGNED)+1,1) AS
SIGNED);
ELSE
SET m = CAST(substring(p,((i%8)*10)+ CAST(substring(n,i+1,1) AS SIGNED)+1,1) AS SIGNED);
END IF;
SET c = CAST(substring(d,(c*10+m+1),1) AS SIGNED);
SET i=i+1;
END WHILE;
IF action = 1
THEN
SET c = CAST(substring(inv,c+1,1) AS SIGNED);
END IF;
RETURN c;
END
//
4.1.3.1.3 How to Create New SCTIDs In an Extension Namespace
The following functions can be used to generate new Concept SCTID's, Description SCTID's
and Relationship SCTID's respectively:
DELIMITER //
CREATE FUNCTION newSCTID(partition int)
RETURNS bigint(20)
MODIFIES SQL DATA
READS SQL DATA
BEGIN
DECLARE uid char(36);
DECLARE sctid bigint;
DECLARE namespace int;
SET namespace = 1000160;
SET uid = UUID();
IF partition = 10
THEN
INSERT INTO next_concept(`uuid`) VALUES (uid);
SELECT (`id` * 1000000000) + (namespace * 100) + partition INTO sctid FROM next_concept WHERE
`uuid` = uid;
ELSEIF partition = 11
THEN
INSERT INTO next_description(`uuid`) VALUES (uid);
SELECT (`id` * 1000000000) + (namespace * 100) + partition INTO sctid FROM next_description
WHERE `uuid` = uid;
ELSE
INSERT INTO next_relationship(`uuid`) VALUES (uid);
SELECT (`id` * 1000000000) + (namespace * 100) + partition INTO sctid FROM next_relationship
WHERE `uuid` = uid;
END IF;
RETURN (sctid * 10) + checkdigit(sctid,1);
END
//
DELIMITER //
CREATE FUNCTION newConceptSCTID()
RETURNS bigint(20)
BEGIN
return newSCTID(10)
END
//
DELIMITER //
CREATE FUNCTION newDescriptionSCTID()
RETURNS bigint(20)
BEGIN
return newSCTID(11)
END
//
В© 2002-2013 International Health Terminology Standards Development Organisation CVR #: 30363434
Extension Maintenance How To Guides | 33
DELIMITER //
CREATE FUNCTION newRelationshipSCTID()
RETURNS bigint(20)
BEGIN
return newSCTID(12)
END
//
4.1.3.2 {main task step 2}
4.2 How to Create a Simple Reference Set
4.2.1 Overview
This guide describes the steps you need to go through to {task objective summary}.
4.2.2 Before You Start
4.2.2.1 Is This For You?
This guide is intended for people who {primary audience}.
This guide is not intended for {audiences not supported}.
4.2.2.2 {other preliminary tasks if any}
4.2.3 {main task group}
4.2.3.1 {main task step 1a}
4.2.3.2 {main task step 2}
4.3 How to Represent Subsets of Concepts Using a Refset
4.3.1 Overview
This guide describes the steps you need to go through to {task objective summary}.
4.3.2 Before You Start
4.3.2.1 Is This For You?
This guide is intended for people who {primary audience}.
This guide is not intended for {audiences not supported}.
В© 2002-2013 International Health Terminology Standards Development Organisation CVR #: 30363434
34 | SNOMED CT SNOMED CT How To Guides July 2013
4.3.2.2 {other preliminary tasks if any}
4.3.3 {main task group}
4.3.3.1 {main task step 1}
4.3.3.2 {main task step 2}
4.4 How to Represent a Mapping Using a Refset (simple and
complex maps)
4.4.1 Overview
This guide describes the steps you need to go through to {task objective summary}.
4.4.2 Before You Start
4.4.2.1 Is This For You?
This guide is intended for people who {primary audience}.
This guide is not intended for {audiences not supported}.
4.4.2.2 {other preliminary tasks if any}
4.4.3 {main task group}
4.4.3.1 {main task step 1}
4.4.3.2 {main task step 2}
4.5 How to Represent Term Preferences Using a Refset (e.g. for
languages and dialects)
4.5.1 Overview
This guide describes the steps you need to go through to {task objective summary}.
4.5.2 Before You Start
4.5.2.1 Is This For You?
This guide is intended for people who {primary audience}.
This guide is not intended for {audiences not supported}.
В© 2002-2013 International Health Terminology Standards Development Organisation CVR #: 30363434
Extension Maintenance How To Guides | 35
4.5.2.2 {other preliminary tasks if any}
4.5.3 {main task group}
4.5.3.1 {main task step 1}
4.5.3.2 {main task step 2}
4.6 How to Update a Refset to Take Account of Changes Between
Releases
4.6.1 Overview
This guide describes the steps you need to go through to {task objective summary}.
4.6.2 Before You Start
4.6.2.1 Is This For You?
This guide is intended for people who {primary audience}.
This guide is not intended for {audiences not supported}.
4.6.2.2 {other preliminary tasks if any}
4.6.3 {main task group}
4.6.3.1 {main task step 1}
4.6.3.2 {main task step 2}
В© 2002-2013 International Health Terminology Standards Development Organisation CVR #: 30363434
36 | SNOMED CT SNOMED CT How To Guides July 2013
Chapter
5
5 Reference Set Implementation How To Guides
5.1 How to Use a Simple Refset of Concepts
5.1.1 Overview
This guide describes how to load and use the contents of the Simple Reference Set tables that are
distributed as part of the SNOMED CT RF2 release.
5.1.2 Typographical and Syntax Conventions
The examples in this document use a style similar to that described in the MySQL manual conventions.
•
•
•
BOLD CAPITAL TEXT represents SQL Keywords
bold lower case represent table names, keys, etc
italic values indicates that, were you to enter a similar statement, you would supply your own values
5.1.3 Typographical and Syntax Conventions
The examples in this document use a style similar to that described in the MySQL manual conventions.
•
•
•
BOLD CAPITAL TEXT represents SQL Keywords
bold lower case represent table names, keys, etc
italic values indicates that, were you to enter a similar statement, you would supply your own values
5.1.4 Simple Reference Table Layout
A Simple Reference Set is a collection of SCTIDs. The Simple Reference Set table contains multiple
collections, each which is uniquely identified by a refsetId.
id
effective Time
000ce1957
-985
-f5179
-8aa1
-6a2c35874a3 20050131
active
moduleId
refsetId
1
900000000000207008 447566000
referenced
ComponentId
415167006
This entry asserts that, as of the January, 2005 release, the 900000000000207008 | SNOMED CT core
| module asserted that 415167006 | Pregabalin 75mg capsule | was a member of the 447566000 | Virtual
medicinal product simple reference set |
More details about the layout of a simple simple reference set can be found in the references at the end
of the section. The key to understanding a simple reference sets is that the refsetId identifies the reference
set and the referenced componentId identifies a set member. Note also, that the component is only
considered to be a member of the reference set if active is "1" at the referenced time.
В© 2002-2013 International Health Terminology Standards Development Organisation CVR #: 30363434
Reference Set Implementation How To Guides | 37
5.1.5 How to load the Simple Refset Table into an SQL Database
5.1.5.1 Download the appropriate set of RF2 SNOMED CT Release Files
Instructions for obtaining RF2 Release files and determining which release type to be used
are listed in the links at the end of the section. The sections below have different instructions for loading
"Full" and "Snapshot" type of release. The "Full" instructions should be used if the user has downloaded
the "Full" release, has applied one or more "Delta" releases or if the user anticipates using "Delta" release
types to apply subsequent updates. The "Snapshot" instructions are (a) simpler and (b) are designed to
prevent duplicate and non-current record errors
5.1.5.2 Creating the SQL Tables
5.1.5.2.1 SNAPSHOT Release Type
The following SQL script will create a simple reference set snapshot table in
MySQL. The key to a snapshot table is the id, meaning that only one entry can be present for each
reference set assertion.
CREATE TABLE `simplerefset_ss` (
`id` varchar(36) NOT NULL,
`effectiveTime` int NOT NULL,
`active` tinyint(1) NOT NULL,
`moduleId` bigint(20) NOT NULL,
`refsetId` bigint(20) NOT NULL,
`referencedComponentId` bigint(20) NOT NULL,
PRIMARY KEY (`id`),
KEY `refset` (`refsetId`),
KEY `component` (`referencedComponentId`)
);
5.1.5.2.2 FULL Release Type
Use the following script to create a imple reference set full table in MySQL. The key to the
full table is a combination of the id and effectiveTime, which allows multiple entries to exist for the same
assertion at different points in time. Full Release Type tables can be updated locally and may have
incremental updates (Delta's) applied:
CREATE TABLE `simplerefset_full` (
`id` varchar(36) NOT NULL,
`effectiveTime` int NOT NULL,
`active` tinyint(1) NOT NULL,
`moduleId` bigint(20) NOT NULL,
`refsetId` bigint(20) NOT NULL,
`referencedComponentId` bigint(20) NOT NULL,
PRIMARY KEY (`id`,`effectiveTime`),
KEY `refset` (`refsetId`),
KEY `component` (`referencedComponentId`)
);
5.1.5.3 Loading the Simple Refset Data
5.1.5.3.1 SNAPSHOT Release Type
Simple reference set snapshots can be loaded using the following syntax:
LOAD DATA local INFILE 'der2_Refset_SimpleSnapshot_edition_releaseDate.txt'
INTO TABLE simplerefset_ss
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;
As an example, the query below will load the July 31, 2012 SNOMED International Release simple
reference set snapshot table:
В© 2002-2013 International Health Terminology Standards Development Organisation CVR #: 30363434
38 | SNOMED CT SNOMED CT How To Guides July 2013
LOAD DATA local INFILE 'der2_Refset_SimpleSnapshot_INT_20120731.txt'
INTO TABLE simplerefset_ss
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;
5.1.5.3.2 FULL Release Type
Full simple reference sets can be loaded using the following syntax:
LOAD DATA local INFILE 'der2_Refset_SimpleFull_edition_releaseDate.txt'
INTO TABLE simplerefset_full
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;
As an example, the query below will load the full July 31, 2012 SNOMED International Release simple
reference set table:
LOAD DATA local INFILE 'der2_Refset_SimpleFull_INT_20120731.txt'
INTO TABLE simplerefset_full
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;
5.1.6 How to List the Available Reference Sets
5.1.6.1 Listing Reference Sets using SNOMED CT Terminology Files
A complete list of simple reference sets can be found as the leaf nodes of the 446609009 |
Simple type reference set | module. The queries below return the subset of those reference sets that
have at least one active entrry in the simple reference file itself.
The following query returns a list of all reference sets that have at least one active member from a snapshot
simple reference set image:
SELECT distinct refsetId
FROM simplerefset_ss where active = 1
The query below returns a list of all reference sets that currently have at least one active member from
a full simple reference set image.
SELECT distinct refsetId
FROM simplerefset_full srs,
(SELECT id, MAX(effectiveTime) AS effectiveTime FROM simplerefset_full GROUP BY id) as
srs_keys
WHERE srs.id = srs_keys.id AND srs.effectiveTime = srs_keys.effectiveTime AND active = 1;
The query above
1. Selects the most recent entry for each unique assertion in the simple reference set file using the inner
query
2. Selects the subset of these entries that are active (active = 1)
3. Returns the reference set identifiers associated with those entries
5.1.6.2 Listing Reference Sets using Command Line Utilities
The following command returns the (a) drops the header line, (b) extracts the fifth column
(refset Id) and (c) returns the unique members from a snapshot simple reference set release:
tail +2 der2_Refset_SimpleSnapshot_edition_releaseDate.txt | cut -f 3,5 | grep ^1 | cut -f 2 | sort
-u
As an example, the following command returns all the reference sets having at least one active member
from the July 2012 International Release:
В© 2002-2013 International Health Terminology Standards Development Organisation CVR #: 30363434
Reference Set Implementation How To Guides | 39
tail +2 der2_Refset_SimpleSnapshot_INT_20120731.txt | cut -f 3,5 | grep ^1 | cut -f 2 | sort -u
5.1.7 How to Retrieve the Members of a Simple Reference Set
5.1.7.1 SNAPSHOT Release Type
The following SQL query will retrieve all active members of the simple reference set from a
snapshot release.
SELECT referencedComponentId
FROM simplerefset_ss WHERE active = 1
AND refsetId = refsetSCTID;
As an example, the query below:
SELECT referencedComponentId
FROM simplerefset_ss WHERE active = 1
AND refsetId = 447565001;
would retrieve all of the active components of the 447565001 | Virtual therapeutic moiety simple reference
set |
5.1.7.2 FULL Release Type
The following SQL query will retrieve active members of a full release of a simple reference
set:
SELECT referencedComponentId
FROM simplerefset_full srs,
(SELECT id, MAX(effectiveTime) AS effectiveTime FROM simplerefset_full WHERE refsetId =
refsetSCTID GROUP BY id) as srs_keys
WHERE srs.id = srs_keys.id AND srs.effectiveTime = srs_keys.effectiveTime AND active = 1;
As an example, the query:
SELECT referencedComponentId
FROM simplerefset_full srs,
(SELECT id, MAX(effectiveTime) AS effectiveTime FROM simplerefset_full WHERE refsetId =
447565001 GROUP BY id) as srs_keys
WHERE srs.id = srs_keys.id AND srs.effectiveTime = srs_keys.effectiveTime AND active = 1;
5.1.7.3 Command Line Utilities
The following command (a) drops the header line, (b) extracts the third, fifth and sixth columns
(active, refsetId and referencedComponentId) (c) filters for active members of the reference set, (d)
extracts the referenced component:
tail +2 der2_Refset_SimpleSnapshot_edition_releaseDate.txt | cut -f 3,5,6 | grep ^1\\\trefsetSCTID
| cut -f 3
As an exmple, the query below:
tail +2 der2_Refset_SimpleSnapshot_INT_20120731.txt | cut -f 3,5,6 | grep ^1\\\t447565001 | cut -f
3
would retrieve all of the active components of the 447565001 | Virtual therapeutic moiety simple reference
set |
В© 2002-2013 International Health Terminology Standards Development Organisation CVR #: 30363434
40 | SNOMED CT SNOMED CT How To Guides July 2013
5.1.8 How to determine whether a concept is a member of a simple reference
set
5.1.8.1 SNAPSHOT Release Type
The following query returns 1 if componentSCTID is a member of the simple reference set
identified by refsetSCTID and 0 otherwise:
SELECT count(*) FROM simplerefset_ss
WHERE refsetid = refsetSCTID AND active = 1
AND referencedComponentId = componentSCTID;
As an example, the query below:
SELECT count(*) FROM simplerefset_ss
WHERE refsetid = 447565001 AND active = 1
AND referencedComponentId = 12559001;
returns "1" because 12559001 | Emetine | is in the 447565001 | Virtual therapeutic moiety simple reference
set | in the July 2012 International Release and:
SELECT count(*) FROM simplerefset_ss
WHERE refsetid = 447565001 AND active = 1
AND referencedComponentId = 74400008;
returns "0" because 74400008 | Appendicitis | is not in the reference set and
SELECT count(*) FROM simplerefset_ss
WHERE refsetid = 447565001 AND active = 1
AND referencedComponentId = 321172009;
returns "0" because 321172009 | Triclofos sodium | is not active.
5.1.8.2 FULL Release Type
The following query returns 1 if componentSCTID is a member of the full release of the
simple reference set identified by refsetSCTID and 0 otherwise:
SELECT count(*)
FROM simplerefset_full srs,
(SELECT id, MAX(effectiveTime) AS effectiveTime FROM simplerefset_full WHERE refsetId =
refsetSCTID GROUP BY id) as srs_keys
WHERE srs.id = srs_keys.id AND srs.effectiveTime = srs_keys.effectiveTime AND active = 1
AND referencedComponentId = componentSCTID;
would retrieve all of the active components of the 447565001 | Virtual therapeutic moiety simple reference
set |
5.1.8.3 Command Line
The following command (a) removes the header line, (b) extracts the active, refsetId and
referencedComponentId columns, (c) filters for active entries in refset sctid, (d) extracts the referenced
component sctid column (e) filters for a referenced component sctid and (e) counts the results, which will
yield either a 0 or a 1:
tail +2 der2_Refset_SimpleSnapshot_edition_releaseDate.txt | cut -f 3,5,6 | grep ^1\\\trefsetSCTID
| cut -f 3 | grep componentSCTID | wc -l
as an example:
tail +2 der2_Refset_SimpleSnapshot_INT_20120731.txt | cut -f 3,5,6 | grep ^1\\\t447565001 | cut -f
3 | grep 12559001 | wc -l
В© 2002-2013 International Health Terminology Standards Development Organisation CVR #: 30363434
Reference Set Implementation How To Guides | 41
returns "1" in the July 2012 International release and
tail +2 der2_Refset_SimpleSnapshot_INT_20120731.txt | cut -f 3,5,6 | grep ^1\\\t447565001 | cut -f
3 | grep 321172009 | wc -l
Returns "0"
5.1.9 How to do set operations with simple reference sets
It is frequently useful to be able to combine simple reference sets, find the members that are in common
between two or more simple reference sets, determine which members are in one set and not another,
etc. The following sections address how these various operations can be performed
5.1.9.1 Simple Reference Set Union
5.1.9.1.1 SNAPSHOT Release Type
The following query will return the concept identifiers that are members of one
or more of the listed simple reference sets:
SELECT distinct referencedComponentId
FROM simplerefset_ss WHERE active = 1
AND refsetId in (referencesetSCTID1, referencesetSCTID2, .. referencesetSCTIDn);
As an example, the query:
SELECT distinct referencedComponentId
FROM simplerefset_ss WHERE active = 1
AND refsetId in (447566000, 447565001);
Will return all concepts that are are either in 447566000 | Virtual medicinal product simple reference set
|, 447565001 | Virtual therapeutic moiety simple reference set | or both.
5.1.9.1.2 FULL Release Type
The query below returns the concept identifiers that are currently members of one or more
of the listed simple reference sets from a full release:
SELECT distinct referencedComponentId
FROM simplerefset_full srs,
(SELECT id, MAX(effectiveTime) AS effectiveTime FROM simplerefset_full GROUP BY id) as
srs_keys
WHERE srs.id = srs_keys.id AND srs.effectiveTime = srs_keys.effectiveTime AND active = 1
AND refsetId in (referencesetSCTID1, referencesetSCTID2, .. referencesetSCTIDn);
The example below:
SELECT distinct referencedComponentId
FROM simplerefset_full srs,
(SELECT id, MAX(effectiveTime) AS effectiveTime FROM simplerefset_full GROUP BY id) as
srs_keys
WHERE srs.id = srs_keys.id AND srs.effectiveTime = srs_keys.effectiveTime AND active = 1
AND refsetId in (447566000, 447565001);
Will return all concepts that are are either in 447566000 | Virtual medicinal product simple reference set
|, 447565001 | Virtual therapeutic moiety simple reference set | or both.
5.1.9.1.3 Command Line
The following query will return the set of sctid's of referenced components in any of the refset
ids. It (a) skips the header record, (b) selects the active, refsetId and referencedComponentId columns,
(c) selects only the active columns with one the target refsets and (d) projects the referenced component
(concept SCTID).
tail +2 der2_Refset_SimpleSnapshot_edition_releaseDate.txt | cut -f 3,5,6 | \
grep ^1\\\t\\\(referencesetSCTID1\\\|referencesetSCTID2\\\|...referencesetSCTIDn\\\) | cut -f 3 | sort
-u
В© 2002-2013 International Health Terminology Standards Development Organisation CVR #: 30363434
42 | SNOMED CT SNOMED CT How To Guides July 2013
As an example:
tail +2 der2_Refset_SimpleSnapshot_INT_20120731.txt | cut -f 3,5,6 | \
grep ^1\\\t\\\(447566000\\\|447565001\\\) | cut -f 3 | sort -u
Will return all concepts that are are either in 447566000 | Virtual medicinal product simple reference set
|, 447565001 | Virtual therapeutic moiety simple reference set | or both.
5.1.9.2 Set Intersection
5.1.9.2.1 SNAPSHOT Release Type Simple Reference Set Union
The following query will return the sctid's common between the two simple
reference sets in the snapshot table:
SELECT distinct referencedComponentId
FROM simplerefset_ss sr1 JOIN simplerefset_ss sr2 USING (referencedComponentId)
WHERE sr1.active = 1 AND sr2.active = 1
AND sr1.refsetId = referencesetSCTID1 AND sr2.refsetId = referencesetSCTID2;
As an example, the following query:
SELECT distinct referencedComponentId
FROM simplerefset_ss sr1 JOIN simplerefset_ss sr2 USING (referencedComponentId)
WHERE sr1.active = 1 AND sr2.active = 1
AND sr1.refsetId = 447565001 AND sr2.refsetId = 447564002;
returns all of the concepts in common between 447565001 | Virtual therapeutic moiety simple reference
set | and 447564002 | Non-human simple reference set | in the July 2012 International Release- a single
concept, 10495001 | Rabies vaccine, animal |.
5.1.9.2.2 FULL Release Type Simple Reference Set Union
The following query will return the sctid's common between the two of the refset ids from a
FULL relase table:
SELECT distinct referencedComponentId
FROM simplerefset_full sr1 JOIN simplerefset_full sr2 using (referencedComponentId),
(SELECT id, MAX(effectiveTime) AS effectiveTime FROM simplerefset_full
WHERE refsetid = referencesetSCTID1 GROUP BY id) as srs1_keys,
(SELECT id, MAX(effectiveTime) AS effectiveTime FROM simplerefset_full
WHERE refsetid = referencesetSCTID2 GROUP BY id) as srs2_keys
WHERE sr1.id = srs1_keys.id AND sr1.effectiveTime = srs1_keys.effectiveTime AND sr1.active =
1
AND sr2.id = srs2_keys.id AND sr2.effectiveTime = srs2_keys.effectiveTime AND sr2.active = 1
AND sr1.refsetId = referencesetSCTID1 AND sr2.refsetId = referencesetSCTID2;
As an example, the query:
SELECT distinct referencedComponentId
FROM simplerefset_full sr1 JOIN simplerefset_full sr2 using (referencedComponentId),
(SELECT id, MAX(effectiveTime) AS effectiveTime FROM simplerefset_full
WHERE refsetid = 447565001 GROUP BY id) as srs1_keys,
(SELECT id, MAX(effectiveTime) AS effectiveTime FROM simplerefset_full
WHERE refsetid = 447564002 GROUP BY id) as srs2_keys
WHERE sr1.id = srs1_keys.id AND sr1.effectiveTime = srs1_keys.effectiveTime AND sr1.active =
1
AND sr2.id = srs2_keys.id AND sr2.effectiveTime = srs2_keys.effectiveTime AND sr2.active = 1
AND sr1.refsetId = 447565001 AND sr2.refsetId = 447564002;
returns all of the concepts in common between 447565001 | Virtual therapeutic moiety simple reference
set | and 447564002 | Non-human simple reference set | in the July 2012 International Release- a single
concept, 10495001 | Rabies vaccine, animal |.
5.1.9.2.3 Command Line Simple Reference Set Union
The following query will return the set of sctid's of referenced components common to all of
the refset ids. The operation steps are: (a) create a scratch file (b) put all the active members of the first
В© 2002-2013 International Health Terminology Standards Development Organisation CVR #: 30363434
Reference Set Implementation How To Guides | 43
refset into the scratch file in ascending order (c) join the contents of the scratch file with all of the active
members of the second refset and (d) remove the scratch file
TMP=$(mktemp -t ss)
tail +2 der2_Refset_SimpleSnapshot_edition_releaseDate.txt | cut -f 3,5,6 | grep
^1\\\treferencesetSCTID1 | cut -f 3 | sort -u > $TMP
tail +2 der2_Refset_SimpleSnapshot_edition_releaseDate.txt | cut -f 3,5,6 | grep
^1\\\treferencesetSCTID2 | cut -f 3 | sort -u | join - $TMP
rm $TMP
As an example, the following query:
TMP=$(mktemp -t ss)
tail +2 der2_Refset_SimpleSnapshot_INT_20120731.txt | cut -f 3,5,6 | grep ^1\\\t447565001 | cut -f
3 | sort -u > $TMP
tail +2 der2_Refset_SimpleSnapshot_INT_20120731.txt | cut -f 3,5,6 | grep ^1\\\t447564002 | cut -f
3 | sort -u | join - $TMP
rm $TMP
returns all of the concepts in common between 447565001 | Virtual therapeutic moiety simple reference
set | and 447564002 | Non-human simple reference set | in the July 2012 International Release- a single
concept, 10495001 | Rabies vaccine, animal |.
5.1.9.3 Set Difference
5.1.9.3.1 SNAPSHOT Release Type Simple Reference Set Difference
The following query will return the sctid's in the first refset identified by
referencesetSCTID1 but not in the second identified by referencesetSCTID2:
SELECT referencedComponentId FROM simplerefset_ss sr1
WHERE active = 1 AND sr1.refsetId = referencesetSCTID1
AND NOT EXISTS (SELECT * FROM simplerefset_ss sr2
WHERE sr2.active = 1 AND sr2.refsetId = referencesetSCTID2 AND
sr1.referencedComponentId = sr2.referencedComponentId);
As an example, the following query will list everything that is active in 447564002 | Non-human simple
reference set | but not in 447565001 | Virtual therapeutic moiety simple reference set |:
SELECT referencedComponentId FROM simplerefset_ss sr1
WHERE active = 1 AND sr1.refsetId = 447564002
AND NOT EXISTS (SELECT * FROM simplerefset_ss sr2
WHERE sr2.active = 1 AND sr2.refsetId = 447565001 AND
sr1.referencedComponentId = sr2.referencedComponentId);
which, for the July 2012 SNOMED CT International Release returns 1907 of the 1908 active concepts
from the 447564002 | Non-human simple reference set |
5.1.9.3.2 Universal Set minus SNAPSHOT Release Set
The following query will return all know active concepts that are not in the reference set
identified by referencesetSCTID:
SELECT id FROM concept_ss
WHERE active=1 AND NOT EXISTS(SELECT * FROM simplerefset_ss
WHERE active = 1 AND refsetId = referencesetSCTID
AND referencedComponentId = id);
As an example:
SELECT id FROM concept_ss
WHERE active=1 AND NOT EXISTS(SELECT * FROM simplerefset_ss
WHERE active = 1 AND refsetId = 447566000
AND referencedComponentId = id);
Would return all active concepts not in the 447566000 | Virtual medicinal product simple reference set |
В© 2002-2013 International Health Terminology Standards Development Organisation CVR #: 30363434
44 | SNOMED CT SNOMED CT How To Guides July 2013
5.1.9.3.3 FULL Release Type Set Difference
The following query will return the sctid's in the first refset identified by referencesetSCTID1
but not in the second identified by referencesetSCTID2:
SELECT referencedComponentId
FROM simplerefset_full srs,
(SELECT id, MAX(effectiveTime) AS effectiveTime FROM simplerefset_full
WHERE refsetId = referencesetSCTID1 GROUP BY id) as srs_keys
WHERE srs.id = srs_keys.id AND srs.effectiveTime = srs_keys.effectiveTime AND active = 1
AND NOT EXISTS
(SELECT * FROM simplerefset_full srs2,
(SELECT id, MAX(effectiveTime) AS effectiveTime FROM simplerefset_full
WHERE refsetId = referencesetSCTID2 GROUP BY id) as srs2_keys
WHERE srs2.active = 1 AND srs2.id = srs2_keys.id and
srs2.effectiveTime=srs2_keys.effectiveTime AND active = 1
AND srs.referencedComponentId = srs2.referencedComponentId);
As an example, the following query will list everything that is active in 447564002 | Non-human simple
reference set | but not in 447565001 | Virtual therapeutic moiety simple reference set |:
SELECT referencedComponentId
FROM simplerefset_full srs,
(SELECT id, MAX(effectiveTime) AS effectiveTime FROM simplerefset_full
WHERE refsetId = 447565002 GROUP BY id) as srs_keys
WHERE srs.id = srs_keys.id AND srs.effectiveTime = srs_keys.effectiveTime AND active = 1
AND NOT EXISTS
(SELECT * FROM simplerefset_full srs2,
(SELECT id, MAX(effectiveTime) AS effectiveTime FROM simplerefset_full
WHERE refsetId = 447564001 GROUP BY id) as srs2_keys
WHERE srs2.active = 1 AND srs2.id = srs2_keys.id and
srs2.effectiveTime=srs2_keys.effectiveTime AND active = 1
AND srs.referencedComponentId = srs2.referencedComponentId);
which, for the July 2012 SNOMED CT International Release returns 1907 of the 1908 active concepts
from the 447564002 | Non-human simple reference set |
5.1.9.3.4 Universal Set minus FULL Release Set
The following query will return all know active concepts that are not in the reference set
identified by referencesetSCTID:
SELECT c.id FROM concept_full c
WHERE active = 1 AND NOT EXISTS (SELECT * FROM simplerefset_full srs1,
(SELECT id, MAX(effectiveTime) AS effectiveTime FROM simplerefset_full
WHERE refsetId = referencesetSCTID GROUP BY id) as srs1_keys
WHERE active = 1 AND srs1.id = srs1_keys.id and srs1.effectiveTime=srs1_keys.effectiveTime
AND referencedComponentId = c.id);
As an example, the query:
SELECT c.id FROM concept_full c
WHERE active = 1 AND NOT EXISTS (SELECT * FROM simplerefset_full srs1,
(SELECT id, MAX(effectiveTime) AS effectiveTime FROM simplerefset_full
WHERE refsetId = 447566000 GROUP BY id) as srs1_keys
WHERE active = 1 AND srs1.id = srs1_keys.id and srs1.effectiveTime=srs1_keys.effectiveTime
AND referencedComponentId = c.id);
Would return all active concepts not in the 447566000 | Virtual medicinal product simple reference set |
5.1.9.3.5 Command Line Set Difference
The following query will return the set of sctid's of referenced components in the first refset
that aren't in the second. The operation steps are: (a) create a scratch file (b) put all the active members
of the reference set identified by referencesetSCTID1 into the scratch file in ascending order (c) join the
contents of the scratch file with all of the active members of the refset identified by referencesetSCTID2
displaying only those in the first set but not the second (d) remove the scratch file.
В© 2002-2013 International Health Terminology Standards Development Organisation CVR #: 30363434
Reference Set Implementation How To Guides | 45
TMP=$(mktemp -t ss)
tail +2 der2_Refset_SimpleSnapshot_INT_20120731.txt | cut -f 3,5,6 | grep ^1\\\treferencesetSCTID1
| cut -f 3 | sort -u > $TMP
tail +2 der2_Refset_SimpleSnapshot_INT_20120731.txt | cut -f 3,5,6 | grep ^1\\\treferencesetSCTID2
| cut -f 3 | sort -u | join -v 1 $TMP rm $TMP
As an example, the following query will list everything that is active in 447564002 | Non-human simple
reference set | but not in 447565001 | Virtual therapeutic moiety simple reference set |:
TMP=$(mktemp -t ss)
tail +2 der2_Refset_SimpleSnapshot_INT_20120731.txt | cut -f 3,5,6 | grep ^1\\\t447564002 | cut -f
3 | sort -u > $TMP
tail +2 der2_Refset_SimpleSnapshot_INT_20120731.txt | cut -f 3,5,6 | grep ^1\\\t447565001 | cut -f
3 | sort -u | join -v 1 $TMP rm $TMP
which, for the July 2012 SNOMED CT International Release returns 1907 of the 1908 active concepts
from the 447564002 | Non-human simple reference set |
5.1.9.3.6 Command Line Universal Set minus SNAPSHOT Set
The following query will return the set of sctid's of referenced components that are not
currently in the reference set. The operation steps are: (a) create a scratch file (b) skip the header line
on the concept SNAPSHOT file (c) extract the concept SCTID and active fields (d) filter only active
concepts (e) extract the concept SCTID, sort the results and store them in the scratch file. It then (g) skips
the header line of the simple reference set SNAPSHOT, (h) extracts the active, refsetId and
referencedComponentId fields, (i) filters for active members of the reference set, (j) extracts the referenced
component id (k) sorts it and (l) joins the contents of the scratch file with all of the active members of the
reference set identified by referencesetSCTID displaying only those in the first set but not the second (m)
removes the scratch file.
TMP=$(mktemp -t ss)
tail +2 sct2_Concept_Snapshot_INT_20120731.txt | cut -f 1,3 | grep \\\t1 | cut -f 1 | sort -u > $TMP
tail +2 der2_Refset_SimpleSnapshot_INT_20120731.txt | cut -f 3,5,6 | grep ^1\\\treferencesetSCTID
| cut -f 3 | sort -u | join -v 1 - $TMP
rm $TMP
As an example, the query:
TMP=$(mktemp -t ss)
tail +2 sct2_Concept_Snapshot_INT_20120731.txt | cut -f 1,3 | grep \\\t1 | cut -f 1 | sort -u > $TMP
tail +2 der2_Refset_SimpleSnapshot_INT_20120731.txt | cut -f 3,5,6 | grep ^1\\\t447566000 | cut -f
3 | sort -u | join -v 1 - $TMP
rm $TMP
Would return all active concepts not in the 447566000 | Virtual medicinal product simple reference set |
5.1.9.3.7 The "Universal" Set
The "Universal" Set from which the components of a simple reference set is drawn, can be
constructed by querying all of the concepts in the distribution concept table. Note that there is not
necessarily a correlation between the active concepts in the concepts table and the active components
of a simple reference set. It is possible for a simple reference set to actively reference concepts that are
no longer active in the concepts table, as (obviously) it is also possible for a reference to an active concept
to no longer be be included in a simple reference set.
Note: All of the operations discussed in this section assume that the referenced components of a
simple reference set are the sctid's of concepts. At the moment there is nothing to physically prevent
simple reference sets from referencing the sctids of Descriptions or Relationships as well, it has not
been determined whether the scope of simple reference sets should include these. For simplicity this
document sticks strictly to concepts
5.1.9.3.7.1 How to generate the "Universal" set from a SNAPSHOT release
The following query returns all active concept sctids from a simple reference set snapshot
release
В© 2002-2013 International Health Terminology Standards Development Organisation CVR #: 30363434
46 | SNOMED CT SNOMED CT How To Guides July 2013
SELECT id from concept_ss WHERE active = 1;
5.1.9.3.7.2 How to generate the "Universal" set from a FULL release
The following query returns all active concept sctids from a simple reference set full release.
It does this by first selecting the latest entry for each concept id, selecting the active concepts from this
set and then projecting the concept identifier
SELECT cf.id
FROM concept_full cf,
(select id, MAX(effectiveTime) AS effectiveTime from concept_full GROUP BY ID) as c_keys
WHERE cf.id = c_keys.id AND cf.effectiveTime = c_keys.effectiveTime and active = 1;
5.1.9.3.7.3 How to generate the "Universal" set from the command line
The following command (a) strips the header line and (b) selects the first column, the concept
id and the third column, the status, (c) selects the active entries and (d) projects the concept identifier.
tail +2 sct2_Concept_Snapshot_edition_releaseDate.txt | cut -f 1,3 | grep 1$ | cut -f 1
As an example,
tail +2 sct2_Concept_Snapshot_INT_20120731.txt | cut -f 1,3 | grep 1$ | cut -f 1 | wc -l
Returns "296433", the number of active concepts in the July 2012 International Release.
5.1.9.4 Related Links
5.2 How to use a Language Refset
5.2.1 Overview
This guide describes how to load and use the contents of the Language Reference Set tables that are
distributed as part of the SNOMED CT RF2 release.
5.2.2 Before You Start
5.2.2.1 Is This For You?
This guide is intended for people who are developing or configuring a software application
to select and use a references set that represents a subset of SNOMED CT concepts.
This guide is not intended for end users of software applications which make use of SNOMED CT reference
sets.
Note: SNOMED CT enabled applications may include a predefined set of references sets that can
be selected manually or are automatically applied according to context of use (e.g. to filter search
contents or to populate a drop-down list).They may also provide a user-friendly way to import additional
reference sets. The instructions for using these types of facilities are specific to the individual
application and are not covered by this guide. However, the basic techniques described in this guide
5.2.2.2 Disclaimer
The SQL and command line examples in this document are exactly that - examples. While
all of them have been tested and return the expected results within a reasonable amount of time, the
reader needs to realize that there are many approaches to addressing the questions the particular solutions
provided in this document are not likely to be the most efficient, let alone optimal for a given situation.
Readers are encouraged to use the examples as guides for constructing queries and other code to fit
their own circumstances.
В© 2002-2013 International Health Terminology Standards Development Organisation CVR #: 30363434
Reference Set Implementation How To Guides | 47
5.2.2.3 Prerequisites
•
•
Familiarity with Release Format 2 (RF2) - This guide assumes that users are already
familiar with the Release Format 2 (RF2) and have already obtained and downloaded the appropriate
release files.
One of:
•
•
Familiarity with Unix command line utilities (e.g. cat, sort, cut, etc.)
Familiarity with SQL - The examples and instructions are are written to and have been tested using
MySQL, but should be readily translatable into other SQL dialects.
A running SQL database - This documents assumes that the user has access to a SQL server and
has created a database.
Note: Some SNOMED CT release files may include text that is encoded in the UTF-8 format.
It is recommended that the default character set and sort order be set to "utf8" and "utf8-bin"
respectively.
5.2.3 Typographical and Syntax Conventions
The examples in this document use a style similar to that described in the MySQL manual conventions.
•
•
•
BOLD CAPITAL TEXT represents SQL Keywords
bold lower case represent table names, keys, etc
italic values indicates that, were you to enter a similar statement, you would supply your own values
5.2.4 Language Refset Table Layout
A Language Reference Set identifies the Descriptions that are considered to be preferred designations
for and acceptable as alternative designations for SNOMED CT concepts in the context of a given language
and, optionally, dialect. The language reference set also specifies, by omission, designations that are not
considered acceptable for a given concept in a a given language.
id
effective
Time
active
moduleId
refsetId
referenced
acceptabilityId
ComponentId
076d91a42
-8095
-3e59
-b774
-2351205462a 20020131
1
900000000000207008 900000000000508004 316562018
900000000000549004
3c3bc813d
-5855
-8bbb
-0425
-7ba9e53b01f 20020131
1
900000000000207008 900000000000509007 316560014
900000000000548007
4429d8408
-4e05
-ee0b
-be26
-1e9862c9
1f 20020131
1
900000000000207008 900000000000508004 316560014
900000000000548007
The entry above asserts that according to module 900000000000207008 | SNOMED CT core |, the
Description with the id of 316562018, which associates the term "Perinatal nose haemorrhage" for the
concept 206421002 | Perinatal epistaxis | is an 900000000000549004 | Acceptable | designation for the
900000000000508004 | GB English | Language Reference Set and the Description with the id of
316560014, which associates the term "Perinatal epistaxis" for the same concept is 900000000000548007
| Preferred | in both the 900000000000508004 | GB English | Language Reference Set and the
900000000000509007 | US English | Language Reference Set
Note that the possible values for the acceptabilityId field all the descendants (leaf concepts?) of
900000000000511003 | Acceptability |, which, at the moment includes:
•
•
900000000000549004 | Acceptable |
900000000000548007 | Preferred |
Note also that other entries may be added at a later date.
More details about the layout of a Language Reference set can be found in the references at the end of
the section.
В© 2002-2013 International Health Terminology Standards Development Organisation CVR #: 30363434
48 | SNOMED CT SNOMED CT How To Guides July 2013
5.2.5 How to load the Language Refset Table into an SQL Database
5.2.5.1 Download the appropriate set of RF2 SNOMED CT Release Files
Instructions for obtaining RF2 Release files and determining which release type to be used
are listed in the links at the end of the section. The sections below have different instructions for loading
"Full" and "Snapshot" type of release. The "Full" instructions should be used if the user has downloaded
the "Full" release, has applied one or more "Delta" releases or if the user anticipates using "Delta" release
types to apply subsequent updates. The "Snapshot" instructions are (a) simpler and (b) are designed to
prevent duplicate and non-current record errors
5.2.5.2 Relationship between Language Reference Set and Description File
The Language Reference Set is closely coupled with the RF2 Description file. Most of the
use cases in this section require that both of these files be present and be loaded from the same release
and Release Type (e.g. FULL, SNAPSHOT or DELTA). For the sake of simplicity, we include instructions
for loading both the Language and Description files below.
5.2.5.3 Creating the SQL Tables
5.2.5.3.1 SNAPSHOT Release Type
The following SQL script will create the Language Reference Set and Description
file SNAPSHOT tables in MySQL. The key to a SNAPSHOT table is just its id, meaning that only one
entry can be present for each reference set assertion.
CREATE TABLE `languagerefset_ss` (
`id` varchar(36) NOT NULL,
`effectiveTime` int NOT NULL,
`active` tinyint(1) NOT NULL,
`moduleId` bigint(20) NOT NULL,
`refsetId` bigint(20) NOT NULL,
`referencedComponentId` bigint(20) NOT NULL,
`acceptabilityId` bigint(20) NOT NULL,
PRIMARY KEY (`id`),
KEY `refset` (`refsetId`),
KEY `component` (`referencedComponentId`)
);
CREATE TABLE `description_ss` (
`id` bigint(20) NOT NULL,
`effectiveTime` int(11) NOT NULL,
`active` tinyint(1) NOT NULL,
`moduleId` bigint(20) NOT NULL,
`conceptId` bigint(20) NOT NULL,
`languageCode` varchar(10) COLLATE utf8_bin NOT NULL,
`typeId` bigint(20) NOT NULL,
`term` text CHARACTER SET utf8 NOT NULL,
`caseSignificanceId` bigint(20) NOT NULL,
PRIMARY KEY (`id`),
KEY `concept` (`conceptId`)
);
Note: The SQL data type for term above is set to text in anticipation of loading additional files such
as the RF2 Language Description file into the Description file. While the maximum size of a term in
the Description file itself is 255 characters, the TIG (ref) states that terms can be up to 32kb in length.
5.2.5.3.2 FULL Release Type
Use the following script to create the Language Reference Set and Description file FULL
table in MySQL. The key to the FULL table is both the id and effectiveTime, meaning that multiple entries
can exist for the same assertion and that Delta’s or local updates may be applied:
В© 2002-2013 International Health Terminology Standards Development Organisation CVR #: 30363434
Reference Set Implementation How To Guides | 49
CREATE TABLE `languagerefset_full` (
`id` varchar(36) NOT NULL,
`effectiveTime` int NOT NULL,
`active` tinyint(1) NOT NULL,
`moduleId` bigint(20) NOT NULL,
`refsetId` bigint(20) NOT NULL,
`referencedComponentId` bigint(20) NOT NULL,
`acceptabilityId` bigint(20) NOT NULL,
PRIMARY KEY (`id`,`effectiveTime`),
KEY `refset` (`refsetId`),
KEY `component` (`referencedComponentId`)
);
CREATE TABLE `description_full` (
`id` bigint(20) NOT NULL,
`effectiveTime` int(11) NOT NULL,
`active` tinyint(1) NOT NULL,
`moduleId` bigint(20) NOT NULL,
`conceptId` bigint(20) NOT NULL,
`languageCode` varchar(10) COLLATE utf8_bin NOT NULL,
`typeId` bigint(20) NOT NULL,
`term` text CHARACTER SET utf8 NOT NULL,
`caseSignificanceId` bigint(20) NOT NULL,
PRIMARY KEY (`id`, `effectiveTime`),
KEY `concept` (`conceptId`)
);
5.2.5.4 Loading the Language Refset Data
5.2.5.4.1 SNAPSHOT Release Type
Language reference set snapshots can be loaded using the following syntax:
LOAD DATA local INFILE 'der2_cRefset_LanguageSnapshot-lang_edition_releaseDate.txt'
INTO TABLE languagerefset_ss
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;
As an example, the following snippet will load the July 31, 2012 SNOMED International Release RF2
English language refset SNAPSHOT table:
LOAD DATA local INFILE 'der2_cRefset_LanguageSnapshot-en_INT_20120731.txt'
INTO TABLE languagerefset_ss
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;
Language reference sets are closely coupled with the core Description and text definition files. As such
it is almost always necessary to have those loaded as well. The instructions below don't go into much
depth - further details can be found in the references.
LOAD DATA local INFILE 'sct2_Description_Snapshot-en_INT_20120731.txt'
INTO TABLE description_ss
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;
LOAD DATA local INFILE 'sct2_TextDefinition_Snapshot-en_INT_20120731.txt'
INTO TABLE description_ss
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;
5.2.5.4.2 FULL Release Type
Language reference set full releases can be loaded using the following syntax:
В© 2002-2013 International Health Terminology Standards Development Organisation CVR #: 30363434
50 | SNOMED CT SNOMED CT How To Guides July 2013
LOAD DATA local INFILE 'der2_cRefset_LanguageFull-lang_edition_releaseDate.txt'
INTO TABLE languagerefset_full
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;
As an example, the following snippet will load the July 31, 2012 SNOMED International Release RF2
English language refset FULL table:
LOAD DATA local INFILE 'der2_cRefset_LanguageFull-en_INT_20120731.txt'
INTO TABLE languagerefset_full
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;
The SQL snippet below load the corresponding July 31, 2012 SNOMED International Release RF2
Description and Text Definition files into the Description file FULL table created above above:
LOAD DATA local INFILE 'sct2_Description_Full-en_INT_20120731.txt'
INTO TABLE description_full
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;
LOAD DATA local INFILE 'sct2_TextDefinition_Full-en_INT_20120731.txt'
INTO TABLE description_full
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;
5.2.6 How to List the Available Language Reference Sets
5.2.6.1 Listing Reference Sets using SNOMED CT Terminology Files
A list of simple reference sets can be found as the leaf nodes of the 900000000000506000
| Language type | hierarchy in the 900000000000012004 | SNOMED CT model component | module. The
unique reference identifiers can also be retrieved from the simplerefset table using:
SELECT distinct refsetId
FROM languagerefset_ss where active = 1
for a SNAPSHOT release and
SELECT distinct refsetId
FROM languagerefset_full lrs,
(SELECT id, MAX(effectiveTime) AS effectiveTime FROM languagerefset_full GROUP BY id) as
lrs_keys
WHERE lrs.id = lrs_keys.id AND lrs.effectiveTime = lrs_keys.effectiveTime AND active = 1;
for a FULL release
5.2.6.2 Listing Reference Sets using Command Line Utilities with the Snapshot
Distribution
The following command (a) drops the header line, (b) extracts the fifth column (refset Id) and (c) returns
the unique members.
tail +2 der2_cRefset_LanguageSnapshot-en_INT_20120731.txt | cut -f 5 | sort -u
5.2.7 Constants Used Throughout this Document
The following table lists the various constants that were available in the SNOMED CT International Release
at the time this document was written (October, 2012). We anticipate that the number of possible values
В© 2002-2013 International Health Terminology Standards Development Organisation CVR #: 30363434
Reference Set Implementation How To Guides | 51
will continue to expand and readers are encouraged to refer to the current SNOMED CT contents for the
most current list of values
Table 1: SCTIDs Used in Language Refset Resolution
Category
SCTID
Full Name
LanguageRefsetSCTID 900000000000509007 United
States of
America
English
language
reference
set
(foundation
metadata
concept)
900000000000508004 Great
Britain
English
language
reference
set
(foundation
metadata
concept)
LanguageAcceptabtyilSCTID 900000000000549004 Acceptable
(foundation
metadata
concept)
900000000000548007 Preferred
(foundation
metadata
concept)
DescriptionTypeSCTID 900000000000003001 Fully
specified
name
(core
metadata
concept)
900000000000013009 Synonym
(core
metadata
concept)
900000000000550004 Definition
(core
metadata
concept)
5.2.8 How to find the correct term for a concept given a language refset,
acceptability and type in a SNAPSHOT Release
To be of value, the Language Refset must be used in combination with the Description Table. In the
following sections, we assume that the corresponding Description table is available and named
description_ss for SNAPSHOT releases and description_full for FULL releases. We also realise that
alternate approaches are available to those shown in the examples below. The purpose of the examples
В© 2002-2013 International Health Terminology Standards Development Organisation CVR #: 30363434
52 | SNOMED CT SNOMED CT How To Guides July 2013
is to try to clearly and unambiguously show what needs to happen whether or not it is the most efficient
from a computational or storage perspective.
While it should be possible to determine the Fully Specified Name of a given concept using just the
Description file, it is possible that various dialects may select different Fully Specified Names. As an
example, it is possible that one Spanish Dialect may choose to use the English FSN while a second may
select the Spanish Variant instead. For this reason, it is recommended that the Language Reference Set
be used for retrieving any Fully Specified Name except the "cannonical" (English) version.
5.2.8.1 SNAPSHOT Release
The following query template can be used to retrieve the appropriate term(s) for a given
languageRefsetSCTID, DescriptionTypeSCTID, LanguageAcceptabilitySCTID and conceptSCTID. Some
of the possible values for the first three of these parameters are listed in the costants section above.
SELECT d.term FROM languagerefset_ss l JOIN description_ss d ON l.referencedComponentId
= d.id
WHERE l.active = 1 AND l.refsetId = languageRefsetSCTID
AND d.conceptId = conceptSCTID AND d.typeId = DescriptionTypeSCTID
AND l.acceptabilityId = LanguageAcceptabilitySCTID;
As an example, the following query:
SELECT d.term FROM languagerefset_ss l JOIN description_ss d ON l.referencedComponentId
= d.id
WHERE l.active = 1 AND l.refsetId = 900000000000509007
AND d.conceptId = 21263006 AND d.typeId = 900000000000003001
AND l.acceptabilityId = 900000000000548007;
selects the 900000000000548007 | Preferred | 900000000000509007 | US English | 900000000000003001
| Fully specified name | for the concept 21263006 | Myxedema coma (disorder) |
Table 2: Query Result
d.term
Myxedema coma (disorder)
And:
SELECT d.term FROM languagerefset_ss l JOIN description_ss d ON l.referencedComponentId
= d.id
WHERE l.active = 1 AND l.refsetId = 900000000000509007
AND d.conceptId = 21263006 AND d.typeId = 900000000000013009
AND l.acceptabilityId = 900000000000548007;
selects the 900000000000548007 | Preferred | 900000000000509007 | US English | 900000000000013009
| Synonym | for 21263006 | Myxedema coma (disorder) |
Table 3: Query Result
d.term
Myxedema coma
Removing parameteres from the query section and additng them to the SELECT section allows the
selection of all parameters of a given type. As an example, moving acceptabilityId from the filter to the
SELECT section:
SELECT d.term, l.acceptabilityId FROM languagerefset_ss l JOIN description_ss d ON
l.referencedComponentId = d.id
WHERE l.active = 1 AND l.refsetId = 900000000000509007
AND d.conceptId = 21263006 AND d.typeId = 900000000000013009;
В© 2002-2013 International Health Terminology Standards Development Organisation CVR #: 30363434
Reference Set Implementation How To Guides | 53
selects all 900000000000509007 | US English | 900000000000013009 | Synonym |s for 21263006 |
Myxedema coma (disorder) |
Table 4: Query Result
d.term
l.acceptabilityId
Myxedema coma
900000000000548007
Hypothyroid coma
900000000000549004
Similarly:
SELECT d.term, l.acceptabilityId FROM languagerefset_ss l JOIN description_ss d ON
l.referencedComponentId = d.id
WHERE l.active = 1 AND l.refsetId = 900000000000509007
AND d.conceptId = 399104001 AND d.typeId = 900000000000550004;
selects all 900000000000509007 | US English | 900000000000550004 | Definition |s for 399104001 |
Mitral valve closure to opening time |
Table 5: Query Result
d.term
l.acceptabilityId
The time interval from the closure 900000000000548007
of the 1st Doppler spectral taken
from the mitral valve to the opening
of the 2nd Doppler spectral of the
mitral valve.
5.2.8.2 How to find the correct term for a concept given a language refset, acceptability
and type in a FULL Release
To be of value, the Language Refset must be used in combination with the Description Table. In the
following sections, we assume that the corresponding Description table is available and named
description_ss for SNAPSHOT releases and description_full for FULL releases. We also realise that
alternate approaches are available to those shown in the examples below. The purpose of the examples
is to try to clearly and unambiguously show what needs to happen whether or not it is the most efficient
from a computational or storage perspective.
While it should be possible to determine the Fully Specified Name of a given concept using just the
Description file, it is possible that various dialects may select different Fully Specified Names. As an
example, it is possible that one Spanish Dialect may choose to use the English FSN while a second may
select the Spanish Variant instead. For this reason, it is recommended that the Language Reference Set
be used for retrieving any Fully Specified Name except the "cannonical" (English) version.
5.2.8.2.1 FULL Release
The following query template can be used to retrieve the appropriate term(s) for a given
languageRefsetSCTID, DescriptionTypeSCTID, LanguageAcceptabilitySCTID and conceptSCTID. Some
of the possible values for the first three of these parameters are listed in the costants section above.
SELECT d.term FROM languagerefset_full l, description_full d,
(SELECT id, MAX(effectiveTime) AS effectiveTime FROM languagerefset_full GROUP BY id) as
lrs_keys,
(SELECT id, MAX(effectiveTime) AS effectiveTime FROM description_full GROUP BY id) as
des_keys
WHERE l.referencedComponentId = d.id
AND l.id = lrs_keys.id AND l.effectiveTime = lrs_keys.effectiveTime
AND d.id = des_keys.id AND d.effectiveTime = des_keys.effectiveTime
AND l.active = 1 AND l.refsetId = languageRefsetSCTID
AND d.conceptId = conceptSCTID AND d.typeId=DescriptionTypeSCTID
AND l.acceptabilityId = LanguageAcceptabilitySCTID;
В© 2002-2013 International Health Terminology Standards Development Organisation CVR #: 30363434
54 | SNOMED CT SNOMED CT How To Guides July 2013
As an example, the following query:
SELECT d.term FROM languagerefset_full l, description_full d,
(SELECT id, MAX(effectiveTime) AS effectiveTime FROM languagerefset_full GROUP BY id) as
lrs_keys,
(SELECT id, MAX(effectiveTime) AS effectiveTime FROM description_full GROUP BY id) as
des_keys
WHERE l.referencedComponentId = d.id
AND l.id = lrs_keys.id AND l.effectiveTime = lrs_keys.effectiveTime
AND d.id = des_keys.id AND d.effectiveTime = des_keys.effectiveTime
AND l.active = 1 AND l.refsetId = 900000000000508004
AND d.conceptId = 21263006 AND d.typeId=900000000000003001
AND l.acceptabilityId = 900000000000548007;
selects the 900000000000548007 | Preferred | 900000000000508004 | GB English | 900000000000003001
| Fully specified name | for the concept 21263006 | Myxedema coma (disorder) |
Table 6: Query Result
d.term
Myxedema coma (disorder)
And:
SELECT d.term FROM languagerefset_full l, description_full d,
(SELECT id, MAX(effectiveTime) AS effectiveTime FROM languagerefset_full GROUP BY id) as
lrs_keys,
(SELECT id, MAX(effectiveTime) AS effectiveTime FROM description_full GROUP BY id) as
des_keys
WHERE l.referencedComponentId = d.id
AND l.id = lrs_keys.id AND l.effectiveTime = lrs_keys.effectiveTime
AND d.id = des_keys.id AND d.effectiveTime = des_keys.effectiveTime
AND l.active = 1 AND l.refsetId = 900000000000508004
AND d.conceptId = 21263006 AND d.typeId=900000000000013009
AND l.acceptabilityId = 900000000000548007;
selects the 900000000000548007 | Preferred | 900000000000508004 | GB English | 900000000000013009
| Synonym | for 21263006 | Myxedema coma (disorder) |
Table 7: Query Result
d.term
Myxoedema coma
Removing parameteres from the query section and additng them to the SELECT section allows the
selection of all parameters of a given type. As an example, moving acceptabilityId from the filter to the
SELECT section:
SELECT d.term, l.acceptabilityId FROM languagerefset_full l, description_full d,
(SELECT id, MAX(effectiveTime) AS effectiveTime FROM languagerefset_full GROUP BY id) as
lrs_keys,
(SELECT id, MAX(effectiveTime) AS effectiveTime FROM description_full GROUP BY id) as
des_keys
WHERE l.referencedComponentId = d.id
AND l.id = lrs_keys.id AND l.effectiveTime = lrs_keys.effectiveTime
AND d.id = des_keys.id AND d.effectiveTime = des_keys.effectiveTime
AND l.active = 1 AND l.refsetId = 900000000000508004
AND d.conceptId = 21263006 AND d.typeId=900000000000013009;
selects all 900000000000508004 | GB English | 900000000000013009 | Synonym |s for 21263006 |
Myxedema coma (disorder) |
В© 2002-2013 International Health Terminology Standards Development Organisation CVR #: 30363434
Reference Set Implementation How To Guides | 55
Table 8: Query Result
d.term
l.acceptabilityId
Myxoedema coma
900000000000548007
Hypothyroid coma
900000000000549004
Similarly:
SELECT d.term, l.acceptabilityId FROM languagerefset_full l, description_full d,
(SELECT id, MAX(effectiveTime) AS effectiveTime FROM languagerefset_full GROUP BY id) as
lrs_keys,
(SELECT id, MAX(effectiveTime) AS effectiveTime FROM description_full GROUP BY id) as
des_keys
WHERE l.referencedComponentId = d.id
AND l.id = lrs_keys.id AND l.effectiveTime = lrs_keys.effectiveTime
AND d.id = des_keys.id AND d.effectiveTime = des_keys.effectiveTime
AND l.active = 1 AND l.refsetId = 900000000000508004
AND d.conceptId = 399104001 AND d.typeId=900000000000550004;
selects all 900000000000508004 | GB English | |900000000000550004 | Definition |s for 399104001 |
Mitral valve closure to opening time |
Table 9: Query Result
d.term
l.acceptabilityId
The time interval from the closure 900000000000548007
of the 1st Doppler spectral taken
from the mitral valve to the opening
of the 2nd Doppler spectral of the
mitral valve.
5.2.8.3 How to find the correct term for a concept given a language refset, acceptability
and type in a Command Line
The following set of commands works with the SNAPSHOT release tables. It takes four parameters:
1.
2.
3.
4.
The ReferencesetSCTID ($1)
The AcceptabilitySCTID ($2)
The DescriptionTypeSCTID ($3)
the conceptSCTID ($4)
Some possible values for the first three parameters can be found in the list of constants earlier in this
document
The commands perform the following steps
1.
2.
3.
4.
5.
6.
7.
8.
9.
Create a scratch file
Strip the heading off of the supplied Language Reference Set snapshot
Isolate the active, refsetId, descriptionId and acceptabilityId fields
Filter only active entries for the supplied ReferencesetSCTID (parameter 1)
Strip the active, refsetId columns
Remove the trailing carriage return (col command)
Filter for entries matching the supplied AcceptabilitySCTID (parameter 2)
Strip the acceptabilityId column, leaving the descriptionId
Sort the Description identifiers ascending order and store the results into a scratch file. At this point,
the scratch file contains all Descriptions for the supplied reference set and acceptability id - something
that could be saved for future use.
10. Strip the heading off of the corresponding Description file snapshot
11. Isolate the id, conceptId, typeId, and term
В© 2002-2013 International Health Terminology Standards Development Organisation CVR #: 30363434
56 | SNOMED CT SNOMED CT How To Guides July 2013
12. Remove any entries that don't contain conceptSCTID (parameter 4) somewhere in the string - this
makes the query run more quickly.
13. Sort the results by descriptionId
14. Join the results with the results of step 9 above on id=descriptionId
15. Strip the descriptionId column
16. Filter for conceptSCTID (parameter 4)
17. Strip off the id column
18. Filter for the supplied DescriptionTypeSCTID (parameter 3)
19. Strip out the term column and print the results
20. Remove the scratch file
TMP=$(mktemp -t ss)
tail +2 ../Refset/Language/der2_cRefset_LanguageSnapshot-en_INT_20120731.txt | cut -f 3,5,6,7 |
grep ^1\\\t$1\\\t | cut -f 3,4 | col | grep \\\t$2$ | cut -f 1 | sort -u > $TMP
tail +2 sct2_Description_Snapshot-en_INT_20120731.txt | cut -f 1,3,5,7,8 | grep \\\t$4\\\t |
sort | join -t$'\t' - $TMP |cut -f 2,3,4,5 | grep ^1\\\t$4\\\t | cut -f 3,4 | grep ^$3\\\t | cut -f 2
rm $TMP
Were the query saved in the Snapshot/Terminology directory as "getterm.sh", the following invocation:
sh getterm.sh 900000000000509007 900000000000548007 900000000000003001 21263006
selects the 900000000000548007 | Preferred | 900000000000509007 | US English | 900000000000003001
| Fully specified name | for 21263006 | Myxedema coma (disorder) |
5.2.9 Related Links
5.3 How to use a Simple Map Refset
5.3.1 Overview
This guide describes the steps you need to go through to use the SNOMED CT RF2 Simple Map Reference
Set.
5.3.2 Before You Start
5.3.2.1 Is This For You?
This guide is intended for people who are developing or configuring a software application
that needs to select preferred and alternate concept Descriptions and/or definitions in a specified language.
This guide is not intended for end users of software applications which make use of SNOMED CT reference
sets.
Note: SNOMED CT enabled applications may include a predefined set of references sets that can
be selected manually or are automatically applied according to context of use (e.g. to filter search
contents or to populate a drop-down list).They may also provide a user-friendly way to import additional
reference sets. The instructions for using these types of facilities are specific to the individual
application and are not covered by this guide. However, the basic techniques described in this guide
5.3.2.2 Disclaimer
The SQL and command line examples in this document are exactly that - examples. While
all of them have been tested and return the expected results within a reasonable amount of time, the
reader needs to realise that there are many approaches to addressing the questions the particular solutions
provided in this document are not likely to be the most efficient, let alone optimal for a given situation.
В© 2002-2013 International Health Terminology Standards Development Organisation CVR #: 30363434
Reference Set Implementation How To Guides | 57
Readers are encouraged to use the examples as guides for constructing queries and other code to fit
their own circumstances.
5.3.2.3 Prerequsites
•
•
Familiarity with Release Format 2 (RF2) - This guide assumes that users are already
familiar with the Release Format 2 (RF2) and have already obtained and downloaded the appropriate
release files.
One of:
•
•
Familiarity with Unix command line utilities (e.g. cat, sort, cut, etc.)
Familiarity with SQL - The examples and instructions are are written to and have been tested using
MySQL, but should be readily translatable into other SQL dialects.
A running SQL database - This documents assumes that the user has access to a SQL server and
has created a database.
Note: Some SNOMED CT release files may include text that is encoded in the UTF-8 format.
It is recommended that the default character set and sort order be set to "utf8" and "utf8-bin"
respectively.
5.3.3 Simple Map Refset Table Layout
A Simple Map Reference Set describes a "mapping" between SNOMED CT resources and
id
effective
Time
active
moduleId
refsetId
referenced
mapTarget
ComponentId
163392153f-8d5
-d4fa
-1b0e
-82da29b42ae 20020131
1
900000000000207008 446608001
10013000
C40.2
9ec10eaa2
-24b5
-0e09
-639e
-43c3a87afc0 20020131
1
900000000000207008 446608001
10024003
C34.3
78fd6f9bd
-8125
-046b
-90e
-f193fe78a31d 20020131
1
900000000000207008 446608001
10025002
C40.1
The entry above asserts that according to module 900000000000207008 | SNOMED CT core |, the
446608001 | ICD-O simple map reference set | asserts that:
•
•
•
10013000 | Lateral meniscus structure | maps to C40.2
10024003 | Structure of base of lung | maps to C34.3
10025002 | Structure of base of phalanx of index finger | maps to C40.1
Depending on the specific refset, the referencedComponentId may reference to a SNOMED CT concept,
Relationship or Description. The meaning of the mapTarget is dependent on the particular reference set,
but the intent of the reference set structure is that it carries a concept, Description, Relationship or other
identifier from an external terminology. For the remainder of this document, we are going to assume that
referencedComponentId references a SNOMED CT concept and mapTarget contains a code from an
external code system.
Note: The SNOMED CT reference distribution files use the heading mapTarget. The current TIG
documentation, however, calls this column mapTarget. We use mapTarget as the identifier in this
document.
5.3.4 How to load the Simple Map Refset Table into an SQL Database
5.3.4.1 Download the appropriate set of RF2 SNOMED CT Release Files
Instructions for obtaining RF2 Release files and determining which Release Type to be used
are listed in the links at the end of the section. The sections below have different instructions for loading
"Full" and "Snapshot" type of release. The "Full" instructions should be used if the user has downloaded
the "Full" release, has applied one or more "Delta" releases or if the user anticipates using "Delta"Release
Types to apply subsequent updates. The "Snapshot" instructions are (a) simpler and (b) are designed to
prevent duplicate and non-current record errors
В© 2002-2013 International Health Terminology Standards Development Organisation CVR #: 30363434
58 | SNOMED CT SNOMED CT How To Guides July 2013
5.3.4.2 Creating the SQL Tables
5.3.4.2.1 SNAPSHOT Release Type
The following SQL script will create a Simple Map Reference Set SNAPSHOT
table in MySQL. The key to a SNAPSHOT table is just its id, meaning that only one entry can be present
for each reference set assertion.
CREATE TABLE `simplemap_ss` (
`id` varchar(36) NOT NULL,
`effectiveTime` int NOT NULL,
`active` tinyint(1) NOT NULL,
`moduleId` bigint(20) NOT NULL,
`refsetId` bigint(20) NOT NULL,
`referencedComponentId` bigint(20) NOT NULL,
`mapTarget` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
KEY `refset` (`refsetId`),
KEY `component` (`referencedComponentId`),
KEY `maptarget` (`mapTarget`)
);
5.3.4.2.2 FULL Release Type
Use the following script to create a Simple Map Reference FULL table in MySQL. The key
to the FULL table is both the id and effectiveTime, meaning that multiple entries can exist for the same
assertion and that Delta’s or local updates may be applied:
CREATE TABLE `simplemap_full` (
`id` varchar(36) NOT NULL,
`effectiveTime` int NOT NULL,
`active` tinyint(1) NOT NULL,
`moduleId` bigint(20) NOT NULL,
`refsetId` bigint(20) NOT NULL,
`referencedComponentId` bigint(20) NOT NULL,
`mapTarget` varchar(255) NOT NULL,
PRIMARY KEY (`id`,`effectiveTime`),
KEY `refset` (`refsetId`),
KEY `component` (`referencedComponentId`),
KEY `maptarget` (`mapTarget`)
);
5.3.4.3 Loading the Simple Map Refset Data
5.3.4.3.1 SNAPSHOT Release Type
The following SQL snippet will load the July 31, 2012 SNOMED International
Release RF2 Simple Map Reference Set file into the SNAPSHOT table created above:
LOAD DATA local INFILE 'der2_sRefset_SimpleMapSnapshot_INT_20120731.txt'
INTO TABLE simplemap_ss
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;
Note that the "INT" and "20120731" portions of the file name may vary depending on the particular release
and date.
5.3.4.3.2 FULL Release Type
The following SQL snippet will load the July 31, 2012 SNOMED International Release RF2
SRS file into the simplerefset FULL table created above above:
LOAD DATA local INFILE 'der2_sRefset_SimpleMapFull_INT_20120731.txt'
INTO TABLE simplemap_full
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;
В© 2002-2013 International Health Terminology Standards Development Organisation CVR #: 30363434
Reference Set Implementation How To Guides | 59
5.3.5 How to List the Available Simple Maps
5.3.5.1 Listing Simple Maps using SNOMED CT Terminology Files
A list of simple reference sets can be found as the leaf nodes of the 900000000000496009
| Simple map | node. The unique reference identifiers can also be retrieved from the simplerefset table
using:
SELECT distinct refsetId
FROM simplemap_ss where active = 1
for a SNAPSHOT release and
SELECT distinct refsetId
FROM simplemap_full mrs,
(SELECT id, MAX(effectiveTime) AS effectiveTime FROM simplemap_full GROUP BY id) as mrs_keys
WHERE mrs.id = mrs_keys.id AND mrs.effectiveTime = mrs_keys.effectiveTime AND active = 1;
for a FULL release
5.3.5.2 Listing Reference Sets using Command Line Utilities
The following command (a) drops the header line, (b) extracts the fifth column (refset Id)
and (c) returns the unique members
tail +2 der2_sRefset_SimpleMapSnapshot_INT_20120731.txt | cut -f 5 | sort -u
5.3.6 How to Retrieve the Members of a Simple Map Reference Set
5.3.6.1 SNAPSHOT Release Type
The following SQL query will retrieve all active members of a Simple Map Reference Set
from SNAPSHOT release
SELECT referencedComponentId, mapTarget
FROM simplemap_ss WHERE active = 1
AND refsetId = (refset sctid);
As an example, the query:
SELECT referencedComponentId, mapTarget
FROM simplemap_ss WHERE active = 1
AND refsetId = 446608001 order by mapTarget
would retrieve all of the entries in the 446608001 | ICD-O simple map reference set | in ascending ICD-O
code order
5.3.6.2 FULL Release Type
The following SQL query will retrieve active members of a Simple Reference Set from a
FULL release
SELECT referencedComponentId, mapTarget
FROM simplemap_full mrs,
(SELECT id, MAX(effectiveTime) AS effectiveTime FROM simplemap_full WHERE refsetId = (refset
sctid) GROUP BY id) as mrs_keys
WHERE mrs.id = mrs_keys.id AND mrs.effectiveTime = mrs_keys.effectiveTime AND active = 1;
As an example, the query:
SELECT referencedComponentId, mapTarget
FROM simplemap_full mrs,
(SELECT id, MAX(effectiveTime) AS effectiveTime FROM simplemap_full WHERE refsetId =
900000000000498005 GROUP BY id) as mrs_keys
В© 2002-2013 International Health Terminology Standards Development Organisation CVR #: 30363434
60 | SNOMED CT SNOMED CT How To Guides July 2013
WHERE mrs.id = mrs_keys.id AND mrs.effectiveTime = mrs_keys.effectiveTime AND active = 1 order
by mapTarget asc
would retrieve all of the entries in the 900000000000498005 | SNOMED RT ID simple map |.
5.3.6.3 Command Line Utilities
The following command (a) drops the header line, (b) extracts the third, fifth and sixth and
seventh columns (active, refsetId and referencedComponentId, mapTarget) (c) filters for active members
of the reference set, (d) extracts the referenced component and corresponding target:
tail +2 der2_sRefset_SimpleMapSnapshot_INT_20120731.txt | cut -f 3,5,6,7 | grep ^1\\\t(refset SCTID)\\\t
| cut -f 3,4 | sort -k2 -t $'\t'
As an example, the command:
tail +2 der2_sRefset_SimpleMapSnapshot_INT_20120731.txt | cut -f 3,5,6,7 | grep
^1\\\t900000000000498005\\\t | cut -f 3,4 | sort -k2 -t $'\t'
retrieves all of the entries in the 900000000000498005 | SNOMED RT ID simple map |.
5.3.7 How to Determine What a Given SCTID Maps To in a simple map
5.3.7.1 SNAPSHOT Release Type
The following query selects all entries for the supplied reference set and component:
SELECT mapTarget
FROM simplemap_ss WHERE active = 1
AND refsetId = (refset sctid) AND referencedComponentId = (source SCTID)
As an example:
SELECT mapTarget
FROM simplemap_ss WHERE active = 1
AND refsetId = 900000000000498005 AND referencedComponentId = 255296002
Selects all map targets for 255296002 | Wedge | in the 900000000000498005 | SNOMED RT ID simple
map | reference set. This returns one result in the 20120731 release - A-0045D
5.3.7.1.1 Full Release Type
The following query performs the same function for a FULL release of a simple map reference
set:
SELECT mapTarget
FROM simplemap_full mrs,
(SELECT id, MAX(effectiveTime) AS effectiveTime FROM simplemap_full WHERE refsetId = (refset
sctid) GROUP BY id) as mrs_keys
WHERE mrs.id = mrs_keys.id AND mrs.effectiveTime = mrs_keys.effectiveTime AND active = 1
AND referencedComponentId = (source SCTID);
As an example:
SELECT mapTarget
FROM simplemap_full mrs,
(SELECT id, MAX(effectiveTime) AS effectiveTime FROM simplemap_full WHERE refsetId =
900000000000497000 GROUP BY id) as mrs_keys
WHERE mrs.id = mrs_keys.id AND mrs.effectiveTime = mrs_keys.effectiveTime AND active = 1
AND referencedComponentId = 116252005;
Selects all map targets for 116252005 | Sulfide | in the 900000000000497000 | CTV3 simple map |
reference set. This returns one result in the 20120731 release - XU41n.
В© 2002-2013 International Health Terminology Standards Development Organisation CVR #: 30363434
Reference Set Implementation How To Guides | 61
5.3.7.1.2 Command Line Utilities
The following command (a) drops the header line, (b) extracts the third, fifth and sixth and
seventh columns (active, refsetId and referencedComponentId, mapTarget) (c) filters for active members
of the reference set that have the given source (d) extracts the corresponding targets:
tail +2 der2_sRefset_SimpleMapSnapshot_INT_20120731.txt | cut -f 3,5,6,7 | grep ^1\\\t(refset
SCTID)\\\t(source SCTID)\\\t | cut -f 4
The following command line query produces the same result as the SNAPSHOT example above:
tail +2 der2_sRefset_SimpleMapSnapshot_INT_20120731.txt | cut -f 3,5,6,7 | grep
^1\\\t900000000000498005\\\t255296002\\\t | cut -f 4
5.3.8 How to determine which SCTIDs a target code maps to
5.3.8.1 SNAPSHOT Release Type
The following query selects all entries for the supplied reference set and component:
SELECT referencedComponentId
FROM simplemap_ss WHERE active = 1
AND refsetId = (refset sctid) AND mapTarget = '(target)'
As an example:
SELECT referencedComponentId from simplemap_ss
WHERE active = 1 AND refsetId = 446608001 and mapTarget ='C21.2'
Selects all SCTIDs that map to the ICD-O Code C21.2 (Cloacogenic zone) in the 446608001 | ICD-O
simple map reference set |, which, in the 20120731, returns:
367658009
367658009 | Entire cloacogenic area of rectum |
72981004
72981004 | Structure of cloacogenic area of rectum
|
362172007
362172007 | Entire transition zone of anal mucous
membrane |
41843001
41843001 | Structure of transition zone of anal
mucous membrane |
5.3.8.1.1 Full Release Type
The following query performs the same function for a FULL release of a simple map reference
set:
SELECT referencedComponentId
FROM simplemap_full mrs,
(SELECT id, MAX(effectiveTime) AS effectiveTime FROM simplemap_full WHERE refsetId = (refset
sctid) GROUP BY id) as mrs_keys
WHERE mrs.id = mrs_keys.id AND mrs.effectiveTime = mrs_keys.effectiveTime AND active = 1
AND mapTarget = '(target)';
and
SELECT referencedComponentId
FROM simplemap_full mrs,
(SELECT id, MAX(effectiveTime) AS effectiveTime FROM simplemap_full WHERE refsetId = 446608001
GROUP BY id) as mrs_keys
WHERE mrs.id = mrs_keys.id AND mrs.effectiveTime = mrs_keys.effectiveTime AND active = 1
AND mapTarget = 'C21.2';
returns the same results as the SNAPSHOT example.
В© 2002-2013 International Health Terminology Standards Development Organisation CVR #: 30363434
62 | SNOMED CT SNOMED CT How To Guides July 2013
5.3.8.1.2 Command Line Utilities
The following command (a) drops the header line, (b) extracts the third, fifth and sixth and
seventh columns (active, refsetId and referencedComponentId, mapTarget) (c) filters for active members
of the reference set (d) filters for the specified map target and (e) extracts the corresponding source sctids:
tail +2 der2_sRefset_SimpleMapSnapshot_INT_20120731.txt | cut -f 3,5,6,7 | grep ^1\\\t(refset SCTID)\\\t
| grep (target) | cut -f 3
The following command line query produces the same result as both of the previous examples above:
tail +2 der2_sRefset_SimpleMapSnapshot_INT_20120731.txt | cut -f 3,5,6,7 | grep ^1\\\t446608001\\\t |
grep C21\\.2 | cut -f 3
5.4 How to use a Complex Map Refset
5.4.1 Overview
This guide describes the steps you need to go through to use the SNOMED CT Complex Map Reference
Set.
5.4.2 Before You Start
5.4.2.1 Is This For You?
This guide is intended for people who are developing or configuring a software application
that needs to select preferred and alternate concept Descriptions and/or definitions in a specified language.
This guide is not intended for end users of software applications which make use of SNOMED CT reference
sets.
Note: SNOMED CT enabled applications may include a predefined set of references sets that can
be selected manually or are automatically applied according to context of use (e.g. to filter search
contents or to populate a drop-down list).They may also provide a user-friendly way to import additional
reference sets. The instructions for using these types of facilities are specific to the individual
application and are not covered by this guide. However, the basic techniques described in this guide
5.4.2.2 Disclaimer
The SQL and command line examples in this document are exactly that - examples. While
all of them have been tested and return the expected results within a reasonable amount of time, the
reader needs to realise that there are many approaches to addressing the questions the particular solutions
provided in this document are not likely to be the most efficient, let alone optimal for a given situation.
Readers are encouraged to use the examples as guides for constructing queries and other code to fit
their own circumstances.
5.4.2.3 Prerequsites
•
•
Familiarity with Release Format 2 (RF2) - This guide assumes that users are already
familiar with the Release Format 2 (RF2) and have already obtained and downloaded the appropriate
release files.
One of:
•
•
Familiarity with Unix command line utilities (e.g. cat, sort, cut, etc.)
Familiarity with SQL - The examples and instructions are are written to and have been tested using
MySQL, but should be readily translatable into other SQL dialects.
A running SQL database - This documents assumes that the user has access to a SQL server and
has created a database.
В© 2002-2013 International Health Terminology Standards Development Organisation CVR #: 30363434
Reference Set Implementation How To Guides | 63
Note: Some SNOMED CT release files may include text that is encoded in the UTF-8 format.
It is recommended that the default character set and sort order be set to "utf8" and "utf8-bin"
respectively.
5.4.3 Complex Map Refset Table Layout
A Complex Map Reference Set describes a rule based mapping between SNOMED CT resources and
external codes, instructions or other variations:
id
effective active
Time
moduleId refsetId e
rfe
rncedComponen
dIt mapGroup mapPriority mapRule mapAdvice mapTarget correlationId
00027
e8
f-7d4
5-568
a-c9d
8-680ce34d
e5f 20130731 1
449080006 447562003 83076007 1
1
IFA
86564006
| Renal
glycinuria,
de
Vries
type
(disorder)
|
IF
E72.0
RENAL
GLYCN
I URA
I,
DE
VRIES
TYPE
CHOOSE
E72.0 |
MAP
OF
SOURCE
CONCEPT
IS
CONTEXT
DEPENDENT
447561005
6cac594c-8e2
5-96e
9-8cb
2-a776b
f5b41 20130731 1
449080006 447562003 83076007 1
2
IFA
129594009
| Renal
glycinuria,
Kaiser
type
(disorder)
|
IF
E72.0
RENAL
GLYCN
I URA
I,
KAISER
TYPE
CHOOSE
E72.0 |
MAP
OF
SOURCE
CONCEPT
IS
CONTEXT
DEPENDENT
447561005
d9421071
0c-75
85
-68
61
-6e
74
-dde71b7c5c 20130731 1
449080006 447562003 83076007 1
3
OTHERWS
IE ALWAYS E72.5
TRUE E72.5
447561005
The entry above asserts that, according to the 449080006 | SNOMED CT to ICD-10 rule-based mapping
module | and the 447562003 | ICD-10 complex map reference set |, 83076007 | Disorder of glycine
metabolism | maps to:
•
•
•
ICD10 category E72.0 if 86564006 | Renal glycinuria, de Vries type | is asserted
ICD10 category E72.0 if 129594009 | Renal glycinuria, Kaiser type | is asserted
ICD10 category E72.5 in all other cases. 447561005
In all three cases above, the correlationId attribute asserts 447561005 | SNOMED CT source code to
target map code correlation not specified |
Depending on the specific refset, the referencedComponentId may reference to a SNOMED CT concept,
Relationship or Description. The meaning of the mapTarget is dependent on the particular reference set,
but the intent of the reference set structure is that it carries a concept, Description, Relationship or other
identifier from an external terminology. For the remainder of this document, we are going to assume that
В© 2002-2013 International Health Terminology Standards Development Organisation CVR #: 30363434
64 | SNOMED CT SNOMED CT How To Guides July 2013
referencedComponentId references a SNOMED CT concept and mapTarget contains a code from an
external code system.
5.4.4 How to load the Complex Map Refset Table into an SQL Database
5.4.4.1 Download the appropriate set of RF2 SNOMED CT Release Files
Instructions for obtaining RF2 Release files and determining which Release Type to be used
are listed in the links at the end of the section. The sections below have different instructions for loading
"Full" and "Snapshot" type of release. The "Full" instructions should be used if the user has downloaded
the "Full" release, has applied one or more "Delta" releases or if the user anticipates using "Delta"Release
Types to apply subsequent updates. The "Snapshot" instructions are (a) simpler and (b) are designed to
prevent duplicate and non-current record errors
5.4.4.2 Creating the SQL Tables
5.4.4.2.1 SNAPSHOT Release Type
The following SQL script will create a Complex Map Reference Set SNAPSHOT
table in MySQL. The key to a SNAPSHOT table is just its id, meaning that only one entry can be present
for each reference set assertion.
CREATE TABLE `complexmap_ss` (
`id` varchar(36) NOT NULL,
`effectiveTime` int NOT NULL,
`active` tinyint(1) NOT NULL,
`moduleId` bigint(20) NOT NULL,
`refsetId` bigint(20) NOT NULL,
`referencedComponentId` bigint(20) NOT NULL,
`mapGroup` int NOT NULL,
`mapPriority` int NOT NULL,
`mapRule` text,
`mapAdvice` text,
`mapTarget` varchar(255),
`correlationId` bigint(20),
`mapCategoryId` bigint(20),
PRIMARY KEY (`id`),
KEY `refset` (`refsetId`),
KEY `component` (`referencedComponentId`),
KEY `maptarget` (`mapTarget`)
);
5.4.4.2.2 FULL Release Type
Use the following script to create a Complex Map Reference FULL table in MySQL. The key
to the FULL table is both the id and effectiveTime, meaning that multiple entries can exist for the same
assertion and that Delta’s or local updates may be applied:
CREATE TABLE `complexmap_full` (
`id` varchar(36) NOT NULL,
`effectiveTime` int NOT NULL,
`active` tinyint(1) NOT NULL,
`moduleId` bigint(20) NOT NULL,
`refsetId` bigint(20) NOT NULL,
`referencedComponentId` bigint(20) NOT NULL,
`mapGroup` int NOT NULL,
`mapPriority` int NOT NULL,
`mapRule` text,
`mapAdvice` text,
`mapTarget` varchar(255),
`correlationId` bigint(20),
PRIMARY KEY (`id`, `effectiveTime`),
KEY `refset` (`refsetId`),
KEY `component` (`referencedComponentId`),
KEY `maptarget` (`mapTarget`)
);
В© 2002-2013 International Health Terminology Standards Development Organisation CVR #: 30363434
Reference Set Implementation How To Guides | 65
5.4.4.3 Loading the Complex Map Refset Data
5.4.4.3.1 SNAPSHOT Release Type
The following SQL snippet will load the July 31, 2013 SNOMED International
Release RF2 Complex Map Reference Set file into the SNAPSHOT table created above:
LOAD DATA local INFILE 'der2_iisssccRefset_ComplexMapSnapshot_INT_20130731.txt'
INTO TABLE complexmap_ss
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;
Note that the "INT" and "20130731" portions of the file name may vary depending on the particular release
and date. Also note that the examples in this section are derived from the
xder2_iisssccRefset_ComplexMapSnapshot_INT_20130731.txt as integrated into the July 2013 release.
5.4.4.3.2 FULL Release Type
The following SQL snippet will load the July 31, 2013 SNOMED International Release RF2
Complex file into the simplerefset FULL table created above above:
LOAD DATA local INFILE 'der2_iisssccRefset_ComplexMapFull_INT_20130731.txt'
INTO TABLE complexmap_full
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;
5.4.5 How to List the Available Complex Maps
5.4.5.1 Listing Complex Maps using SNOMED CT Terminology Files
A list of simple reference sets can be found as the leaf nodes of the 900000000000496009
| Simple map | node. The unique reference identifiers can also be retrieved from the simplerefset table
using:
SELECT distinct refsetId
FROM complexmap_ss where active = 1
for a SNAPSHOT release and
SELECT distinct refsetId
FROM complexmap_full mrs,
(SELECT id, MAX(effectiveTime) AS effectiveTime FROM complexmap_full GROUP BY id) as mrs_keys
WHERE mrs.id = mrs_keys.id AND mrs.effectiveTime = mrs_keys.effectiveTime AND active = 1;
for a FULL release
5.4.5.2 Listing Reference Sets using Command Line Utilities
The following command (a) drops the header line, (b) extracts the fifth column (refset Id)
and (c) returns the unique members
tail +2 der2_iisssccRefset_ComplexMapMCSnapshot_INT_20130731.txt | cut -f 5 | sort -u
5.4.6 How to Extract the Contents of a Complex Map
5.4.6.1 SNAPSHOT Release Type
The following query associates all map entries associated with a given complex map:
SELECT * FROM complexmap_mc_ss
WHERE refsetId = (complex map SCTID) AND active = 1
ORDER BY referencedComponentId, mapGroup, mapPriority
В© 2002-2013 International Health Terminology Standards Development Organisation CVR #: 30363434
66 | SNOMED CT SNOMED CT How To Guides July 2013
Note that this returns an ordered list, as the maps are applied in group and priority order.
As an example, the following query:
SELECT id, effectiveTime, referencedComponentId,
mapGroup, mapPriority, convert(mapRule using UTF8), convert(mapAdvice using UTF8),
mapTarget, correlationId, mapCategoryId FROM complexmap_mc_ss
WHERE refsetId = 447562003 AND active = 1
ORDER BY referencedComponentId, mapGroup, mapPriority LIMIT 5
returns:
id
effective referenced map
Time
ComponentId Group
map
Priority
mapRule mapAdvice map
Target
coorelation map
Id
CategoryId
217a9bea
0-b4e
5-499
8-a61
e-4d661e722b0 20130731 140004
1
1
IFA
90979004
| Chronic
tonsillitis
(disorder)
|
IF
J35.0
CHRONIC
TONSILLITIS
CHOOSE
J35.0 |
MAP OF
SOURCE
CONCEPT
IS
CONTEXT
DEPENDENT
447561005 447639009
69703317
4-11e
5-d4d
b-853
e-5
e2
f0
31
f09b 20130731 140004
1
2
IFA
232406009
| Chronic
pharyngeal
candidiasis
(disorder)
|
IF
B37.8
CHRONIC
PHARYNGEAL
CANDIDIASIS
CHOOSE
B37.8 |
MAP OF
SOURCE
CONCEPT
IS
CONTEXT
DEPENDENT
447561005 447639009
174a79318
-81f5
-cf79
-90
cf-81f048f7 20130731 140004
1
3
OTHERWISE ALWAYS J31.2
TRUE
J31.2
447561005 447637006
02dc8b54b-1ce5
b
-fb8-b2c9
8
-f4140426
ff 20130731 162004
1
1
TRUE
ALWAYS F31.1
F31.1
447561005 447637006
4586ab942-3ad5-e868
2
-fce-45
c7
f65
3fd7 20130731 181007
1
1
TRUE
ALWAYS J18.0
J18.0
447561005 447637006
5.4.6.2 FULL Release Type
The equivalent query for the FULL release is:
SELECT mrs.* FROM complexmap_mc_full mrs,
(SELECT id, MAX(effectiveTime) AS effectiveTime FROM complexmap_full
WHERE refsetId = (refset SCTID) GROUP BY id ) as mrs_keys
WHERE mrs.id = mrs_keys.id AND mrs.effectiveTime = mrs_keys.effectiveTime AND active = 1
ORDER BY referencedComponentId, mapGroup, mapPriority;
and
SELECT mrs.id, mrs.effectiveTime, referencedComponentId,
mapGroup, mapPriority, convert(mapRule using UTF8),
convert(mapAdvice using UTF8), mapTarget, correlationId, mapCategoryId
FROM complexmap_mc_full mrs,
В© 2002-2013 International Health Terminology Standards Development Organisation CVR #: 30363434
Reference Set Implementation How To Guides | 67
(SELECT id, MAX(effectiveTime) AS effectiveTime FROM complexmap_full
WHERE refsetId = 447562003 GROUP BY id ) as mrs_keys
WHERE mrs.id = mrs_keys.id AND mrs.effectiveTime = mrs_keys.effectiveTime AND active = 1
ORDER BY referencedComponentId, mapGroup, mapPriority;
returns the same results as above.
5.4.6.3 Command Line Utilities
The following script (a) extracts the active, refset and component through mapCategoryId
fields, (b) filters for active entries in the refset, (c) strips the active and refset identifiers and (d) sorts the
results numerically.
cut -f 3,5,6,7,8,9,10,11,12,13,14 der2_iisssccRefset_ComplexMapMCSnapshot_INT_20130731.txt | \
grep ^1\\\t(refset SCTID)\\\t | cut -f 3,4,5,6,7,8,9,10 | sort -n -k1 -k2 -k3 -t$'\t'
produces the same results as the queries above without the id and effectiveTime attributes.
As an example, the following query:
cut -f 3,5,6,7,8,9,10,11,12,13,14 der2_iisssccRefset_ComplexMapMCSnapshot_INT_20130731.txt | \
grep ^1\\\t447562003\\\t | cut -f 3,4,5,6,7,8,9,10 | sort -n -k1 -k2 -k3 -t$'\t' | head -5
Produces the same values as the SNAPSHOT sample query.
5.4.7 How to Extract the Map Rules for a Complex Map and Source SCTID
5.4.7.1 SNAPSHOT Release Type
Various filters can be applied to the base query above. As an example:
SELECT * FROM complexmap_mc_ss
WHERE refsetId = (complex map SCTID) AND active = 1
AND referencedComponentId = (source SCTID)
ORDER BY mapGroup, mapPriority;
Selects all entries with the given Source SCTID. Example:
SELECT mapGroup, mapPriority, convert(mapRule using UTF8),
convert(mapAdvice using UTF8), mapTarget, correlationId, mapCategoryId FROM
complexmap_mc_ss
WHERE refsetId = 447562003 AND active = 1
AND referencedComponentId = 32595002
ORDER BY mapGroup, mapPriority;
produces:
map Group
map Priority map Rule
map Advice map Target
correlation
Id
value Id
1
1
IF
G57.6
MORTON'S
METATARSALGIA
CHOOSE
G57.6 | MAP
OF SOURCE
CONCEPT IS
CONTEXT
DEPENDENT
447561005
447639009
IFA
30085007 |
Morton's
metatarsalgia
(disorder) |
В© 2002-2013 International Health Terminology Standards Development Organisation CVR #: 30363434
68 | SNOMED CT SNOMED CT How To Guides July 2013
map Group
map Priority map Rule
map Advice map Target
correlation
Id
value Id
1
2
IFA
47374004 |
Tarsal tunnel
syndrome
(disorder) |
IF TARSAL
G57.5
TUNNEL
SYNDROME
CHOOSE
G57.5 | MAP
OF SOURCE
CONCEPT IS
CONTEXT
DEPENDENT
447561005
447639009
1
3
IFA
85007004 |
Meralgia
paresthetica
(disorder) |
IF
G57.1
MERALGIA
PARESTHETICA
CHOOSE
G57.1 | MAP
OF SOURCE
CONCEPT IS
CONTEXT
DEPENDENT
447561005
447639009
1
4
IFA
193141005 |
Diabetic
mononeuritis
multiplex
(disorder) |
IF DIABETIC E14.4
MONONEURITIS
MULTIPLEX
CHOOSE
E14.4 | MAP
OF SOURCE
CONCEPT IS
CONTEXT
DEPENDENT
447561005
447639009
1
5
IFA
230605007 |
Lyme
mononeuritis
multiplex
(disorder) |
IF LYME
A69.2
MONONEURITIS
MULTIPLEX
CHOOSE
A69.2 | MAP
OF SOURCE
CONCEPT IS
CONTEXT
DEPENDENT
447561005
447639009
1
6
IFA
30292005 |
Mononeuritis
multiplex
(disorder) |
IF
G58.7
MONONEURITIS
MULTIPLEX
CHOOSE
G58.7 | MAP
OF SOURCE
CONCEPT IS
CONTEXT
DEPENDENT
447561005
447639009
1
7
IFA
58850003 |
Mononeuritis
of upper limb
(disorder) |
IF
G56.9
MONONEURITIS
OF UPPER
LIMB
CHOOSE
G56.9 | MAP
OF SOURCE
CONCEPT IS
CONTEXT
DEPENDENT
447561005
447639009
В© 2002-2013 International Health Terminology Standards Development Organisation CVR #: 30363434
Reference Set Implementation How To Guides | 69
map Group
map Priority map Rule
map Advice map Target
correlation
Id
value Id
1
8
IFA
62235007 |
Mononeuritis
of lower limb
(disorder) |
IF
G57.9
MONONEURITIS
OF LOWER
LIMB
CHOOSE
G57.9 | MAP
OF SOURCE
CONCEPT IS
CONTEXT
DEPENDENT
447561005
447639009
1
9
OTHERWISE ALWAYS
TRUE
G58.9
G58.9
447561005
447637006
2
1
IFA
193141005 |
Diabetic
mononeuritis
multiplex
(disorder) |
IF DIABETIC G59.0
MONONEURITIS
MULTIPLEX
CHOOSE
G59.0 | MAP
OF SOURCE
CONCEPT IS
CONTEXT
DEPENDENT
447561005
447639009
2
2
IFA
230605007 |
Lyme
mononeuritis
multiplex
(disorder) |
IF LYME
G59.8
MONONEURITIS
MULTIPLEX
CHOOSE
G59.8 | MAP
OF SOURCE
CONCEPT IS
CONTEXT
DEPENDENT
447561005
447639009
2
3
OTHERWISE MAP
TRUE
SOURCE
CONCEPT
CANNOT BE
CLASSIFIED
WITH
AVAILABLE
DATA
447561005
447638001
5.4.7.2 FULL Release Type
The query:
SELECT * FROM complexmap_mc_full mrs,
(SELECT id, MAX(effectiveTime) AS effectiveTime FROM complexmap_mc_full
WHERE refsetId = (refset SCTID) GROUP BY id ) as mrs_keys
WHERE mrs.id = mrs_keys.id AND mrs.effectiveTime = mrs_keys.effectiveTime AND active = 1
AND referencedComponentId = (source SCTID)
ORDER BY mapGroup, mapPriority;
Selects all entries with the given Source SCTID. Example:
SELECT * FROM complexmap_mc_full mrs,
(SELECT id, MAX(effectiveTime) AS effectiveTime FROM complexmap_mc_full
WHERE refsetId = 447562003 GROUP BY id ) as mrs_keys
WHERE mrs.id = mrs_keys.id AND mrs.effectiveTime = mrs_keys.effectiveTime AND active = 1
В© 2002-2013 International Health Terminology Standards Development Organisation CVR #: 30363434
70 | SNOMED CT SNOMED CT How To Guides July 2013
AND referencedComponentId = 32595002
ORDER BY mapGroup, mapPriority;
Produces the same results as The SNAPSHOT Release Type
5.4.7.3 Command Line Utilities
The following script (a) extracts the active, refset and component through mapCategoryId
fields, (b) filters for active entries in the refset, (c) strips the active and refset identifiers and (d) sorts the
results numerically.
cut -f 3,5,6,7,8,9,10,11,12,13,14 der2_iisssccRefset_ComplexMapMCSnapshot_INT_20130731.txt | \
grep ^1\\\t(refset SCTID)\\\t(source SCTID)\\\t | cut -f 4,5,6,7,8,9,10 | sort -n -k1 -k2 -k3 -t$'\t'
produces the same results as the queries above without the id and effectiveTime attributes.
As an example, the following query:
cut -f 3,5,6,7,8,9,10,11,12,13,14 der2_iisssccRefset_ComplexMapMCSnapshot_INT_20130731.txt | \
grep ^1\\\t447562003\\\t32595002\\\t | cut -f 4,5,6,7,8,9,10 | sort -n -k1 -k2 -k3 -t$'\t'
Produces the same values as the SNAPSHOT Sample Query.
5.4.8 How to Determine all the SCTIDs associated with a given Map Target
5.4.8.1 SNAPSHOT Release Type
The query below selects all of the Source SCTID's that are associated with a given map
target:
SELECT distinct(referencedComponentId) FROM complexmap_mc_ss
WHERE refsetId = (complex map SCTID) AND active = 1
AND mapTarget = '(target ID)';
Example:
SELECT distinct(referencedComponentId) FROM complexmap_mc_ss
WHERE refsetId = 447562003 AND active = 1
AND mapTarget = 'G57.1';
produces:
referencedComponentId
85007004
32595002
62235007
5.4.8.2 FULL Release Type
The query:
SELECT distinct(referencedComponentId) FROM complexmap_mc_full mrs,
(SELECT id, MAX(effectiveTime) AS effectiveTime FROM complexmap_mc_full
WHERE refsetId = (refset SCTID) GROUP BY id ) as mrs_keys
WHERE mrs.id = mrs_keys.id AND mrs.effectiveTime = mrs_keys.effectiveTime AND active = 1
AND mapTarget = '(target ID)';
Selects all entries with the given Source SCTID. Example:
SELECT distinct(referencedComponentId) FROM complexmap_mc_full mrs,
(SELECT id, MAX(effectiveTime) AS effectiveTime FROM complexmap_mc_full
WHERE refsetId = 447562003 GROUP BY id ) as mrs_keys
WHERE mrs.id = mrs_keys.id AND mrs.effectiveTime = mrs_keys.effectiveTime AND active = 1
AND mapTarget = 'G57.1';
В© 2002-2013 International Health Terminology Standards Development Organisation CVR #: 30363434
Reference Set Implementation How To Guides | 71
Produces:
referencedComponentId
85007004
32595002
62235007
5.4.8.3 Command Line Utilities
The following script (a) extracts the active, refset and component through mapCategoryId
fields, (b) filters for active entries in the refset, (c) strips the active and refset identifiers and (d) sorts the
results numerically.
cut -f 3,5,6,11 der2_iisssccRefset_ComplexMapMCSnapshot_INT_20130731.txt | col | \
grep ^1\\\t(refset SCTID)\\\t | grep (map Target)$ | cut -f 3
produces the same results as the queries above without the id and effectiveTime attributes.
As an example, the following query:
cut -f 3,5,6,11 der2_iisssccRefset_ComplexMapMCSnapshot_INT_20130731.txt | col | \
grep ^1\\\t447562003\\\t | grep G57\\\.1$ | cut -f 3
Produces:
85007004
32595002
62235007
В© 2002-2013 International Health Terminology Standards Development Organisation CVR #: 30363434
72 | SNOMED CT SNOMED CT How To Guides July 2013
Chapter
6
6 Clinical Information How To Guides
6.1 How to choose an SNOMED CT Extension strategy, should I
use post coordination or add new concepts to an extension?
Introduction
SNOMED CT implementations looking to go beyond precoordinated concepts expressivity are required
to add new concept definitions in local structures. The two most common approaches are to use
postcoordinated expressions or to add new concepts in a local extension. Each approach has its strengths
and weaknesses; the goal of the HowTo guide is to provide an overview of the differences between these
strategies.
6.1.1 Implementation based on postcoordinated expressions
Data entry is supported by structured forms that guide the user in creating postcoordinated expressions
that refine existing concepts in the terminology.These tools provide the user with approved postcoordination
options, constraining expressiveness to assure compliance with the SNOMED CT Concept Model.
Storing expressions directly in the clinical records is not recommended. The result of a transformation
depends on the available definitions of the concepts used in original expression. Comparability of
expressions depends on all expressions being derived from normal forms based on the same set of
definitions. Thus this would require all expressions in record entries to be reviewed and updated as
necessary each time a new release of SNOMED CT is installed.
There are also performance reasons, storing expressions in the clinical record requires comparison of
expressions at run time and is thus not fully optimised.
Support for postcoordinated expressions should be implemented using an Expressions Reference Table
(ERT). All expressions used are stored in a reference data table. An internal Identifier is used to reference
expressions rather than (or in addition to including them verbatim in the record entry).
6.1.2 Implementation based on local concept extensions
This approach is called Managed Content Addition (MCA). New concepts, Descriptions and Relationships
are added to an extension so that all content required can be recorded in a precoordinated form.
Users choose precoordinated concepts to express clinical information; if the concept needed is not
available the user makes an uncoded entry with a text Description, which will be reviewed by a local
modeler. The modelers add the concept to the local SNOMED CT Extension with a new set of defining
Relationships, or just add a new Description to an existing concept.
The extensions need to be integrated with new releases of SNOMED CT, and resulting from changes in
new releases modelling of the extension concepts may need to be revised.
6.1.3 Features comparison
postcoordination
Extension
В© 2002-2013 International Health Terminology Standards Development Organisation CVR #: 30363434
Clinical Information How To Guides | 73
Data entry
Structured forms, expression
discovery from text phrases
Text search
Subsumption and equivalency
testing
Normalisation process
DL Classifier
Editorial effort
Distributed, users do the
refinement. This is less costly.
Centralized, a team of modelers
creates new concepts. More
costly.
Upgrade to new SNOMED CT
releases
All expressions should be
checked for references to
outdated components
All concepts should be checked
for references to outdated
components
Risk of errors
The guided data entry tools
should limit the possibility of
errors. Any error is limited to one
use
Requires a Quality Assurance
process that monitors extension
concept definitions to prevent
redundancy, errors,
Description-definition mismatch,
etc.
Re-usability
Search terms may be linked to
Descriptions make the new
expressions, but in general
concept readily available for new
expressions are not looked up and text searches
re-used.
Text representation
A text representation can be
Descriptions provide a natural text
computed for each
representation for the concept
postcoordinated expression,
based on the parent concepts and
refinements
Accuracy of the representation
The user has the power to choose The user usually chooses a text
the exact attributes required for form and relies on the confidence
the clinical situation
that the concept is properly
modelled
Usability by clinical users
Depending on the implementation Users require no training, they use
used more or less training maybe natural language to search for
required.The UI constrains user's clinical terms
input
Representation of primitives (a
new drug, new organism, new
symptom, etc)
Expressiveness is limited to
refinements, not possible in
hierarchies with no attributes
Fully supported, in Primitive
concepts the definition is backed
by the Fully Specified Name
Sharing (Interoperability)
Expressions can be shared with
no changes
New concepts definitions can be
transformed to a postcoordinated
expression for sharing
В© 2002-2013 International Health Terminology Standards Development Organisation CVR #: 30363434
74 | SNOMED CT SNOMED CT How To Guides July 2013
6.2 How to Represent Clinical ideas Using SNOMED CT
6.2.1 Overview
This guide describes the steps you need to go through to {task objective summary}.
6.2.2 Before You Start
6.2.2.1 Is This For You?
This guide is intended for people who {primary audience}.
This guide is not intended for {audiences not supported}.
6.2.2.2 {other preliminary tasks if any}
6.2.3 {main task group}
6.2.3.1 {main task step 1}
6.2.3.2 {main task step 2}
6.3 How to Enter Data Represented Using SNOMED CT
6.3.1 Overview
This guide describes the steps you need to go through to {task objective summary}.
6.3.2 Before You Start
6.3.2.1 Is This For You?
This guide is intended for people who {primary audience}.
This guide is not intended for {audiences not supported}.
6.3.2.2 {other preliminary tasks if any}
6.3.3 {main task group}
6.3.3.1 {main task step 1}
6.3.3.2 {main task step 2}
6.4 How to Retrieve Data Represented Using SNOMED CT
6.4.1 Overview
This guide describes the steps you need to go through to {task objective summary}.
В© 2002-2013 International Health Terminology Standards Development Organisation CVR #: 30363434
Clinical Information How To Guides | 75
6.4.2 Before You Start
6.4.2.1 Is This For You?
This guide is intended for people who {primary audience}.
This guide is not intended for {audiences not supported}.
6.4.2.2 {other preliminary tasks if any}
6.4.3 {main task group}
6.4.3.1 {main task step 1}
6.4.3.2 {main task step 2}
6.5 How to Communicate Data Represented Using SNOMED CT
6.5.1 Overview
This guide describes the steps you need to go through to {task objective summary}.
6.5.2 Before You Start
6.5.2.1 Is This For You?
This guide is intended for people who {primary audience}.
This guide is not intended for {audiences not supported}.
6.5.2.2 {other preliminary tasks if any}
6.5.3 {main task group}
6.5.3.1 {main task step 1}
6.5.3.2 {main task step 2}
В© 2002-2013 International Health Terminology Standards Development Organisation CVR #: 30363434
76 | SNOMED CT SNOMED CT How To Guides July 2013
Chapter
7
7 Referencing and Commenting
This document contains a way to reference topics a way that is not dependent on changes to the structure
of the document as new versions are released including additional topics. These references are web
addresses that will point to the latest version of and topic in the document.
If you are using the PDF version of the document there are three icons to the right of each title which
provide useful information and relevant links.
•
The
icon indicates the status of the topic (see Status).
•
The
icon provides a link to the web address to accessing and referencing this topic online.
Please use these reference to identify or share references to the topic as section and page numbers
change between versions.
•
The
icon links direct to a page where you can submit comments or error report about
this topic.The comment tracker is an online resource that requires you to login to an IHTSDO CollabNet.
If you do not have an account, there is an option to create an account available on the login page.
If you are using the online web version of this document then there is a single bookmark icon
which, when clicked, opens a small form with an easy copy and paste option for access to the topic
reference and button to click to take you direct to the comment tracker.
В© 2002-2013 International Health Terminology Standards Development Organisation CVR #: 30363434
Документ
Категория
Без категории
Просмотров
56
Размер файла
859 Кб
Теги
1/--страниц
Пожаловаться на содержимое документа