Search



Page tree

  

Composite subtype hierarchy views enable selection of supertypes and subtypes of specified concepts accompanied by human-readable terms for each the selected concepts. The hierarchy views include  supertype parentssubtype childrensupertype ancestors and subtype descendants of specified concepts. More specialized views are also included to list the proximal primitive parents of a specified concepts and to list concepts that share a specified proximal primitive parent. All these views use include a human-readable term (either the fully specified name or the preferred synonym) for each concept listed in the output1

Supertype Parent and Subtype Child Views

For each snapshot view the SNOMED CT example database includes two views that select the supertype parents of a specified concept and two views that select the subtype children of a specified concept. These views select the id and either the fully specified name or preferred synonym of each parent or child concept. The characteristics of each of these views are shown in  Table 4.8.3-1 and a general template for the SQL definitions of these views is shown in  Template 4.8.3-1. To create each of the views named in the table, the placeholders for {termtype} need to be replaced with values in the Specific Settings column of the table. 

Example 4.8.3-1 demonstrates the use of these views to select the id and preferred term for a specified concept and all of its supertype parents and subtype children.

Table 4.8.3-1: Composite Views of Supertype Parents and Subtype Children

Name2

DescriptionSpecific Settings
{termtype}
snap_rel_parent_fsnSelects the id and fully specified name of each supertype parent of a concept specified by conceptId.fsn
snap_rel_parent_prefSelects the id and preferred synonym of each supertype parent of a concept specified by conceptId.pref

snap_rel_child_fsn

Selects the id and fully specified name of each subtype child of a concept specified by conceptId.fsn

snap_rel_child_pref

Selects the id and preferred synonym of each subtype child of a concept specified by conceptId.

pref

Template 4.8.3-1: SQL Templates for Composite Views of Supertype Parents and Subtype Children

-- Supertype Parent View
CREATE VIEW `snap_rel_parent_{termtype}` AS
    SELECT `r`.`targetId` AS `id`, `d`.`term` AS `term`, `r`.`sourceId` AS `conceptId`
	FROM `snap_relationship` `r` JOIN `snap_{termtype}` `d` ON `r`.`targetId` = `d`.`conceptId`
    WHERE `r`.`active` = 1  AND `r`.`typeId` = 116680003;
-- Subtype Child View: Differences are `sourceId` changed to `targetId` and `targetId` changed to `sourceId` as shown below
CREATE VIEW `snap_rel_child_{termtype}` AS
    SELECT `r`.`sourceId` AS `id`, `d`.`term` AS `term`, `r`.`destinationId` AS `conceptId`
	FROM `snap_relationship` `r` JOIN `snap_{termtype}` `d` ON `r`.`sourceId` = `d`.`conceptId`
    WHERE `r`.`active` = 1  AND `r`.`typeId` = 116680003;


Example 4.8.3-1: Selecting Supertype Parents and Subtype Children

SQL Query
Select "Concept", conceptid, term from snap_pref where conceptId=6025007
UNION
Select "Supertype Parent", id, term from snap_rel_parent_pref where conceptId=6025007
UNION
Select "Subtype Child", id, term from snap_rel_child_pref where conceptId=6025007;
Result
Conceptidterm
Concept6025007

Laparoscopic appendectomy

Supertype Parent51316009

Laparoscopic procedure

Supertype Parent80146002Appendectomy
Supertype Parent264274002

Endoscopic operation

Supertype Parent440588003

Endoscopic procedure on appendix

Subtype Child174041007

Laparoscopic emergency appendectomy

Subtype Child307581005

Laparoscopic interval appendectomy

Subtype Child708876004

Robot assisted laparoscopic appendectomy

Transitive Closure Views of Supertype Ancestors and Subtype Descendants

For each snapshot view the SNOMED CT example database includes two views that select the  supertype ancestors of a specified concept and two views that select the  subtype descendants of a specified concept. These views select the id and either the fully specified name or preferred synonym of each ancestor or descendant concept. The characteristics of each of these views are shown in  Table 4.8.3-2 and a general template for the SQL definitions of these views is shown in  Template 4.8.3-2. To create each of the views named in the table, the placeholders for {termtype} need to be replaced with values in the Specific Settings column of the table. 

