LOINC

Vocabulary_ID=6

The following describes the procedure to update the LOINC vocabulary  with the latest release of the source.

The load process is a complete refresh of data. All new vocabulary entieties will be added. All vocabulary entities that do not exist in the latest files set will be marked as deprecated.


1.1 Scripts Transfer

All scripts and files listed at the bottom of this page should be transferred to specially created directory (eg /data/LOINC).


 Script Name Description
06_create_schema.sql Create prestage-stage schema with tables and indexes
06_transform_row_relations.sql
 Convert and store in stage table concept_relationship_stage
06_load_to_prestage_concepts.ctl  Concepts control file
06_load_to_prestage_relations.ctl
 Relations control file
06_load_to_prestage_deprecated.ctl  Historical concepts control file
06_load_concepts.sql
 Load only new concepts into concept table, add invalid code information
06_load_relations.sql 
 Load only new relationships into relationship table, add invalid code information.
06_transform_row_concepts.sql   
 Convert and store in stage table concept_stage
06_transform_row_maps.sql Convert and store in stage table SOURCE_TO_CONCEPT_MAP_STAGE


1.2 Download data from source

            1.2.1 Download data from CMS source

                    The most current international release is located at the following URL: https://loinc.org/downloads/files/loinc-and-relma-complete-download/gotoCopyrightedFile

                    Note: Make sure you perform updates in chronological order. So, if you want to process a January and April release, execute the below steps for January, and after successful completion, for April.

                    Download the latest release file (e.g.LOINCTAB.zip and LOINC_V240_MULTI-AXIAL_HIERARCHY.zip)
                     Transfer downloaded zip file to /data/LOINC directory.

                     Latest production zip file is ~5MB in size

 

1.3 Build Database Schema

All tables will be located in the schema LOINC_<DateOfSourceFile> as following: LOINC_20120131
The following steps will create a schema and will grant to a schema owner appropriate permissions.


In addition to the schema, it will create tables to load raw data: LOINC as well as staging tables: 

CONCEPT_STAGE, CONCEPT_ANCESTOR_STAGE, 

 

CONCEPT_RELATIONSHIP_STAGE, 

 

SOURCE_TO_CONCEPT_MAP_STAGE



$ sqlplus System/<SystemPass>@DEV_VOCAB @06_create_schema.sql < LOINC_Schema> <Pass_LOINC_Schemar>

Format of  LOINC_Schema should be  LOINC_YYYYMMDD (e.g.  LOINC_20120914)


1.4 Raw data load into Oracle tables


 1.4.1 Extract files from the zip archive into the current directory

        $ unzip -u LOINCTAB.zip
                    $ unzip -u LOINC_V240_MULTI-AXIAL_HIERARCHY.zip 


