Exercise 3: OMOP integration

Work in progress. These tutorial materials are still under active development and will continue to change until the tutorial takes place on October 20, 2026. Content, links, and exercises may be incomplete or shift without notice.

Goal

Understand and query the person-place-time model, validate vocabulary roles, and calculate temporally aligned exposure metrics: mean PM2.5 during pregnancy, and mean PM2.5 in the year before a COPD diagnosis. Then build cohorts stratified by exposure with the Capr extension.

OMOP is person/event-centric; geospatial sources are place/time/attribute-centric. The bridge between them is: PERSON/clinical event ← LOCATION_HISTORY → LOCATION ← derived EXTERNAL_EXPOSURE. LOCATION_HISTORY captures entity, location, relationship, and start/end dates (supporting residential and other relationship types, while avoiding overlap ambiguity). EXTERNAL_EXPOSURE captures what, when, where, how related, source, and the numeric/categorical value and unit. Standard concepts always win over lexical-only mappings: an existing exact Standard concept is used when available, and a provisional source concept is retained or mapped rather than invented.

You continue with the omopgis.external_exposure table you populated in Exercise 2 (or the fallback file). All SQL runs in the gaia-db container (docker compose exec gaia-db psql -U postgres -d gaiacore).

Steps

Part A: vocabulary roles

Look at the mini vocabulary. The dataset ships with a small OMOP vocabulary in the omopgis schema (concept, concept_ancestor, concept_relationship, vocabulary, and so on): the standard concepts used by the data plus the OMOP GIS (geometry semantics), OMOP Exposome (toxicants), and OMOP SDoH (contextual determinants) concepts. See how many concepts each vocabulary contributes:
```
SELECT vocabulary_id, COUNT(*) AS n_concepts FROM omopgis.concept GROUP BY 1 ORDER BY 2 DESC;
```
Check the role of every concept in external_exposure. Each concept column should hold a concept from the right vocabulary and class:
```
SELECT ee.exposure_concept_id, c1.concept_name AS exposure_concept, c1.vocabulary_id AS exposure_vocabulary,
       ee.exposure_relationship_concept_id, c2.concept_name AS relationship, c2.vocabulary_id AS relationship_vocabulary,
       ee.exposure_type_concept_id, c3.concept_name AS exposure_type, c3.concept_class_id,
       ee.exposure_source_value, ee.exposure_relationship_source_value,
       ee.unit_concept_id, COUNT(*) AS n
FROM omopgis.external_exposure ee
LEFT JOIN omopgis.concept c1 ON c1.concept_id = ee.exposure_concept_id
LEFT JOIN omopgis.concept c2 ON c2.concept_id = ee.exposure_relationship_concept_id
LEFT JOIN omopgis.concept c3 ON c3.concept_id = ee.exposure_type_concept_id
GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12;
```
The query returns two rows, one per exposure variable: monthly PM2.5, and the county SES index (Socioeconomic Status, with the SDOH Database as its type; one row per residence interval because it is static). Which vocabulary does each of the three concepts come from, and why? The type concept describes the kind of data source (here an “Exposure Type Concept”, Air Quality Database); the geometry type of the source (MultiPolygon) is recorded in Gaia’s backbone.geom_index, not on the exposure row. exposure_source_value holds the Gaia variable_source_id of the variable and exposure_relationship_source_value the verbatim spatial operator (ST_Within); compare it with the relationship concept (Within). Two things should bother you. The exposure concept is named “Annual Mean” of PM2.5, but each row is a monthly value: what does that mean for how you can use it? And unit_concept_id is empty: where is the unit declared, and what would you map it to?

Part B: mean PM2.5 during pregnancy

Find the pregnancy episodes. Pregnancy is modeled in the OMOP EPISODE table. Two persons have one, and the answer key is in demo.expected_result:
```
SELECT person_id, episode_start_date, episode_end_date FROM omopgis.episode;
SELECT fixture_tag, person_id, metric, value FROM demo.expected_result WHERE fixture_tag LIKE 'PREGNANCY%';
```
For each, list the LOCATION_HISTORY rows overlapping the episode. One person moved counties during the pregnancy.

Join exposure to the episode and calculate the mean, weighting each row by the number of days it overlaps the episode. Treating any overlap as full inclusion is the mistake this exercise is designed to catch: the first and last months overlap only partially.

WITH preg AS (
    SELECT person_id, episode_start_date AS s, episode_end_date AS e
    FROM omopgis.episode WHERE episode_object_concept_id = 4299535),   -- Pregnancy