Example 4.8.3-2 demonstrates the use of these views to select the id and preferred term for a specified concept and all of its supertype ancestors and subtype descendants.

Table 4.8.3-2: Transitive Closure Views of Supertype Ancestors and Subtype Descendants

Name3

DescriptionSpecific Settings
{termtype}
snap_tc_ancestor_fsn
Selects the id and fully specified name of each supertype ancestor of a concept specified by conceptId. fsn
snap_tc_ancestor_pref
Selects the id and preferred synonym of each supertype ancestor of a concept specified by conceptId.

pref

snap_tc_descendant_fsn
Selects the id and fully specified name of each subtype descendant of a concept specified by conceptId. fsn
snap_tc_descendant_pref
Selects the id and preferred synonym of each subtype descendant of a concept specified by conceptId.

pref

Template 4.8.3-2: SQL Templates for Composite Views of Supertype Ancestors and Subtype Descendants

-- Supertype Ancestor View: Differences are `sourceId` changed to `targetId` and `targetId` changed to `sourceId` as shown below
CREATE VIEW `snap_tc_ancestor_{termtype}` AS
(SELECT `r`.`supertypeId` `id`,`d`.`term` `term`,`r`.`subtypeId` `conceptId`
    FROM  `snap_transclose` `r`
	JOIN `snap_{termtype}` `d` ON (`r`.`supertypeId` = `d`.`conceptId`));


--- Subtype Descendant View
CREATE VIEW `snap_tc_descendant_{termtype}` AS
(SELECT `r`.`subtypeId` `id`,`d`.`term` `term`,`r`.`supertypeId` `conceptId`
    FROM  `snap_transclose` `r`
    JOIN `snap_{termtype}` `d` ON (`r`.`subtypeId` = `d`.`conceptId`));

Example 4.8.3-2: Selecting Supertype Ancestors and Subtype Descendants

SQL Query
Select "Concept", conceptid, term from snap_pref where conceptId=16001004
UNION
Select "Ancestor", id, term from snap_tc_ancestor_pref where conceptId=16001004
UNION
Select "Descendant", id, term from snap_tc_descendant_pref where conceptId=16001004;
Result
ConceptconceptIdterm
Concept16001004Otalgia
Ancestor22253000Pain
Ancestor102957003Neurological finding
Ancestor106147001Sensory nervous system finding
Ancestor118234003Finding by site
Ancestor118236001Ear and auditory finding
Ancestor118254002Finding of head and neck region
Ancestor138875005SNOMED CT Concept
Ancestor247234006Ear finding
Ancestor276435006Pain / sensation finding
Ancestor279001004Pain finding at anatomical site
Ancestor297268004Ear, nose and throat finding
Ancestor301354004Pain of ear structure
Ancestor301857004Finding of body region
Ancestor404684003Clinical finding
Ancestor406122000Head finding
Ancestor699697007Finding of sensation by site
Descendant12336008Referred otalgia
Descendant74123003Otogenic otalgia
Descendant162356005Earache symptoms
Descendant162359003Bilateral earache
Descendant430879002Posterior auricular pain
Descendant1084561000119106Bilateral referred otalgia of ears
Descendant1089561000119107Referred otalgia of left ear
Descendant1092171000119100Referred otalgia of right ear

Proximal Primitive Parent Views

For each snapshot view the SNOMED CT example database includes two views that select the  proximal primitive parents of a specified concept and two views that select the  subtype children of a specified concept. These views select the id and either the fully specified name or preferred synonym of each parent or child concept. The characteristics of each of these views are shown in  Table 4.8.3-3 and a general template for the SQL definitions of these views is shown in  Template 4.8.3-3. To create each of the views named in the table, the placeholders for {viewtype} need to be replaced with values in the Specific Settings column of the table. 

