Measurement Relations

In this section, we explain how structured data for measurement relations is represented in the NLP API JSON output and in the Output Database schema. We also provide sample queries to illustrate the key aspects of the data schema.

After reading this page, we hope you will:

1. Understand what measurement relations are.
2. Understand what measurement value entities are and how they can be found.
3. Know how to query measurement relations.

The content in this section builds on the concepts and data models explained in earlier sections (see Entity-level Tables, and Document-level Tables)

What are Measurement Relations?

Measurements are ubiquitous in medical reports. The NLP API detects measurement relations in all sections of Clinical reports, and in the FINDINGS and IMPRESSIONS section of Radiology reports. A measurement relation has two arguments: the subject of the measurement, and the measurement value.

Let’s consider this sentence.

The patient had a sodium level of 140 mEq/L.

For the above sentence, the NLP API extracts the subject and value entities that comprise the measurement relation as shown:

The NLP API identifies the subject of the measurement (sodium level), and the measurement value (140 mEq/L) including measurement unit. The NLP API recognizes units of measure supported by the Unified Code for Units of Measure (UCUM) standard including units of measurement for multi-dimensional measurements. The following are some examples of sentences from which measurement relations will be extracted:

There is a 2.3 cm tumor in the left upper lobe of the lung. 
Within the left parietal lobe, there is a 1.2 x 2.1 x 1.8 cm (ML x AP x CC) mass which demonstrates extensive surrounding white matter low attenuation change consistent with edema.

The NLP API currently provides some support for the detection of measurement values that are unit-less. At the time of writing of this document, we have only enabled unitless measurement extraction for a limited number of lab tests, but we have plans to support a larger number of lab tests. If you are planning to utilize this feature in your NLP data extraction pipeline, we recommend that you contact us to discuss your requirements.

The following are examples of measurements commonly found in clinical notes:

The temperature was 101.
Sodium is 135, potassium 4.2, chloride 94, bicarbonate 31, BUN 21, creatinine 1.2 and glucose 408.
He had a fever to 102.5 , heart rate 126 , blood pressure 97 / 46 , respiratory rates 22 - 44.
The patients sodium level was 140.

Measurement Values

Measurement values are extracted as part of the measurement-relations feature when it is enabled. Note, however, that measurement values can also be extracted independently of the measurement relations by enabling the entity-measurement-unit feature during processing. The entity type of a measurement value can be used to query for measurement values independently of their association with measurement relations.

Measurement values, for example 140 mEq/L or 1.2 x 2.1 x 1.8 cm when found are extracted by the NLP API as regular entities. However, measurement value entities have an additional attribute: the measurement-unit attribute. This attribute stores the units of measurement associated with the measured value. See Measurement Units.

Measurement value entities are always associated with these following entity types (semantic type classification):

• emtelligent ontology: measurement
• medcin ontology: measurement

You can find measurement value entities by querying for entities with the above entity types. To understand how entities relate to entity types, see Entity-level Tables.

JSON Data Model

Consider the following sentence:

The patient had a sodium level of 140 mEq/L.

A measurement relation will be extracted from the sentence above as shown in the following JSON excerpt:

{
    "relations": {
        "measurements": [
            {
                "label": "RMS0",
                "attributes": {
                    "confidence": 1
                },
                "args": {
                    "value": {
                        "ref": "E0",
                        "text": [
                            "140 mEq/L"
                        ]
                    },
                    "subject": {
                        "ref": "E3",
                        "text": [
                            "sodium level"
                        ]
                    }
                },
                "concept_links": []
            }
        ]
    }
}

Notice the following:

• The relations object contains a measurements array. This array contains an object representing a measurement relation instance.
• A measurement relation instance has two named arguments: the subject and the value. Each is a reference to an Entity
• Each entity reference resolves to an object in the entities array. Key information about the entity is stored in its attributes member.

The the next JSON excerpt shows the JSON output for the patient entity

{
    "entities": {
        "found": [
            {
                "label": "E0",
                "spans": [
                    {
                        "start": 34,
                        "end": 43
                    }
                ],
                "section_name": "INTRO",
                "attributes": {
                    "heading_status": "outside_heading",
                    "polarity": "asserted",
                    "uncertainty": "certain",
                    "question_status": "not_question",
                    "guidance": "not_guidance",
                    "measurement_unit": [
                        "mEq/L"
                    ],
                    "known_ambiguity": "unambiguous"
                },
                "concept_links": [
                    "Csnomed0",
                    "Cumls_loinc0"
                ],
                "locations": [
                    "S1",
                    "SEC-0"
                ],
                "entity_type": {
                    "umls_loinc": "Clinical Attribute",
                    "snomed": "attribute",
                    "umls": "Quantitative Concept",
                    "emtelligent": "measurement value"
                },
                "concept_confidences": {},
                "text": [
                    "140 mEq/L"
                ]
            }
        ]
    }
}

