Using LOINC codes in Leaf to search an OMOP clinical database

[artgoldberg]

Hi folks

We use Logical Observation Identifier Names and Codes (LOINC) codes for laboratory results in our OMOP clinical database.

To configure Leaf to query this, we have a file that we obtained from a Leaf repo some years ago that starts like this:

CREATE TABLE [LEAF_SCRATCH].[LOINC](
	[AUI] [nvarchar](20) NULL,
	[ParentAUI] [nvarchar](20) NULL,
	[MinCode] [nvarchar](20) NULL,
	[MaxCode] [nvarchar](20) NULL,
	[CodeCount] [int] NULL,
	[OntologyType] [nvarchar](20) NULL,
	[SqlSetWhere] [nvarchar](1000) NULL,
	[UiDisplayName] [nvarchar](1000) NULL
) ON [PRIMARY]
GO

INSERT [LEAF_SCRATCH].[LOINC] ([AUI], [ParentAUI], [MinCode], [MaxCode], [CodeCount], [OntologyType], [SqlSetWhere], [UiDisplayName]) VALUES (N'A27438428', N'A28293869', N'63877-5', N'63877-5', 1, N'LNC', N'= ''63877-5''', N'How many Y in total did you take premarin - exclude those periods when you temporarily stopped:Time:Pt:^Patient:Qn:California Teachers Study (LNC:63877-5)')

It inserts 192K+ rows of LOINC information in the LOINC table.

How should the MinCode and MaxCode fields from this table be joined with an OMOP Concept table?

Thanks
Arthur

[ndobb]

Hi Arthur,

I'm guessing your lab results are in the OMOP Measurement table, which you should be able to JOIN to Concept using measurement_concept_id. The appropriate LOINC code would then be in the concept_code column.

The "LOINC" table you have has a column, SqlSetWhere, which should include = and BETWEEN statements. Assuming you want to add the LOINC tree to the Find Patients screen, the key is to configure your WHERE clause to query concept_code + SqlSetWhere.

So you'll need to have the JOIN logic of Measurement and Concept implemented in some way to end up with the correct WHERE clauses for each LOINC concept. This could be done by:

1. Creating a view, e.g., v_Measurement, which performs the JOIN and includes concept_code as an output column.

2. Creating a somewhat complex WHERE clause using EXISTS. For example, given a row where the MinCode and MaxCode are both "63877-5" (and thus you'd want an = to query a single code, and not BETWEEN), you'd want to create a concept with the WHERE clause:

   EXISTS (SELECT 1
           FROM dbo.concept AS @C
           WHERE @C.vocabulary_id = 'LOINC'
                 AND @.measurement_concept_id = @C.concept_id
                 AND @C.concept_code = '63877-5')

Please let me know if that makes sense or anything is unclear.

Best,
-nic

[artgoldberg]

@ndobb Thanks Nic. We'll investigate.