Example 4.8.3-3 demonstrates the use of these views to select the id and preferred term for a specified concept and its proximal primitive parentsExample 4.8.3-4 demonstrates the use of these views to select the id and preferred term for a specified concept and all the concepts that have this concept as a proximal primitive parent.

Table 4.8.3-3: Views of Proximal Primitive Supertype Ancestors and Concepts with a Specific Proximal Primitive Ancestor

Name3

DescriptionSpecific Settings
{viewtype}

snap_pp_parent_fsn

Selects the id and fully specified name of each proximal primitive parent of a concept specified by conceptId.fsn

snap_pp_parent_pref

Selects the id and preferred synonym of each proximal primitive parent of a concept specified by conceptId.pref

snap_pp_child_fsn

Selects the id and fully specified name of each concept with a proximal primitive parent specified by conceptId.

fsn

snap_pp_child_pref

Selects the id and preferred synonym of each concept with a proximal primitive parent specified by conceptId.

pref

Template 4.8.3-3: SQL Templates for Proximal Primitive Supertype Views

-- Proximal primitive parents of a specified concept
CREATE VIEW `snap_pp_parent_{viewtype}` AS
(SELECT `r`.`supertypeId` `id`,`d`.`term` `term`,`r`.`subtypeId` `conceptId`
    FROM  `snap_proximal_primitives` `r`
    JOIN `snap_{viewtype}` `d` ON (`r`.`supertypeId` = `d`.`conceptId`));


-- Concepts with a specified proximal primitive parent concept
CREATE VIEW `snap_pp_child_{viewtype}` AS
(SELECT `r`.`subtypeId` `id`,`d`.`term` `term`,`r`.`supertypeId` `conceptId`
    FROM  `snap_proximal_primitives` `r`
    JOIN `snap_{viewtype}` `d` ON (`r`.`subtypeId` = `d`.`conceptId`));

Example 4.8.3-3: Selecting Proximal Primitive Parents of a Concept

SQL Query
--
Select "Concept", conceptid, term from snap_pref where conceptId=21522001
UNION
Select "Proximal Primitive Parent", id, term from snap_pp_parent_pref where conceptId=21522001;
Result
ConceptconceptIdterm
Concept21522001Abdominal pain
Proximal Primitive Parent22253000Pain

Example 4.8.3-4: Selecting Concepts with a Specified Proximal Primitive Parent


Select "Concept", conceptid, term from snap_pref where conceptId=22253000
UNION
Select "Concept with PP-Parent: 21522001|Pain|", id, term from snap_pp_child_pref where conceptId=22253000;
Result
ConceptconceptIdterm
Concept22253000Pain
Concept with PP-Parent: 21522001 |Pain|4448006Allergic headache
Concept with PP-Parent: 21522001 |Pain|4568003Retrosternal pain
Concept with PP-Parent: 21522001 |Pain|6561007Pain in urethra
Concept with PP-Parent: 21522001 |Pain|10601006Pain in lower limb
Concept with PP-Parent: 21522001 |Pain|12584003Bone pain
Concept with PP-Parent: 21522001 |Pain|15803009Bladder pain
Concept with PP-Parent: 21522001 |Pain|16513000Postcordotomy pain
Concept with PP-Parent: 21522001 |Pain|18876004Pain in finger
Concept with PP-Parent: 21522001 |Pain|20793008Scapulalgia
Concept with PP-Parent: 21522001 |Pain|21522001Abdominal pain
Concept with PP-Parent: 21522001 |Pain|21545007Tenalgia
Concept with PP-Parent: 21522001 |Pain|29857009Chest pain
Concept with PP-Parent: 21522001 |Pain|30473006Pain in pelvis
Concept with PP-Parent: 21522001 |Pain|30989003Knee pain
... total of 240 rows returned ...

.


Footnotes
Ref Notes
1 The terms are displayed by using the Composite Description Views described in the previous section.
2 The prefix snap is replaced by snap1 or snap2 for retrospective views.
3 Transitive closure and proximal primitive views are only available for the current snapshot. [ a b ]


Feedback
  • No labels