For brevity, only the entity (E0) representing the measurement value is shown. Notice that measurement values may have units associated with them. This information is captured in the measurement_units attribute and is stored as an array of strings, or null if no units were found. Measurement units are discussed in detail in the next section.

Measurement Units

Measurement value entities may have additional attributes which other entities do not: the measurement_unit. The units of measurement that are present will be extracted and stored in this attribute. The measurement unit attribute values can be the following:

• null if no units are provided
• an array of strings containing one element. For example, the NLP API extracts a single unit of measurement, mEq/L from the measurement value entity 140 mEq/L.
• an array of strings containing multiple elements. The NLP API can extract multiple units of measurement when they are explicitly mentioned. For example, from the measurement value term 1.2 cm x 2.1 cm x 1.8 cm, the measurement_unit attribute value is ['cm', 'cm', 'cm'].

It should be noted that for multi-dimensional measurement values, only the units explicitly stated are captured in the measurement unit attribute. For example, the measurement value written as 1.2 x 2.1 cm x 1.8 cm, will be extracted as ['cm', 'cm'].

Measurement Relation Tables

The following ER diagram, shows the relationship between the measurementrelation table and related entity tables, in particular the foundentitymeasurementunit table.

From the diagram above, notice the following key points about the measurement relation schema:

• The measurementrelation table contains foreign key IDs for the entity table for the measurement subject and the measurement value: subject_id and value_id keys respectively.
• By joining these keys with the primary key of the entity table, you can retrieve information stored in entity-related tables such as associated concepts, entity types and location for the entity. See Entity-level Tables.
• Data about the measurement value’s units is stored in the foundentitymeasurementunit table.
• The foundentity table has a one-to-many relation to the foundentitymeasurementunit table. As discussed in previous sections, this allows the NLP API to capture the units for multi-dimensional values.
• Notice also that polarity and uncertainty attributes are tracked for entities, both the subject and the value entities. Polarity and uncertainty are not attributes of the measurement relation itself.

Sample Queries

In this section, we present examples of SQL queries that retrieve information about measurements that NLP API has extracted from your reports. The first three queries show how to work with the measurementrelation table, while the fourth is a practical example that combines a concept search with measurement relations to find mentions of a specific diagnostic test (e.g. forced ejection fraction volume) and corresponding results when present.

This section contains the following queries:

• Query: Find measurement values
• Query: List measurement relations showing subject, values and units
• Query: List measurement relations with no units
• Query: Find all FEV1 scores, their values and units

Query: Find measurement values

In the sample query below, we are looking for any entity that is a measurement value. To do this we simply query for measurement value entities using the foundentity table, where the found entity has an entity type of measurement value and entity type ontology is emtelligent.

Notice, however, that we also apply a window function to concatenate the measurement units from the foundentitymeasurementunit table into a comma-delimited string. This is necessary because one measurement value entity may be associated with multiple units.

This query is not very useful because it does not tell us what was measured. To extract this information about the subject (the entity being measured), we need to use measurement relations. The next sample query shows how to query measurement relations to retrieve the subject and the value of the measurement.

SELECT val_fet.type_name as value_type, val_fe.text as value_text,
    -- aggregate because measurement values can have more than one units associated with it
    string_agg(val_femu.text, ' x ') OVER (PARTITION BY val_femu.found_entity_id) AS units
FROM foundentity val_fe
        JOIN foundentitytype val_fet
        ON val_fe.id = val_fet.found_entity_id
        LEFT JOIN foundentitymeasurementunit val_femu
        ON val_fe.id = val_femu.found_entity_id
WHERE val_fet.ontology = 'snomed' -- classifying ontology
AND val_fet.type_name = 'attribute'  -- semantic type in SNOMED
LIMIT 5;

Query: List measurement relations showing subject, values and units

This query finds measurement relations and displays the annotated terms for the subject and value arguments, and some of their attributes. To provide context, this query also displays the sentence which contains the measurement relation.

To find information about measurement relations we begin with the measurementrelations table. From there various JOIN clauses are used to retrieve the attributes about the subject and value entities from the tables that store them in the NLP API’s relational database schema. These include: the foundentity and foundentitymeasurementunit tables.

Because this query touches a large number of tables, it may appear to be complex. However, it is quite straight-forward. The key is to ensure you keep track of whether you are retrieving entity information for the subject or the value of the measurement relation in each JOIN clause. The query consist of three parts:

1. Part 1: Retrieve the attributes of the subject argument by performing an inner join between the measurementrelation table subject_id with the entity table id key.
2. Part 2: Retrieve the attributes of the value argument by performing an inner join between the measurementrelation table value_id with the entity table id key. When retrieving attributes for the measurement value argument, we perform a left join with the foundentitymeasurementunit table to retrieve the value’s units in addition to the other foundentity-related tables.
3. Part 3: Retrieve the sentence where the measurement subject was found by joining with the foundentitylocation table which then allows us to access the location and document tables. For more information about document-related attributes see Document-level Tables. Note: in the results table, the sentence is redacted for privacy reasons.

SELECT measurementrelation.id,
   sub_fe.text                                                                AS subject,
   val_fe.text                                                                AS value,
   string_agg(femu_val.text, ' x ') OVER (PARTITION BY measurementrelation.id) AS units,
   sub_fe.polarity                                                             AS sub_polarity,
   val_fe.polarity                                                             AS val_polarity,
   sub_sl.text AS sentence
FROM measurementrelation
     -- 1. retrieve the entity attributes of the subject of the measurement relation
     JOIN entity sub_e
          ON measurementrelation.subject_id = sub_e.id AND sub_e.type_ = 'found'
     JOIN foundentity sub_fe
          ON sub_e.entity_id = sub_fe.id
-- 2. retrieve the entity attributes of the value of the measurement relation
     JOIN entity val_e
          ON measurementrelation.value_id = val_e.id AND val_e.type_ = 'found'
     JOIN foundentity val_fe
          ON val_e.entity_id = val_fe.id
-- 3. retrieve sentence by joining with location and document tables using the subject's foundentity id   << INTERNAL >> moved this to before the LEFT JOIN statement.
     JOIN foundentitylocation sub_fel
          ON sub_fe.id = sub_fel.found_entity_id
     JOIN location sub_l
          ON sub_fel.location_id = sub_l.id AND sub_l.type_ = 'sentence'
     JOIN sentencelocation sub_sl
          ON sub_sl.id = sub_l.location_id 
     LEFT JOIN foundentitymeasurementunit femu_val -- retrieve units associated with a measurement value. LEFT JOIN required !
               ON femu_val.found_entity_id = val_fe.id
ORDER BY 1

Query: List measurement relations with no units

In this query, we want to retrieve measurement relations whose measurement values have no units.

This query is essentially the same as the previous query. For each measurement relation, we retrieve and display the subject and value arguments, their attributes and the sentence where the measurement relation was found. However, to return only those measurement relations where the measurement is ‘unit-less’, we make two changes:

1. We perform a left join on the foundentitymeasurementunit table and the foundentity table.
2. In the WHERE clause, we filter for rows where the text column of the foundentitymeasurementunit table (which stores the unit of measurement) is null.

Tip: You may find more results in Clinical reports which typically have more measurement values with implied units, than in reports such as Radiology and Pathology reports. Because the query below JOINs with the document table, you can easily filter for the type of report to include by adding a logical condition to the WHERE clause: for example, document.category = 'Clinical'.

SELECT measurementrelation.id, sub_fe.text AS subject, val_fe.text AS value,
    femu_val.text as unit,
    sub_fe.polarity AS sub_polarity,
    val_fe.polarity AS val_polarity,
    sub_sl.text AS sentence
FROM measurementrelation
        -- 1. retrieve the entity attributes of the subject of the measurement relation 
        JOIN entity sub_e
        ON measurementrelation.subject_id = sub_e.id AND sub_e.type_='found'
        JOIN foundentity sub_fe
        ON sub_e.entity_id = sub_fe.id
        -- 2. retrieve the entity attributes of the value of the measurement relation
        JOIN entity val_e
        ON measurementrelation.value_id = val_e.id AND val_e.type_='found'
        JOIN foundentity val_fe
        ON val_e.entity_id = val_fe.id
        -- 3. retrieve sentence by joining with location and document tables using the subject's foundentity id
        JOIN foundentitylocation sub_fel
        ON sub_fe.id = sub_fel.found_entity_id
        JOIN location sub_l
        ON sub_fel.location_id = sub_l.id AND sub_l.type_ = 'sentence'
        JOIN sentencelocation sub_sl
        ON sub_sl.id = sub_l.location_id 
        LEFT JOIN foundentitymeasurementunit femu_val -- Note: LEFT JOIN required
        ON femu_val.found_entity_id = val_fe.id
WHERE femu_val.text IS NULL -- required to find  unitless measurements
ORDER BY 1;

Query: Find all FEV1 scores, their values and units

Forced expired (or expiratory) volume in one second, is also commonly referred to as FEV1, is a measurement obtained during spirometry/pulmonary function testing. The SNOMED concept for this measurement is 251944000 |Forced expired volume in one second/forced vital capacity ratio (observable entity)| and 59328004 |Forced expired volume in 1 second (observable entity)|. The measurement value for FEV1 scores is typically given as a percentage or in litres.

