Light1YT
b83ffedec3
§9.x forecast metrics filter objective_lots.district (informal micro-districts)
but /analyze resolves the official admin district from ekb_districts → admin
name matched 0 rows → silent empty forecast (market_metrics('Кировский')→0).
Add a curated micro→admin alias table seeding all 35 micro values to their
primary admin (8 EKB admins). Curated not spatial: complex_id join hits only
~28% of rows and complexes is multi-city, so ST_Contains scatters micros
uniformly. 4 cross-city values (Н.Тагил/Каменск) contained in one noted bucket.
Coverage: 8/8 admins resolve, 35/35 mapped, 0 unmapped. Idempotent + additive
(CREATE TABLE IF NOT EXISTS + INSERT ON CONFLICT DO NOTHING); applied to prod
+ registered in _schema_migrations. Resolver/fallback documented in header;
market_metrics wiring is Step 2 (separate PR). Refs #969 #949.
151 lines
14 KiB
PL/PgSQL
151 lines
14 KiB
PL/PgSQL
-- 131_district_vocab_map.sql
|
||
-- feat/district-vocab-reconciliation — словарь-мост между ДВУМЯ районными
|
||
-- вокабулярами ЕКБ, чтобы §9.x forecast metrics перестали возвращать пустой прогноз.
|
||
--
|
||
-- ПРОБЛЕМА (ground-truthed на prod):
|
||
-- • §9.x market-метрики фильтруют objective_lots.district — это НЕФОРМАЛЬНЫЕ
|
||
-- МИКРО-районы ЕКБ (35 distinct значений: «Уралмаш», «ВИЗ», «Уктус» …).
|
||
-- • Но /analyze резолвит у участка ОФИЦИАЛЬНЫЙ АДМИН-район из ekb_districts
|
||
-- (8 полигонов: Кировский, Ленинский, Чкаловский …).
|
||
-- • Админ-имя → 0 строк в objective_lots → молча пустой прогноз.
|
||
-- Напр. market_metrics('Кировский') → obj_count=0; market_metrics('Академический')
|
||
-- → 34 только потому, что 'Академический' случайно есть в ОБОИХ вокабулярах.
|
||
-- Этот файл создаёт явный alias-маппинг micro→admin для всех 35 микро-значений,
|
||
-- чтобы consumer (Step 2, НЕ здесь) мог развернуть админ-имя в список микро.
|
||
--
|
||
-- ПОЧЕМУ КУРИРОВАННЫЙ SEED, А НЕ ПРОСТРАНСТВЕННОЕ НАЗНАЧЕНИЕ (verified, prod):
|
||
-- Пространственный путь через complexes НЕнадёжен и сознательно ОТВЕРГНУТ:
|
||
-- 1) objective_lots.complex_id = complexes.id джойнит лишь ~28% строк
|
||
-- (≈300k / 1.08M — частичный/неверный FK-путь).
|
||
-- 2) complexes (2023 строки, лишь 1516 с geom) — МУЛЬТИГОРОДСКАЯ таблица
|
||
-- (coords lat 56.38–58.05, lon 57.76–65.27 — далеко за пределами ЕКБ).
|
||
-- 3) Наивный ST_Contains(ekb_districts.geom, complexes.geom) равномерно
|
||
-- размазывает каждый микро по всем 8 админам (мусор).
|
||
-- Поэтому маппинг построен по АДМИНИСТРАТИВНОЙ ГЕОГРАФИИ ЕКБ (публичное знание),
|
||
-- а 28%-spatial использован ТОЛЬКО как sanity-check — где он противоречит
|
||
-- реальной географии (частый случай: «Парковый»/«Пионерский» спутаны), доверяем
|
||
-- географии. Конфликты задокументированы построчно ниже в comment у INSERT.
|
||
--
|
||
-- НЕОДНОЗНАЧНЫЕ / ПОГРАНИЧНЫЕ микро (решения зафиксированы — PRIMARY admin):
|
||
-- • Центр — историч. центр делится Ленинский/Кировский; PRIMARY=Ленинский.
|
||
-- • Широкая Речка — пограничье ВИЗ/Академ; официально Верх-Исетский (spatial шумел).
|
||
-- • Совхоз — Нижне-Исетский/Совхозный → Чкаловский (spatial шумел Ленинским).
|
||
-- • УНЦ — Краснолесье/Юго-Запад → Ленинский.
|
||
-- • Солнечный — новый ЖР на юге (Кольцовский тр.) → Чкаловский.
|
||
-- • Парковый,Пионерский,Компрессорный,Вокзальный — spatial назначал неверный админ
|
||
-- (Орджоникидзевский/Академ); по географии исправлены
|
||
-- (Октябрьский / Железнодорожный / Октябрьский / Железнодорожный).
|
||
--
|
||
-- CROSS-CITY значения (НЕ микро-районы ЕКБ! data-quality дыра в objective_lots —
|
||
-- таблица регион-wide, не только ЕКБ):
|
||
-- • Тагилстроевский, Дзержинский → районы НИЖНЕГО ТАГИЛА.
|
||
-- • Синарский, Красногорский → районы КАМЕНСКА-УРАЛЬСКОГО.
|
||
-- У них НЕТ корректного ЕКБ-админа. Чтобы выполнить инвариант «0 unmapped»,
|
||
-- они СОБРАНЫ В ОДИН bucket (admin='Чкаловский') и помечены note='cross-city: …'.
|
||
-- Контейнмент в один админ (а не разброс по восьми) держит 7 админов 100% чистыми
|
||
-- и делает эти строки тривиально фильтруемыми в Step-2 (WHERE note IS NULL).
|
||
-- ВАЖНО: на практике эти строки почти никогда не сработают — /analyze резолвит
|
||
-- админ-имя ТОЛЬКО через полигоны ekb_districts (экстент города ЕКБ), поэтому
|
||
-- участок в Н.Тагиле/Каменске не даст ни одного из 8 ЕКБ-админ-имён и уйдёт в
|
||
-- NULL/unmapped fallback (см. ниже), а не подтянет cross-city лоты.
|
||
--
|
||
-- РЕЗОЛВЕР + FALLBACK (здесь ТОЛЬКО документируем; wiring в market_metrics = Step 2):
|
||
-- Consumer разворачивает админ-имя в микро:
|
||
-- SELECT micro_district FROM ekb_district_alias
|
||
-- WHERE admin_district = CAST(:d AS text); -- psycopg v3 CAST-форма (не двоеточие-каст)
|
||
-- Затем фильтрует objective_lots.district IN (<микро>).
|
||
-- FALLBACK: если у админа нет алиасов (реальная дыра) ИЛИ district участка
|
||
-- NULL / 'не определён' → consumer падает на EKB-wide (district=None, без фильтра)
|
||
-- + штраф к confidence. Никогда не возвращает молча пустой набор.
|
||
-- Step-2-фильтр cross-city: добавить AND note IS NULL, если нужны только истинные
|
||
-- ЕКБ-микро.
|
||
--
|
||
-- Deploy: auto-applied deploy.yml через _schema_migrations (ровно один раз).
|
||
-- Idempotent + ADDITIVE: CREATE TABLE/INDEX IF NOT EXISTS + INSERT … ON CONFLICT
|
||
-- DO NOTHING. НЕТ DROP/TRUNCATE реальных данных. Повторный прогон безопасен.
|
||
-- Migration order: эта SQL-миграция ПЕРВОЙ; backend (market_metrics резолвер) — Step 2,
|
||
-- отдельным PR (этот файл его НЕ трогает).
|
||
|
||
BEGIN;
|
||
|
||
-- ── 1. Таблица-словарь micro → admin ───────────────────────────────────────────
|
||
-- micro_district — более тонкая единица, поэтому он UNIQUE (один микро принадлежит
|
||
-- ровно одному PRIMARY админу). admin_district обязан быть одним из 8 имён
|
||
-- ekb_districts с непустым geom (НЕ 'не определён') — гарантируем CHECK через
|
||
-- FK на ekb_districts ниже не делаем (там есть строка 'не определён'); вместо
|
||
-- этого инвариант обеспечивается seed-данными и проверяется acceptance-запросом.
|
||
-- note — пометка неоднозначности/cross-city (NULL = чистый однозначный ЕКБ-микро).
|
||
CREATE TABLE IF NOT EXISTS ekb_district_alias (
|
||
admin_district TEXT NOT NULL, -- официальный админ-район ЕКБ (1 из 8)
|
||
micro_district TEXT NOT NULL, -- неформальный микро-район (objective_lots.district)
|
||
note TEXT, -- NULL=clean EKB micro; иначе ambiguity/cross-city
|
||
PRIMARY KEY (admin_district, micro_district),
|
||
UNIQUE (micro_district)
|
||
);
|
||
|
||
COMMENT ON TABLE ekb_district_alias IS
|
||
'Мост micro→admin для ЕКБ-вокабуляров (feat/district-vocab-reconciliation). '
|
||
'objective_lots.district хранит НЕФОРМАЛЬНЫЕ микро-районы (35 шт.), а /analyze '
|
||
'резолвит ОФИЦИАЛЬНЫЙ админ-район из ekb_districts (8 шт.) → раньше админ-имя '
|
||
'давало 0 строк (пустой прогноз). Маппинг КУРИРОВАННЫЙ по админ-географии ЕКБ '
|
||
'(spatial через complexes отвергнут: 28%-джойн, мультигород). micro_district '
|
||
'UNIQUE (микро → ровно один PRIMARY админ). Резолвер: SELECT micro_district '
|
||
'WHERE admin_district = CAST(:d AS text). note IS NOT NULL → cross-city/ambiguous '
|
||
'(фильтруй note IS NULL для чистых ЕКБ-микро). Потребитель — market_metrics (Step 2).';
|
||
|
||
COMMENT ON COLUMN ekb_district_alias.note IS
|
||
'NULL = однозначный микро-район ЕКБ. cross-city:… = район ДРУГОГО города '
|
||
'(Н.Тагил/Каменск-Уральский), собран в bucket Чкаловский, исключать в Step-2. '
|
||
'ambiguous:… = микро на границе двух админов, выбран PRIMARY.';
|
||
|
||
-- ── 2. Seed: все 35 micro-значений objective_lots.district → PRIMARY admin ──────
|
||
-- Покрытие admin (после seed): Академический,Верх-Исетский,Железнодорожный,Кировский,
|
||
-- Ленинский,Октябрьский,Орджоникидзевский,Чкаловский — все 8 имеют ≥1 чистый микро.
|
||
-- ON CONFLICT DO NOTHING — повторный прогон не перезатирает (и не падает на PK/UNIQUE).
|
||
INSERT INTO ekb_district_alias (admin_district, micro_district, note) VALUES
|
||
-- Академический (1)
|
||
('Академический', 'Академический', NULL),
|
||
-- Верх-Исетский (2)
|
||
('Верх-Исетский', 'ВИЗ', NULL),
|
||
('Верх-Исетский', 'Широкая Речка', 'ambiguous: граница ВИЗ/Академ; официально Верх-Исетский'),
|
||
-- Железнодорожный (6)
|
||
('Железнодорожный', 'Пионерский', NULL),
|
||
('Железнодорожный', 'Старая Сортировка', NULL),
|
||
('Железнодорожный', 'Новая Сортировка', NULL),
|
||
('Железнодорожный', 'Завокзальный', NULL),
|
||
('Железнодорожный', 'Заречный', NULL),
|
||
('Железнодорожный', 'Вокзальный', NULL),
|
||
-- Кировский (2)
|
||
('Кировский', 'Втузгородок', NULL),
|
||
('Кировский', 'ЖБИ', NULL),
|
||
-- Ленинский (4)
|
||
('Ленинский', 'Юго-Западный', NULL),
|
||
('Ленинский', 'Автовокзал', NULL),
|
||
('Ленинский', 'Центр', 'ambiguous: центр делится Ленинский/Кировский; PRIMARY Ленинский'),
|
||
('Ленинский', 'УНЦ', NULL),
|
||
-- Октябрьский (6)
|
||
('Октябрьский', 'Компрессорный', NULL),
|
||
('Октябрьский', 'Сибирский', NULL),
|
||
('Октябрьский', 'Парковый', NULL),
|
||
('Октябрьский', 'Новокольцовский', NULL),
|
||
('Октябрьский', 'Шарташский рынок', NULL),
|
||
('Октябрьский', 'Кольцово', NULL),
|
||
-- Орджоникидзевский (2)
|
||
('Орджоникидзевский', 'Эльмаш', NULL),
|
||
('Орджоникидзевский', 'Уралмаш', NULL),
|
||
-- Чкаловский (8 чистых + 4 cross-city bucket)
|
||
('Чкаловский', 'Уктус', NULL),
|
||
('Чкаловский', 'Чермет', NULL),
|
||
('Чкаловский', 'Ботанический', NULL),
|
||
('Чкаловский', 'Солнечный', NULL),
|
||
('Чкаловский', 'Совхоз', NULL),
|
||
('Чкаловский', 'Горный Щит', NULL),
|
||
('Чкаловский', 'Лечебный', NULL),
|
||
('Чкаловский', 'Химмаш', NULL),
|
||
('Чкаловский', 'Тагилстроевский', 'cross-city: район Нижнего Тагила, НЕ микро ЕКБ — exclude in Step-2'),
|
||
('Чкаловский', 'Дзержинский', 'cross-city: район Нижнего Тагила, НЕ микро ЕКБ — exclude in Step-2'),
|
||
('Чкаловский', 'Красногорский', 'cross-city: район Каменска-Уральского, НЕ микро ЕКБ — exclude in Step-2'),
|
||
('Чкаловский', 'Синарский', 'cross-city: район Каменска-Уральского, НЕ микро ЕКБ — exclude in Step-2')
|
||
ON CONFLICT DO NOTHING;
|
||
|
||
COMMIT;
|