如何从Snomed Postgres SQL数据库中查找关系 [英] How to find relationship from Snomed Postgres Sql Database

问题描述

问题陈述：

从Snomed CT数据库中提取所有父母，祖父母，子女和孙子

说明：

我正在尝试在本地机器上设置经过命名的数据库以提取关系（所有父母和孩子）（使用concept_id）。

我从

我非常感谢任何帮助或建议。

解决方案

根据 NHS CT浏览器可能无法从任何地方访问，它93880001有三个父母：

• 肺恶性肿瘤（疾病）


• 胸腔内原发性恶性肿瘤（疾病）


• 呼吸道原发性恶性肿瘤（疾病）

和31个孩子：

• 肺实质（癌）癌


• 肺上皮样血管性血管内皮瘤（疾病）


• 非霍奇金氏肺淋巴瘤（疾病）


• 非小细胞肺癌（疾病）


• 依此类推...

求高的方法较低的层次结构是使用 relationship_f.sourceid 和 relationship_f.destinationid 。但是，原始表不是用户友好的，所以我建议提出一些意见。我已经从

首先，我们创建一个包含概念ID和首选名称的视图：

 创建视图conceptpreferredname为
选择不同的c.id conceptId，d.term首选名称，d.id descriptionId 
 FROM postgres.snomedct.concept_f c 
内部联接postgres.snomedct.description_f d 
 ON c.id = d.conceptId 
 AND d.active ='1'
 AND d.typeId ='900000000000013009'
内部联接postgres.snomedct.langrefset_f l 
 ON d.id = l.referencedComponentId 
 AND l.active ='1'
 AND l .refSetId ='900000000000508004'-GB英文
 AND l.acceptabilityId ='900000000000548007'; 
  

然后我们看一下关系：

  CREATE VIEW关系与名称AS 
 SELECT ID，生效时间，活动状态，
 moduleId，cpn1.preferredName moduleIdName，
 sourceId，cpn2.preferredName sourceIdName，
 destinationId，cpn3.preferredName destinationIdName，
 RelationshipGroup，
 typeId，cpn4.preferredName typeIdName，
 characterTypeId，cpn5.preferredName characterTypeIdName，
修饰符ID，cpn6.preferredName ModifyIdName $ b $来自postgres.snomedct.relationship_f关系的b，
 conceptpreferredname cpn1，
 conceptpreferredname cpn2，
 conceptpreferredname cpn3，
 conceptpreferredname cpn4，
 conceptpreferredname cpn5，
 conceptpreferredname cpn6 
 WHERE moduleId = cpn1.conceptId 
 AND sourceId = cpn2.conceptId 
 AND destinationId = cpn3.conceptId 
 AND typeId = cpn4.conceptId 
 AND characterTypeId = cpn 5.conceptId 
 AND修饰符= cpn6.conceptId; 
  

因此，要打印出三个父级概念的名称和ID的查询将是：

 选择* 
从Relationshipwithnames r 
其中r.sourceId ='93880001'
和r.active ='1'
和r.typeIdName ='是'; 
  

请注意，这实际上返回了三个额外的概念，在线SNOMED浏览器认为它们已过时。我不确定为什么。

要打印子概念的名称和ID，请用<$替换 destinationId c $ c> sourceId ：

  select * 
 from Relationshipwithnames r 
其中r.destinationId ='93880001'
和r.active ='1'
并且r.typeIdName ='是a'; 
  

请注意，这实际上返回了十六个额外的概念，在线SNOMED浏览器认为它们已过时。再次，我找不到可靠的方法从结果中仅排除这16个。

从这里开始，获取祖父母和孙子女的查询非常简单。

Problem Statement:

Extract all parents, grandparents, child, and grandchild from Snomed CT database

Description:

I am trying to set up the snomed database on my local box to extract relationships (all parents and child) for a particular concept (using concept_id).

I have downloaded snomed data from https://download.nlm.nih.gov/umls/kss/IHTSDO20190131/SnomedCT_InternationalRF2_PRODUCTION_20190131T120000Z.zip

Then I imported data into Postgres SQL DB using a script which I found here https://github.com/IHTSDO/snomed-database-loader/tree/master/PostgreSQL

But I didn't find any relationship between these tables so that I can fetch parents, grandparents, children and grandchildren for a particular concept id (I tried with lung cancer 93880001)

Following image contains table structure:

I really appreciate any help or suggestions.

解决方案

According to the NHS CT Browser, which may not be accessible from everywhere, 93880001 has three parents:

• Malignant tumor of lung (disorder)
• Primary malignant neoplasm of intrathoracic organs (disorder)
• Primary malignant neoplasm of respiratory tract (disorder)

and 31 children:

• Carcinoma of lung parenchyma (disorder)
• Epithelioid hemangioendothelioma of lung (disorder)
• Non-Hodgkin's lymphoma of lung (disorder)
• Non-small cell lung cancer (disorder)
• and so on...

The way to find higher and lower levels of the hierarchy is to use relationship_f.sourceid and relationship_f.destinationid. However, the raw tables are not user friendly so I would suggest making some views. I have taken the code from the Oracle .sql files in this GitHub repo.

First, we make a view with concept IDs and preferred names:

create view conceptpreferredname as
SELECT distinct c.id conceptId, d.term preferredName, d.id descriptionId
FROM postgres.snomedct.concept_f c
inner JOIN postgres.snomedct.description_f d
  ON c.id = d.conceptId
  AND d.active = '1'
  AND d.typeId = '900000000000013009'
inner JOIN postgres.snomedct.langrefset_f l
  ON d.id = l.referencedComponentId
  AND l.active = '1'
  AND l.refSetId = '900000000000508004'  -- GB English
  AND l.acceptabilityId = '900000000000548007';

Then we make a view of relationships:

CREATE VIEW relationshipwithnames AS
SELECT id, effectiveTime, active,
    moduleId, cpn1.preferredName moduleIdName,
    sourceId, cpn2.preferredName sourceIdName,
    destinationId, cpn3.preferredName destinationIdName,
    relationshipGroup,
    typeId, cpn4.preferredName typeIdName,
    characteristicTypeId, cpn5.preferredName characteristicTypeIdName,
    modifierId, cpn6.preferredName modifierIdName
from postgres.snomedct.relationship_f relationship,
    conceptpreferredname cpn1,
    conceptpreferredname cpn2,
    conceptpreferredname cpn3,
    conceptpreferredname cpn4,
    conceptpreferredname cpn5,
    conceptpreferredname cpn6
WHERE moduleId = cpn1.conceptId
AND sourceId = cpn2.conceptId
AND destinationId = cpn3.conceptId
AND typeId = cpn4.conceptId
AND characteristicTypeId = cpn5.conceptId
AND modifierId = cpn6.conceptId;

So a query to print out the names and ids of the three parent concepts would be:

select *
from relationshipwithnames r
where r.sourceId = '93880001'
and r.active = '1'
and r.typeIdName = 'Is a';

Note that this actually returns three extra concepts, which the online SNOMED browser thinks are obsolete. I am not sure why.

To print out the names and ids of child concepts, replace destinationId with sourceId:

select *
from relationshipwithnames r
where r.destinationId = '93880001'
and r.active = '1'
and r.typeIdName = 'Is a';

Note that this actually returns sixteen extra concepts, which the online SNOMED browser thinks are obsolete. Again, I cannot find a reliable way to exclude only these sixteen from the results.

From here, queries to get grandparents and grandchildren are straightforward.

这篇关于如何从Snomed Postgres SQL数据库中查找关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！