Entity-level Tables

In this section, we explore the schema representation of Entities, their attributes, and how they relate to standardized ontologies via Concepts and Entity types. We will present sample queries to show how to retrieve useful information from the entity-level tables. We have also provided some helper queries at the end of the section that list various data labels, such as ontology names and semantic types, that can be used as filter parameters.

After reading this page, you will be able to:

1. Explore the entities extracted by the NLP API
2. Query for entities with specific attributes
3. Search for entities by ontology, concepts, or semantic types
4. Show the annotated terms that were detected by the NLP API’s entity recognition engine

Entity, FoundEntity and AssumedEntity Tables

Entity objects represent terms that are recognized and extracted by the NLP API. These are stored in the entity table. An entity can be of two types: found and assumed. Found entities are those entities that are explicitly mentioned in the report. Found entities are explicit references to the patient, recognized terms, and report sections. Assumed entities are implicit references to the patient, and are used when querying for Experiencer relations (see Experiencer Relations).

Information about FoundEntities is stored in the foundentity table. This table contains many key attributes such as the report section where the entity was found, the polarity, and uncertainty of the entity.

• The section_name column stores the heading of the report section where an entity was found. Knowing the section where an entity occurs provides important context for data analysis. For example, a disorder mentioned in the PAST MEDICAL HISTORY has a different connotation than a disorder mentioned in the DISCHARGE DIAGNOSIS section. To make data analysis easier, the NLP API normalizes section information by mapping commonly found headings to standardized headings. The value stored in the section column depends on the document type processed. For Radiology reports, for example, typical sections are INDICATION, TECHNIQUE, FINDING, IMPRESSION, COMPARISON, and ADDENDUM.
• The polarity table column has the value asserted or negated
• The uncertainty table column has the value certain or uncertain
• The heading_status table column that indicates if the entity is inside of or outside of a section heading has the value inside_heading or outside_heading
• The text table column stores the annotated text for the entity
• The start table column stores the start offset of the found entity. The start offset and the text is useful for locating an entity in a body of text.

In addition to these attributes, the NLP API recognizes a third type of attribute: the measurement unit. This attribute is extracted when the entity-measurement-unit feature or the measurement-relations feature is enabled during processing. Because of the one-to-many relation between measurement entities and their units, measurement unit attributes are stored in its own table: the foundentitymeasurement table. Measurement values and measurement units attributes are discussed in-depth with respect to Measurement Relations. Note, however, measurement unit attributes can also be extracted independently of measurement relations.

The following ER diagram, shows the relation between the entity, and foundentity and related tables.

Query: Count of entities found in FINDINGS

This query returns the number of entities found in the FINDINGS section that are asserted with no uncertainty using the foundentity table.

SELECT count(*) AS term_count
  FROM foundentity f
 WHERE f.section_name = 'FINDINGS'
   AND f.polarity = 'asserted'
   AND f.uncertainty = 'certain';

This query is not very useful because we only know that the term was positively asserted but we do not know what that term is. The next query shows how to extract the annotated term associated with Entity instances.

Query: Count of distinct terms

This query extracts the annotated term for an entity:

SELECT f.text AS annotated_term, count(*) AS frequency
  FROM foundentity f
 WHERE f.polarity = 'asserted'
   AND f.uncertainty = 'certain'
 GROUP BY f.text
 ORDER BY 1
 LIMIT 5;

While an improvement on the previous query, the data returned by this query is still not useful for data analysis. This is because many terms can refer to the same concept. Take, for example, diabetes which also commonly appears in medical reports as diabetes mellitus, or abbreviated as DM. It is therefore useful to map related entities to a single semantic representation within a standardized vocabulary. The NLP API supports multiple biomedical ontologies for this purpose. It maps extracted terms (or entities) to concepts identifiers and semantic types from various ontologies. Understanding the database schema relations between entities, concepts and entity types is the subject of the next section.

Concepts and Entity Type Tables

