Skip to main content

Classifying medicines with a concept map

The SQL on FHIR Terminology in SQL page defines how the content of a ValueSet or ConceptMap is presented to a SQL query: as a table that the query joins to, like any other. This example uses that approach to classify the medicines in a set of FHIR records, then answers two questions with SQL:

  • How many patients are on an active medicine in each class?
  • Which patients are on a bone resorption inhibitor but not on calcium?

The terminology is answered from a local terminology store, so no terminology server is needed. The columns of the relation and the functions that create it are described in Terminology in SQL.

The data​

The example uses two inputs:

  • FHIR resources in NDJSON format: Patient, MedicationStatement and MedicationRequest resources, with medicines coded using the Australian Medicines Terminology (AMT), which is part of SNOMED CT-AU.
  • A ConceptMap that relates AMT products to the classes they belong to, such as 373301002 |Bone resorption inhibitor| and 767178007 |Calcium and/or calcium compound|. A product may belong to many classes, and each class is a separate mapping with the equivalence relatedto.

The results below come from a synthetic cohort of 1,000 patients and a map of 925,540 mappings, with the canonical https://example.org/amt-extended-91b6e2c4/ConceptMap/product-to-classification|0.1.0.

Importing the concept map​

Import the ConceptMap into a local terminology store. This is done once; the store can then be reused by any number of sessions.

from pathling import PathlingContext

PathlingContext.create().import_fhir_terminology(
    "product-to-classification.json", "tx-store"
)

Flattening the resources​

Create a context in local terminology mode, pointing at the store. The concept map is read into the driver when its relation is created and held in the relation, so a map of this size needs a larger driver heap than the default (1 GB in Python, 2 GB in R); 4 GB was sufficient here.

Each resource type is then flattened with a ViewDefinition, and the result is registered as a temporary view so that SQL can refer to it by name. The ViewDefinition for MedicationStatement takes the SNOMED CT code of the medicine:

{
    "resourceType": "ViewDefinition",
    "name": "medication_statement",
    "resource": "MedicationStatement",
    "select": [
        {
            "column": [
                { "name": "id", "path": "getResourceKey()" },
                {
                    "name": "patient_id",
                    "path": "subject.getReferenceKey(Patient)"
                },
                { "name": "status", "path": "status" },
                {
                    "name": "code",
                    "path": "medication.ofType(CodeableConcept).coding.where(system = 'http://snomed.info/sct').code.first()"
                },
                {
                    "name": "display",
                    "path": "medication.ofType(CodeableConcept).coding.where(system = 'http://snomed.info/sct').display.first()"
                }
            ]
        }
    ]
}

The medication_request view has the same columns for MedicationRequest, and the patient view includes id, given and family.

from pathling import PathlingContext

pc = PathlingContext.create(
    spark_conf={"spark.driver.memory": "4g"},
    terminology_mode="local",
    terminology_storage_path="tx-store",
)

data = pc.read.ndjson("fhir")
for name in ["patient", "medication_statement", "medication_request"]:
    with open(f"views/{name}.json") as f:
        data.view(json=f.read()).createOrReplaceTempView(name)

Registering the concept map​

The concept map relation is registered under the name that the SQL uses for it, product_to_class. In a SQL on FHIR SQLView, this name would be the label of the ConceptMap's depends-on dependency; here, registering it is all that is needed.

The map has more mappings than the default cap of 100,000, so the cap is raised. A map that exceeds the cap is rejected rather than truncated.

pc.concept_map(
    "https://example.org/amt-extended-91b6e2c4/ConceptMap/product-to-classification|0.1.0",
    max_mappings=1_000_000,
).createOrReplaceTempView("product_to_class")

The relation has one row per mapping. Its first rows show one product with several classes (version columns omitted):

source_codesource_displaytarget_codetarget_displayrelationship
1000001000168107Rivaroxaban (Apo) 10 mg tablet, 30, bottle106205006Haemostasis related substancerelated-to
1000001000168107Rivaroxaban (Apo) 10 mg tablet, 30, bottle1091008Coagulation factor inhibitorrelated-to
1000001000168107Rivaroxaban (Apo) 10 mg tablet, 30, bottle12878005Thiophenerelated-to

The relationship column holds the R5 code related-to, converted from the R4 equivalence relatedto in the ConceptMap.

Classifying the medicines​

A view combines the statements and requests, keeps the active ones, and joins each medicine to its classes. A medicine in several classes produces several rows, and a medicine that the map does not classify produces none.

pc.spark.sql("""
    CREATE OR REPLACE TEMP VIEW medication_class AS
    WITH medication AS (
      SELECT id AS medication_id, patient_id, status, code, display
      FROM medication_statement
      UNION ALL
      SELECT id, patient_id, status, code, display
      FROM medication_request
    )
    SELECT m.medication_id,
           m.patient_id,
           m.code,
           m.display,
           c.target_code AS class_code,
           c.target_display AS class_display
    FROM medication m
    JOIN product_to_class c
      ON c.source_system = 'http://snomed.info/sct'
     AND c.source_code = m.code
     AND c.relationship = 'related-to'
    WHERE m.status = 'active'
""")

Patients in each class​

A patient is counted once in every class reached by any of their active medicines:

pc.spark.sql("""
    SELECT class_code,
           class_display,
           COUNT(DISTINCT patient_id) AS patients,
           COUNT(DISTINCT code) AS products
    FROM medication_class
    GROUP BY class_code, class_display
    ORDER BY patients DESC, class_code
""").show(10, truncate=False)
class_codeclass_displaypatientsproducts
372586001Antihypertensive agent429288
866173006Renin-angiotensin system inhibitor379253
372912004HMG-CoA reductase inhibitor318181
373267003Antilipaemic agent318181
372733002Angiotensin-converting enzyme inhibitor311169
373258003Gastrointestinal agent277123
372525000Proton pump inhibitor274120
372590004Ulcer healing agent274120
87918000Mineral18678
31212001Calcium compound18476

Bone resorption inhibitors without calcium​

This query finds the patients on a medicine in the class 373301002 who are on no medicine in the class 767178007. The test for calcium is a NOT EXISTS over the same view, so it uses the classes that the map assigns, not a list of products written into the query.

pc.spark.sql("""
    SELECT p.id AS patient_id,
           p.given,
           p.family,
           string_agg(DISTINCT m.display, '; ') AS bone_resorption_inhibitors
    FROM medication_class m
    JOIN patient p ON p.id = m.patient_id
    WHERE m.class_code = '373301002'
      AND NOT EXISTS (
        SELECT 1 FROM medication_class c
        WHERE c.patient_id = m.patient_id AND c.class_code = '767178007'
      )
    GROUP BY p.id, p.given, p.family
    ORDER BY p.id
""").show(5, truncate=False)

The query returns 33 patients, the first five of which are:

patient_idgivenfamilybone_resorption_inhibitors
Patient/cohort-0012GlenysSINGHAlendrobell Plus D3 70 mg/140 microgram tablet, 4
Patient/cohort-0038BeverleyANDERSONRaloxifene hydrochloride 60 mg tablet
Patient/cohort-0041PaulineWILSONAlendronate 10 mg tablet
Patient/cohort-0075PaulineJOHNSONAlendronate 70 mg + colecalciferol 70 microgram (2800 units) tablet
Patient/cohort-0085DianneKELLYRisedronate sodium 35 mg tablet

Further reading​