ov AS (
    SELECT p.person_id, ee.value_as_number AS v,
           GREATEST(ee.exposure_start_date, p.s) AS os, LEAST(ee.exposure_end_date, p.e) AS oe
    FROM preg p
    JOIN omopgis.external_exposure ee
      ON ee.person_id = p.person_id AND ee.exposure_concept_id = 2052499839
     AND ee.exposure_start_date <= p.e AND ee.exposure_end_date >= p.s)
SELECT person_id, COUNT(*) AS n_rows,
       ROUND(AVG(v)::numeric, 4) AS naive_mean,
       ROUND((SUM(v * (oe - os + 1)) / SUM(oe - os + 1))::numeric, 4) AS weighted_mean,
       SUM(oe - os + 1) AS days_covered
FROM ov GROUP BY person_id ORDER BY person_id;

Compare both columns to demo.expected_result. Which one is correct? Why is the difference larger for the person who moved? Check that days_covered equals the length of the episode.

Part C: mean PM2.5 in the year before a COPD diagnosis

Calculate the 12-month lookback before each person’s first COPD diagnosis. Persons need a full 12 months of observation before the index date, and the window must end before the index date so no exposure after the diagnosis leaks in:

WITH idx AS (
    SELECT person_id, MIN(condition_start_date) AS index_date
    FROM omopgis.condition_occurrence WHERE condition_concept_id = 255573 GROUP BY person_id),
elig AS (
    SELECT i.person_id, i.index_date
    FROM idx i
    JOIN omopgis.observation_period op
      ON op.person_id = i.person_id
     AND i.index_date - 365 >= op.observation_period_start_date
     AND i.index_date <= op.observation_period_end_date),
ov AS (
    SELECT e.person_id, ee.value_as_number AS v,
           GREATEST(ee.exposure_start_date, e.index_date - 365) AS os,
           LEAST(ee.exposure_end_date, e.index_date - 1) AS oe
    FROM elig e
    JOIN omopgis.external_exposure ee
      ON ee.person_id = e.person_id AND ee.exposure_concept_id = 2052499839
     AND ee.exposure_start_date <= e.index_date - 1
     AND ee.exposure_end_date >= e.index_date - 365)
SELECT (SELECT COUNT(*) FROM idx) AS copd_cases,
       (SELECT COUNT(*) FROM elig) AS with_12m_lookback,
       ROUND(AVG(m)::numeric, 2) AS mean_lookback_pm25, ROUND(STDDEV(m)::numeric, 2) AS sd
FROM (SELECT person_id, SUM(v * (oe - os + 1)) / SUM(oe - os + 1) AS m FROM ov GROUP BY 1) x;

How many cases are lost because they were diagnosed in the first year of observation? What would happen to the mean if you let the window run to the end of the diagnosis month instead?

Check the SDOH covariate. Persons should have 12 SDOH observations. Find anyone who does not, and decide how a downstream feature should treat the missing value:
```
SELECT person_id, COUNT(*) FROM omopgis.observation GROUP BY 1 HAVING COUNT(*) < 12;
```

Part D: exposure-based cohorts with CaprForExtensions

You build the cohorts here and use them unchanged in Exercise 4, where CohortMethod compares them. The design is a fixed index date (2016-01-01) for everyone, with the exposure groups defined by the PM2.5 value on the index month, and COPD as the outcome:

Cohort id	Name	Definition
1	High PM2.5 at index	PM2.5 >= 10 µg/m³ in the month starting 2016-01-01, with 730 days of prior observation
2	Low PM2.5 at index	PM2.5 < 8 µg/m³ in the month starting 2016-01-01, with 730 days of prior observation
3	COPD	First COPD diagnosis
4	CKD (negative-control outcome)	First CKD diagnosis

Define the cohorts with CaprForExtensions. Run this in RStudio from the pre-built ohdsi/gaia-core:main image (started as described in the tutorial checklist; it has Capr, CirceR, and both extension packages installed, so there is nothing to install). external_exposure is registered as a custom domain so exposure criteria can be written like ordinary Capr criteria:

library(Capr)
library(CaprForExtensions)
library(DatabaseConnector)
library(SqlRender)

connectionDetails <- createConnectionDetails(
  dbms = "postgresql", server = "gaia-db/gaiacore", port = 5432,
  user = "postgres", password = "<provided>",
  pathToDriver = Sys.getenv("DATABASECONNECTOR_JAR_FOLDER"))
connection <- connect(connectionDetails)

registerCustomDomain(
  domain_id = "externalExposure",
  domain_name = "External Exposure",
  table = "external_exposure",
  schema = "@cdm_database_schema",
  person_id_field = "person_id",
  start_date_field = "exposure_start_date",
  end_date_field = "exposure_end_date",
  concept_id_field = "exposure_concept_id"
)