In the NLP API, recognized terms are related to concepts and semantic types in multiple ontologies. A concept corresponds to the standardized representation of a recognized term in a specific ontology. Entity types correspond to the semantic type or classification for a set of related concepts. For example, the various variant forms for diabetes, such as diabetes mellitus and DM are mapped to the concept diabetes mellitus of semantic type disorder in the SNOMED CT ontology. The ontologies that entities are mapped to are specified during data processing as the NLP API commandline parameters.

Each found entity may be associated with one or more concepts and entity types depending on the number of ontologies selected during document processing. For example, in the JSON output for the entity diabetes found in a report processed with the snomed-ontology and radlex-ontology features enabled, the diabetes entity has:

• Two concept ids, Cradlex1 and Csnomed1, in its concept_links attribute
• Multiple entity types, one for each ontology selected at processing, and if available for the ontologies selected, one for the UMLS metathesaurus in its entity_type attribute.

{
    "label": "E1",
    "spans": [
        {
            "start": 12,
            "end": 20
        }
    ],
    "section_name": "INDICATION",
    "attributes": {
        "heading_status": "outside_heading",
        "polarity": "asserted",
        "uncertainty": "certain",
        "question_status": "not_question",
        "guidance": "not_guidance",
        "known_ambiguity": "unambiguous"
    },
    "concept_links": [
        "Cradlex1",
        "Csnomed0"
    ],
    "locations": [
        "S2",
        "SEC-1"
    ],
    "entity_type": {
        "radlex": "clinical finding",
        "snomed": "disorder",
        "umls": "Disease or Syndrome"
    },
    "concept_confidences": {
        "Csnomed0": 1
    },
    "text": [
        "Diabetes"
    ]
}

In the Output Database schema, the relation between found entities, concepts and entity types, is implemented by the following tables:

• foundentityconcept,
• concept, and
• foundentitytype

The following ER diagram shows the relation between these tables.

It is important to note the following:

1. The foundentity table and the tables whose names begin with foundentity such as foundentityconcept, foundentitytype and foundentityspan, are related using the found_entity_id foreign key reference.
2. The foundentityconcept table relates the foundentity to the concept table. The relation between the foundentityconcept table and the concept table is a composite foreign key reference that is composed of the columns that store the concept ID and the ontology data in the respective tables. This is important when performing a join between these tables.
3. The foundentitytype table stores the ontology-specific semantic classification of the entity in the type_name column. For example, the SNOMED CT entity type for the term diabetes the type_name column contains the semantic type of disorder and the ontology column contains the value snomed, whereas for RadLex the type_name column contains clinical finding and the ontology column contains radlex .
4. The foundentityspan table stores the span offsets of the terms associated with a foundentity. For more information see FoundEntitySpan Table.

Query: Show concept description for entities

This query lists all entity occurrences and attributes for the SNOMED CT concept id for diabetes by joining the following tables:

• foundentity
• concept

The SNOMED CT concept id for diabetes is 73211009. You can look up SNOMED CT concept identifiers using the SNOMED CT Browser.

SELECT fe.id as foundentity_id,
      c.description AS concept_description, 
      c.ontology AS concept_ontology, 
      fe.polarity, 
      fe.uncertainty,
      fe.section_name, 
      fe.text AS annotated_text
  FROM foundentity fe
          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
             -- IMPORTANT: JOIN ON clause compares both concept id and ontology fields of the composite foreign key reference
WHERE c.concept_id = '73211009' -- SNOMED CT concept ID for `diabetes mellitus`
  AND c.ontology = 'snomed'     -- use only SNOMED CT ontology
ORDER BY 1
LIMIT 5;

Query: Find frequency of SNOMED CT concepts of type ‘disorder’

The following query uses the foundentitytype and foundentityconcept join tables to relate a concept to it’s semantic type for a given ontology. In this example, we have chosen SNOMED CT, and the semantic type of disorder.

Note, you can narrow down the search results to return only disorder concepts that appear in a specific section of a report by adding a logical condition on the section_name column of foundentitytable.