In this query, we want to retrieve all occurrences of FEV1 related terms. If the measurement values and/or units are provided, we want to extract them also. If no measurement values are provided e.g. FEV1 score is normal, we want to return a row in the result set displaying NULL for values and units when they are not given.

This query is composed of 2 CTEs:

• The first CTE, FEV1_mentions, performs a simple concept query to find the foundentities associated with this term.
• The second CTE, FEV1_values, retrieves values from the measurementrelation table and units from foundentitymeasurementunit table. Note that a measurement value may have several units, hence a string aggregation function is used.

In the final select statement, we combine the results of both CTEs using a LEFT JOIN to ensure that FEV1 mentions with no accompanying measurement units is retained in the results.

WITH fev1_mentions AS (
    -- retrieve fev1 mentions
    SELECT fe.id as subject_feid, e.id as subject_eid, fe.text as subject_term
    FROM foundentity fe
        JOIN entity e on e.entity_id = fe.id AND e.type_ = 'found'
        JOIN foundentityconcept fec on fe.id = fec.found_entity_id
        JOIN concept c on fec.concept_id = c.concept_id and fec.concept_ontology = c.ontology
    WHERE
        c.ontology = 'snomed' AND c.concept_id IN ('59328004','251944000') -- FEV1 and FEV1:FVC ratio concepts
),
fev1_values AS (
-- retrieve  values and units when present
SELECT mr.id as mrel_id,
        mr.subject_id as subject_eid,
        val_fe.id as value_feid,
        val_fe.text as value_term,
        string_agg( val_femu.text, ', ') as units
FROM measurementrelation mr
    JOIN fev1_mentions fm ON mr.subject_id = fm.subject_eid
    JOIN entity val_e on mr.value_id = val_e.id AND val_e.type_='found'
    JOIN foundentity val_fe ON val_e.entity_id = val_fe.id
    -- retrieve unit(s) for value term if present (using LEFT JOIN)
    LEFT JOIN foundentitymeasurementunit val_femu ON val_fe.id = val_femu.found_entity_id
GROUP BY mrel_id, mr.subject_id, value_feid, value_term
)
SELECT
    fm.subject_term,
    fv.value_term,
    fv.units,
    s.text as sentence
FROM fev1_mentions fm
    -- retrieve value terms if available (LEFT JOIN is required)
    LEFT JOIN fev1_values fv ON fm.subject_eid = fv.subject_eid
    -- retrieve sentence using the subject entity
    JOIN foundentitylocation fel on fm.subject_feid = fel.found_entity_id
    JOIN location l on fel.location_id = l.id AND l.type_='sentence'
    JOIN sentencelocation s on l.location_id = s.id
    JOIN document d on l.document_id = d.id;

Utility SQL Queries

Below are some helpful data exploration queries for measurement relations.

-- Find the number of measurement relations in the data
SELECT count(*) from measurementrelation;
-- Count of measurement relations by the subject's concept and section
SELECT sub_c.description, sub_fe.section_name, count(*) as count
FROM measurementrelation
        JOIN entity sub_e
        ON measurementrelation.subject_id = sub_e.id AND sub_e.type_='found'
        JOIN foundentity sub_fe
        ON sub_e.entity_id = sub_fe.id
        JOIN foundentityconcept sub_fec
        ON sub_fec.found_entity_id = sub_fe.id
        JOIN concept sub_c
        ON sub_fec.concept_id = sub_c.concept_id AND sub_fec.concept_ontology = sub_c.ontology
WHERE sub_c.ontology = 'snomed' -- other ontologies can be used
GROUP BY sub_c.description, sub_fe.section_name
LIMIT 5;
-- Count of measurements by subject's entity type (aka semantic type)
SELECT sub_fet.type_name, count(*) as count
FROM measurementrelation
        JOIN entity sub_e
        ON measurementrelation.subject_id = sub_e.id AND sub_e.type_='found'
        JOIN foundentity sub_fe
        ON sub_e.entity_id = sub_fe.id
        JOIN foundentitytype sub_fet
        ON sub_fe.id = sub_fet.found_entity_id
WHERE sub_fet.ontology = 'snomed' -- ontology used for semantic type classification of concepts. Other ontologies can be used e.g. 'umls'
GROUP BY sub_fet.type_name
LIMIT 5;
-- List entity types for entities that are measurement values
SELECT val_fet.ontology, val_fet.type_name
FROM measurementrelation
        JOIN entity val_e
        ON measurementrelation.value_id = val_e.id AND val_e.type_='found'
        JOIN foundentity val_fe
        ON val_e.entity_id = val_fe.id
        JOIN foundentitytype val_fet
        ON val_fe.id = val_fet.found_entity_id
GROUP BY val_fet.ontology, val_fet.type_name
LIMIT 5;