Feeder Vellmoor Distribution · control room Synthetic data Sun 02:00

Definitions / planned_work_collision

Planned work colliding with a fault

detector, runs realtime

Booked planned work starting soon on a feeder that has an open fault, or whose tie feeder (its backup supply) has one, or that is itself the backup for a faulted feeder. Taking it out would leave customers with no way back.

planning · Head of Network Planning · version 1

Planned outages not cancelled, starting after now and within lookahead_hours, where an open unplanned ticket covering a whole feeder or a section of one sits on the work's feeder, on its tie feeder, or on a feeder whose tie is the work's feeder. The agent's own tickets count for agent_ticket_hold_hours.

Latest change: First version.

SQL
WITH faults AS (
  SELECT o.id AS outage_id, o.feeder_id, o.opened_at, f.tie_feeder_id
  FROM outages o JOIN feeders f ON f.id = o.feeder_id
  WHERE o.transformer_id IS NULL AND o.source <> 'planned' AND coalesce(o.status, '') <> 'withdrawn'
    AND o.opened_at <= {now} AND (o.restored_at IS NULL OR o.restored_at > {now})
    AND (o.source <> 'agent' OR o.opened_at > {now} - INTERVAL ({agent_ticket_hold_hours}) HOUR)
),
works AS (
  SELECT p.*, f.tie_feeder_id AS work_tie
  FROM planned_outages p JOIN feeders f ON f.id = p.feeder_id
  WHERE p.cancelled_at IS NULL AND p.start_at > {now} AND p.start_at <= {now} + INTERVAL ({lookahead_hours}) HOUR
    AND p.notified_at <= {now}
),
clash AS (
  SELECT w.id AS planned_outage_id, w.feeder_id, w.start_at, w.end_at, w.reason,
         min(fa.opened_at) AS fault_opened_at, list(DISTINCT fa.outage_id) AS outage_ids,
         list(DISTINCT fa.feeder_id) AS fault_feeder_ids,
         bool_or(fa.feeder_id = w.feeder_id) AS same_feeder
  FROM works w JOIN faults fa
    ON fa.feeder_id = w.feeder_id OR fa.feeder_id = w.work_tie OR fa.tie_feeder_id = w.feeder_id
  GROUP BY w.id, w.feeder_id, w.start_at, w.end_at, w.reason
)
SELECT 'planned_work' AS subject_type, CAST(c.planned_outage_id AS TEXT) AS subject_id, c.planned_outage_id,
       c.feeder_id, greatest(c.fault_opened_at, c.start_at - INTERVAL ({lookahead_hours}) HOUR) AS started_at,
       'high' AS severity,
       json_object(
         'planned_outage_id', c.planned_outage_id,
         'feeder_id', c.feeder_id,
         'work_starts_at', c.start_at,
         'work_ends_at', c.end_at,
         'reason', c.reason,
         'fault_outage_ids', c.outage_ids,
         'fault_feeder_ids', c.fault_feeder_ids,
         'same_feeder', c.same_feeder,
         'hours_to_start', round(date_diff('minute', {now}, c.start_at) / 60.0, 1)
       ) AS evidence
FROM clash c
ORDER BY started_at, c.planned_outage_id

Tests

meaningtestresultobservedexpected
planningcount_betweenpass0between 0.0 and 10.0
planninguniquepass{'duplicates': 0}planned_outage_id unique
planningdetector_shapepass{'missing_columns': [], 'bad_rows': 0, 'rows': 0}columns ['subject_type', 'subject_id', 'started_at', 'evidence'], evidence is an object