· 5 years ago · Mar 04, 2020, 12:36 PM
1DO $_2$
2 BEGIN
3
4
5--full recreate, all tables are included (much slower)
6SELECT devv5.FastRecreateSchema(main_schema_name=>'devv5', include_concept_ancestor=>true, include_deprecated_rels=>true, include_synonyms=>true);
7
8
9
10
11/**************************************************************************
12* Copyright 2016 Observational Health Data Sciences and Informatics (OHDSI)
13*
14* Licensed under the Apache License, Version 2.0 (the "License");
15* you may not use this file except in compliance with the License.
16* You may obtain a copy of the License at
17*
18* http://www.apache.org/licenses/LICENSE-2.0
19*
20* Unless required by applicable law or agreed to in writing, software
21* distributed under the License is distributed on an "AS IS" BASIS,
22* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
23* See the License for the specific language governing permissions and
24* limitations under the License.
25*
26* Authors: Oleg Zhuk, Polina Talapova, Dmitry Dymshyts, Alexander Davydov, Timur Vakhitov, Christian Reich
27* Date: 2020
28**************************************************************************/
29
30--1. Update a 'latest_update' field to a new date
31DO $_$
32BEGIN
33 PERFORM VOCABULARY_PACK.SetLatestUpdate(
34 pVocabularyName => 'LOINC',
35 pVocabularyDate => (SELECT vocabulary_date FROM sources.loinc LIMIT 1),
36 pVocabularyVersion => (SELECT vocabulary_version FROM sources.loinc LIMIT 1),
37 pVocabularyDevSchema => 'DEV_LOINC'
38);
39END $_$;
40
41--2. Truncate all working tables
42TRUNCATE TABLE concept_stage;
43TRUNCATE TABLE concept_relationship_stage;
44TRUNCATE TABLE concept_synonym_stage;
45TRUNCATE TABLE pack_content_stage;
46TRUNCATE TABLE drug_strength_stage;
47
48--Prepare tables
49--RUN PRELOAD_STAGE
50
51--3. Load LOINC concepts indicating Measurements or Observations from a source table of 'sources.loinc' into the CONCEPT_STAGE
52INSERT INTO concept_stage (
53 concept_name,
54 domain_id,
55 vocabulary_id,
56 concept_class_id,
57 standard_concept,
58 concept_code,
59 valid_start_date,
60 valid_end_date,
61 invalid_reason
62 )
63SELECT CASE
64 WHEN loinc_num = '66678-4'
65 AND property = 'Hx'
66 THEN 'History of Diabetes (regardless of treatment) [PhenX]'
67 WHEN loinc_num = '82312-0'
68 THEN 'History of ' || REPLACE(long_common_name, 'andor', 'and/or')
69 WHEN property = 'Hx'
70 AND long_common_name !~* 'hx|histor|reported|status|narrative|^do you|^have you|^does|^has |education|why you|timing|virtuoso|maestro|grade|received|cause|allergies|in the past'
71 THEN 'History of ' || long_common_name
72 ELSE long_common_name -- AVOF-819
73 END AS concept_name,
74 CASE
75 WHEN CLASSTYPE IN (
76 '1',
77 '2'
78 )
79 AND (
80 survey_quest_text ~ '\?' -- manually defined source attributes indicating the 'Observation' domain
81 OR scale_typ = 'Set'
82 OR property IN (
83 'Hx',
84 'Addr',
85 'Anat',
86 'ClockTime',
87 'Date',
88 'DateRange',
89 'Desc',
90 'EmailAddr',
91 'Instrct',
92 'Loc',
93 'Pn',
94 'Tele',
95 'TmStp',
96 'TmStpRange',
97 'Txt',
98 'URI',
99 'Xad',
100 'Bib'
101 )
102 OR (
103 property = 'ID'
104 AND system IN (
105 '^BPU',
106 '^Patient',
107 'Vaccine'
108 )
109 )
110 OR system IN (
111 '^Family member',
112 '^Neighborhood',
113 '^Brother',
114 '^Daughter',
115 '^Sister',
116 '^Son',
117 '^CCD',
118 '^Census tract',
119 '^Clinical trial protocol',
120 '^Community',
121 '*',
122 '?',
123 '^Contact',
124 '^Donor',
125 '^Emergency contact',
126 '^Event',
127 '^Facility',
128 'Provider',
129 'Report',
130 'Repository',
131 'School',
132 'Surgical procedure'
133 )
134 OR (
135 system IN (
136 '^Patient',
137 '*^Patient'
138 )
139 AND (
140 scale_typ IN (
141 'Doc',
142 'Nar',
143 'Nom',
144 'Ord',
145 'OrdQn'
146 )
147 AND (
148 method_typ NOT IN ('Apgar')
149 OR method_typ IS NULL
150 )
151 OR property IN (
152 'Arb',
153 'Imp',
154 'NRat',
155 'Num',
156 'PrThr',
157 'RelRto',
158 'Time',
159 'Type',
160 'Find'
161 )
162 AND class NOT IN (
163 'COAG',
164 'PULM'
165 )
166 )
167 )
168 )
169 AND (
170 long_common_name !~* 'scale|score'
171 OR long_common_name ~* 'interpretation|rose dyspnea scale'
172 )
173 AND (
174 method_typ != 'Measured'
175 OR method_typ IS NULL
176 )
177 AND loinc_num NOT IN (
178 '65712-2',
179 '65713-0'
180 )
181 THEN 'Observation' -- AVOF-1579
182 WHEN CLASSTYPE = '1'
183 THEN 'Measurement'
184 WHEN CLASSTYPE = '2'
185 THEN 'Measurement'
186 WHEN CLASSTYPE = '3'
187 THEN 'Observation'
188 WHEN CLASSTYPE = '4'
189 THEN 'Observation'
190 END AS domain_id,
191 v.vocabulary_id,
192 CASE
193 WHEN CLASSTYPE IN (
194 '1',
195 '2'
196 )
197 AND (
198 survey_quest_text ~ '\?' -- manually defined source attributes indicating the 'Clinical Observation' concept class
199 OR scale_typ = 'Set'
200 OR property IN (
201 'Hx',
202 'Addr',
203 'Anat',
204 'ClockTime',
205 'Date',
206 'DateRange',
207 'Desc',
208 'EmailAddr',
209 'Instrct',
210 'Loc',
211 'Pn',
212 'Tele',
213 'TmStp',
214 'TmStpRange',
215 'Txt',
216 'URI',
217 'Xad',
218 'Bib'
219 )
220 OR (
221 property = 'ID'
222 AND system IN (
223 '^BPU',
224 '^Patient',
225 'Vaccine'
226 )
227 )
228 OR system IN (
229 '^Family member',
230 '^Neighborhood',
231 '^Brother',
232 '^Daughter',
233 '^Sister',
234 '^Son',
235 '^CCD',
236 '^Census tract',
237 '^Clinical trial protocol',
238 '^Community',
239 '*',
240 '?',
241 '^Contact',
242 '^Donor',
243 '^Emergency contact',
244 '^Event',
245 '^Facility',
246 'Provider',
247 'Report',
248 'Repository',
249 'School',
250 'Surgical procedure'
251 )
252 OR (
253 system IN (
254 '^Patient',
255 '*^Patient'
256 )
257 AND (
258 scale_typ IN (
259 'Doc',
260 'Nar',
261 'Nom',
262 'Ord',
263 'OrdQn'
264 )
265 AND (
266 method_typ NOT IN ('Apgar')
267 OR method_typ IS NULL
268 )
269 OR property IN (
270 'Arb',
271 'Imp',
272 'NRat',
273 'Num',
274 'PrThr',
275 'RelRto',
276 'Time',
277 'Type',
278 'Find'
279 )
280 AND class NOT IN (
281 'COAG',
282 'PULM'
283 )
284 )
285 )
286 )
287 AND (
288 long_common_name !~* 'scale|score'
289 OR long_common_name ~* 'interpretation|rose dyspnea scale'
290 )
291 AND (
292 method_typ != 'Measured'
293 OR method_typ IS NULL
294 )
295 AND loinc_num NOT IN (
296 '65712-2',
297 '65713-0'
298 )
299 THEN 'Clinical Observation' -- AVOF-1579
300 WHEN CLASSTYPE = '1'
301 THEN 'Lab Test'
302 WHEN CLASSTYPE = '2'
303 THEN 'Clinical Observation'
304 WHEN CLASSTYPE = '3'
305 THEN 'Claims Attachment'
306 WHEN CLASSTYPE = '4'
307 THEN 'Survey'
308 END AS concept_class_id,
309 'S' AS standard_concept,
310 LOINC_NUM AS concept_code,
311 COALESCE(c.valid_start_date, v.latest_update) AS valid_start_date,
312 CASE
313 WHEN STATUS IN (
314 'DISCOURAGED',
315 'DEPRECATED'
316 )
317 THEN CASE
318 WHEN C.VALID_END_DATE > V.LATEST_UPDATE
319 OR C.VALID_END_DATE IS NULL
320 THEN V.LATEST_UPDATE
321 ELSE C.VALID_END_DATE
322 END
323 ELSE TO_DATE('20991231', 'yyyymmdd')
324 END AS valid_end_date,
325 CASE
326 WHEN EXISTS (
327 SELECT 1
328 FROM sources.map_to m
329 WHERE m.loinc = l.loinc_num
330 )
331 THEN 'U'
332 WHEN STATUS = 'DISCOURAGED'
333 THEN 'D'
334 WHEN STATUS = 'DEPRECATED'
335 THEN 'D'
336 ELSE NULL
337 END AS invalid_reason
338FROM sources.loinc l
339JOIN vocabulary v ON v.vocabulary_id = 'LOINC'
340LEFT JOIN concept c ON c.concept_code = l.LOINC_NUM
341 AND c.vocabulary_id = 'LOINC';
342
343--todo: confirm that these concepts are not expected to be Measurements storing the result
344--todo: define concept_class_id
345/*--3.1. Update Domains for concepts representing Imaging procedures based on hierarchy
346update concept_stage
347set domain_id = 'Procedure'
348WHERE
349 concept_code in
350 (
351 select code
352 from sources.loinc_hierarchy
353 where
354 path_to_root ~ ('^(LP7787\-7\.LP29684\-5|LP7787\-7\.LP7797\-6\.LP29680\-3)\.') and --LP29684-5 Radiology LP29680-3 Eye ultrasound
355 code not in (select loincnumber from sources.loinc_partlink where partnumber in ('LP7753-9','LP200093-5','LP200395-4')) -- LOINC Parts that identify direct measures or scores
356 )
357;*/
358
359--4. Add LOINC Classes from a manual table of 'sources.loinc_class' into the CONCEPT_STAGE
360INSERT INTO concept_stage (
361 concept_name,
362 domain_id,
363 vocabulary_id,
364 concept_class_id,
365 standard_concept,
366 concept_code,
367 valid_start_date,
368 valid_end_date,
369 invalid_reason
370 )
371SELECT concept_name,
372 CASE
373 WHEN concept_name ~* 'history|report|document|miscellaneous|public health' -- manually defined word patterns indicating the 'Observation' domain
374 THEN 'Observation'
375 ELSE domain_id
376 END, -- AVOF-1579
377 vocabulary_id,
378 concept_class_id,
379 'C',
380 concept_code,
381 valid_start_date,
382 valid_end_date,
383 invalid_reason
384FROM sources.loinc_class
385
386UNION ALL
387
388-- add missed 'Document Ontology' LOINC Class (AVOF-757)
389SELECT 'Document Ontology' AS concept_name,
390 'Observation' AS domain_id,
391 'LOINC' AS vocabulary_id,
392 'LOINC Class' AS concept_class_id,
393 'C' AS standard_concept,
394 'DOC.ONTOLOGY' AS concept_code,
395 '1970-01-01' AS valid_start_date,
396 '2099-12-31' AS valid_end_date,
397 NULL AS invalid_reason;
398
399--5. Add LOINC Attributes ('Parts') and LOINC Hierarchy concepts into the CONCEPT_STAGE
400INSERT INTO concept_stage
401(
402 concept_name,
403 domain_id,
404 vocabulary_id,
405 concept_class_id,
406 standard_concept,
407 concept_code,
408 valid_start_date,
409 valid_end_date,
410 invalid_reason
411)
412WITH s AS
413(
414-- pick LOINC Parts of 6 classes (classes that have links in 'Primary' LinkTypeName)
415SELECT DISTINCT p.PartNumber, p.PartDisplayName, p.parttypename, p.status
416FROM sources.loinc_part p -- contains LOINC Parts and defines their validity ('status' field)
417WHERE p.PartTypeName IN ('SYSTEM', 'METHOD', 'PROPERTY', 'TIME', 'COMPONENT', 'SCALE') -- list of Primary LOINC Parts
418
419 UNION ALL
420
421-- pick LOINC Hierarchy concepts (Attributive Panels, non-primary Parts and ~400 Undefined attributes)
422SELECT DISTINCT code,
423 COALESCE(p.partdisplayname,code_text) AS PartDisplayName,
424 'LOINC Hierarchy' AS parttypename,
425 CASE
426 WHEN p.status IS NOT NULL THEN p.status
427 ELSE 'ACTIVE'
428 END AS status
429FROM sources.loinc_hierarchy lh
430 LEFT JOIN sources.loinc_part p --to get a validity of concept (a 'status' field)
431ON lh.code = p.partnumber -- LOINC Attribute
432WHERE code LIKE 'LP%' -- all LOINC Hierсrchy concepts have 'LP' at the beginning of the names (including ~400 undefined concepts and LOINC panels)
433 AND TRIM(code) NOT IN (SELECT TRIM(p.partnumber)
434 FROM sources.loinc_part p
435 WHERE p.PartTypeName IN ('SYSTEM', 'METHOD', 'PROPERTY', 'TIME', 'COMPONENT', 'SCALE')) --excluding Primary LOINC Parts added above
436)
437
438SELECT DISTINCT
439 trim(s.PartDisplayName) AS concept_name,
440 CASE WHEN PartDisplayName ~* ('directive|^age\s+|lifetime risk|alert|attachment|\s+date|comment|\s+note|consent|identifier|\s+time|\s+number|' ||
441 'date and time|coding system|interpretation|status|\s+name|\s+report|\s+id$|s+id\s+|version|instruction|known exposure|priority|ordered|available|requested|issued|flowsheet|\s+term|' ||
442 'reported|not yet categorized|performed|risk factor|device|administration|\s+route$|suggestion|recommended|narrative|ICD code|reference|' ||
443 'reviewed|information|intention|^Reason for|^Received|Recommend|provider|subject|summary|time\s+|document') -- manually defined word patterns indicating the 'Observation' domain
444
445 AND PartDisplayName !~* ('thrombin time|clotting time|bleeding time|clot formation|kaolin activated time|closure time|protein feed time|Recalcification time|reptilase time|russell viper venom time|' ||
446 'implanted device|dosage\.vial|isolate|within lymph node|cancer specimen|tumor|chromosome|inversion|bioavailable')
447 THEN 'Observation'
448 ELSE 'Measurement' --AVOF-1579 --will be corrected below (5.1) for 6 Primary LOINC Parts
449 END AS domain_id,
450 'LOINC' AS vocabulary_id,
451 CASE WHEN s.parttypename = 'SYSTEM' THEN 'LOINC System'
452 WHEN s.parttypename = 'METHOD' THEN 'LOINC Method'
453 WHEN s.parttypename = 'PROPERTY' THEN 'LOINC Property'
454 WHEN s.parttypename = 'TIME' THEN 'LOINC Time'
455 WHEN s.parttypename = 'COMPONENT' THEN 'LOINC Component'
456 WHEN s.parttypename = 'SCALE' THEN 'LOINC Scale'
457 ELSE 'LOINC Hierarchy'
458 END AS concept_class_id,
459 case s.status
460 when 'DEPRECATED' then null
461 else 'C' --will be corrected below (5.1) for 6 Primary LOINC Parts
462 end AS standard_concept,
463 s.PartNumber AS concept_code, -- LOINC Attribute or Hierarchy concept
464 COALESCE(c.valid_start_date, v.latest_update) AS valid_start_date,-- preserve the valid_start_date for already existing concepts
465 CASE WHEN s.status = 'DEPRECATED'
466 THEN CASE WHEN c.valid_end_date <= latest_update
467 THEN c.valid_end_date -- preserve valid_end_date for already existing DEPRECATED concepts
468 ELSE GREATEST(COALESCE(c.valid_start_date, v.latest_update), -- assign LOINC 'latest_update' as 'valid_end_date' for new concepts which have to be deprecated in the current release
469 latest_update - 1) END -- assign LOINC 'latest_update-1' as 'valid_end_date' for already existing concepts, which have to be deprecated in the current release
470 ELSE to_date('20991231', 'yyyymmdd') END as valid_end_date, -- default value of 31-Dec-2099 for the rest
471 CASE WHEN s.status IN ('ACTIVE', 'INACTIVE') THEN NULL -- define concept validity according to the 'status' field
472 WHEN s.status = 'DEPRECATED' THEN 'D'
473 ELSE 'X' END AS invalid_reason --IF there are any changes in LOINC source we don't know about. GenericUpdate() will fail in case of 'X' in invalid_reason field
474FROM s
475JOIN vocabulary v ON v.vocabulary_id = 'LOINC'
476LEFT JOIN concept c ON c.concept_code = s.PartNumber -- already existing LOINC concepts
477 AND c.vocabulary_id = 'LOINC';
478
479--5.1. Update Domain = 'Observation' and standard_concept = NULL for attributes that are not part of Hierarchy (AVOF-2222)
480with hierarchy as
481(
482 select code
483 from sources.loinc_hierarchy
484 where
485 code not in (select partnumber from sources.loinc_partlink where parttypename != 'CLASS') and
486 code !~ '^\d'
487)
488update concept_stage c
489set
490 domain_id = 'Observation',
491 standard_concept = null
492where
493 concept_code not in (select code from hierarchy) and --currently hierarchy does not overlap with 6 LP classes, but this might be helpful in further development
494 concept_class_id ~ 'LOINC (System|Method|Property|Time|Component|Scale)'
495;
496
497--6. Build 'Subsumes' relationships from LOINC Ancestors to Descendants using a source table of 'sources.loinc_hierarchy'
498INSERT INTO concept_relationship_stage (
499 concept_code_1,
500 concept_code_2,
501 relationship_id,
502 vocabulary_id_1,
503 vocabulary_id_2,
504 valid_start_date,
505 valid_end_date,
506 invalid_reason
507 )
508SELECT DISTINCT immediate_parent AS concept_code_1, -- LOINC Ancestor
509 code AS concept_code_2, -- LOINC Descendant
510 'Subsumes' AS relationship_id,
511 'LOINC' AS vocabulary_id_1,
512 'LOINC' AS vocabulary_id_2,
513 TO_DATE('19700101', 'yyyymmdd') AS valid_start_date,
514 TO_DATE('20991231', 'yyyymmdd') AS valid_end_date,
515 NULL AS invalid_reason
516FROM sources.loinc_hierarchy
517WHERE immediate_parent IS NOT NULL; -- when immediate parent is null then there is no Ancestor
518
519--7. Build 'Has system', 'Has method', 'Has property', 'Has time aspect', 'Has component', and 'Has scale type' relationships from LOINC Measurements/Observations to Primary LOINC Parts (attributes)
520-- assign specific links using a TYPE of LOINC Part using 'sources.loinc_partlink'
521INSERT INTO concept_relationship_stage
522(
523 concept_code_1,
524 concept_code_2,
525 vocabulary_id_1,
526 vocabulary_id_2,
527 relationship_id,
528 valid_start_date,
529 valid_end_date,
530 invalid_reason
531)
532WITH s AS
533(
534 SELECT DISTINCT loincnumber, -- LOINC Measurement/Observation
535 p.PartNumber,-- Primary LOINC Part
536 p.status,
537 CASE
538 WHEN p.parttypename = 'SYSTEM' THEN 'Has system'
539 WHEN p.parttypename = 'METHOD' THEN 'Has method'
540 WHEN p.parttypename = 'PROPERTY' THEN 'Has property'
541 WHEN p.parttypename = 'TIME' THEN 'Has time aspect'
542 WHEN p.parttypename = 'COMPONENT' THEN 'Has component'
543 WHEN p.parttypename = 'SCALE' THEN 'Has scale type'
544 END AS relationship_id
545FROM sources.loinc_partlink pl
546JOIN sources.loinc_part p
547 ON pl.PartNumber = p.PartNumber -- Primary LOINC Part
548WHERE pl.LinkTypeName IN ('Primary')
549),
550-- pick already existing relationships between LOINC Measurements/Observations and Primary LOINC Parts (it's needed to pull the validity dates from basic tables)
551cr AS (SELECT DISTINCT c.concept_code AS concept_code_1, -- LOINC Measurement/Observation
552 c.vocabulary_id,
553 relationship_id,
554 cc.concept_code AS concept_code_2, -- -- Primary LOINC Part ?
555 cc.vocabulary_id,
556 cr.valid_start_date,
557 cr.valid_end_date,
558 cr.invalid_reason
559FROM concept_relationship cr
560 JOIN concept c ON cr.concept_id_1 = c.concept_id
561 JOIN concept cc ON cr.concept_id_2 = cc.concept_id
562WHERE c.vocabulary_id = 'LOINC'
563AND cc.vocabulary_id = 'LOINC')
564
565SELECT DISTINCT s.loincnumber AS concept_code_1,
566 partnumber AS concept_code_2,
567 'LOINC' AS vocabulary_id_1,
568 'LOINC' AS vocabulary_id_2,
569 s.relationship_id AS relationship_id,
570 COALESCE (cr.valid_start_date, -- preserve valid_start_date for already existing relationships
571 LEAST (c.valid_end_date, cc.valid_end_date, v.latest_update)) AS valid_start_date, -- compare and assign earliest date of 'valid_end_date' of a LOINC concept AS 'valid_start_date' for NEW relationships of concepts deprecated in the current release OR 'latest update' for the rest of the codes
572 CASE WHEN cr.valid_end_date <= v.latest_update THEN cr.valid_end_date -- preserve valid_end_date for already existing relationships
573 WHEN (c.invalid_reason IS NOT NULL) OR (cc.invalid_reason IS NOT NULL) THEN LEAST(c.valid_end_date, cc.valid_end_date) -- compare and assign earliest date of 'valid_end_date' of a LOINC concept as 'valid_end_date' for NEW relationships of concepts deprecated in the current release
574 ELSE TO_DATE('20991231', 'yyyymmdd') END as valid_end_date, -- for the rest of the codes
575 CASE WHEN (c.invalid_reason IS NOT NULL) OR (cc.invalid_reason IS NOT NULL) THEN 'D'
576 ELSE NULL END AS invalid_reason
577FROM s
578 LEFT JOIN concept_stage c -- to define deprecated LOINC Observations/Measurements in the current release
579 ON c.concept_code = s.loincnumber --LOINC Observation/Measurement in the current release
580 AND c.vocabulary_id = 'LOINC'
581 LEFT JOIN concept_stage cc -- to define deprecated LOINC Parts
582 ON cc.concept_code = s.partnumber -- LOINC Part
583 LEFT JOIN cr ON (cr.concept_code_1, cr.relationship_id, cr.concept_code_2) = (s.loincnumber, s.relationship_id, s.partnumber) -- already existing relationships between LOINC concepts
584 JOIN vocabulary v ON v.vocabulary_id = 'LOINC';
585
586--8. Build 'Subsumes' relationships between LOINC Classes using a source table of 'sources.loinc_class' and a similarity of a class name beginning (ancestor class_name LIKE descendant class_name || '%').
587INSERT INTO concept_relationship_stage (
588 concept_code_1,
589 concept_code_2,
590 relationship_id,
591 vocabulary_id_1,
592 vocabulary_id_2,
593 valid_start_date,
594 valid_end_date,
595 invalid_reason
596 )
597SELECT DISTINCT l2.concept_code AS concept_code_1, -- LOINC Class Ancestor
598 l1.concept_code AS concept_code_2, -- LOINC Class Descendant
599 'Subsumes' AS relationship_id,
600 'LOINC' AS vocabulary_id_1,
601 'LOINC' AS vocabulary_id_2,
602 TO_DATE('19700101', 'yyyymmdd') AS valid_start_date,
603 TO_DATE('20991231', 'yyyymmdd') AS valid_end_date,
604 NULL AS invalid_reason
605FROM sources.loinc_class l1,
606 sources.loinc_class l2
607WHERE l1.concept_code LIKE l2.concept_code || '%'
608 AND l1.concept_code <> l2.concept_code
609
610UNION ALL
611
612--add 'Subsumes' relationship from 'Document' ('DOC') to 'Document Ontology' Class ('DOC.ONTOLOGY') manually to embed the 'Document Ontology' hierarchical branch to the Document Hierarchy (AVOF-757)
613SELECT 'DOC' AS concept_code_1,
614 'DOC.ONTOLOGY' AS concept_code_2,
615 'Subsumes' AS relationship_id,
616 'LOINC' AS vocabulary_id_1,
617 'LOINC' AS vocabulary_id_2,
618 TO_DATE('19700101', 'yyyymmdd') AS valid_start_date,
619 TO_DATE('20991231', 'yyyymmdd') AS valid_end_date,
620 NULL AS invalid_reason;
621
622--9. Build 'Subsumes' relationships from LOINC Classes to LOINC concepts indicating Measurements or Observations with the use of source tables of 'sources.loinc_class' and 'sources.loinc' to create Multiaxial Hierarchy
623INSERT INTO concept_relationship_stage (
624 concept_code_1,
625 concept_code_2,
626 relationship_id,
627 vocabulary_id_1,
628 vocabulary_id_2,
629 valid_start_date,
630 valid_end_date,
631 invalid_reason
632 )
633SELECT l.class AS concept_code_1, -- LOINC Class concept
634 l.loinc_num AS concept_code_2, -- LOINC Observation/Measurement concept
635 'Subsumes' AS relationship_id,
636 'LOINC' AS vocabulary_id_1,
637 'LOINC' AS vocabulary_id_2,
638 TO_DATE('19700101', 'yyyymmdd') AS valid_start_date,
639 TO_DATE('20991231', 'yyyymmdd') AS valid_end_date,
640 NULL AS invalid_reason
641FROM sources.loinc_class lc,
642 sources.loinc l
643WHERE lc.concept_code = l.class;
644
645--9.1 Delete wrong relationship between 'PANEL.H' class (History & Physical order set) and 38213-5 'FLACC pain assessment panel' (AVOF-352)
646DELETE
647FROM concept_relationship_stage
648WHERE concept_code_1 = 'PANEL.H' || chr(38) || 'P' -- '&' = chr(38)
649 AND concept_code_2 = '38213-5'
650 AND relationship_id = 'Subsumes';
651
652--10.1 Add to the CONCEPT_SYNONYM_STAGE all synonymic names from a source table of 'sources.loinc'
653INSERT INTO concept_synonym_stage (
654 synonym_concept_code,
655 synonym_name,
656 synonym_vocabulary_id,
657 language_concept_id
658 ) (
659 --values of a 'RelatedNames2' field
660 SELECT loinc_num AS synonym_concept_code,
661 SUBSTR(relatednames2, 1, 1000) AS synonym_name,
662 'LOINC' AS synonym_vocabulary_id,
663 4180186 AS language_concept_id -- English
664 FROM sources.loinc WHERE relatednames2 IS NOT NULL
665
666UNION
667
668 -- values of a 'consumer_name' field that were previously used as preferred name (in 195 cases)
669 SELECT loinc_num AS synonym_concept_code,
670 consumer_name AS synonym_name,
671 'LOINC' AS synonym_vocabulary_id,
672 4180186 AS language_concept_id -- English
673 FROM sources.loinc WHERE consumer_name IS NOT NULL
674
675UNION
676
677 -- values of the 'ShortName' field
678 SELECT loinc_num AS synonym_concept_code,
679 shortname AS synonym_name,
680 'LOINC' AS synonym_vocabulary_id,
681 4180186 AS language_concept_id -- English
682 FROM sources.loinc WHERE shortname IS NOT NULL
683
684UNION
685
686 --'long_common_name' field values which were changed ('History of')
687 SELECT loinc_num AS synonym_concept_code,
688 long_common_name AS synonym_name,
689 'LOINC' AS synonym_vocabulary_id,
690 4180186 AS language_concept_id -- English
691 FROM sources.loinc WHERE long_common_name NOT IN (
692 SELECT concept_name
693 FROM concept_stage
694 )
695 )-- NB! We do not add synonyms for LOINC Answers (a 'description' field) due to their vague formulation
696UNION
697--'PartName' field values which are synonyms for 'PartDisplayName' field values in sources.loinc_part
698 SELECT DISTINCT
699 pl.PartNumber AS synonym_concept_code,
700 p.PartName AS synonym_name,
701 'LOINC' AS synonym_vocabulary_id,
702 4180186 AS language_concept_id --English language
703 FROM sources.loinc_partlink pl
704 JOIN sources.loinc_part p
705 ON pl.PartNumber = p.PartNumber
706 WHERE pl.PartNumber IN (SELECT concept_code FROM concept_stage)
707 AND pl.PartName != p.PartDisplayName; -- pick only different names
708;
709
710--11. Add LOINC Answers from 'sources.loinc_answerslist' and 'sources.loinc_answerslistlink' source tables to the CONCEPT_STAGE
711INSERT INTO concept_stage (
712 concept_name,
713 domain_id,
714 vocabulary_id,
715 concept_class_id,
716 standard_concept,
717 concept_code,
718 valid_start_date,
719 valid_end_date,
720 invalid_reason
721 )
722SELECT DISTINCT trim(ans_l.displaytext) AS concept_name,
723 'Meas Value' AS domain_id,
724 'LOINC' AS vocabulary_id,
725 'Answer' AS concept_class_id,
726 'S' AS standard_concept,
727 ans_l.answerstringid AS concept_code,
728 TO_DATE('19700101', 'yyyymmdd') AS valid_start_date,
729 TO_DATE('20991231', 'yyyymmdd') AS valid_end_date,
730 NULL AS invalid_reason
731FROM sources.loinc_answerslist ans_l -- Answer containing table
732JOIN sources.loinc_answerslistlink ans_l_l ON ans_l_l.answerlistid = ans_l.answerlistid -- 'AnswerListID' field unites Answers with Questions
733JOIN sources.loinc l ON l.loinc_num = ans_l_l.loincnumber -- to confirm the connection of 'AnswerListID' with LOINC concepts indicating Measurements and Observations (currently all of them are connected)
734WHERE ans_l.answerstringid IS NOT NULL;--'AnswerStringID' value may be null
735
736--12. Build 'Has Answer' relationships from LOINC Questions to Answers with the use of such source tables as 'sources.loinc_answerslist' and 'sources.loinc_answerslistlink'
737INSERT INTO concept_relationship_stage (
738 concept_code_1,
739 concept_code_2,
740 relationship_id,
741 vocabulary_id_1,
742 vocabulary_id_2,
743 valid_start_date,
744 valid_end_date,
745 invalid_reason
746 )
747SELECT DISTINCT ans_l_l.loincnumber AS concept_code_1, -- LOINC Question code
748 ans_l.answerstringid AS concept_code_2, -- LOINC Answer code
749 'Has Answer' AS relationship_id,
750 'LOINC' AS vocabulary_id_1,
751 'LOINC' AS vocabulary_id_2,
752 TO_DATE('19700101', 'yyyymmdd') AS valid_start_date,
753 TO_DATE('20991231', 'yyyymmdd') AS valid_end_date,
754 NULL AS invalid_reason
755FROM sources.loinc_answerslist ans_l -- Answer containing table
756JOIN sources.loinc_answerslistlink ans_l_l ON ans_l_l.answerlistid = ans_l.answerlistid -- 'AnswerListID' field unites Answers with Questions
757WHERE ans_l.answerstringid IS NOT NULL;-- 'AnswerStringID' may be empty
758
759--13. Build 'Panel contains' relationships from LOINC Panels to their descendants with the use of 'sources.loinc_forms' table
760INSERT INTO concept_relationship_stage (
761 concept_code_1,
762 concept_code_2,
763 relationship_id,
764 vocabulary_id_1,
765 vocabulary_id_2,
766 valid_start_date,
767 valid_end_date,
768 invalid_reason
769 )
770SELECT DISTINCT parentloinc AS concept_code_1, -- LOINC Panel code
771 loinc AS concept_code_2, -- LOINC Descendant code
772 'Panel contains' AS relationship_id,
773 'LOINC' AS vocabulary_id_1,
774 'LOINC' AS vocabulary_id_2,
775 TO_DATE('19700101', 'yyyymmdd') AS valid_start_date,
776 TO_DATE('20991231', 'yyyymmdd') AS valid_end_date,
777 NULL AS invalid_reason
778FROM sources.loinc_forms -- Panel containing table
779WHERE loinc <> parentloinc;-- to exclude cases when parents and children are represented by the same concepts
780
781--14.1 Build temporary 'LOINC - SNOMED eq' relationships between LOINC Attributes and SNOMED Attributes (will be dropped in 14.6). Afterward 'Maps to' may be built instead.
782INSERT INTO concept_relationship_stage (
783 concept_code_1,
784 concept_code_2,
785 vocabulary_id_1,
786 vocabulary_id_2,
787 relationship_id,
788 valid_start_date,
789 valid_end_date,
790 invalid_reason
791 )
792-- note, there are 39 LOINC Parts which have more than one link to SNOMED due to different representation of Systems and Components in vocabularies
793SELECT DISTINCT maptarget AS concept_code_1, -- LOINC Attribute code
794 referencedcomponentid AS concept_code_2, -- SNOMED Attribute code
795 'LOINC' AS vocabulary_id_1,
796 'SNOMED' AS vocabulary_id_2,
797 'LOINC - SNOMED eq' AS relationship_id,
798 v.latest_update AS valid_start_date,
799 TO_DATE('20991231', 'yyyymmdd') AS valid_end_date,
800 NULL AS invalid_reason
801FROM sources.scccrefset_mapcorrorfull_int s
802JOIN concept_stage c
803 ON maptarget = c.concept_code --LOINC Attribute
804 AND c.vocabulary_id = 'LOINC'
805 AND c.invalid_reason IS NULL
806JOIN concept d
807 ON referencedcomponentid = d.concept_code --SNOMED Attribute
808 AND d.vocabulary_id = 'SNOMED'
809 AND d.invalid_reason IS NULL
810JOIN vocabulary v
811 ON c.vocabulary_id = v.vocabulary_id --valid_start_date
812WHERE s.attributeid in ('246093002', '704319004', '704327008', '718497002') --'Component', 'Inheres in' (Component-like), 'Direct site' (System-like), 'Inherent location' (Component-like)
813;
814/* Excluded attribute IDs:
815Process output - reduplicate a Component
816Process agent - link from a LOINC Component to a possible SNOMED System, useless in mapping ('Kidney structure')
817Property type - links from a LOINC Component to a possible SNOMED Property (useless, non-SNOMED logic)
818Technique - link from a LOINC Component to SNOMED Technique (useless, non-SNOMED logic)
819Characterizes - senseless 'Excretory process' */
820
821-- 14.2 Build temporary relationships between LOINC Measurements and respective SNOMED attributes given by the table of 'sources.scccrefset_expressionassociation_int' (will be dropped in 14.6).
822-- Note, that some suggested by LOINC relationship_ids ('Characterizes', 'Units', 'Relative to', 'Process agent' 'Inherent location') are useless in the context of a mapping to SNOMED.
823INSERT INTO concept_relationship_stage
824(
825 concept_code_1,
826 concept_code_2,
827 vocabulary_id_1,
828 vocabulary_id_2,
829 relationship_id,
830 valid_start_date,
831 valid_end_date,
832 invalid_reason
833)
834-- extract LOINC Measurement codes, LOINC-to-SNOMED relationship_id identifiers and related SNOMED Attributes from sources.scccrefset_expressionassociation_int
835WITH t1 AS
836(SELECT maptarget, -- LOINC Measurement code
837 SPLIT_PART(REGEXP_REPLACE(REGEXP_SPLIT_TO_TABLE(expression,','),'^\d+:',''),'=',1) AS sn_comp2, -- LOINC to SNOMED relationship_id identifier
838 SPLIT_PART(REGEXP_REPLACE(REGEXP_SPLIT_TO_TABLE(expression,','),'^\d+:',''),'=',2) AS sn_comp3 -- related SNOMED Attribute
839FROM sources.scccrefset_expressionassociation_int)
840
841SELECT DISTINCT maptarget AS concept_code_1,-- LOINC Measurement code
842c3.concept_code AS concept_code_2, -- SNOMED Attribute code
843'LOINC' AS vocabulary_id_1,
844'SNOMED' AS vocabulary_id_2,
845CASE WHEN c2.concept_name in ('Time aspect', 'Process duration') THEN 'Has time aspect'
846 WHEN c2.concept_name in ('Component','Process output') THEN 'Has component'
847 WHEN c2.concept_name = 'Direct site' THEN 'Has dir proc site'
848 WHEN c2.concept_name = 'Inheres in' THEN 'Inheres in'
849 WHEN c2.concept_name = 'Property type' THEN 'Has property'
850 WHEN c2.concept_name = 'Scale type' THEN 'Has scale type'
851 WHEN c2.concept_name = 'Technique' THEN 'Has technique'
852 WHEN c2.concept_name = 'Precondition' THEN 'Has precondition'
853 END AS relationship_id,
854v.latest_update AS valid_start_date,
855TO_DATE('20991231','yyyymmdd') AS valid_end_date,
856NULL AS invalid_reason
857FROM t1 a
858JOIN concept_stage c1
859 ON maptarget = c1.concept_code -- LOINC Lab test
860 AND c1.invalid_reason IS NULL
861 AND c1.vocabulary_id = 'LOINC'
862JOIN concept c2
863 ON sn_comp2 = c2.concept_code -- LOINC to SNOMED relationship_id identifier
864 AND c2.vocabulary_id = 'SNOMED'
865 AND c2.concept_name IN ('Time aspect', 'Process duration', 'Component', 'Process output', 'Direct site', 'Inheres in', 'Property type', 'Scale type', 'Technique', 'Precondition')
866JOIN concept c3
867 ON c3.concept_code = sn_comp3 -- SNOMED Attribute
868 AND c3.vocabulary_id = 'SNOMED'
869 AND c3.invalid_reason IS NULL
870JOIN vocabulary v
871 ON c1.vocabulary_id = v.vocabulary_id
872;
873
874-- 14.3 Build 'Is a' from LOINC Measurements to SNOMED Measurements in CONCEPT_RELATIONSHIP_STAGE to create a hierarchical cross-walks;
875-- 14.3.1 create temporary tables with SNOMED and LOINC attribute pools
876---- 'sn_attr' contains normalized set of SNOMED Measurements and respective attributes, taking into account useful relationship_ids and STATUS of SNOMED concepts (pick only Fully defined ones)
877DROP TABLE IF EXISTS sn_attr;
878CREATE TABLE sn_attr
879AS
880(WITH t1
881AS
882((SELECT *
883FROM (SELECT c.concept_code AS sn_code,
884 c.concept_name AS sn_name,
885 r.relationship_id,
886 d.concept_code AS attr_code,
887 d.concept_name AS attr_name,
888 COUNT(1) OVER (PARTITION BY c.concept_code,r.relationship_id) AS cnt
889 FROM concept c
890 JOIN concept_relationship r
891 ON c.concept_id = concept_id_1
892 AND r.invalid_reason IS NULL
893 JOIN concept d ON d.concept_id = concept_id_2
894 WHERE c.vocabulary_id = 'SNOMED'
895 AND c.domain_id = 'Measurement'
896 AND c.standard_concept = 'S'
897 AND d.vocabulary_id = 'SNOMED'
898 AND r.concept_id_1 NOT IN (SELECT concept_id_1
899 FROM concept_relationship
900 WHERE relationship_id IN ('Has intent','Has measurement'))
901 AND relationship_id IN ('Has component','Has scale type','Has specimen','Has dir proc site','Inheres in')) kk
902WHERE cnt = 1)),-- exclude concepts with multiple attributes from one category
903-- get a list of Fully defined SNOMED concepts, using sources.sct2_concept_full_merged, to weed out Primitive SNOMED Measurements composed of inadequate attribute set
904def_status AS
905(SELECT DISTINCT c.concept_code,
906 FIRST_VALUE(f.statusid) OVER (PARTITION BY f.id ORDER BY f.effectivetime DESC) AS statusid -- the 'statusid' field may be both Fully define and Primitive at the same time, to distinguish Fully define ones use 'effectivetime' field
907 FROM sources.sct2_concept_full_merged f -- the source table indicating 'definition status' of SNOMED concepts
908 JOIN concept c ON c.vocabulary_id = 'SNOMED' AND c.standard_concept = 'S'
909 AND c.concept_code = CAST (f.id AS VARCHAR)),
910snomed_concept AS (SELECT * FROM def_status WHERE statusid = 900000000000073002)
911SELECT zz.* FROM t1 zz
912 JOIN snomed_concept kk ON kk.concept_code = zz.sn_code
913 WHERE zz.sn_code not in ('104193001','104194007','104178000','370990004','401298000','399177007','399193003','115253009','395129003','409613001',
914 '399143002','115340009','430925007','104568008','121806006','445132000', '104326007', '104323004', '697001', '413058006') -- SNOMED concepts with wrong sets of attributes
915AND sn_name !~* 'screening'
916);
917
918-- create an index for the temporary table of 'sn_attr' to speed up next table creation
919DROP INDEX if exists l_attr_name;
920CREATE index l_attr_name ON sn_attr (LOWER (attr_name));
921ANALYZE sn_attr;
922
923-- 'LC_ATTR' contains normalized set of relationships between LOINC Measurements and SNOMED Attributes
924DROP TABLE IF EXISTS lc_attr;
925CREATE TABLE lc_attr
926AS
927 (
928-- AXIS 1: build links between TOP-6 LOINC Systems or 'Quantitative'/'Qualitative' Scales AND respective SNOMED Attributes
929WITH lc_attr_add AS
930(
931 SELECT
932 c.concept_code as lc_code,
933 c.concept_name as lc_name,
934 CASE WHEN r1.concept_code_2 not in ( 'LP7753-9', 'LP7751-3') THEN 'Has dir proc site' ELSE 'Has scale type' END AS relationship_id,
935 CASE WHEN r1.concept_code_2 IN ('LP7057-5', 'LP21304-8', 'LP7068-2', 'LP185760-8', 'LP7536-8', 'LP7576-4', 'LP7578-0', 'LP7579-8', 'LP7067-4', 'LP7073-2') --'Bld', 'Bld.dot', 'BldC', 'Plas/Bld', 'Ser/Plas/Bld', 'Ser/Plas', 'Ser/Plas.ultracentrifugate', 'RBC'
936 THEN '119297000' -- Blood specimen
937WHEN r1.concept_code_2 = 'LP7567-3' THEN '119364003' -- Serum specimen
938WHEN r1.concept_code_2 = 'LP7681-2' THEN '122575003' -- Urine specimen
939WHEN r1.concept_code_2 = 'LP7156-5' THEN '258450006' -- Cerebrospinal fluid sample
940WHEN r1.concept_code_2 = 'LP7479-1' THEN '119361006' -- Plasma specimen
941WHEN r1.concept_code_2 = 'LP7604-4' THEN '119339001' -- Stool specimen
942WHEN r1.concept_code_2 = 'LP7753-9' THEN '30766002' -- Quantitative
943WHEN r1.concept_code_2 = 'LP7751-3' THEN '26716007' -- Qualitative
944END AS attr_code
945FROM concept_stage c
946 JOIN concept_relationship_stage r1 ON (concept_code_1,vocabulary_id_1) = (c.concept_code,c.vocabulary_id) -- LOINC Measurement
947 AND c.vocabulary_id = 'LOINC' and c.domain_id = 'Measurement' and c.invalid_reason is null and c.standard_concept = 'S'
948 AND concept_code_2 IN ('LP7057-5','LP21304-8','LP7068-2','LP185760-8','LP7536-8','LP7576-4','LP7578-0','LP7579-8','LP7567-3','LP7681-2','LP7156-5','LP7479-1','LP7604-4', 'LP7753-9', 'LP7067-4', 'LP7073-2', 'LP7751-3') -- list of needful LOINC Parts (System and Scale)
949 AND r1.relationship_id in ('Has system', 'Has scale type')
950),
951-- AXIS 2: get links given by the source between LOINC Measurements and SNOMED Attributes
952lc_sn AS (
953 SELECT concept_code_1 AS lc_code,
954 lc.concept_name AS lc_name,
955 r1.relationship_id,
956 concept_code_2 AS attr_code
957FROM concept_relationship_stage r1
958 JOIN concept_stage lc ON (lc.concept_code,lc.vocabulary_id) = (r1.concept_code_1,r1.vocabulary_id_1) -- LOINC Measurement
959 AND r1.vocabulary_id_1 = 'LOINC' and lc.standard_concept = 'S' and lc.invalid_reason is null and lc.domain_id = 'Measurement'
960 JOIN concept la ON (la.concept_code,la.vocabulary_id) = (r1.concept_code_2,r1.vocabulary_id_2) -- SNOMED Attribute
961 AND r1.vocabulary_id_2 = 'SNOMED' and la.invalid_reason is null
962 AND r1.relationship_id IN ('Has component', 'Has dir proc site', 'Inheres in', 'Has scale type') -- list of useful relationship_ids
963 WHERE (concept_code_1, r1.relationship_id) not in (select lc_code, relationship_id from lc_attr_add) -- to exclude duplicates
964 ),
965-- AXIS 3: build links between LOINC Measurements and SNOMED Attributes using given by the source mappings of LOINC Attributes to SNOMED Attributes
966lc_attr_1 AS
967(
968SELECT DISTINCT l2.concept_code AS lc_code,
969 l2.concept_name AS lc_name,
970 'Has component' AS relationship_id,
971 la.concept_code AS attr_code
972FROM concept_relationship_stage r1
973 JOIN concept_stage lc
974 ON (lc.concept_code,lc.vocabulary_id) = (r1.concept_code_1,r1.vocabulary_id_1) -- LOINC Component
975 AND r1.vocabulary_id_1 = 'LOINC' and concept_class_id = 'LOINC Component'
976 JOIN concept la
977 ON (la.concept_code,la.vocabulary_id) = (r1.concept_code_2,r1.vocabulary_id_2) -- SNOMED Attribute
978 AND r1.vocabulary_id_2 = 'SNOMED'
979 AND r1.relationship_id = 'LOINC - SNOMED eq' and la.concept_class_id = 'Substance'
980 JOIN concept_relationship_stage x1
981 ON (x1.concept_code_1,x1.vocabulary_id_1) = (lc.concept_code,lc.vocabulary_id)-- LOINC Component
982 AND x1.relationship_id = 'Subsumes' -- LOINC Component 'Subsumes' LOINC Panel
983 JOIN concept_relationship_stage x2
984 ON (x2.concept_code_1,x2.vocabulary_id_1) = (x1.concept_code_2,x1.vocabulary_id_2) -- LOINC Panel
985 AND x2.relationship_id = 'Subsumes' -- LOINC Panel 'Subsumes' LOINC Measurement
986 JOIN concept_stage l2 on (l2.concept_code, l2.vocabulary_id) = (x2.concept_code_2, x2.vocabulary_id_2) -- LOINC Measurement
987 AND l2.vocabulary_id = 'LOINC'
988 AND l2.standard_concept = 'S' and l2.standard_concept = 'S' and l2.invalid_reason is null
989 AND l2.domain_id = 'Measurement'
990 WHERE (l2.concept_code, 'Has component') not in (select lc_code, relationship_id from lc_sn)
991 ),
992-- AXIS 4: build links between LOINC Measurements and SNOMED Attributes using Components of LOINC Measurements and name similarity of SNOMED Attributes
993lc_attr_2 AS (
994SELECT DISTINCT r1.concept_code_1 AS lc_code,
995 lc.concept_name AS lc_name, -- preserved for word-pattern filtering
996 r1.relationship_id,
997 x1.attr_code AS attr_code
998FROM concept_relationship_stage r1
999 JOIN concept_stage lc
1000 ON (lc.concept_code,lc.vocabulary_id) = (r1.concept_code_1,r1.vocabulary_id_1) -- LOINC Measurement
1001 AND r1.vocabulary_id_1 = 'LOINC' and lc.standard_concept = 'S' AND lc.invalid_reason IS NULL AND lc.domain_id = 'Measurement'
1002 JOIN concept_stage la
1003 ON (la.concept_code,la.vocabulary_id) = (r1.concept_code_2,r1.vocabulary_id_2) -- LOINC Component
1004 AND r1.vocabulary_id_2 = 'LOINC'
1005 AND r1.relationship_id = 'Has component'
1006 JOIN sn_attr x1 on (
1007 LOWER (SPLIT_PART (la.concept_name,'.',1)) = LOWER (x1.attr_name)
1008 OR LOWER (SPLIT_PART (la.concept_name,'^',1)) = LOWER (x1.attr_name)) -- SNOMED Attribute
1009 WHERE (r1.concept_code_1, r1.relationship_id) not in (select lc_code, relationship_id from lc_sn)
1010AND (r1.concept_code_1, r1.relationship_id) not in (select lc_code, relationship_id from lc_attr_1) -- exclude duplicates
1011)
1012-- get input
1013SELECT DISTINCT lc_code, lc_name, relationship_id, attr_code
1014FROM (SELECT * FROM lc_attr_add
1015UNION ALL
1016SELECT * FROM lc_sn
1017UNION ALL
1018SELECT * FROM lc_attr_1
1019UNION ALL
1020SELECT * FROM lc_attr_2) lc
1021-- weed out LOINC Measurements with inapplicable properties in the SNOMED architecture context
1022 JOIN sources.loinc j
1023 ON j.loinc_num = lc.lc_code
1024 AND j.property !~ 'Rto|Ratio|^\w.Fr|Imp|Prid|Zscore|Susc|^-$'-- exclude ratio/interpretation/identifier/z-score/susceptibility-related concepts
1025WHERE lc_name !~* 'susceptibility|protein\.monoclonal') -- susceptibility may have property other than 'Susc'
1026;
1027
1028DROP INDEX if exists sn_attr_idx;
1029CREATE index sn_attr_idx ON sn_attr (attr_code);
1030ANALYZE sn_attr;
1031
1032DROP INDEX if exists sn_code_1;
1033create index sn_code_1 on sn_attr (sn_code);
1034ANALYZE sn_attr;
1035
1036DROP INDEX if exists lc_code_1;
1037create index lc_code_1 on lc_attr (lc_code);
1038ANALYZE lc_attr;
1039
1040--14.3.2 Build hierarchical links of 'Is a' from LOINC Measurements to SNOMED Measurements in CONCEPT_RELATIONSHIP_STAGE using common attribute combinations (top-down)
1041INSERT INTO concept_relationship_stage (
1042 concept_code_1,
1043 concept_code_2,
1044 vocabulary_id_1,
1045 vocabulary_id_2,
1046 relationship_id,
1047 valid_start_date,
1048 valid_end_date,
1049 invalid_reason
1050 )
1051 -- AXIS 1: get 3-attribute Measurements (Component+Specimen+Scale)
1052WITH ax_1 AS
1053(
1054 SELECT DISTINCT z4.lc_code,
1055 z4.lc_name, -- to preserve names for word-pattern filtering
1056 x3.sn_code,
1057 x3.sn_name
1058FROM sn_attr x1 -- X1 - SNOMED attribute pool
1059 JOIN lc_attr z1 -- Z1 - LOINC attribute pool
1060 ON x1.attr_code = z1.attr_code -- common Component
1061 AND x1.relationship_id = 'Has component'
1062 AND z1.relationship_id = 'Has component'
1063 JOIN sn_attr x2
1064 ON x2.sn_code = x1.sn_code -- common 2-attribute SNOMED Measurement
1065 JOIN lc_attr z2
1066 ON x2.attr_code = z2.attr_code -- common Site
1067 AND z2.relationship_id IN ('Has dir proc site', 'Inheres in') -- given by the source relationships indicating SNOMED Specimens
1068 AND x2.relationship_id = 'Has specimen'
1069 JOIN sn_attr x3
1070 ON x3.sn_code = x2.sn_code -- common 3-attribute SNOMED Measurement
1071 JOIN lc_attr z3
1072 ON z3.attr_code = x3.attr_code -- common Scale
1073 AND z3.relationship_id = 'Has scale type'
1074 AND x3.relationship_id = 'Has scale type'
1075 JOIN lc_attr z4
1076 ON z4.lc_code = z3.lc_code
1077 AND z4.lc_code = z2.lc_code
1078 AND z4.lc_code = z1.lc_code -- common 3-attribute LOINC Measurement
1079WHERE x1.sn_code IN (SELECT sn_code
1080 FROM sn_attr
1081 GROUP BY sn_code
1082 HAVING COUNT(1) = 3) -- to restrict SNOMED attribute pool
1083),
1084-- AXIS 2: get 2-attribute Measurements (Component+Specimen)
1085ax_2
1086AS
1087(SELECT DISTINCT z3.lc_code,
1088 z3.lc_name,
1089 x2.sn_code,
1090 x2.sn_name
1091FROM sn_attr x1 -- X1 - SNOMED attribute pool
1092 JOIN lc_attr z1 -- Z1 - LOINC attribute pool
1093 ON x1.attr_code = z1.attr_code -- common Component
1094 AND x1.relationship_id = 'Has component'
1095 AND z1.relationship_id = 'Has component'
1096 JOIN sn_attr x2 ON x2.sn_code = x1.sn_code -- common 2-attribute SNOMED Measurement
1097 JOIN lc_attr z2
1098 ON x2.attr_code = z2.attr_code -- common Site
1099 AND z2.relationship_id IN ('Has dir proc site', 'Inheres in') -- given by the source relationships indicating SNOMED Specimens
1100 AND x2.relationship_id IN ('Has specimen')
1101 JOIN lc_attr z3
1102 ON z3.lc_code = z2.lc_code
1103 AND z3.lc_code = z1.lc_code -- common 2-attribute LOINC Measurement
1104WHERE x1.sn_code IN (SELECT sn_code
1105 FROM sn_attr
1106 GROUP BY sn_code
1107 HAVING COUNT(1) = 2) -- to restrict SNOMED attribute pool
1108AND z3.lc_code NOT IN (SELECT lc_code FROM ax_1) -- exclude duplicates
1109),
1110-- AXIS 3: get 2-attribute Measurements (Component+Scale)
1111ax_3 AS (
1112SELECT DISTINCT z3.lc_code,
1113 z3.lc_name,
1114 x2.sn_code,
1115 x2.sn_name
1116FROM sn_attr x1 --X1 - SNOMED attribute pool
1117 JOIN lc_attr z1 -- Z1 - LOINC attribute pool
1118 ON x1.attr_code = z1.attr_code -- common Component
1119 AND x1.relationship_id = 'Has component'
1120 AND z1.relationship_id = 'Has component'
1121 JOIN sn_attr x2 ON x2.sn_code = x1.sn_code -- common 2-attribute SNOMED Measurement
1122 JOIN lc_attr z2
1123 ON x2.attr_code = z2.attr_code -- common Scale
1124 AND z2.relationship_id = 'Has scale type'
1125 AND x2.relationship_id = 'Has scale type'
1126 JOIN lc_attr z3
1127 ON z3.lc_code = z2.lc_code
1128 AND z3.lc_code = z1.lc_code -- common 2-attribute LOINC Measurement
1129WHERE x1.sn_code IN (SELECT sn_code
1130 FROM sn_attr
1131 GROUP BY sn_code
1132 HAVING COUNT(1) = 2) -- to restrict SNOMED attribute pool
1133AND z3.lc_code NOT IN (SELECT lc_code FROM ax_1) -- exclude duplicates
1134),
1135-- AXIS 4: get 1-attribute Measurements (Component)
1136ax_4
1137AS
1138(SELECT DISTINCT z1.lc_code,
1139 z1.lc_name,
1140 x1.sn_code,
1141 x1.sn_name
1142FROM sn_attr x1 --X1 - SNOMED attribute pool
1143 JOIN lc_attr z1 -- Z1 - LOINC attribute pool
1144 ON x1.attr_code = z1.attr_code-- common Component
1145 AND x1.relationship_id = 'Has component'
1146 AND z1.relationship_id = 'Has component'
1147WHERE x1.sn_code IN (SELECT sn_code
1148 FROM sn_attr
1149 GROUP BY sn_code
1150 HAVING COUNT(1) = 1) -- to restrict SNOMED attribute pool
1151AND z1.lc_code NOT IN (SELECT lc_code FROM ax_1)
1152AND z1.lc_code NOT IN (SELECT lc_code FROM ax_2)
1153AND z1.lc_code NOT IN (SELECT lc_code FROM ax_3) -- exclude duplicates
1154 ),
1155-- unite all AXES
1156 all_ax
1157AS
1158(SELECT * FROM ax_1
1159UNION
1160SELECT * FROM ax_2
1161UNION
1162SELECT * FROM ax_3
1163UNION
1164SELECT * FROM ax_4)
1165-- get input for CONCEPT_RELATIONSHIP_STAGE
1166SELECT lc_code AS concept_code_1,
1167--lc_name,
1168 sn_code AS concept_code_2,
1169-- sn_name,
1170 'LOINC' AS vocabulary_id_1,
1171 'SNOMED' AS vocabulary_id_2,
1172 'Is a' AS relationship_id,
1173 v.latest_update AS valid_start_date,
1174 TO_DATE('20991231','yyyymmdd') AS valid_end_date,
1175 NULL AS invalid_reason
1176FROM all_ax
1177JOIN vocabulary v ON 'LOINC' = v.vocabulary_id -- to get latest update
1178 -- get rid of wrong SNOMED concepts with the same sets of attributes
1179WHERE (lc_code,sn_code) NOT IN (
1180 SELECT lc_code, sn_code FROM all_ax
1181 WHERE sn_name ~* 'dipstick' AND lc_name !~* 'test strip'
1182 OR (lc_name ~* 'titer|presence' AND sn_name ~* ' level')
1183 OR (lc_name !~* 'titer' AND sn_name ~* ' titer')
1184 OR (lc_name ~* '\/' AND sn_name ~* ' titer')
1185 OR (lc_name !~* 'count|100|#' AND sn_name ~* 'count')
1186 OR (lc_name ~* 'morpholog|presence' AND sn_name ~* 'count')
1187 OR (lc_name !~* 'fasting glucose' AND sn_name ~* 'fasting glucose')
1188 OR (lc_name !~* 'microscop' AND sn_name ~* 'microscop')
1189 OR (lc_name !~* 'culture|isolate' AND sn_name ~* 'culture')
1190 )
1191-- note, some LOINC Measurements may be mapped to 2 SNOMED Measurements
1192AND (lc_code,sn_code) NOT IN (SELECT lc_code,sn_code
1193 FROM all_ax
1194 WHERE lc_code IN (SELECT lc_code FROM all_ax GROUP BY lc_code HAVING COUNT(1) > 1)
1195 AND (lc_name ~* 'fasting glucose' AND sn_name !~* 'fasting glucose' OR lc_name ~* 'test strip' AND sn_name !~* 'dipstick')
1196 AND (lc_code,sn_code) NOT IN (SELECT lc_code,sn_code FROM all_ax WHERE sn_name ~* 'quantitative')
1197 )
1198;
1199
1200--14.4 Build hierarchical links 'Is a' from LOINC Lab Tests to SNOMED Measurements with the use of LOINC Component - SNOMED Attribute name similarity in CONCEPT_RELATIONSHIP_STAGE
1201INSERT INTO concept_relationship_stage (
1202 concept_code_1,
1203 concept_code_2,
1204 vocabulary_id_1,
1205 vocabulary_id_2,
1206 relationship_id,
1207 valid_start_date,
1208 valid_end_date,
1209 invalid_reason
1210 )
1211SELECT DISTINCT l1.concept_code AS concept_code_1,
1212 s1.concept_code AS concept_code_2,
1213 'LOINC' AS vocabulary_id_1,
1214 'SNOMED' AS vocabulary_id_2,
1215 'Is a' AS relationship_id,
1216 v.latest_update AS valid_start_date,
1217 TO_DATE('20991231', 'yyyymmdd') AS valid_end_date,
1218 NULL AS invalid_reason
1219-- get LOINC Components for all LOINC Measurements
1220FROM concept_relationship_stage r
1221JOIN concept_stage l1 ON (r.concept_code_1, r.vocabulary_id_1) = (l1.concept_code, l1.vocabulary_id) -- LOINC Measurement
1222AND l1.vocabulary_id = 'LOINC' AND l1.standard_concept = 'S' AND l1.invalid_reason is null
1223JOIN concept_stage l2 ON (r.concept_code_2, r.vocabulary_id_2) = (l2.concept_code, l2.vocabulary_id) -- LOINC Component
1224AND r.relationship_id = 'Has component' AND r.vocabulary_id_1 = 'LOINC'
1225AND r.vocabulary_id_2 = 'LOINC'
1226-- get SNOMED Measurements using name similarity (LOINC Component||' measurement' = SNOMED Measurement)
1227JOIN concept s1 ON
1228COALESCE (
1229LOWER (SPLIT_PART (l2.concept_name,'^',1))||' measurement', LOWER (SPLIT_PART (l2.concept_name,'.',1))||' measurement', lower (l2.concept_name)||' measurement'
1230 ) = lower (s1.concept_name) -- SNOMED Measurement
1231AND s1.vocabulary_id = 'SNOMED' AND s1.domain_id = 'Measurement' AND s1.standard_concept = 'S'
1232JOIN vocabulary v ON 'LOINC' = v.vocabulary_id -- get valid_start_date
1233-- weed out LOINC Measurements with inapplicable properties in the SNOMED architecture context
1234JOIN sources.loinc j ON l1.concept_code = j.loinc_num
1235 AND j.property !~ 'Rto|Ratio|^\w.Fr|Imp|Prid|Zscore|Susc|^-$'-- ratio/interpretation/identifier/z-score/susceptibility-related concepts
1236WHERE l1.concept_name !~* 'susceptibility|protein\.monoclonal' -- susceptibility may have property other than 'Susc'
1237AND s1.concept_code NOT IN ('16298007', '24683000') -- 'Rate measurement', 'Uptake measurement'
1238AND l1.concept_code NOT IN (SELECT concept_code_1 FROM concept_relationship_stage WHERE relationship_id = 'Is a' and vocabulary_id_2 = 'SNOMED'); -- exclude duplicates
1239
1240--14.5 drop temporary tables
1241DROP TABLE if exists sn_attr;
1242DROP TABLE if exists lc_attr;
1243
1244--14.6 drop temporary links (currently, the source does not support the LOINC-SNOMED refsets, so we do not have to add such links to CDM)
1245DELETE
1246FROM concept_relationship_stage
1247WHERE vocabulary_id_1 = 'LOINC'
1248 AND vocabulary_id_2 = 'SNOMED'
1249 AND relationship_id <> 'Is a'
1250;
1251
1252--15. Build 'LOINC - CPT4 eq' relationships (mappings) from LOINC Measurements to CPT4 Measurements or Procedures with the use of a 'sources.cpt_mrsmap' table (mappings)
1253INSERT INTO concept_relationship_stage (
1254 concept_code_1,
1255 concept_code_2,
1256 vocabulary_id_1,
1257 vocabulary_id_2,
1258 relationship_id,
1259 valid_start_date,
1260 valid_end_date,
1261 invalid_reason
1262 )
1263SELECT l.fromexpr AS concept_code_1, -- LOINC code
1264 UNNEST(STRING_TO_ARRAY(l.toexpr, ',')) AS concept_code_2, -- CPT4 code
1265 'LOINC' AS vocabulary_id_1,
1266 'CPT4' AS vocabulary_id_2,
1267 'LOINC - CPT4 eq' AS relationship_id,
1268 v.latest_update AS valid_start_date,
1269 TO_DATE('20991231', 'yyyymmdd') AS valid_end_date,
1270 NULL AS invalid_reason
1271FROM sources.cpt_mrsmap l,
1272 vocabulary v
1273WHERE v.vocabulary_id = 'LOINC';
1274
1275--16. Build 'Concept replaced by' relationships for updated LOINC concepts and deprecate already existing replacing mappings with the use of a 'sources.map_to' table
1276INSERT INTO concept_relationship_stage (
1277 concept_code_1,
1278 concept_code_2,
1279 vocabulary_id_1,
1280 vocabulary_id_2,
1281 relationship_id,
1282 valid_start_date,
1283 valid_end_date,
1284 invalid_reason
1285 )
1286SELECT l.loinc AS concept_code_1, -- updated LOINC concept
1287 l.map_to AS concept_code_2, -- replacing LOINC concept
1288 'LOINC' AS vocabulary_id_1,
1289 'LOINC' AS vocabulary_id_2,
1290 'Concept replaced by' AS relationship_id,
1291 v.latest_update AS valid_start_date,
1292 TO_DATE('20991231', 'yyyymmdd') AS valid_end_date,
1293 NULL AS invalid_reason
1294FROM sources.map_to l,
1295 vocabulary v
1296WHERE v.vocabulary_id = 'LOINC'
1297
1298UNION ALL
1299
1300--for some pairs of concepts LOINC gives us a reverse mapping 'Concept replaced by' so we need to deprecate such old mappings
1301SELECT c1.concept_code,
1302 c2.concept_code,
1303 c1.vocabulary_id,
1304 c2.vocabulary_id,
1305 r.relationship_id,
1306 r.valid_start_date,
1307 (
1308 SELECT latest_update - 1
1309 FROM vocabulary
1310 WHERE vocabulary_id = 'LOINC'
1311 AND latest_update IS NOT NULL
1312 ),
1313 'D'
1314FROM concept c1,
1315 concept c2,
1316 concept_relationship r,
1317 sources.map_to mt
1318WHERE c1.concept_id = r.concept_id_1
1319 AND c2.concept_id = r.concept_id_2
1320 AND c1.vocabulary_id = 'LOINC'
1321 AND c2.vocabulary_id = 'LOINC'
1322 AND r.relationship_id IN (
1323 'Concept replaced by',
1324 'Maps to'
1325 )
1326 AND r.invalid_reason IS NULL
1327 AND mt.map_to = c1.concept_code
1328 AND mt.loinc = c2.concept_code;
1329
1330--17. Add LOINC Document Ontology concepts with the use of a 'sources.loinc_documentontology' table to the CONCEPT_STAGE
1331INSERT INTO concept_stage (
1332 concept_name,
1333 domain_id,
1334 vocabulary_id,
1335 concept_class_id,
1336 standard_concept,
1337 concept_code,
1338 valid_start_date,
1339 valid_end_date,
1340 invalid_reason
1341 )
1342SELECT DISTINCT d.partname AS concept_name, -- LOINC Document name
1343 'Meas Value' AS domain_id,
1344 'LOINC' AS vocabulary_id,
1345 CASE d.parttypename
1346 WHEN 'Document.TypeOfService'
1347 THEN 'Doc Type of Service'
1348 WHEN 'Document.SubjectMatterDomain'
1349 THEN 'Doc Subject Matter'
1350 WHEN 'Document.Role'
1351 THEN 'Doc Role'
1352 WHEN 'Document.Setting'
1353 THEN 'Doc Setting'
1354 WHEN 'Document.Kind'
1355 THEN 'Doc Kind'
1356 END AS concept_class_id,
1357 'S' AS standard_concept, -- LOINC Document code
1358 d.partnumber AS concept_code,
1359 v.latest_update AS valid_start_date,
1360 TO_DATE('20991231', 'yyyymmdd') AS valid_end_date,
1361 NULL AS invalid_reason
1362FROM sources.loinc_documentontology d,
1363 vocabulary v
1364WHERE v.vocabulary_id = 'LOINC'
1365 AND d.partname NOT LIKE '{%}';-- decision to exclude LP173061-5 '{Settings}' and LP187187-2 '{Role}' PartNames was probably made due to vague reverse relationship formulations: Concept X 'Has setting' '{Setting}' or Concept Y 'Has role' {Role}.
1366
1367--18. Build 'Has type of service', 'Has subject matter', 'Has role', 'Has setting', 'Has kind' reverse relationships from LOINC concepts indicating Measurements or Observations to LOINC Document Ontology concepts
1368INSERT INTO concept_relationship_stage (
1369 concept_code_1,
1370 concept_code_2,
1371 vocabulary_id_1,
1372 vocabulary_id_2,
1373 relationship_id,
1374 valid_start_date,
1375 valid_end_date,
1376 invalid_reason
1377 )
1378SELECT d.loincnumber AS concept_code_1, -- LOINC Meas/Obs code
1379 d.partnumber AS concept_code_2, -- LOINC Document code
1380 'LOINC' AS vocabulary_id_1,
1381 'LOINC' AS vocabulary_id_2,
1382 CASE d.parttypename
1383 WHEN 'Document.TypeOfService'
1384 THEN 'Has type of service'
1385 WHEN 'Document.SubjectMatterDomain'
1386 THEN 'Has subject matter'
1387 WHEN 'Document.Role'
1388 THEN 'Has role'
1389 WHEN 'Document.Setting'
1390 THEN 'Has setting'
1391 WHEN 'Document.Kind'
1392 THEN 'Has kind'
1393 END AS relationship_id,
1394 v.latest_update AS valid_start_date,
1395 TO_DATE('20991231', 'yyyymmdd') AS valid_end_date,
1396 NULL AS invalid_reason
1397FROM sources.loinc_documentontology d,
1398 vocabulary v
1399WHERE v.vocabulary_id = 'LOINC'
1400 AND d.partname NOT LIKE '{%}';
1401
1402--19. Add hierarchical LOINC Group Category and Group concepts to the CONCEPT_STAGE
1403INSERT INTO concept_stage (
1404 concept_name,
1405 domain_id,
1406 vocabulary_id,
1407 concept_class_id,
1408 standard_concept,
1409 concept_code,
1410 valid_start_date,
1411 valid_end_date,
1412 invalid_reason
1413 )
1414--add LOINC Group Categories
1415SELECT DISTINCT trim (lgt.category) AS concept_name, -- LOINC Category name from sources.loinc_grouploincterms
1416 'Measurement' AS domain_id,
1417 v.vocabulary_id AS vocabulary_id,
1418 'LOINC Group' AS concept_class_id,
1419 'C' AS standard_concept,
1420 lg.parentgroupid AS concept_code, -- LOINC Category code from sources.loinc_group
1421 v.latest_update AS valid_start_date,
1422 TO_DATE('20991231', 'yyyymmdd') AS valid_end_date,
1423 NULL AS invalid_reason
1424FROM sources.loinc_group lg -- table with codes of LOINC Category concepts
1425JOIN sources.loinc_grouploincterms lgt ON lg.groupid = lgt.groupid -- table with names of LOINC Category concepts
1426JOIN vocabulary v ON v.vocabulary_id = 'LOINC'
1427WHERE lgt.category IS NOT NULL
1428
1429UNION ALL
1430
1431--add LOINC Groups
1432SELECT trim (lg.lgroup) AS concept_name, -- LOINC Group name
1433 'Measurement' AS domain_id,
1434 v.vocabulary_id AS vocabulary_id,
1435 'LOINC Group' AS concept_class_id,
1436 'C' AS standard_concept,
1437 lg.groupid AS concept_code, -- LOINC Group code
1438 v.latest_update AS valid_start_date,
1439 TO_DATE('20991231', 'yyyymmdd') AS valid_end_date,
1440 NULL AS invalid_reason
1441FROM sources.loinc_group lg
1442JOIN vocabulary v ON v.vocabulary_id = 'LOINC';
1443
1444--20. Build 'Is a' relationships to create a hierarchy for LOINC Group Categories and Groups
1445INSERT INTO concept_relationship_stage (
1446 concept_code_1,
1447 concept_code_2,
1448 vocabulary_id_1,
1449 vocabulary_id_2,
1450 relationship_id,
1451 valid_start_date,
1452 valid_end_date,
1453 invalid_reason
1454 )
1455-- from LOINC concepts indicating Measurements and Observations to LOINC Groups using sources.loinc_grouploincterms
1456SELECT lgt.loincnumber AS concept_code_1, -- LOINC Observation or Measurement concepts
1457 lgt.groupid AS concept_code_2, --LOINC Group code
1458 'LOINC' AS vocabulary_id_1,
1459 'LOINC' AS vocabulary_id_2,
1460 'Is a' AS relationship_id,
1461 v.latest_update AS valid_start_date,
1462 TO_DATE('20991231', 'yyyymmdd') AS valid_end_date,
1463 NULL AS invalid_reason
1464FROM sources.loinc_grouploincterms lgt
1465JOIN vocabulary v ON v.vocabulary_id = 'LOINC'
1466JOIN concept_stage cs1 ON cs1.concept_code = lgt.groupid --LOINC Group code
1467JOIN concept_stage cs2 ON cs2.concept_code = lgt.loincnumber -- LOINC Observation or Measurement concepts
1468
1469UNION ALL
1470
1471--from LOINC Groups to LOINC Group Categories using sources.loinc_group
1472SELECT lg.groupid AS concept_code_1, -- LOINC Group code as a descendant
1473 lg.parentgroupid AS concept_code_2, -- LOINC Group Category code as an ancestor
1474 'LOINC' AS vocabulary_id_1,
1475 'LOINC' AS vocabulary_id_2,
1476 'Is a' AS relationship_id,
1477 v.latest_update AS valid_start_date,
1478 TO_DATE('20991231', 'yyyymmdd') AS valid_end_date,
1479 NULL AS invalid_reason
1480FROM sources.loinc_group lg
1481JOIN vocabulary v ON v.vocabulary_id = 'LOINC'
1482JOIN concept_stage cs1 ON cs1.concept_code = lg.parentgroupid -- LOINC Group Category code
1483JOIN concept_stage cs2 ON cs2.concept_code = lg.groupid;-- LOINC Group code
1484
1485--21. Add LOINC Group Categories and Groups to the CONCEPT_SYNONYM_STAGE
1486INSERT INTO concept_synonym_stage (
1487 synonym_concept_code,
1488 synonym_name,
1489 synonym_vocabulary_id,
1490 language_concept_id
1491 )
1492-- add proper name of LOINC Group Categories and Groups (probably to simplify perception of their long descriptions)
1493SELECT cs.concept_code AS synonym_concept_code, -- proper name of LOINC Group Categories and Groups
1494 cs.concept_name AS synonym_name,
1495 'LOINC' AS synonym_vocabulary_id,
1496 4180186 AS language_concept_id -- English
1497FROM concept_stage cs
1498WHERE cs.concept_class_id = 'LOINC Group'
1499
1500UNION
1501
1502-- add long descriptions of LOINC Group Categories and Groups
1503SELECT lpga.parentgroupid AS synonym_concept_code, -- LOINC Group Category code
1504 SUBSTR(lpga.lvalue, 1, 1000) AS synonym_name, -- long description of LOINC Group Categories
1505 'LOINC' AS synonym_vocabulary_id,
1506 4180186 AS language_concept_id -- English
1507FROM sources.loinc_parentgroupattributes lpga;-- table with descriptions of LOINC Group Categories
1508
1509--22. Add Chinese language synonyms (AVOF-2231) from UMLS
1510insert into concept_synonym_stage
1511 (synonym_name,synonym_concept_code,synonym_vocabulary_id,language_concept_id)
1512select
1513 m.str,
1514 c.concept_code,
1515 'LOINC',
1516 4182948 --Chinese language
1517from concept_stage c
1518join sources.mrconso m on
1519 m.code = concept_code and
1520 sab = 'LNC-ZH-CN'
1521;
1522
1523--23. Working with replacement mappings
1524DO $_$
1525BEGIN
1526 PERFORM VOCABULARY_PACK.CheckReplacementMappings();
1527END $_$;
1528
1529--24. Add mapping from deprecated to fresh concepts
1530DO $_$
1531BEGIN
1532 PERFORM VOCABULARY_PACK.AddFreshMAPSTO();
1533END $_$;
1534
1535--25. Delete ambiguous 'Maps to' mappings
1536DO $_$
1537BEGIN
1538 PERFORM VOCABULARY_PACK.DeleteAmbiguousMAPSTO();
1539END $_$;
1540
1541--26. Build reverse relationships. This is necessary for the next point.
1542INSERT INTO concept_relationship_stage (
1543 concept_code_1,
1544 concept_code_2,
1545 vocabulary_id_1,
1546 vocabulary_id_2,
1547 relationship_id,
1548 valid_start_date,
1549 valid_end_date,
1550 invalid_reason
1551 )
1552SELECT crs.concept_code_2,
1553 crs.concept_code_1,
1554 crs.vocabulary_id_2,
1555 crs.vocabulary_id_1,
1556 r.reverse_relationship_id,
1557 crs.valid_start_date,
1558 crs.valid_end_date,
1559 crs.invalid_reason
1560FROM concept_relationship_stage crs
1561JOIN relationship r ON r.relationship_id = crs.relationship_id
1562WHERE NOT EXISTS (
1563 -- the inverse record
1564 SELECT 1
1565 FROM concept_relationship_stage i
1566 WHERE crs.concept_code_1 = i.concept_code_2
1567 AND crs.concept_code_2 = i.concept_code_1
1568 AND crs.vocabulary_id_1 = i.vocabulary_id_2
1569 AND crs.vocabulary_id_2 = i.vocabulary_id_1
1570 AND r.reverse_relationship_id = i.relationship_id
1571 );
1572
1573--27. Add to the concept_relationship_stage and deprecate all relationships which do not exist there
1574INSERT INTO concept_relationship_stage (
1575 concept_code_1,
1576 concept_code_2,
1577 vocabulary_id_1,
1578 vocabulary_id_2,
1579 relationship_id,
1580 valid_start_date,
1581 valid_end_date,
1582 invalid_reason
1583 )
1584SELECT a.concept_code,
1585 b.concept_code,
1586 a.vocabulary_id,
1587 b.vocabulary_id,
1588 relationship_id,
1589 r.valid_start_date,
1590 CURRENT_DATE,
1591 'D'
1592FROM concept a
1593JOIN concept_relationship r
1594 ON a.concept_id = concept_id_1
1595 AND r.invalid_reason IS NULL
1596 AND r.relationship_id NOT IN (
1597 'Concept replaced by',
1598 'Concept replaces'
1599 )
1600JOIN concept b
1601 ON b.concept_id = concept_id_2
1602WHERE
1603 a.vocabulary_id = 'LOINC'
1604 AND b.vocabulary_id IN ('LOINC', 'SNOMED')
1605 AND a.concept_id <> b.concept_id
1606 AND NOT EXISTS (
1607 SELECT 1
1608 FROM concept_relationship_stage crs_int
1609 WHERE crs_int.concept_code_1 = a.concept_code
1610 AND crs_int.concept_code_2 = b.concept_code
1611 AND crs_int.vocabulary_id_1 = a.vocabulary_id
1612 AND crs_int.vocabulary_id_2 = b.vocabulary_id
1613 AND crs_int.relationship_id = r.relationship_id
1614 );
1615
1616-- 28. Deprecate 'Maps to' mappings to deprecated and upgraded concepts
1617DO $_$
1618BEGIN
1619 PERFORM VOCABULARY_PACK.DeprecateWrongMAPSTO();
1620END $_$;
1621
1622
1623-- At the end, the three tables concept_stage, concept_relationship_stage and concept_synonym_stage should be ready to be fed into the generic_update.sql script
1624
1625
1626
1627
1628--stage tables checks
1629--RUN all queries from Vocabulary-v5.0/working/QA_stage_tables.sql
1630--All queries should retrieve NULL
1631
1632--DRUG stage tables checks
1633--RUN all queries from Vocabulary-v5.0/working/drug_stage_tables_QA.sql --All queries should retrieve NULL
1634--RUN all queries from Vocabulary-v5.0/working/Drug_stage_QA_optional.sql --All queries should retrieve NULL, but see comment inside
1635
1636
1637
1638--GenericUpdate; devv5 - static variable
1639DO $_$
1640BEGIN
1641 PERFORM devv5.GenericUpdate();
1642END $_$;
1643
1644
1645
1646
1647
1648--basic tables checks
1649--RUN all queries from Vocabulary-v5.0/working/CreateNewVocabulary_QA.sql --All queries should retrieve NULL
1650
1651
1652--DRUG basic tables checks
1653--RUN all queries from Vocabulary-v5.working/Basic_tables_QA.sql --All queries should retrieve NULL
1654
1655
1656--QA checks
1657--should retrieve NULL
1658select * from QA_TESTS.GET_CHECKS();
1659
1660
1661
1662
1663 DO $_$
1664 BEGIN
1665 PERFORM VOCABULARY_PACK.pManualConceptAncestor(
1666 pVocabularies => 'SNOMED,LOINC'
1667 );
1668 END $_$
1669;
1670
1671
1672--get_summary - changes in tables between dev-schema (current) and devv5/prodv5/any other schema
1673--supported tables: concept, concept_relationship, concept_ancestor
1674
1675--first clean cache
1676select * from qa_tests.purge_cache();
1677
1678
1679--summary (table to check, schema to compare)
1680select *
1681from qa_tests.get_summary (table_name=>'concept',pCompareWith=>'devv5');
1682
1683--summary (table to check, schema to compare)
1684select * from qa_tests.get_summary (table_name=>'concept_relationship',pCompareWith=>'devv5');
1685
1686--summary (table to check, schema to compare)
1687select * from qa_tests.get_summary (table_name=>'concept_ancestor',pCompareWith=>'devv5');
1688
1689--Statistics QA checks
1690--changes in tables between dev-schema (current) and devv5/prodv5/any other schema
1691select * from qa_tests.get_domain_changes(pCompareWith=>'devv5'); --Domain changes
1692select * from qa_tests.get_newly_concepts(pCompareWith=>'devv5'); --Newly added concepts grouped by vocabulary_id and domain
1693select * from qa_tests.get_standard_concept_changes(pCompareWith=>'devv5'); --Standard concept changes
1694select * from qa_tests.get_newly_concepts_standard_concept_status(pCompareWith=>'devv5'); --Newly added concepts and their standard concept status
1695select * from qa_tests.get_changes_concept_mapping(pCompareWith=>'devv5'); --Changes of concept mapping status grouped by target domain
1696
1697
1698--update LOINC Parts start date
1699UPDATE concept
1700SET valid_start_date = TO_DATE('19700101','yyyymmdd')
1701WHERE vocabulary_id = 'LOINC'
1702 AND concept_class_id IN ('LOINC Component', 'LOINC Method', 'LOINC Property', 'LOINC Scale', 'LOINC System', 'LOINC Time')
1703 AND valid_start_date = '2019-12-13'
1704;
1705
1706
1707DROP TABLE IF EXISTS concept_tmp_2020_03_04_3;
1708CREATE TABLE IF NOT EXISTS concept_tmp_2020_03_04_3
1709as (select * from concept);
1710
1711DROP TABLE IF EXISTS concept_relationship_tmp_2020_03_04_3;
1712CREATE TABLE IF NOT EXISTS concept_relationship_tmp_2020_03_04_3
1713as (select * from concept_relationship);
1714
1715DROP TABLE IF EXISTS concept_ancestor_tmp_2020_03_04_3;
1716CREATE TABLE IF NOT EXISTS concept_ancestor_tmp_2020_03_04_3
1717as (select * from concept_ancestor);
1718
1719
1720END $_2$
1721;