Definition

The Observation data model captures coded clinical observations recorded in EMIS Web. It includes allergies, immunisations, measurements, problem-linked observations, and referral markers.

Constraints and Notes

• Soft deletion : When is_deleted is true, data fields will be nullified in upstream transformations and should be excluded from most analytical queries. Primary keys and foreign keys to patient data are preserved so deleted records and their relationships can still be identified. Foreign keys to coding tables are nullified as the coded information is no longer valid.
• snomed_concept_id is resolved from code_id and may be NULL if no matching concept exists.
• Observations can be organised hierarchically through the parent_observation_id field, allowing related conditions to be grouped together, e.g. diastolic and systolic values of a blood pressure measurement.

Examples

Recent numeric observations for a specific patient

SELECT
  observation_id,
  patient_id,
  effective_datetime,
  snomed_concept_id,
  numeric_operator,
  numeric_value,
  numeric_unit,
  consultation_id,
  organisation
FROM explorer_open_safely.observation
WHERE
  patient_id = <patient_id>
  AND organisation = <organisation>
  AND numeric_value IS NOT NULL
  AND NOT is_deleted
ORDER BY effective_datetime DESC

Active problem-linked observations

SELECT
  observation_id,
  patient_id,
  effective_datetime,
  observation_type_description,
  problem_observation_id,
  problem_end_datetime,
  organisation
FROM explorer_open_safely.observation
WHERE
  is_problem
  AND problem_end_datetime IS NULL
  AND NOT is_deleted