Getting Cohorts Using WebAPI

Exercise

Using the OHDSI WebAPI

Get cohort definition for Acute Myocardial Infraction
Populate Eunomia GiBleed cohort table with patients satisfying this definition

We defined this cohort using R and sql in the tutorial here.

I have already defined this cohort on the Atlas demo site, but it could potentially be deleted in the future so the links below could be broken at some point. To be safe, I have preserved the cohort definition and concept sets used in

resources/ami-cohort

Cohort Definition

Myocardial Infraction Cohort

Concept Sets Used

Packages

HADES packages used

Eunomia for connecting to a synthetic OMOP CDM database.
DatabaseConnector for connecting to our Eunomia dataset and populating the cohort table.
ROhdsiWebApi for interfacing with the OHDSI WebAPI to get a cohort definition from the Atlas demo site.

Installation

install.packages("remotes")

# Hades packages
install.packages("Eunomia")
remotes::install_github("OHDSI/ROhdsiWebApi")
install.packages("DatabaseConnector")

Get AMI Cohort Definition

base_url <- "https://api.ohdsi.org/WebAPI"
cohort_id <- 1790272
generate_stats <- F

# If webapi is down or our cohort was deleted, we will just load the sql from our local file
sql <- tryCatch({
  # First, get the cohort definition
  cohort_definition <- ROhdsiWebApi::getCohortDefinition(
    cohortId = cohort_id,
    baseUrl = base_url
  )
  
  # Pretty complicated looking. Have a look if you want
  #str(cohort_definition)
  
  # Next, get the OHDSI sql using the cohort definition
  ROhdsiWebApi::getCohortSql(
    cohortDefinition = cohort_definition,
    baseUrl = base_url,
    generateStats = generate_stats
  )
}, error = function() {
  readLines("resources/ami-cohort/cohort-definition/ami-cohort.sql")
})

Populate Cohort Table

Connect to Database

datasetName = "GiBleed"
dbms = "sqlite"

datasetLocation <- Eunomia::getDatabaseFile(
  datasetName = datasetName, 
  dbms = dbms, 
  databaseFile = tempfile(fileext = ".sqlite")
)

attempting to download GiBleed

attempting to extract and load: C:\Users\mccul\AppData\Local\Temp\RtmpqARFGu/GiBleed_5.3.zip to: C:\Users\mccul\AppData\Local\Temp\RtmpqARFGu/GiBleed_5.3.sqlite

connectionDetails <- DatabaseConnector::createConnectionDetails(dbms = dbms, server = datasetLocation)
connection = DatabaseConnector::connect(connectionDetails = connectionDetails)

Connecting using SQLite driver

DatabaseConnector::querySql(connection = connection, sql = "select count(*) from cohort;")

ABCDEFGHIJ0123456789

COUNT(*) <dbl>
0

Add Visits

I’m adding some visits so we get some results in the cohort. Fudging the data a bit…

source("util.R")
insert_visits(connection)

[1] "Adding visits..."


insert into concept (
  concept_id,
  concept_name,
  domain_id,
  vocabulary_id,
  concept_class_id,
  standard_concept,
  concept_code,
  valid_start_date,
  valid_end_date,
  invalid_reason
)
values (
  262--concept_id,
  ,'Emergency Room and Inpatient Visit'--concept_name,
  ,'Visit'--domain_id,
  ,'Visit'--vocabulary_id,
  ,'Visit'--concept_class_id,
  ,'S'--standard_concept,
  ,'ERIP'--concept_code,
  ,strftime('%s', '1970-01-01 00:00:00')--valid_start_date,
  ,strftime('%s', '2099-12-31 00:00:00')--valid_end_date,
  ,null--invalid_reason
);

