Missing concept entries in new v5 vocab download

Developers
1

All,

I’m setting up v5 on a fresh laptop using Postgres. I downloaded the CDM and conceptual vocabularies last week. When I attempt to apply the PostgreSQL CDM contraints to a loaded DB (using 1000 patient CMS sample) I’m getting a foreign key violation:

Concept 2313863

ALTER TABLE concept_relationship ADD CONSTRAINT fpk_concept_relationship_c_1 FOREIGN KEY (concept_id_1) REFERENCES concept (concept_id);

[Error] Script lines: 223-224 ----------------------
ERROR: insert or update on table “concept_relationship” violates foreign key constraint “fpk_concept_relationship_c_1”
Detail: Key (concept_id_1)=(2313863) is not present in table “concept”.
Line: 1 

SELECT * FROM concept_relationship where concept_id_1 = 2313863
=> 11 records with concept_id_1 = 2313863

select * from concept where concept_id = 2313863
=> 0 records

Concept 2108905

ALTER TABLE concept_relationship ADD CONSTRAINT fpk_concept_relationship_c_2 FOREIGN KEY (concept_id_2) REFERENCES concept (concept_id);

[Error] Script lines: 230-231 ----------------------
ERROR: insert or update on table “concept_relationship” violates foreign key constraint “fpk_concept_relationship_c_2”
Detail: Key (concept_id_2)=(2108905) is not present in table “concept”.
Line: 1 

SELECT * FROM concept_relationship where concept_id_1 = 2108905
=> 6 records

select * from concept where concept_id = 2108905
=> 0 records

1 Like
2

@wstephens:

Did you run the CPT4 utility? 2108905 is a CPT4 Concept. You need to run the CPT4 utility in order to get those codes in. We are not allowed to distribute CPT4, we have to have you download it from the UMLS. The utility does that automatically for you. I know it’s a stupid inconvenience, but we tried all methods of persuasion to not have to do it, to no avail. The AMA is stubbornly fixed on the idea that only the NLM and the CMS can distribute this vocabulary.

Let me know how it goes.

1 Like
3

Christian,

You can also register OHDSI to be an authorized content distributor with the UMLS, then authenticate users that wish to download terminologies using their authentication service. Details at https://uts.nlm.nih.gov//help/license/validateumlsuserhelp.html

This is actually required for many of the OHDSI terminologies, including SNOMED CT if you allow downloading content. (Even if not, you need their click-through license agreement to avoid liability; details here: http://ihtsdo.org/fileadmin/user_upload/doc/download/Ihtsdo_Guidance_PublicAccessBrowserAndTools_20141211.pdf)

We use something similar in our online browser, with a click-through license to browse SNOMED CT and then prompting for a UMLS license as required to access OHDSI terminologies. https://mq.b2i.sg

Hope this helps,
Brandon

4

@Brandon_Ulrich:

Good point. We should reconsider now that we have an automated vocabulary build and distribution system. The problem is that we haven’t got a specified application, but we are distributing the vocabularies to the users for all sorts of things observational research. That’s the usual problem, and that’s also what we ran into with the AMA. Anyway, we should still take another look and see whether we can tweak it.

5

Running that now.

Thanks.