1.4.2 Verify that the following 2 files have been created:

     LOINCDB.TXT               (Latest file size ~1MB)

     LOINC_MAP_TO.TXT     (Latest file size ~40KB)

     LOINC_V240_MULTI-AXIAL_HIERARCHY.CSV (Latest file size ~7MB)

   

            1.4.3    Load CMS raw data into Oracle. 

            Each sqlldr process will run for about 1min.

             $ sqlldr LOINC_20120914/myPass@DEV_VOCAB control=06_load_to_prestage_concepts.ctl


            $ sqlldr LOINC_20120914/myPass@DEV_VOCAB control=06_load_to_prestage_relations.ctl


            $ sqlldr LOINC_20120914/myPass@DEV_VOCAB control=06_load_to_prestage_deprecated.ctl


            1.4.5 Verify that files with extension .bad are empty, no records have been rejected.


            1.4.6 Copy data and intermediate files to backup area In this step, downloaded files will be archived to preserve source of the data. Discuss a location of backup area with your system administrator.         

                $ cp -Ru /data/LOINC/*.zip /data/backup_area ; cp -Ru /data/LOINC/*.log /data/backup_area


            1.4.7  Verify that number of records loaded is equivalent to prior production load

                     sqlplus LOINC_20120914/myPass@DEV_VOCAB

            Execute the following SQL commands:

    SELECT count(*) FROM LOINC;
            Current production row count is:     70,971 Rows


    SELECT count(*) FROM LOINC_49;
            Current production row count is:     77,722 Rows


    SELECT count(*) FROM LOINC_MAP_TO;
            Current production row count is:     2,449 Rows



1.5 Loading Staging Tables from raw


1.5.1 Convert and store in staging table concepts

Loaded from raw staged data RXNCONSO into the staged data CONCEPT_STAGE

In this step we will perform substitute GUID identifiers into numerical format.

 

$  sqlplus LOINC_20120914/myPass@DEV_VOCAB @06_transform_row_concepts.sql


1.5.2  Verify that number of records loaded is equivalent to prior production load

    In this step we will verify that number of active records in staging tables is valid prior to transfer data to historical DEV schema. 

    a. Number of Records in stage table

    b. Number of records in DEV schema not deleted (active, prior load production)

    c. How many records would be added to DEV schema table

            d. How many active DEV records will be marked for deletion


    In most cases  number of records in stage (a) should be greater than number of active records in production (b) 
    In rare occasion that might not be the case. It is responsibility of the developer running the scripts to verify that that is the the valid.



     $ sqlplus  LOINC_20120914/myPass@DEV_VOCAB

 Execute the following SQL commands:

SELECT '- Num Rec in stage' AS scr, COUNT (8) cnt
  FROM CONCEPT_STAGE c
 WHERE C.VOCABULARY_ID IN (06, 49)
UNION ALL
SELECT '- Num Rec in DEV not deleted' AS scr, COUNT (8) cnt
  FROM DEV.CONCEPT d
 WHERE d.VOCABULARY_ID IN (06, 49) AND NVL (d.INVALID_REASON, 'X') <> 'D'
UNION ALL
SELECT '- How many records would be new in DEV added' AS scr, COUNT (8) cnt
  FROM CONCEPT_STAGE c
 WHERE C.VOCABULARY_ID IN (06, 49)
       AND NOT EXISTS
              (SELECT 1
                 FROM DEV.CONCEPT d
                WHERE d.VOCABULARY_ID IN (06, 49) AND c.CONCEPT_CODE = D.CONCEPT_CODE)
UNION ALL
SELECT '- How many DEV active will be marked for deletion' AS scr,
       COUNT (8) cnt
  FROM DEV.CONCEPT d
 WHERE     d.VOCABULARY_ID IN (06, 49)
       AND NVL (d.INVALID_REASON, 'X') <> 'D'
       AND NOT EXISTS
              (SELECT 1
                 FROM CONCEPT_STAGE c
                WHERE c.VOCABULARY_ID IN (06, 49) AND c.CONCEPT_CODE = D.CONCEPT_CODE);

Current Result:
- Num Rec in stage                                                   99660
- Num Rec in DEV not deleted                                    98193
- How many records would be new in DEV added         1247
- How many DEV active will be marked for deletion          39



        1.5.3    Load new concepts into DEV schema concept table,mark deprecated concepts as deleted.

          Valid start date, valid end date and invalid reason are left empty for now.
          Transfer records from CONCEPT_STAGE to DEV.CONCEPT table


    $  sqlplus LOINC_20120914/myPass@DEV_VOCAB @06_load_concepts.sql


     1.5.4    Load new concept relationships stage, identify invalid codes  information 

               Loaded from the raw staged data RXNREL into the staged data CONCEPT_RELATIONSHIP_STAGE
               identify invalid code information    
 

          $  
sqlplus LOINC_20120914/myPass@DEV_VOCAB @06_transform_row_relations.sql

1.5.5  Verify that number of records loaded is equivalent to prior production load

         sqlplus LOINC_20120914/myPass@DEV_VOCAB

SELECT '- Num Rec in stage' AS scr, COUNT (8) cnt

  FROM CONCEPT_RELATIONSHIP_STAGE c

UNION ALL

SELECT '- Num Rec in DEV not deleted' AS scr, COUNT (8) cnt

  FROM DEV.CONCEPT_RELATIONSHIP d

 WHERE EXISTS

          (SELECT 1

             FROM DEV.CONCEPT c

            WHERE c.VOCABULARY_ID IN (06, 49) AND c.CONCEPT_ID = CONCEPT_ID_1)

       AND EXISTS

              (SELECT 1

                 FROM DEV.CONCEPT c

                WHERE c.VOCABULARY_ID IN (06, 49)

                      AND c.CONCEPT_ID = CONCEPT_ID_2)

       AND NVL (d.INVALID_REASON, 'X') <> 'D'

       AND D.RELATIONSHIP_ID IN (010, 144, 1, 135)

UNION ALL

SELECT '- How many records would be new in DEV added' AS scr, COUNT (8) cnt

  FROM CONCEPT_RELATIONSHIP_STAGE c

 WHERE NOT EXISTS

              (SELECT 1

                 FROM DEV.CONCEPT_RELATIONSHIP d

                WHERE     c.CONCEPT_ID_1 = d.CONCEPT_ID_1

                      AND c.CONCEPT_ID_2 = d.CONCEPT_ID_2

                      AND c.RELATIONSHIP_ID = d.RELATIONSHIP_ID)

UNION ALL

SELECT '- How many DEV active will be marked for deletion' AS scr,

       COUNT (8) cnt

  FROM DEV.CONCEPT_RELATIONSHIP d

 WHERE EXISTS

          (SELECT 1

             FROM DEV.CONCEPT c

            WHERE c.VOCABULARY_ID IN (06, 49) AND c.CONCEPT_ID = CONCEPT_ID_1)

       AND EXISTS

              (SELECT 1

                 FROM DEV.CONCEPT c

                WHERE c.VOCABULARY_ID IN (06, 49)

                      AND c.CONCEPT_ID = CONCEPT_ID_2)

       AND NVL (d.INVALID_REASON, 'X') <> 'D'

       AND NOT EXISTS

                  (SELECT 1

                     FROM CONCEPT_RELATIONSHIP_STAGE c

                    WHERE     c.CONCEPT_ID_1 = d.CONCEPT_ID_1

                          AND c.CONCEPT_ID_2 = d.CONCEPT_ID_2

                          AND c.RELATIONSHIP_ID = d.RELATIONSHIP_ID)

       AND D.RELATIONSHIP_ID IN (010, 144, 1, 135);                   

Current Result:

 - Num Rec in stage

159104

 - Num Rec in DEV not deleted

157978

 - How many records would be new in DEV added

1330

 - How many DEV active will be marked for deletion

204

1.5.6  
Load new concept relationships into DEV schema,mark deprecated concept relatuionships as deleted
         Transfer records from CONCEPT_RELATIONSHIP_STAGE to DEV.CONCEPT_RELATIONSHIP table       


     $ 
 sqlplus LOINC_20120914/myPass@DEV_VOCAB @06_load_relations.sql 

       1.5.4 Convert and store in staging table maps

Loaded from raw staged data RXNCONSO into the staged data SOURCE_TO_CONCEPT_MAP_STAGE


 

$  sqlplus LOINC_20120914/myPass@DEV_VOCAB @06_transform_row_maps.sql


1.5.7  Verify that number of records loaded is equivalent to prior production load

         In this step we will verify that number of active records in staging tables is valid prior to transfer data to historical DEV schema. 

    a. Number of Records in stage table

    b. Number of records in DEV schema not deleted (active, prior load production)

    c. How many records would be added to DEV schema table

            d. How many active DEV records will be marked for deletion


            In most cases  number of records in stage (a) should be greater than number of active records in production (b) 
            In rare occasion that might not be the case. It is responsibility of the developer running the scripts to verify that that is the the valid.



     $ sqlplus LOINC_20120914/myPass@DEV_VOCAB



        Execute the following SQL commands:
SELECT '- Num Rec in stage' AS scr, COUNT (8) cnt
  FROM SOURCE_TO_CONCEPT_MAP_STAGE c
 WHERE c.SOURCE_VOCABULARY_ID IN (06)
UNION ALL
SELECT '- Num Rec in DEV not deleted' AS scr, COUNT (8) cnt
  FROM DEV.SOURCE_TO_CONCEPT_MAP d
 WHERE     d.SOURCE_VOCABULARY_ID IN (06)
       AND D.TARGET_VOCABULARY_ID IN (06)
       AND NVL (d.INVALID_REASON, 'X') <> 'D'
UNION ALL
SELECT '- How many records would be new in DEV added' AS scr, COUNT (8) cnt
  FROM SOURCE_TO_CONCEPT_MAP_STAGE c
 WHERE c.SOURCE_VOCABULARY_ID IN (06) AND c.TARGET_VOCABULARY_ID IN (06)
       AND NOT EXISTS
                  (SELECT 1
                     FROM DEV.SOURCE_TO_CONCEPT_MAP d
                    WHERE     d.SOURCE_VOCABULARY_ID IN (06)
                          AND c.SOURCE_CODE = D.SOURCE_CODE
                          AND d.SOURCE_VOCABULARY_ID = c.SOURCE_VOCABULARY_ID
                          AND d.MAPPING_TYPE = c.MAPPING_TYPE
                          AND d.TARGET_CONCEPT_ID = c.TARGET_CONCEPT_ID
                          AND d.TARGET_VOCABULARY_ID = c.TARGET_VOCABULARY_ID)
UNION ALL
SELECT '- How many DEV active will be marked for deletion' AS scr,
       COUNT (8) cnt
  FROM DEV.SOURCE_TO_CONCEPT_MAP d
 WHERE     d.SOURCE_VOCABULARY_ID IN (06)
       AND D.TARGET_VOCABULARY_ID IN (06)
       AND NVL (d.INVALID_REASON, 'X') <> 'D'
       AND d.VALID_START_DATE <
              TO_DATE (
                 SUBSTR (USER, REGEXP_INSTR (USER, '_[[:digit:]]') + 1, 256),
                 'YYYYMMDD')
       AND NOT EXISTS
                  (SELECT 1
                     FROM SOURCE_TO_CONCEPT_MAP_STAGE c
                    WHERE     c.SOURCE_VOCABULARY_ID IN (06)
                          AND c.SOURCE_CODE = D.SOURCE_CODE
                          AND d.SOURCE_VOCABULARY_ID = c.SOURCE_VOCABULARY_ID
                          AND d.MAPPING_TYPE = c.MAPPING_TYPE
                          AND d.TARGET_CONCEPT_ID = c.TARGET_CONCEPT_ID
                          AND d.TARGET_VOCABULARY_ID = c.TARGET_VOCABULARY_ID)
       AND EXISTS
              (SELECT 1
                 FROM SOURCE_TO_CONCEPT_MAP_STAGE c
                WHERE     d.SOURCE_CODE = c.SOURCE_CODE
                      AND d.SOURCE_VOCABULARY_ID = c.SOURCE_VOCABULARY_ID
                      AND d.MAPPING_TYPE = c.MAPPING_TYPE
                      AND d.TARGET_VOCABULARY_ID = c.TARGET_VOCABULARY_ID);

                Current Result:
- Num Rec in stage                                                       72625
- Num Rec in DEV not deleted                                        71464
- How many records would be new in DEV added             3009
- How many DEV active will be marked for deletion          1848


    1.5.8   Load new maps into DEV schema concept table,mark deprecated concepts as deleted. Valid start date, valid end date and invalid reason are left empty for now.
          Transfer records from SOURCE_TO_CONCEPT_MAP_STAGE to DEV.SOURCE_TO_CONCEPT_MAP table

    $  sqlplus HCPCS_20120507/myPass@DEV_VOCAB @06_load_maps.sql




ċ
06_create_schema.sql
(7k)
Serg Vereshagin,
Oct 24, 2013, 4:46 AM
ċ
06_load_concepts.sql
(4k)
Serg Vereshagin,
Oct 24, 2013, 4:47 AM
ċ
06_load_maps.sql
(6k)
Serg Vereshagin,
Oct 24, 2013, 4:47 AM
ċ
06_load_relations.sql
(5k)
Serg Vereshagin,
Oct 24, 2013, 4:47 AM
ċ
06_load_to_prestage_concepts.ctl
(2k)
Serg Vereshagin,
Oct 24, 2013, 4:47 AM
ċ
06_load_to_prestage_deprecated.ctl
(1k)
Serg Vereshagin,
Oct 24, 2013, 4:47 AM
ċ
06_load_to_prestage_relations.ctl
(1k)
Serg Vereshagin,
Oct 24, 2013, 4:47 AM
ċ
06_transform_row_concepts.sql
(4k)
Serg Vereshagin,
Oct 24, 2013, 4:47 AM
ċ
06_transform_row_maps.sql
(4k)
Serg Vereshagin,
Oct 24, 2013, 4:47 AM
ċ
06_transform_row_relations.sql
(4k)
Serg Vereshagin,
Oct 24, 2013, 4:47 AM
Comments