OHDSI Home | Forums | Wiki | Github

Concepts found in concept_relationship but not concept_ancestor

Vocabulary Users
#1

I’m attempting to create a SQL Server query that takes a condition concept and returns the most relevant SNOMED concept(s). I’d like to use the concept ancestor table, but quite a few concept ids are not found even though they exist in concept relationship. For example 37201430 is an ICD10CM concept id for S03.01XA. I can follow the “Is a” relationship up two levels and get two concept ids (4123293 & 375415), but without the concept ancestor table it’s a dirtier query and I don’t know which resulting concept id is more relevant programmatically. We should have the latest vocabulary, Is there an updated version of concept ancestor that I can download?

#2

If you are doing this for an ETL, then you should use the ‘Maps to’
relationship. Here is a query used to create an augmented source to
concept map for v5 that has both the source and target concept ids. It is
limited to vocabularies we typically map from.

CREATE OR REPLACE view source_to_concept_map_5
( source_vocabulary_id, source_concept_id, source_code,
source_code_description, source_domain, source_standard_concept
, target_concept_id, target_vocabulary_id, target_concept_name,
target_domain, target_standard_concept
)
AS
SELECT s.vocabulary_id AS source_vocabulary_id
, s.concept_id AS source_concept_id
, s.concept_code AS source_code
, s.concept_name AS source_code_description
, s.domain_id AS source_domain
, s.standard_concept AS source_standard_concept
, COALESCE(t.concept_id, 0 ) AS target_concept_id
, COALESCE(t.vocabulary_id, ‘None’ ) AS target_vocabulary_id
, COALESCE(t.concept_name, ‘No matching concept’ ) AS
target_concept_name
, t.domain_id AS target_domain
, t.standard_concept AS target_standard_concept
FROM concept s
LEFT OUTER JOIN concept_relationship rel ON concept_id_1 = s.concept_id
AND relationship_id = ‘Maps to’
AND rel.invalid_reason IS NULL
LEFT OUTER JOIN concept t ON t.standard_concept = ‘S’ AND t.concept_id =
concept_id_2
WHERE s.vocabulary_id IN( ‘ICD9CM’, ‘ICD9Proc’, ‘CPT4’, ‘HCPCS’,
‘ICD10CM’, ‘LOINC’, ‘NDC’, ‘GPI’, ‘ICD10PCS’, ‘SNOMED’ ) ;

#3

Thanks Don. That works better than my query, however it still misses some concepts that need to look higher up the tree for a SNOMED match. For the S03.01XA example, here are the results of the query:

source_vocabulary_id	source_concept_id	source_code	source_code_description				source_domain		source_standard_concept	target_concept_id	target_vocabulary_id	target_concept_name	target_domain	target_standard_concept
ICD10CM			37201430		S03.01XA	Dislocation of jaw, right side, initial encounter	Condition				0			None			No matching concept	NULL		NULL
#4

This code does return SNOMED codes and I can tell that 302964004 (S03.0) is a closer hierarchical match than 82271004 (S03). I’m not sure the best way to extract the best match. Perhaps longest c2_concept_code ,S03.0 vs. S03, (not shown) would work.

SELECT *
  FROM (
		SELECT cr.concept_id_1
			 , c1_concept_code = c1.concept_code
			 , c1_concept_name = c1.concept_name
			 , cr.relationship_id
			 , cr.concept_id_2
			 , c2_concept_code = c2.concept_code
			 , c2_concept_name = c2.concept_name
		  FROM [dbo].[concept_relationship] cr
		 INNER JOIN [dbo].[concept] c1
			ON c1.concept_id = cr.concept_id_1
		 INNER JOIN [dbo].[concept] c2
			ON c2.concept_id = cr.concept_id_2
	  ) crc1
INNER JOIN (
		SELECT cr.concept_id_1
			 , cr.relationship_id
			 , cr.concept_id_2
			 , c2_concept_code = c2.concept_code
			 , c2_concept_name = c2.concept_name
			 , c2.vocabulary_id
		  FROM [dbo].[concept_relationship] cr
		 INNER JOIN [dbo].[concept] c2
			ON c2.concept_id = cr.concept_id_2
		  ) crc2
	ON crc1.concept_id_2 = crc2.concept_id_1
   AND crc1.relationship_id IN ('Maps to', 'Is a')
   AND crc2.relationship_id IN ('Maps to', 'Is a')
 WHERE crc1.concept_id_1 = 37201430

concept_id_1	relationship_id	concept_id_2	c2_concept_code	c2_concept_name
10391		Is a		1572385		S03		Dislocation and sprain of joints and ligaments of head
10391		Maps to		4123293		302964004	Dislocation of temporomandibular joint
1572385		Maps to		375415		82271004	Injury of head
37201429	Is a		1572385		S03		Dislocation and sprain of joints and ligaments of head
37201429	Is a		10391		S03.0		Dislocation of jaw
#5

Hi, @WustlRob
we are aware the issue that “S03.01XA” doesn’t have mapping.
We’ll update the vocabulary soon

t