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

Definitions / wrong_feeder_meter

Meter on the wrong feeder

detector, runs hourly

A meter whose supply behaves like another feeder's: it lost and regained power together with a cluster of meters on a different feeder, while its recorded feeder stayed on. The GIS connection is probably wrong, so outage tickets and switching plans miss it.

assets · Head of Asset Management · version 2

A cluster is at least cluster_min_meters last gasps from one recorded feeder within the same minute. A meter matches a cluster on another feeder when its own last gasp is within gasp_window_seconds of the cluster's first gasp and its next restore is within restore_window_seconds of a restore from that cluster's feeder, while its own recorded feeder had no cluster in that minute and no planned work starting on it. Reported with one match when that interruption lasted sustained_minutes or more (a meter's own reboot almost never does), otherwise with min_matches; the feeder it matches most often is named.

Latest change: One match is enough only when the interruption was sustained; a meter rebooting in the same minute as another feeder's auto-reclose was being reported.

SQL
WITH ev AS (
  SELECT e.meter_id, e."at", e.type, m.feeder_id
  FROM meter_events e JOIN meters m ON m.id = e.meter_id
  WHERE e."at" > {now} - INTERVAL ({look_back_days}) DAY AND e."at" <= {now}
),
clusters AS (
  SELECT feeder_id, date_trunc('minute', "at") AS minute, min("at") AS first_gasp, count(*) AS n
  FROM ev WHERE type = 'last_gasp'
  GROUP BY feeder_id, date_trunc('minute', "at")
  HAVING count(*) >= {cluster_min_meters}
),
lone AS (
  SELECT g.meter_id, g.feeder_id AS own_feeder, g."at" AS gasp_at, c.feeder_id AS other_feeder, c.minute
  FROM ev g
  JOIN clusters c ON c.feeder_id <> g.feeder_id
   AND abs(date_diff('second', c.first_gasp, g."at")) <= {gasp_window_seconds}
  WHERE g.type = 'last_gasp'
    AND NOT EXISTS (SELECT 1 FROM clusters o WHERE o.feeder_id = g.feeder_id
                    AND abs(date_diff('second', o.first_gasp, g."at")) <= {gasp_window_seconds})
    AND NOT EXISTS (SELECT 1 FROM planned_outages p WHERE p.feeder_id = g.feeder_id
                    AND abs(date_diff('second', p.start_at, g."at")) <= 300)
),
matched AS (
  SELECT l.meter_id, l.own_feeder, l.other_feeder, l.gasp_at,
         (SELECT min(b."at") FROM ev b WHERE b.meter_id = l.meter_id AND b.type = 'power_restore' AND b."at" > l.gasp_at) AS back_at
  FROM lone l
),
confirmed AS (
  SELECT m.*
  FROM matched m
  WHERE m.back_at IS NOT NULL AND EXISTS (
    SELECT 1 FROM ev b WHERE b.feeder_id = m.other_feeder AND b.type = 'power_restore' AND b.meter_id <> m.meter_id
      AND abs(date_diff('second', b."at", m.back_at)) <= {restore_window_seconds})
),
per_meter AS (
  SELECT meter_id, own_feeder, other_feeder, count(DISTINCT gasp_at) AS matches, min(gasp_at) AS first_match,
         max(gasp_at) AS last_match,
         count(DISTINCT gasp_at) FILTER (WHERE back_at >= gasp_at + INTERVAL ({sustained_minutes}) MINUTE) AS sustained
  FROM confirmed GROUP BY meter_id, own_feeder, other_feeder
  HAVING count(DISTINCT gasp_at) >= {min_matches}
      OR count(DISTINCT gasp_at) FILTER (WHERE back_at >= gasp_at + INTERVAL ({sustained_minutes}) MINUTE) >= 1
  QUALIFY row_number() OVER (PARTITION BY meter_id ORDER BY count(DISTINCT gasp_at) DESC, other_feeder) = 1
)
SELECT 'meter' AS subject_type, CAST(p.meter_id AS TEXT) AS subject_id, p.meter_id, p.own_feeder AS feeder_id,
       p.first_match AS started_at, 'low' AS severity,
       json_object(
         'meter_id', p.meter_id,
         'serial', m.serial,
         'recorded_feeder_id', p.own_feeder,
         'recorded_transformer_id', m.transformer_id,
         'behaves_like_feeder_id', p.other_feeder,
         'matches', p.matches,
         'sustained_matches', p.sustained,
         'first_match', p.first_match,
         'last_match', p.last_match
       ) AS evidence
FROM per_meter p JOIN meters m ON m.id = p.meter_id
ORDER BY started_at, p.meter_id

Tests

meaningtestresultobservedexpected
assetscount_betweenpass39between 5.0 and 80.0
assetsuniquepass{'duplicates': 0}meter_id unique
assetsdetector_shapepass{'missing_columns': [], 'bad_rows': 0, 'rows': 39}columns ['subject_type', 'subject_id', 'started_at', 'evidence'], evidence is an object