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

Definitions / outage_cluster

Outage cluster

detector, runs realtime

Customers off supply that the control room does not know about yet: meters that sent a last gasp and no restore, grouped by transformer (or by feeder when three or more transformers on it are dark), on equipment no open ticket and no planned work covers.

control · Head of Network Operations · version 3

A meter is dark when its latest last gasp in the last lookback_minutes is at least sustained_minutes old and no power restore followed it. A transformer is reported when at least min_gasps of its meters are dark, they are at least min_share of its meters, and fewer of the meters that went dark with them have come back than are still dark; a transformer whose every meter is dark is reported after lone_minutes even with a single meter (a farm or a pumping station on its own transformer). Transformers inside an open OMS ticket (the agent's own tickets count for agent_ticket_hold_hours) or a planned outage are left out. Three or more dark transformers on one feeder are one feeder-level row.

Latest change: A transformer with every meter dark for lone_minutes is reported even when it has one meter; small rural transformers were never reported.

SQL
WITH gasps AS (
  SELECT e.meter_id, max(e."at") AS gasp_at
  FROM meter_events e
  WHERE e.type = 'last_gasp' AND e."at" > {now} - INTERVAL ({lookback_minutes}) MINUTE AND e."at" <= {now}
  GROUP BY e.meter_id
),
backs AS (
  SELECT e.meter_id, max(e."at") AS back_at
  FROM meter_events e
  WHERE e.type = 'power_restore' AND e."at" > {now} - INTERVAL ({lookback_minutes}) MINUTE AND e."at" <= {now}
  GROUP BY e.meter_id
),
state AS (
  SELECT g.meter_id, g.gasp_at, m.feeder_id, m.transformer_id, t.position,
         (b.back_at IS NULL OR b.back_at < g.gasp_at) AS dark
  FROM gasps g
  JOIN meters m ON m.id = g.meter_id
  JOIN transformers t ON t.id = m.transformer_id
  LEFT JOIN backs b ON b.meter_id = g.meter_id
),
covered AS (
  SELECT DISTINCT t.id AS transformer_id
  FROM outages o
  JOIN transformers t ON t.feeder_id = o.feeder_id
   AND (o.transformer_id = t.id OR (o.transformer_id IS NULL AND t.position >= o.from_position))
  WHERE o.opened_at <= {now} AND coalesce(o.status, '') <> 'withdrawn'
    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)
  UNION
  SELECT t.id
  FROM planned_outages p
  JOIN transformers t ON t.feeder_id = p.feeder_id
   AND (p.transformer_id = t.id OR (p.transformer_id IS NULL AND t.position >= p.from_position))
  WHERE p.cancelled_at IS NULL AND p.start_at - INTERVAL 30 MINUTE <= {now} AND p.end_at + INTERVAL 60 MINUTE >= {now}
),
size AS (SELECT transformer_id, count(*) AS meters FROM meters GROUP BY transformer_id),
per_tx AS (
  SELECT s.feeder_id, s.transformer_id, min(s.position) AS position,
         count(*) FILTER (WHERE s.dark AND s.gasp_at <= {now} - INTERVAL ({sustained_minutes}) MINUTE) AS dark,
         count(*) FILTER (WHERE s.dark AND s.gasp_at <= {now} - INTERVAL ({lone_minutes}) MINUTE) AS dark_long,
         count(*) FILTER (WHERE NOT s.dark) AS back,
         min(s.gasp_at) FILTER (WHERE s.dark) AS first_gasp,
         list(s.meter_id ORDER BY s.meter_id) FILTER (WHERE s.dark) AS meter_ids,
         any_value(z.meters) AS meters
  FROM state s
  JOIN size z ON z.transformer_id = s.transformer_id
  WHERE s.transformer_id NOT IN (SELECT transformer_id FROM covered)
  GROUP BY s.feeder_id, s.transformer_id
),
hit AS (
  SELECT * FROM per_tx
  WHERE back < dark AND ((dark >= {min_gasps} AND dark >= {min_share} * meters) OR dark_long >= meters)
),
per_feeder AS (
  SELECT feeder_id, count(*) AS transformers FROM hit GROUP BY feeder_id
),
rows AS (
  SELECT 'feeder' AS subject_type, CAST(h.feeder_id AS TEXT) AS subject_id, h.feeder_id, NULL::INTEGER AS transformer_id,
         min(h.first_gasp) AS started_at, sum(h.dark) AS dark_meters, sum(h.meters) AS customers_on_equipment,
         min(h.position) AS from_position, list(h.transformer_id ORDER BY h.transformer_id) AS transformer_ids,
         flatten(list(h.meter_ids)) AS meter_ids
  FROM hit h JOIN per_feeder p ON p.feeder_id = h.feeder_id
  WHERE p.transformers >= {section_min_transformers}
  GROUP BY h.feeder_id
  UNION ALL
  SELECT 'transformer', CAST(h.transformer_id AS TEXT), h.feeder_id, h.transformer_id, h.first_gasp, h.dark, h.meters,
         h.position, [h.transformer_id], h.meter_ids
  FROM hit h JOIN per_feeder p ON p.feeder_id = h.feeder_id
  WHERE p.transformers < {section_min_transformers}
),
calls AS (
  SELECT r.subject_id, count(c.id) AS calls
  FROM rows r, unnest(r.meter_ids) AS u(meter_id)
  JOIN meters m ON m.id = u.meter_id
  JOIN customer_calls c ON c.premise_id = m.premise_id AND c."at" >= r.started_at AND c."at" <= {now}
  GROUP BY r.subject_id
),
priority AS (
  SELECT r.subject_id, count(*) AS priority
  FROM rows r, unnest(r.meter_ids) AS u(meter_id)
  JOIN meters m ON m.id = u.meter_id
  JOIN priority_register p ON p.premise_id = m.premise_id
  GROUP BY r.subject_id
)
SELECT r.subject_type, r.subject_id, r.feeder_id, r.transformer_id, r.started_at,
       CASE WHEN r.dark_meters >= 100 OR coalesce(p.priority, 0) > 0 THEN 'high' ELSE 'medium' END AS severity,
       json_object(
         'feeder_id', r.feeder_id,
         'feeder', f.code,
         'transformer_id', r.transformer_id,
         'affected_transformer_ids', r.transformer_ids,
         'from_position', r.from_position,
         'dark_meters', r.dark_meters,
         'customers_on_equipment', r.customers_on_equipment,
         'meter_ids', r.meter_ids[1:{max_listed_meters}],
         'first_gasp', r.started_at,
         'minutes_dark', round(date_diff('second', r.started_at, {now}) / 60.0, 1),
         'customer_calls', coalesce(c.calls, 0),
         'priority_customers', coalesce(p.priority, 0),
         'open_tickets_network', (SELECT count(*) FROM outages o WHERE o.opened_at <= {now} AND o.source <> 'planned'
                                  AND coalesce(o.status, '') <> 'withdrawn'
                                  AND (o.restored_at IS NULL OR o.restored_at > {now}))
       ) AS evidence
FROM rows r
JOIN feeders f ON f.id = r.feeder_id
LEFT JOIN calls c ON c.subject_id = r.subject_id
LEFT JOIN priority p ON p.subject_id = r.subject_id
ORDER BY r.started_at, r.subject_id

Tests

meaningtestresultobservedexpected
controlcount_betweenpass0between 0.0 and 40.0
controlstorm_eveningFAIL0between 3.0 and 60.0
controluniquepass{'duplicates': 0}subject_id unique
controldetector_shapepass{'missing_columns': [], 'bad_rows': 0, 'rows': 0}columns ['subject_type', 'subject_id', 'started_at', 'evidence'], evidence is an object