delete from visit_occurrence where visit_occurrence_id >= 1000000;
insert into visit_occurrence(
  visit_occurrence_id
  ,person_id
  ,visit_concept_id
  ,visit_start_date
  ,visit_start_datetime
  ,visit_end_date
  ,visit_end_datetime
  ,visit_type_concept_id
  ,provider_id
  ,care_site_id
  ,visit_source_value
  ,visit_source_concept_id
  ,admitting_source_concept_id
  ,admitting_source_value
  ,discharge_to_concept_id
  ,discharge_to_source_value
  ,preceding_visit_occurrence_id
)
values(
  1000000--visit_occurrence_id
  ,1491--person_id
  ,9201--visit_concept_id
  ,strftime('%s', '2011-07-11 12:34:56')--visit_start_date
  ,strftime('%s', '2011-07-11 12:34:56')--visit_start_datetime
  ,strftime('%s', '2011-07-13 12:34:56')--visit_end_date
  ,strftime('%s', '2011-07-13 12:34:56')--visit_end_datetime
  ,0--visit_type_concept_id
  ,null--provider_id
  ,null--care_site_id
  ,null--visit_source_value
  ,0--visit_source_concept_id
  ,0--admitting_source_concept_id
  ,null--admitting_source_value
  ,0--discharge_to_concept_id
  ,null--discharge_to_source_value
  ,null--preceding_visit_occurrence_id
), 
(
  1000001--visit_occurrence_id
  ,105--person_id
  ,262--visit_concept_id
  ,strftime('%s', '2005-11-10 12:34:56')--visit_start_date
  ,strftime('%s', '2005-11-10 12:34:56')--visit_start_datetime
  ,strftime('%s', '2005-11-13 12:34:56')--visit_end_date
  ,strftime('%s', '2005-11-13 12:34:56')--visit_end_datetime
  ,0--visit_type_concept_id
  ,null--provider_id
  ,null--care_site_id
  ,null--visit_source_value
  ,0--visit_source_concept_id
  ,0--admitting_source_concept_id
  ,null--admitting_source_value
  ,0--discharge_to_concept_id
  ,null--discharge_to_source_value
  ,null--preceding_visit_occurrence_id
),
(
  1000002--visit_occurrence_id
  ,96--person_id
  ,262--visit_concept_id
  ,strftime('%s', '1997-01-24 12:34:56')--visit_start_date
  ,strftime('%s', '1997-01-24 12:34:56')--visit_start_datetime
  ,strftime('%s', '1997-01-26    12:34:56')--visit_end_date
  ,strftime('%s', '1997-01-26 12:34:56')--visit_end_datetime
  ,0--visit_type_concept_id
  ,null--provider_id
  ,null--care_site_id
  ,null--visit_source_value
  ,0--visit_source_concept_id
  ,0--admitting_source_concept_id
  ,null--admitting_source_value
  ,0--discharge_to_concept_id
  ,null--discharge_to_source_value
  ,null--preceding_visit_occurrence_id
),
(
  1000003--visit_occurrence_id
  ,3049--person_id
  ,9203--visit_concept_id
  ,strftime('%s', '1986-12-04 12:34:56')--visit_start_date
  ,strftime('%s', '1986-12-04 12:34:56')--visit_start_datetime
  ,strftime('%s', '1986-12-06    12:34:56')--visit_end_date
  ,strftime('%s', '1986-12-06 12:34:56')--visit_end_datetime
  ,0--visit_type_concept_id
  ,null--provider_id
  ,null--care_site_id
  ,null--visit_source_value
  ,0--visit_source_concept_id
  ,0--admitting_source_concept_id
  ,null--admitting_source_value
  ,0--discharge_to_concept_id
  ,null--discharge_to_source_value
  ,null--preceding_visit_occurrence_id
),
(
  1000004--visit_occurrence_id
  ,2692--person_id
  ,9201--visit_concept_id
  ,strftime('%s', '1998-06-28 12:34:56')--visit_start_date
  ,strftime('%s', '1998-06-28 12:34:56')--visit_start_datetime
  ,strftime('%s', '1998-06-30 12:34:56')--visit_end_date
  ,strftime('%s', '1998-06-30 12:34:56')--visit_end_datetime
  ,0--visit_type_concept_id
  ,null--provider_id
  ,null--care_site_id
  ,null--visit_source_value
  ,0--visit_source_concept_id
  ,0--admitting_source_concept_id
  ,null--admitting_source_value
  ,0--discharge_to_concept_id
  ,null--discharge_to_source_value
  ,null--preceding_visit_occurrence_id
)
;

  |                                                                            
  |                                                                      |   0%
  |                                                                            
  |=======================                                               |  33%
  |                                                                            
  |===============================================                       |  67%
  |                                                                            
  |======================================================================| 100%

Executing SQL took 0.00916 secs

Add Subjects

DatabaseConnector::renderTranslateExecuteSql(connection = connection, 
                                             sql = sql, 
                                             vocabulary_database_schema="main", 
                                             cdm_database_schema="main", 
                                             target_database_schema="main",
                                             target_cohort_table = "cohort",
                                             target_cohort_id = "1")

Executing SQL took 0.0567 secs

Check Results

We should get the same subjects as we did in the defining cohorts table. Let’s check

Subjects in original: (1491, 105, 96, 3049, 2692)

sql <- "
  select * from @cdm.cohort;
"
cdm <- "main"
DatabaseConnector::renderTranslateQuerySql(
  connection = connection,
  sql = sql,
  cdm = cdm
)

ABCDEFGHIJ0123456789

COHORT_DEFINITION_ID <chr>	SUBJECT_ID <chr>	COHORT_START_DATE <date>	COHORT_END_DATE <date>
1	96	1997-01-25	1997-02-01
1	105	2005-11-11	2005-11-18
1	1491	2011-07-12	2011-07-19
1	2692	1998-06-29	1998-07-06
1	3049	1986-12-05	2018-05-04

Great, we got the same subjects.

See the characterization tutorial for how to get some interesting stats on these subjects.