bot-backend
4d063d6827
Quarterly Rosreestr dataset_КАДАСТРСТОИМОСТЬ (cadastral value) gives a per-cad-quarter cadastral ₽/m². Registered deals priced below 0.7x that quarter's cadastral median are dropped from mv_quarter_price_per_m2 as noise (data-entry junk / non-arm's-length deals that drag medians down). - 178_schema_cadastral_value.sql: rosreestr_cadastral_value (+ staging), region-66-scoped, idempotent. - 178b_load_cadastral_value.py: psycopg v3 staging COPY -> typed region-66 INSERT, idempotent skip-if-loaded. Run MANUALLY by operator after deploy. - 179_mv_quarter_price_cadastral_floor.sql: recreates BOTH MVs (CASCADE drops the dependent index MV). Adds cad_floor CTE + LEFT JOIN guard to mv_quarter_price_per_m2; mv_quarter_price_index recreated verbatim. Safe to auto-apply on prod before any cadastral data exists: empty rosreestr_cadastral_value -> cad_floor has 0 rows -> floor_ppm2 IS NULL for every quarter -> predicate keeps ALL deals -> MV byte-identical to the pre-floor (95) definition. Proven by a rolled-back dry-run (66:% EXCEPT both directions = 0). With the experiment cad source loaded the floor moves 480 of 1972 quarters (median 66788 -> 67350). NN bumped from spec's 172/173 to 178/178b/179 (172-177 already on main).
77 lines
3.8 KiB
PL/PgSQL
77 lines
3.8 KiB
PL/PgSQL
-- 178_schema_cadastral_value.sql
|
||
-- Rosreestr open dataset: dataset_КАДАСТРСТОИМОСТЬ (cadastral value).
|
||
-- Source URL pattern (region group 60–92, contains Свердловская обл. = 66):
|
||
-- https://rosreestr.gov.ru/.../dataset_КАДАСТРСТОИМОСТЬ_r-r_60-92_y_{YYYY}_q_{Q}.csv
|
||
-- Encoding: UTF-8. Separator: '~'. QUOTE '"'. NULL '' (empty string).
|
||
-- Header columns (verified, in order):
|
||
-- number ~ realestate_type_code ~ purpose_code ~ area ~ region_code ~ district ~
|
||
-- city ~ quarter_cad_number ~ street ~ registration_quarter ~ application_quarter ~
|
||
-- cost ~ cost_per_area ~ period
|
||
--
|
||
-- Purpose: cadastral ₽/m² floor per cadastral quarter, used to denoise the deals
|
||
-- price index (mv_quarter_price_per_m2) — registered deals priced below
|
||
-- 0.7 × the cadastral median for their quarter are dropped as data noise.
|
||
-- realestate_type_code='002001003000' (помещения/квартиры) — same code as
|
||
-- rosreestr_deals; quarter_cad_number JOIN-compatible with rosreestr_deals.
|
||
-- `number` = count of aggregated objects collapsed into that source row.
|
||
--
|
||
-- Scope: region-66-scoped — the .py loader (178b_load_cadastral_value.py) filters
|
||
-- region_code='66' on INSERT. The 60–92 CSV is loaded once per quarter MANUALLY
|
||
-- by an operator after deploy (this migration only creates the empty schema).
|
||
--
|
||
-- Dependencies: none. Auto-applied on prod via deploy.yml (_schema_migrations).
|
||
-- Idempotent: CREATE TABLE/INDEX IF NOT EXISTS — safe to re-apply.
|
||
|
||
BEGIN;
|
||
|
||
-- Typed target table (region-66-scoped after loader filter).
|
||
CREATE TABLE IF NOT EXISTS rosreestr_cadastral_value (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
source_quarter VARCHAR(8) NOT NULL, -- '2026Q1' for traceability
|
||
-- Source columns (typed)
|
||
number INTEGER, -- aggregated object count
|
||
realestate_type_code TEXT, -- '002001003000' = помещения
|
||
purpose_code TEXT,
|
||
area NUMERIC, -- м²
|
||
region_code SMALLINT, -- 66 = Свердловская обл.
|
||
district TEXT,
|
||
city TEXT,
|
||
quarter_cad_number VARCHAR(30), -- '66:41:0506001' — JOIN-ключ
|
||
street TEXT,
|
||
registration_quarter TEXT,
|
||
application_quarter TEXT,
|
||
cost NUMERIC, -- кадастровая стоимость, ₽
|
||
cost_per_area NUMERIC, -- кадастровая ₽/m²
|
||
period TEXT,
|
||
-- Audit
|
||
loaded_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
);
|
||
|
||
-- Lookup index for the cad_floor CTE (filter by quarter + apartment type code).
|
||
CREATE INDEX IF NOT EXISTS rosreestr_cadastral_value_cq_type_idx
|
||
ON rosreestr_cadastral_value (quarter_cad_number, realestate_type_code);
|
||
|
||
-- Idempotent skip-if-loaded check in the loader is by source_quarter.
|
||
CREATE INDEX IF NOT EXISTS rosreestr_cadastral_value_source_quarter_idx
|
||
ON rosreestr_cadastral_value (source_quarter);
|
||
|
||
-- Staging: один общий буфер. Перед каждым кварталом TRUNCATE.
|
||
-- 14 source columns, all TEXT, in CSV header order — typed cast happens on INSERT.
|
||
CREATE UNLOGGED TABLE IF NOT EXISTS rosreestr_cadastral_value_staging (
|
||
number TEXT,
|
||
realestate_type_code TEXT,
|
||
purpose_code TEXT,
|
||
area TEXT,
|
||
region_code TEXT,
|
||
district TEXT,
|
||
city TEXT,
|
||
quarter_cad_number TEXT,
|
||
street TEXT,
|
||
registration_quarter TEXT,
|
||
application_quarter TEXT,
|
||
cost TEXT,
|
||
cost_per_area TEXT,
|
||
period TEXT
|
||
);
|
||
|
||
COMMIT;
|