OHDSI Home | Forums | Wiki | Github

Location Region inclusion criteria resulting in errors when generating a Cohort Definition

Implementers
, ,
#1

Our team has a local setup of Atlas and WebAPI 2.8.2 to use with our patient data in an Oracle database. One of the requirements from our users is that they need to be able to search on Location information, e.g. find all patients with a particular condition that live in a certain geographic region (like a city or state). We have converted our patient data to CDM 5.4, which contains a Location table (we have confirmed that this Location table is populated).

We have created a Concept Set in Atlas that contains a city (e.g. “Albuquerque”) and then we have created a Cohort Definition with an inclusion criteria of “Location Region” that uses the previously created location concept set (here called “Location Concept Set”):

image
image3080×662 182 KB

When we try to generate the cohort from our CDM source, we get a “Generation Status” of ‘FAILED’. Clicking this gives us a somewhat unhelpful SQL error that in part says:

SQL state [null]; error code [0]; Failed to execute batch update; nested exception is java.sql.SQLException: Failed to execute batch update

When we check the Atlas logs, we are able to see the full output of the SQL, including a snippet that is referencing the CDM.LOCATION_HISTORY table:

SELECT C.entity_id AS person_id,
  C.location_history_id as event_id,
  C.start_date as start_date,
  NVL(C.end_date,TO_DATE(TO_CHAR(2099,'0000')||'-'||TO_CHAR(12,'00')||'-'||TO_CHAR(31,'00'), 'YYYY-MM-DD')) as end_date,
  CAST(NULL as NUMBER(19)) as visit_occurrence_id,
  C.start_date as sort_date
FROM (SELECT l.*, lh.*
  FROM CDM.LOCATION l
    JOIN CDM.LOCATION_HISTORY lh ON l.location_id = lh.location_id
JOIN OHDSI.z98fsb3nCodesets codesets on ((l.region_concept_id = codesets.concept_id and codesets.codeset_id = 3))
    WHERE lh.domain_id = 'PERSON'
 ) C
-- End Location region Criteria

The LOCATION_HISTORY table does not exist in the CDM 5.4 spec. It was going to be introduced in CDM 6.0 but that version appears to have been scrapped for now (OMOP version 6 - put on ice?). I also found this somewhat related OHDSI forum post asking a similar question about location (Location information in Atlas (Geography, State)) but it was never confirmed if Atlas with CDM 5.4 supported filtering on Location data.

Can someone confirm if using Atlas against CDM 5.4 data should allow us to use Location as an inclusion criteria in a cohort? And if so, why is Atlas + WebAPI 2.8.2 expecting the existence of a LOCATION_HISTORY table, which was only introduced in the unused CDM 6.0? Is there any way to fix and/or work around this?

As a final note, I did try a similar location concept set + cohort definition creation in the OHDSI Atlas Demo site and the generation of the cohort definition failed with an error that includes:

nested exception is org.postgresql.util.PSQLException: ERROR: relation "synpuf1k.location_history" does not exist
  Position: 893

So the LOCATION_HISTORY appears to be a problem even on the demo site…

Any help would be greatly appreciated!

1 Like
#2

So the location region criteria type is only cdm 6, and we haven’t implemented a function (yet) where if you use attributes from a certain CDM version it will restrict you from generating on a CDM source that doesn’t meet the version requirements.

If you want to use location in your queries in 5.4, I believe there is a function where you can check the location of the place of service:

image
image1086×187 9.85 KB

In this case, you’d build a concept set of Location concepts (which have a hierarchy of regions (ie: country->state->county) which would let you restrict criteria to visits within a region.

I have not used this function at all, so I can’t discuss too deeply on things to consider or limitations.

t