| OHDSI Home | Forums | Wiki | Github |
Hello,
My goal is to add new observation values to the observation table and map those observations to new concepts in the concept tables. Previously, my lab would use the concept_id 0 as the placeholder. My question is then, are there conventions in place to create new concept_ids beyond just using 0?
Thank you,
Rich M
A bit more info, we have observations that are generated from the data but that cannot be described using concepts from any of the standard vocabulary terminologies. For example, we define an observation for ‘prolonged psychotropic exposure’ that is custom to our lab’s analysis and cannot be mapped to SNOMED. We would still like to load those observations because the vast majority of the other patient data uses standard vocab concepts and we want to do as much work as possible within the OHDSI CDM and architecture.
Hi @rkboyce, in our environment, we have similar situations. Our general
approach is that we store these non-standard elements in the _SOURCE_VALUE
field, and then set concept_id = 0 (for unmapped concept), that way, we
preserve the source data and can query for it as desired. I would
generally advocate against trying to create ‘in-house concept_ids’ since
they’d be local and no useable elsewhere anyway.
Hi - In our case, we’ve done what @Patrick_Ryan describes for one-off cases, but created local concepts for either 1) situations in which a particular value is mixed with standard values in the data (e.g. a custom lab result), and we want to be able to phrase queries against foo_concept_id for all cases, or 2) situations in which we want to record relationships among network-local concepts in concept_relationship so they’re explicit in the database.
Here’s our checklist for new terms, in case it’s helpful:
vocabulary_id, domain_id, and concept_class_id exist in the concept table and in the appropriate metadata table. If not, recurse.concept table. Use the next available value in the concept ID sequence (2000000000,3000000000].concept_relationship:insert into concept_relationship (concept_id_1, concept_id_2, relationship_id, invalid_reason, valid_start_date, valid_end_date) select concept_id, concept_id, 'Maps to', NULL, valid_start_date, valid_end_date from concept where concept_id =n;
insert into concept_relationship (concept_id_1, concept_id_2, relationship_id, invalid_reason, valid_start_date, valid_end_date) select concept_id, concept_id, 'Mapped from', NULL, valid_start_date, valid_end_date from concept where concept_id =n;
source_to_concept_map so everything is in one place), insert the appropriate ‘Maps to’/‘Mapped from’ rows into concept_relationship.concept_relationship as appropriate.concept_ancestor: insert into concept_ancestor (ancestor_concept_id, descendant_concept_id, min_levels_of_separation, max_levels_of_separation) select concept_id, concept_id, 0, 0 from concept where concept_id =n;. You may also want to include other mappings if the new concept is part of a hierarchy.For what it is worth - I was really interested in figuring out / finding a protocol for this as well. Great thread.
Friends:
@bailey totally described it. I will add that to the documentation.
About new concepts in existing vocabularies: We now established a relationship with SNOMED though Jim Case. We already submitted a few humble change and addition requestions, and the miracle happened: They accepted some of them. So, this is something we can now take advantage off. If you think you need a term (like @rkboyce’s “prolonged psychotropic exposure”) let’s discuss and then submit.
@rkboyce: I assume you mean chronic illegal drug abuse, correct? I cannot believe there isn’t anything in SNOMED. Are you sure?