179 lines
6.8 KiB
SQL
179 lines
6.8 KiB
SQL
-- ============================================================
|
||
-- ADS-B Track Quality Metrics
|
||
-- Замени: 56.121, 37.216 — координаты антенны
|
||
-- Замени: 150 — радиус ЗУП в км (начни с 150)
|
||
-- ============================================================
|
||
|
||
|
||
-- ============================================================
|
||
-- 0. Вспомогательная функция: расстояние от точки до антенны (км)
|
||
-- Используется PostGIS ST_Distance с geography
|
||
-- ============================================================
|
||
|
||
|
||
-- ============================================================
|
||
-- 1. ГИСТОГРАММА РАССТОЯНИЙ — для определения ЗУП
|
||
-- Показывает плотность точек по кольцам от антенны
|
||
-- ============================================================
|
||
WITH antenna AS (
|
||
SELECT ST_SetSRID(ST_MakePoint(37.216, 56.121), 4326)::geography AS pt
|
||
),
|
||
point_distances AS (
|
||
SELECT
|
||
tp.track_id,
|
||
ST_Distance(tp.geom::geography, a.pt) / 1000 AS dist_km
|
||
FROM fr24.track_points tp, antenna a
|
||
WHERE tp.observed_at >= NOW() - INTERVAL '7 days'
|
||
)
|
||
SELECT
|
||
(dist_km / 10)::int * 10 AS dist_bucket_km, -- кольца по 10 км
|
||
COUNT(*) AS point_count,
|
||
COUNT(DISTINCT track_id) AS track_count
|
||
FROM point_distances
|
||
GROUP BY dist_bucket_km
|
||
ORDER BY dist_bucket_km;
|
||
|
||
|
||
-- ============================================================
|
||
-- 2. МЕТРИКИ ПО ТРЕКАМ (M1–M4) за последние 7 дней
|
||
-- Только треки с точками в ЗУП
|
||
-- ============================================================
|
||
WITH antenna AS (
|
||
SELECT ST_SetSRID(ST_MakePoint(37.216, 56.121), 4326)::geography AS pt
|
||
),
|
||
-- Точки с расстоянием от антенны
|
||
pts AS (
|
||
SELECT
|
||
tp.track_id,
|
||
tp.track_point_id,
|
||
tp.observed_at,
|
||
tp.geom,
|
||
tp.ground_speed_kt,
|
||
tp.heading_deg,
|
||
ST_Distance(tp.geom::geography, a.pt) / 1000 AS dist_km
|
||
FROM fr24.track_points tp, antenna a
|
||
WHERE tp.observed_at >= NOW() - INTERVAL '7 days'
|
||
),
|
||
-- Только точки в ЗУП
|
||
zup_pts AS (
|
||
SELECT * FROM pts WHERE dist_km <= 150
|
||
),
|
||
-- Последовательные пары точек для M1/M2/скорость
|
||
pairs AS (
|
||
SELECT
|
||
track_id,
|
||
observed_at,
|
||
dist_km,
|
||
ground_speed_kt,
|
||
-- расстояние до следующей точки (м)
|
||
ST_Distance(
|
||
geom::geography,
|
||
LEAD(geom) OVER (PARTITION BY track_id ORDER BY observed_at)::geography
|
||
) AS step_dist_m,
|
||
-- временной интервал до следующей точки (сек)
|
||
EXTRACT(EPOCH FROM (
|
||
LEAD(observed_at) OVER (PARTITION BY track_id ORDER BY observed_at) - observed_at
|
||
)) AS step_dt_sec
|
||
FROM zup_pts
|
||
),
|
||
-- Производная скорость между точками
|
||
pairs_with_speed AS (
|
||
SELECT *,
|
||
CASE WHEN step_dt_sec > 0 THEN step_dist_m / step_dt_sec ELSE NULL END AS computed_speed_ms
|
||
FROM pairs
|
||
WHERE step_dist_m IS NOT NULL AND step_dt_sec IS NOT NULL
|
||
),
|
||
-- Агрегация по треку
|
||
track_metrics AS (
|
||
SELECT
|
||
track_id,
|
||
-- M3: кол-во точек в ЗУП
|
||
COUNT(*) AS n_zup,
|
||
-- M1: медианное расстояние между точками
|
||
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY step_dist_m) AS median_step_m,
|
||
MAX(step_dist_m) AS max_step_m,
|
||
-- M2: медианный интервал между точками
|
||
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY step_dt_sec) AS median_dt_sec,
|
||
MAX(step_dt_sec) AS max_gap_sec,
|
||
-- gap rate: доля интервалов > 30 сек
|
||
ROUND(
|
||
SUM(CASE WHEN step_dt_sec > 30 THEN 1 ELSE 0 END)::numeric
|
||
/ NULLIF(COUNT(*), 0), 3
|
||
) AS gap_rate,
|
||
-- M4: outlier ratio — вычисленная скорость > 350 м/с (Mach 1)
|
||
ROUND(
|
||
SUM(CASE WHEN computed_speed_ms > 350 THEN 1 ELSE 0 END)::numeric
|
||
/ NULLIF(COUNT(*), 0), 3
|
||
) AS outlier_ratio,
|
||
MIN(dist_km) AS min_dist_km,
|
||
MAX(dist_km) AS max_dist_km
|
||
FROM pairs_with_speed
|
||
GROUP BY track_id
|
||
HAVING COUNT(*) >= 5 -- минимальный значимый трек
|
||
),
|
||
-- ============================================================
|
||
-- 3. TRACK QUALITY SCORE (TQS)
|
||
-- TQS = 0.3*(1-gap_rate) + 0.3*(1-outlier_ratio)
|
||
-- + 0.2*min(n_zup/20, 1) + 0.2*(coverage_ratio ≈ 1 - gap_rate)
|
||
-- ============================================================
|
||
tqs AS (
|
||
SELECT
|
||
track_id,
|
||
n_zup,
|
||
median_step_m,
|
||
median_dt_sec,
|
||
max_gap_sec,
|
||
gap_rate,
|
||
outlier_ratio,
|
||
min_dist_km,
|
||
max_dist_km,
|
||
ROUND(
|
||
0.3 * (1 - COALESCE(gap_rate, 0))
|
||
+ 0.3 * (1 - COALESCE(outlier_ratio, 0))
|
||
+ 0.2 * LEAST(n_zup::numeric / 20, 1)
|
||
+ 0.2 * (1 - COALESCE(gap_rate, 0)) -- coverage ≈ 1 - gap_rate
|
||
, 3) AS tqs
|
||
FROM track_metrics
|
||
)
|
||
SELECT * FROM tqs
|
||
ORDER BY tqs DESC;
|
||
|
||
|
||
-- ============================================================
|
||
-- 4. RECEPTION HEALTH INDEX (RHI) — итоговый по дням
|
||
-- Мониторинг деградации антенны/приёмника во времени
|
||
-- ============================================================
|
||
WITH antenna AS (
|
||
SELECT ST_SetSRID(ST_MakePoint(37.216, 56.121), 4326)::geography AS pt
|
||
),
|
||
daily AS (
|
||
SELECT
|
||
DATE(tp.observed_at) AS day,
|
||
COUNT(DISTINCT tp.track_id) AS tracks_total,
|
||
COUNT(*) AS points_total,
|
||
-- точки в ЗУП
|
||
COUNT(*) FILTER (
|
||
WHERE ST_Distance(tp.geom::geography, a.pt) / 1000 <= 150
|
||
) AS points_in_zup,
|
||
COUNT(DISTINCT tp.track_id) FILTER (
|
||
WHERE ST_Distance(tp.geom::geography, a.pt) / 1000 <= 150
|
||
) AS tracks_in_zup
|
||
FROM fr24.track_points tp, antenna a
|
||
WHERE tp.observed_at >= NOW() - INTERVAL '14 days'
|
||
GROUP BY DATE(tp.observed_at)
|
||
)
|
||
SELECT
|
||
day,
|
||
tracks_total,
|
||
tracks_in_zup,
|
||
points_total,
|
||
points_in_zup,
|
||
ROUND(points_in_zup::numeric / NULLIF(points_total, 0), 3) AS zup_ratio,
|
||
-- RHI: нормализуем tracks_in_zup на медиану по периоду
|
||
ROUND(
|
||
tracks_in_zup::numeric
|
||
/ NULLIF(AVG(tracks_in_zup) OVER (), 0)
|
||
, 3) AS rhi
|
||
FROM daily
|
||
ORDER BY day;
|