SELECT fet.type_name as semantic_type, 
        c.ontology as concept_ontology, 
        c.description as concept_description, 
        count(*) AS frequency
  FROM foundentity fe
          JOIN foundentitytype fet
          ON fe.id = fet.found_entity_id
          JOIN foundentityconcept fec
          ON fe.id = fec.found_entity_id
          JOIN concept c
          ON fec.concept_id = c.concept_id AND fet.ontology = c.ontology
WHERE fet.ontology = 'snomed'    -- filter for SNOMED CT entity types
  AND fet.type_name = 'disorder' -- filter for SNOMED CT semantic type of 'disorder'
--AND fe.section_name = 'FINDINGS'  
GROUP BY fet.type_name, c.ontology, c.description
ORDER BY 1
LIMIT 5

Query: Find section names

This query returns a list of all normalized section names in the database.

SELECT DISTINCT section_name FROM foundentity

Note that NLP API recognizes many more sections than shown above. It also extracts section names for sections and subsections of medical reports. The level of the section can be retrieved from the sectionlocation table. More information is about this is found in the Document-level Tables page.

Additional Resources

Ontology References

Here are some useful links for looking up ontology-specific concept identifiers:

• To look up or browse SNOMED CT concepts: https://browser.ihtsdotools.org
• To browse RadLex concepts: http://www.radlex.org
• To browse some of the UMLS ontologies: https://uts.nlm.nih.gov/metathesaurus.html

Utility SQL Queries

While planning a query, you will often find that you need to understand how annotated terms are mapped to concepts and entity types before selecting a subset to analyze. Below are some data exploration queries that may be helpful.

/**
  Useful exploratory queries for available concepts and types
*/
-- List of available ontologies for semantic types
SELECT DISTINCT fet.ontology
  FROM foundentitytype fet;
-- List of available ontologies for concepts
SELECT DISTINCT c.ontology
  FROM concept c;
-- List frequency of annotated terms
SELECT text, count(*) AS frequency
  FROM foundentity
 GROUP BY text
 ORDER BY frequency DESC;
-- List of available semantic types for ontology
SELECT DISTINCT fet.type_name
  FROM foundentitytype fet
 WHERE fet.ontology = 'snomed';
-- List concept descriptions for a specific ontology
SELECT c.ontology, c.description
  FROM concept c
 WHERE c.ontology = 'snomed'               -- change to ontology of interest
 GROUP BY c.ontology, c.description;
-- List frequency of entity types for a specific ontology
SELECT fet.ontology, fet.type_name, count(*) AS total
  FROM foundentitytype fet
 WHERE fet.ontology = 'radlex'             -- change to ontology of interest
 GROUP BY fet.ontology, fet.type_name
 ORDER BY total DESC;
-- Browse annotated terms of snomed type: procedure, disorder or finding
SELECT fet.type_name, fe.text as annotated_text, count(*) AS frequency
  FROM foundentity fe
          JOIN foundentitytype fet
          ON fe.id = fet.found_entity_id
 WHERE fet.ontology = 'snomed' 
   AND fet.type_name IN ('disorder', 'finding', 'procedure')
 GROUP BY fet.type_name, fes.text
 ORDER BY type_name DESC;
-- Browse concepts by semantic type
SELECT fet.ontology AS type_ontology, fet.type_name, c.ontology AS concept_ontology, c.description, count(*) AS total
  FROM concept c
          JOIN foundentityconcept fec
          ON c.concept_id = fec.concept_id AND c.ontology = fec.concept_ontology
          JOIN foundentitytype fet
          ON fet.found_entity_id = fec.found_entity_id
WHERE c.ontology = 'snomed'             -- any source ontology for Concepts interest
  AND fet.ontology = 'umls'             -- any ontology for the semantic type categorization of interest
GROUP BY fet.ontology, fet.type_name, c.ontology, c.description
ORDER BY total DESC;