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.

In the location model, there are several indicators that can be used to distinguish the current operational status of a location:

• is_deleted - The most direct indicator, showing whether a location record has been marked as deleted in the source system.

• open_date and close_date- These datetime fields indicate when the location became operational and when it ceased operations. A location without a close_date (NULL) or with a close_date in the future would typically be considered currently operational.

• transform_datetime - While not directly indicating operational status, this shows when the record was last updated, which can be useful for determining the currency of the status information.

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

Model Changes in iPCV v2

Standardised and enhanced postcode formatting

We have improved how postcodes are processed i.e. we now take the original ‘postcode’ field (instead of limited ‘postcodenospace’ column) and apply consistent formatting by:

• Stripping all spaces

• Removing dashes and other invalid characters

• Converting all values to uppercase

Why?

iPCV v1 used a calculated field ‘postcodenospace’ in EMIS Web which removed spaces but was limited to 7 characters and did not fully account for formatting inconsistencies like dashes or lowercase characters. This led to postcode formatting issues in some outputs.

Customer benefit

This change provides the following benefits:

• Cleaner more reliable postcode data for downstream analysis

• Improved consistency across all views using post code data

• Reduced need for customer side cleaning or handling of post code values

Customer action

No customer action is required unless your logic depends on the previous postcode format. Customers may wish to validate that any postcode matching or filtering logic continues to function as expected with the new standardised format

Added alternate columns for deprecated columns to use for lookup

We have added relevant parent_location_id column as an alternate for the deprecated emis_parent_location_guid column, which can be used to lookup for the parent details when needed

Customer benefit

Improves performance as we avoided complexity in the query

Customer action

• Update your queries to use parent_location_id column where emis_parent_location_guid is referenced

Schema updates for data maintenance

• The processing_id columns is removed as it is no longer required

Benefit

• The data model is streamlined by removing deprecated columns from iPCV v1 and v2, resulting in a cleaner and more efficient structure

Customer Action

• Review your queries and remove any references to the deprecated processing_id column to align with the updated schema and avoid confusion

Examples

Currently active locations

SELECT
  location_id,
  organisation,
  location_name,
  location_type_description,
  house_name_flat_number,
  number_and_street,
  town,
  postcode,
  phone_number
FROM hive.explorer_ipcv.srv_location
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
  location_id,
  organisation,
  location_name,
  location_type_description,
  open_date,
  close_date,
  town,
  postcode
FROM hive.explorer_ipcv.srv_location
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.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.srv_location l
LEFT JOIN hive.explorer_ipcv.srv_location p
  ON l.parent_location_id = p.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