Definition

The location model provides consolidated information about healthcare facilities, including identifying details, contact information, physical addresses, operational dates, and hierarchical relationships.

Information

The location model provides a comprehensive view of all locations associated with healthcare organizations in the system. It captures physical premises where healthcare services are delivered, including GP practice sites, clinics, and other care delivery locations.

The data model consolidates location details including contact information, address data, operational dates, and hierarchical relationships between locations. Each location record is uniquely identified by the combination of location_id and organization, allowing for consistent tracking of locations across different healthcare providers. The model includes both active and historical locations, with appropriate indicators to distinguish current operational status.

Grain and Scope

Each record is uniquely identified by either combining location_id and organisation or the location_uuid column.

This model includes the following key identifiers:

• location_id: The unique internal identifier for the location record within an organisation.
• location_guid: The GUID for the location within an organisation.
• location_uuid: The UUID derived from location_id and organisation, providing a stable unique identifier.

The following fields are important for tracking data lineage and freshness:

• is_deleted: Indicates whether location record has been deleted at source.
• open_date: The date indicating when the location was opened.
• close_date: The date indicating when the location was closed.
• transform_datetime: The timestamp indicating when the record was last processed and updated in the data model. This field is crucial for understanding the current state of the data.

Things to be aware of

• Postcodes have no spaces.

• Closed customer organisations are absent. Their locations are removed from this model, so a location referenced by an older record may not resolve.

• location_name is not unique and can change. Join on location_id + organisation, or on location_uuid.

• Contact details are unvalidated free text.

Overview

flowchart TB
    subgraph container["Data Collection"]
     direction TB
        n10["Location Identifier"]
        n11["Location Type"]
        n12["Address"]
    end

    n17[Organisation 1] --> n7
    n17[Organisation 1] --> n5
    n18[Organisation 2] --> n6
    n18[Organisation 2] --> n8
    n18[Organisation 2] --> n4

    n7["Location 123"] --> container
    n5["Location 98"] --> container
    n6["Location 123"] --> container
    n4["Location 20"] --> container
    n8["Location 47"] --> container

    n10 --> n13["Gather Locations"]
    n11 --> n13
    n12 --> n13
    n13 --> |"Unique Location IDs"|n16["123, 98,20,47"]
    n14["ETL"] --> n15["Location Model"]
    n16 --> n14

    n7@{ shape: rect}
    n10@{ shape: rect}
    n5@{ shape: rect}
    n6@{ shape: rect}
    n11@{ shape: rect}
    n4@{ shape: rect}
    n8@{ shape: rect}
    n12@{ shape: rect}
    n13@{ shape: extract}
    n16@{ shape: rect}
    n14@{ shape: event}
    n15@{ shape: internal-storage}
    classDef green fill:#B2DFDB,stroke:#00897B,stroke-width:2px
    classDef orange fill:#FFE0B2,stroke:#FB8C00,stroke-width:2px
    classDef blue fill:#BBDEFB,stroke:#1976D2,stroke-width:2px
    classDef yellow fill:#FFF9C4,stroke:#FBC02D,stroke-width:2px
    classDef pink fill:#F8BBD0,stroke:#C2185B,stroke-width:2px
    classDef purple fill:#E1BEE7,stroke:#8E24AA,stroke-width:2px

    style n7 stroke:#9961a4
    style n10 stroke:#9961a4

    style n5 stroke:#9961a4
    style n6 stroke:#9961a4
    style n4 stroke:#9961a4
    style n8 stroke:#9961a4
    style n10 stroke:#9961a4
    style n11 stroke:#9961a4
    style n12 stroke:#9961a4
    style n13 stroke:#9961a4
    style n14 stroke:#9961a4
    style n15 stroke:#9961a4
    style n16  stroke:#9961a4
    style container fill:#f9f9f9,stroke:#9961a4


    linkStyle 0 stroke:#117abf,fill:none
    linkStyle 1 stroke:#117abf,fill:none
    linkStyle 2 stroke:#117abf,fill:none
    linkStyle 3 stroke:#117abf,fill:none
    linkStyle 4 stroke:#117abf,fill:none
    linkStyle 5 stroke:#117abf,fill:none
    linkStyle 6 stroke:#117abf,fill:none
    linkStyle 7 stroke:#117abf,fill:none
    linkStyle 8 stroke:#117abf,fill:none
    linkStyle 9 stroke:#117abf,fill:none
    linkStyle 10 stroke:#117abf,fill:none

Examples

Currently active locations

SELECT
  emis_location_id,
  organisation,
  location_name,
  location_type_description,
  house_name_flat_number,
  number_and_street,
  town,
  postcode,
  phone_number
FROM
  hive.explorer_ipcv_vanilla.location_v2
WHERE
  is_deleted = FALSE
  AND (
    close_date IS NULL
    OR close_date > CURRENT_DATE
  )
ORDER BY
  organisation,
  location_name;

Locations closed within the last year

SELECT
  emis_location_id,
  organisation,
  location_name,
  location_type_description,
  open_date,
  close_date,
  town,
  postcode
FROM
  hive.explorer_ipcv_vanilla.location_v2
WHERE
  close_date IS NOT NULL
  AND close_date > DATE_ADD('year', -1, CURRENT_DATE)
  AND close_date <= CURRENT_DATE
ORDER BY
  close_date DESC;

Location hierarchy (parent-child relationships)

SELECT
  l.emis_location_id,
  l.organisation,
  l.location_name AS child_location,
  l.location_type_description AS child_type,
  p.location_name AS parent_location,
  p.location_type_description AS parent_type,
  l.postcode,
  l.phone_number
FROM
  hive.explorer_ipcv_vanilla.location_v2 l
  LEFT JOIN hive.explorer_ipcv_vanilla.location_v2 p ON l.parent_location_id = p.emis_location_id
  AND l.organisation = p.organisation
WHERE
  l.is_deleted = FALSE
  AND (
    l.close_date IS NULL
    OR l.close_date > CURRENT_DATE
  )
  AND l.parent_location_id IS NOT NULL
ORDER BY
  l.organisation,
  p.location_name,
  l.location_name;