lekss361
57c6d119f5
Co-authored-by: lekss361 <lekss361@gendsgn.local> Co-committed-by: lekss361 <lekss361@gendsgn.local>
96 lines
6.7 KiB
PL/PgSQL
96 lines
6.7 KiB
PL/PgSQL
-- 137_ekb_krt_sites.sql
|
||
-- #1060 (Site Finder v2 / GG-форсайт) — реестр КРТ-площадок ЕКБ.
|
||
--
|
||
-- Источник: https://xn--80acgfbsl1azdqr.xn--p1ai/дляработы/гиз/крт
|
||
-- (екатеринбург.рф, SSR-страница, 15+ площадок).
|
||
--
|
||
-- Реквизиты каждой площадки: застройщик, вид застройки (жилая/нежилая),
|
||
-- параметры договора КРТ (номер/дата/цена аукциона), градпотенциал, площадь,
|
||
-- список постановлений ПАГЕ. Геометрия отсутствует в источнике — join к
|
||
-- opportunity_krt запланирован отдельно.
|
||
--
|
||
-- Ключ: contract_number — присутствует у площадок с заключённым договором КРТ
|
||
-- (большинство). Площадки без договора (ранние стадии «подготовки к аукциону»)
|
||
-- используют site_name как резервный ключ (UNIQUE(site_name) + partial UNIQUE
|
||
-- по contract_number WHERE contract_number IS NOT NULL реализован через два
|
||
-- раздельных индекса + DO-блоки ниже).
|
||
--
|
||
-- Deploy: auto-applied deploy.yml через _schema_migrations (ровно один раз по NN).
|
||
-- Idempotent (CREATE TABLE/INDEX IF NOT EXISTS + guarded DO-блоки по pg_constraint).
|
||
-- Без INSERT — данные приходят из ekb_krt_sync Celery-воркера (#1060).
|
||
--
|
||
-- Параллельный PR берёт номер 138 — этот файл 137 зарезервирован для этого PR.
|
||
|
||
BEGIN;
|
||
|
||
-- ── 1. Таблица ekb_krt_sites ─────────────────────────────────────────────────
|
||
CREATE TABLE IF NOT EXISTS ekb_krt_sites (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
site_name TEXT NOT NULL, -- «N. КРТ жилой/нежилой застройки в районе ул. X»
|
||
district TEXT, -- район/квартал (извлечён из названия best-effort)
|
||
krt_kind TEXT, -- «жилой застройки» | «нежилой застройки»
|
||
developer_name TEXT, -- застройщик из текста договора
|
||
contract_number TEXT, -- «1-2/21.7-14» (NULL у незавершённых площадок)
|
||
contract_date DATE, -- дата договора КРТ
|
||
auction_start_price NUMERIC, -- начальная стоимость аукциона, руб.
|
||
auction_final_price NUMERIC, -- цена по результатам торгов, руб.
|
||
gradpotential_sqm NUMERIC, -- градостроительный потенциал, кв. м
|
||
area_ha NUMERIC, -- площадь территории, га
|
||
mkd_count INTEGER, -- всего многоквартирных домов (если есть)
|
||
paga_decisions JSONB, -- [{date, number, title}] постановления ПАГЕ
|
||
raw_block TEXT, -- исходный текст блока (для отладки/re-parse)
|
||
source_url TEXT, -- URL страницы-источника
|
||
fetched_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
||
);
|
||
|
||
COMMENT ON TABLE ekb_krt_sites IS
|
||
'Реестр КРТ-площадок Екатеринбурга (#1060, GG-форсайт). '
|
||
'Источник: екатеринбург.рф/дляработы/гиз/крт. '
|
||
'Обновляется еженедельно воркером ekb_krt_sync. '
|
||
'Геометрия отсутствует — join к opportunity_krt планируется отдельно.';
|
||
|
||
COMMENT ON COLUMN ekb_krt_sites.contract_number IS
|
||
'Номер договора КРТ («1-2/21.7-14»). NULL у площадок на стадии подготовки к аукциону.';
|
||
COMMENT ON COLUMN ekb_krt_sites.paga_decisions IS
|
||
'JSON-массив постановлений ПАГЕ: [{date: «DD.MM.YYYY», number: «NNN», title: «...»}]. '
|
||
'Все постановления из блока, включая об утверждении ППТ/ПМТ.';
|
||
COMMENT ON COLUMN ekb_krt_sites.krt_kind IS
|
||
'«жилой застройки» или «нежилой застройки» — вид КРТ из названия площадки.';
|
||
|
||
-- ── 2. UNIQUE по site_name — надёжный ключ для уникальности (присутствует всегда) ──
|
||
-- Служит ON CONFLICT target для UPSERT воркера. contract_number NULL у ранних стадий,
|
||
-- поэтому site_name выбран основным дедуп-ключом. Дополнительно: уникальный индекс
|
||
-- по contract_number WHERE NOT NULL — для быстрого lookup по номеру договора.
|
||
DO $$
|
||
BEGIN
|
||
IF NOT EXISTS (
|
||
SELECT 1 FROM pg_constraint WHERE conname = 'uq_ekb_krt_sites_site_name'
|
||
) THEN
|
||
ALTER TABLE ekb_krt_sites
|
||
ADD CONSTRAINT uq_ekb_krt_sites_site_name UNIQUE (site_name);
|
||
END IF;
|
||
END
|
||
$$;
|
||
|
||
-- ── 3. Partial unique index по contract_number (где не NULL) ─────────────────
|
||
-- Не через DO-блок + ALTER (partial UNIQUE через ALTER ADD CONSTRAINT не поддерживает
|
||
-- WHERE clause в PostgreSQL). Вместо этого — CREATE UNIQUE INDEX IF NOT EXISTS с WHERE.
|
||
CREATE UNIQUE INDEX IF NOT EXISTS uix_ekb_krt_sites_contract_number
|
||
ON ekb_krt_sites (contract_number)
|
||
WHERE contract_number IS NOT NULL;
|
||
|
||
-- ── 4. Индексы ───────────────────────────────────────────────────────────────
|
||
-- Поиск по виду застройки (жилая/нежилая).
|
||
CREATE INDEX IF NOT EXISTS idx_ekb_krt_sites_krt_kind
|
||
ON ekb_krt_sites (krt_kind);
|
||
|
||
-- Поиск по застройщику (JOIN с domrf_kn / анализ по девелоперу).
|
||
CREATE INDEX IF NOT EXISTS idx_ekb_krt_sites_developer
|
||
ON ekb_krt_sites (developer_name)
|
||
WHERE developer_name IS NOT NULL;
|
||
|
||
-- Свежесть данных / ORDER BY fetched_at для мониторинга.
|
||
CREATE INDEX IF NOT EXISTS idx_ekb_krt_sites_fetched_at
|
||
ON ekb_krt_sites (fetched_at DESC);
|
||
|
||
COMMIT;
|