pm25 <- cs(2052499839, name = "PM2.5, CDC monthly county mean")
indexMonth <- dateRange("exposure_start_date", "2016-01-01", "2016-01-01")

highPm25 <- cohort(
  entry = entry(
    externalExposure(pm25, dateRange = indexMonth,
                     valueAsNumber = numericValue("value_as_number", ">=", 10)),
    observationWindow = continuousObservation(730, 0)),
  exit = exit(endStrategy = observationExit()))
lowPm25 <- cohort(
  entry = entry(
    externalExposure(pm25, dateRange = indexMonth,
                     valueAsNumber = numericValue("value_as_number", "<", 8)),
    observationWindow = continuousObservation(730, 0)),
  exit = exit(endStrategy = observationExit()))
copd <- cohort(entry = entry(conditionOccurrence(cs(255573, name = "COPD"))),
               exit = exit(endStrategy = observationExit()))
ckd  <- cohort(entry = entry(conditionOccurrence(cs(46271022, name = "CKD"))),
               exit = exit(endStrategy = observationExit()))

The package compiles extension criteria into placeholder observation queries, lets CirceR generate the SQL, and then substitutes the extension-table logic back in. Concept sets are resolved against the vocabulary tables, which the dataset provides in omopgis (use omopgis as both the CDM and the vocabulary schema).

Generate the cohorts into one cohort table, demo.cohort, with ids 1 to 4 as in the table above, and keep it for Exercise 4:

executeSql(connection, "CREATE TABLE demo.cohort (cohort_definition_id int, subject_id int,
                                                  cohort_start_date date, cohort_end_date date)")
definitions <- list(highPm25, lowPm25, copd, ckd)
for (i in seq_along(definitions)) {
  cohortJson <- compileExtendedCohort(definitions[[i]])
  options <- buildExtensionOptions(cohort_id = i, cdm_schema = "omopgis", target_schema = "demo",
                                   target_table = "cohort", generate_stats = FALSE)
  sql <- buildExtendedCohortQuery(cohortJson, options)   # read it: find where external_exposure appears
  sql <- translate(render(sql, warnOnMissingParameters = FALSE), targetDialect = "postgresql")
  executeSql(connection, sql, progressBar = FALSE)
}
querySql(connection, "SELECT cohort_definition_id, COUNT(DISTINCT subject_id) AS persons
                      FROM demo.cohort GROUP BY 1 ORDER BY 1")

Validate every cohort against hand-written SQL. For example, cohort 1:
```
SELECT COUNT(DISTINCT person_id) FROM omopgis.external_exposure
WHERE exposure_concept_id = 2052499839 AND exposure_start_date = '2016-01-01' AND value_as_number >= 10;
```
On the reference build the counts are 2,717 persons in cohort 1, 3,099 in cohort 2 (the 4,184 persons between 8 and 10 are in neither), 1,086 in cohort 3 and 1,384 in cohort 4, and the package reproduces them exactly. If Capr’s count and yours differ, find out why (inclusive dates, observation window, mover rows). Check that no person is in both cohort 1 and cohort 2, and suppress small counts in anything you share.

Notice what this design costs: the group is defined by one month’s value, which is a noisy proxy for a person’s long-run exposure (monthly values vary by about 2.5 µg/m³ around a county’s mean). Misclassification like this pulls the comparison toward the null. Where does that show up in Exercise 4?

Deliverable

Mean PM2.5 during pregnancy for the two persons (weighted and naive, with an explanation of the difference), the 12-month lookback exposure summary for COPD cases, the list of semantic fields your calculations depended on (concepts, relationship and exposure-type concepts, units, and the vocabulary each came from), and the four cohort definitions with counts and their SQL validation.

Check Your Work

Overlap weighting is correct: no interval is treated as fully included when it is only partial, and your weighted means match demo.expected_result
No post-outcome leakage: the lookback window ends the day before the index date
The mover’s pregnancy uses rows from both residences
You identified that the unit is declared in the Gaia catalog but empty in unit_concept_id, and that “annual mean” in the concept name does not match the monthly grain
Each concept is used in its correct role (an exposure concept is not confused with a relationship, exposure type, or unit concept)
Capr cohort counts match your SQL counts (or you can explain the difference), and small counts are suppressed in anything shared

Bridge to Session 4: validated exposure facts become covariates; they do not become causal effects automatically.

Previous: Exercise 2 | Next: Exercise 4

OHDSI GIS WG