Integration of Department Metadata into EMAP for Efficient Location Filtering

[zmek]

Integration of Department Metadata into EMAP for Efficient Inpatient Location Filtering

Background

We need department level data in EMAP. This is necessary so that we can efficiently identify patients who are occupying inpatient beds at a point in time, or patients in the ED at a point in time.

Useful metadata includes:

• which site a department is on (Grafton Way, Queen's Square etc)
• whether a department is a ward or flow area (ie relevant to those managing patient flow)
• whether a department that is a ward or flow area is classified as an inpatient overnight location (from RPT_GRP_NINE field in Clarity's CLARITY_DEP table)

Current State

• EMAP stores only location strings without department metadata
• Current location filtering uses hardcoded list of ~500 location strings converted to location IDs
• Performance bottleneck: large array filtering in SQL queries
• Missing ~3,500 additional inpatient ward locations identified in Clarity analysis

Problem Statement

Need efficient method to filter EMAP data by ward/flow area classification for:

1. Large-scale model training data extraction
2. Real-time inpatient prediction applications

Current query pattern:

-- Slow: filtering by large array of location IDs
SELECT * FROM [Join of hospital_visit and location_visit] 
WHERE location_id = ANY(ARRAY[123, 456, 789, ...]) -- 500+ IDs

Desired query pattern:

-- Fast: filtering by metadata flags
SELECT * FROM emap_table e
WHERE e.is_ward_or_flow_area = true
  AND e.site_location_id IN ('10201', '10218')
  AND e.classification in ('2', '20') -- Inpatient - Overnight Ward or ICU location

Proposed Solutions

Option 1: Extend EMAP Hoover

Description: Modify the EMAP data ingestion pipeline to populate department metadata from Clarity during hoover process.

Implementation:

• Add columns to EMAP location/department tables: rpt_grp_nine, is_ward_or_flow_area, site_location_id, etc.
• Modify hoover to lookup metadata from Clarity during ingestion
• Create indexes on metadata columns

Pros:

• Best query performance (single table, no joins)
• Self-contained EMAP database
• Scales efficiently with location growth
• Optimal for real-time applications

Cons:

• Requires hoover development resources
• Testing overhead for data pipeline changes
• One-time implementation effort

---

Option 2: Cross-Database Views/Federated Queries

Description: Create views in EMAP that join to Clarity metadata tables using foreign data wrappers.

Implementation:

CREATE FOREIGN TABLE clarity_dept_metadata (...) 
SERVER clarity_server OPTIONS (...);

CREATE VIEW emap_with_metadata AS
SELECT e.*, cmd.is_ward_or_flow_area, cmd.site_location_id
FROM emap_locations e
JOIN clarity_dept_metadata cmd ON e.location_string = cmd.location_string;

Pros:

• No changes to core EMAP pipeline
• Always current metadata
• Quick to implement

Cons:

• Cross-database query performance issues
• Network latency impact
• System dependencies
• Poor for real-time applications

---

Option 3: Periodic Metadata Sync Table

Description: Create lookup table in EMAP refreshed periodically from Clarity.

Implementation:

CREATE TABLE location_metadata (
    location_string VARCHAR(255) PRIMARY KEY,
    department_id VARCHAR(50),
    department_name VARCHAR(255),
    rpt_grp_nine VARCHAR(10),
    is_ward_or_flow_area BOOLEAN,
    site_location_id VARCHAR(20),
    last_updated TIMESTAMP
);

Pros:

• Good query performance (local joins)
• No hoover changes required
• Quick implementation
• Immediate performance gains

Cons:

• Metadata staleness between syncs
• Additional sync process maintenance
• Scheduling/monitoring overhead

[zmek]

@stefpiatek suggests that the existing hoover can be modified for this. A query like this would get the required metadata:

SELECT 
    dep.DEPARTMENT_ID AS departmentId,
    dep.DEPARTMENT_NAME AS departmentName,
    dep.RPT_GRP_NINE AS rptGrpNine,
    dep.REV_LOC_ID AS siteLocationId,
    CASE 
        WHEN dep.RPT_GRP_NINE IN ('2', '9', '11', '12', '16', '19', '20', '25') 
        THEN 1 
        ELSE 0 
    END AS isWardOrFlowArea,
    CASE 
        WHEN dep.RPT_GRP_NINE IN ('2', '20') 
        THEN 1 
        ELSE 0 
    END AS isCoreInpatientArea,
    dept_class.NAME AS departmentClassification,
    site_loc.LOC_NAME AS siteName,
    room.ROOM_CSN_ID AS roomCsn,
    room.ROOM_READY_YN AS isRoomReady,
    room.ROOM_NUMBER AS roomHl7,
    bed.BED_CSN_ID AS bedCsn,
    bed.BED_LABEL AS bedHl7,
    facility_type.NAME AS bedFacility,
    (COALESCE(mpi.MPI_ID, 'null') + '^' + COALESCE(room.ROOM_NUMBER, 'null') + '^' + COALESCE(bed.BED_LABEL, 'null')) AS hl7String
FROM CLARITY_REPORT.dbo.CLARITY_DEP AS dep
LEFT JOIN CLARITY_REPORT.dbo.ZC_DEP_RPT_GRP_9 AS dept_class
    ON dep.RPT_GRP_NINE = dept_class.RPT_GRP_NINE
LEFT JOIN CLARITY_REPORT.dbo.CLARITY_LOC AS site_loc
    ON dep.REV_LOC_ID = site_loc.LOC_ID
LEFT JOIN CLARITY_REPORT.dbo.CL_DEP_ID AS mpi
    ON dep.DEPARTMENT_ID = mpi.DEPARTMENT_ID
    AND mpi.MPI_ID_TYPE_ID = (
        SELECT ID_TYPE
        FROM CLARITY_REPORT.dbo.IDENTITY_ID_TYPE
        WHERE ID_TYPE_NAME = 'DEPARTMENT ID TYPE'
    )
LEFT JOIN CLARITY_REPORT.dbo.CLARITY_ROM AS room
    ON dep.DEPARTMENT_ID = room.DEPARTMENT_ID
LEFT JOIN CLARITY_REPORT.dbo.CLARITY_BED AS bed
    ON room.ROOM_ID = bed.ROOM_ID
LEFT JOIN CLARITY_REPORT.dbo.CL_BED_SPEC_FACIL AS bed_facility
    ON bed.BED_CSN_ID = bed_facility.BED_CSN_ID
LEFT JOIN CLARITY_REPORT.dbo.ZC_BED_SPEC_FACIL AS facility_type
    ON bed_facility.BED_SPEC_FACIL_C = facility_type.BED_SPEC_FACIL_C
WHERE 
    dep.REV_LOC_ID IN ('10201', '10218')  -- Limit to UCH + Grafton Way
    AND (COALESCE(mpi.MPI_ID, 'null') + '^' + COALESCE(room.ROOM_NUMBER, 'null') + '^' + COALESCE(bed.BED_LABEL, 'null')) != 'null^null^null'
    AND (dep.DEPARTMENT_NAME IS NULL OR dep.DEPARTMENT_NAME NOT LIKE 'ZZZ%')
ORDER BY dep.DEPARTMENT_ID, room.ROOM_NUMBER, bed.BED_LABEL;

[zmek]

This would also require 3 new columns on star.department

• rptGrpNine (I am using this name because it is closest to how Clarity refers to department attributes, but it does not have to be this; let's discuss)
• isWardOrFlowArea (I'm using this name because it is used in a BI dashboard data extraction developed by Dominic Thurgood for the bed manager team)
• isCoreInpatientArea - ditto

[thompson318]

EMAP code written in #121.
Hoover ready for review but pending update to EMAP to add columns.
@zmek to review column names.

[zmek]

@skeating

I suggest column name DepartmentType or department_type (following convention) for what is currently referred to as rptGrpNine

Also (and sorry for late notice) I realise that I've put a hard-coded clause in the SQL.
dep.REV_LOC_ID IN ('10201', '10218') -- Limit to UCH + Grafton Way

This does not seem elegant or complete, and will lead to rows on the star.department table (eg QS or WMS wards) having null values for the new columns.

Instead can we add the site location information to star.department as well and remove this hard-coding? I realise that we have no lookup for what these values refer to but I think that is liveable with for now.

[skeating]

@zmek

so you are suggesting another column site_location in the department table
and then taking out that clause in the sequel

[zmek]

Yes that's right

[zmek]

@skeating we can't use HL7 messages to populate site_location. See the email I sent yesterday

[MonikaSvata]

This can't be validate in the real run. Picking up data from Clarity so this isn't an issue.

@skeating and @stefpiatek review before the 2 queries are merged.