LOINC Project

Goals

Methods

Collate LOINC with other Terminology Sources

Import all LOINC/RELMA tables into the data base management system that already has UMLS, MeSH, etc.

Using the Access Export feature one one table at a time.

Can't upload the main LOINC table (lovingly called the "Elephant" in C-NPU) because of some Oracle buffer alignment error. But I don't need that, because I can recreate the big LOINC table from its parts:

SELECT LOINC_NUM, 
       SYSTEM.PART AS SYSTEM, 
       SUPERSYSTEM.PART AS SUPERSYSTEM, 
       COMPONENT.PART AS COMPONENT, 
       COMPONENT_1.PART AS COMPONENT_1, 
       COMPONENT_2.PART AS COMPONENT_2, 
       COMPONENT_3.PART AS COMPONENT_3, 
       PROPERTY.PART AS PROPERTY, 
       SCALE.PART AS SCALE,
       TIMECORE.PART AS TIMECORE,
       l.*
  FROM LOINC_DETAIL_TYPE_1 l
  INNER JOIN LOINC_PART SYSTEM ON SYSTEM.PART_NUM = SYSTEMCORE_PN
  LEFT OUTER JOIN LOINC_PART SUPERSYSTEM ON SUPERSYSTEM.PART_NUM = SYSTEMSUPERSYSTEM_PN
  INNER JOIN LOINC_PART COMPONENT ON COMPONENT.PART_NUM = COMPONENT_PN
  LEFT OUTER JOIN LOINC_PART COMPONENT_1 ON COMPONENT_1.PART_NUM = COMPSUBPART1_PN
  LEFT OUTER JOIN LOINC_PART COMPONENT_2 ON COMPONENT_2.PART_NUM = COMPSUBPART2_PN
  LEFT OUTER JOIN LOINC_PART COMPONENT_3 ON COMPONENT_3.PART_NUM = COMPSUBPART3_PN
  INNER JOIN LOINC_PART PROPERTY ON PROPERTY.PART_NUM = PROPERTYMIXEDCASE_PN
  INNER JOIN LOINC_PART SCALE ON SCALE.PART_NUM = SCALE_PN
  INNER JOIN LOINC_PART TIMECORE ON TIMECORE.PART_NUM = TIMECORE_PN

Some example queries

SELECT --+FIRST_ROWS
       u.*, sc.*
  FROM UMLS.UNII_CONCEPT u
    INNER JOIN UMLS.MRCONSO C1 ON(lower(C1.STR) = lower(u.displayname) AND c1.LAT = 'ENG')
    INNER JOIN UMLS.MRCONSO C2 ON(C2.CUI = C1.CUI AND C2.SAB = 'SNOMEDCT')
    INNER JOIN UMLS.SCT_CONCEPT sc ON(sc.CONCEPTID = C2.CODE)

SELECT * FROM UMLS.MESH_ISA_CLOSURE m
    INNER JOIN UMLS.MESH_CONCEPT sc ON(sc.code = m.sourceCode)
    INNER JOIN UMLS.MESH_CONCEPT tc ON(tc.code = m.targetCode)
    WHERE tc.name = 'Penicillins'

See also OriginalNpu