fdb54834e5
- data/sql/98_*: rename from 89 to avoid collision with existing 89_drop_dead_brin - _get_red_lines: ST_Length(ST_Intersection(planar)::geography) instead of ST_Area(ST_Intersection(::geography, ::geography)) — fixes PostGIS 3.4 tolerance error and returns correct non-zero length for LINESTRING intersections - Rename intersection_area_sqm → intersection_length_m across schema, TS types, frontend component, and tests; add test_get_red_lines_db_exception_returns_empty Addresses review-bot feedback on PR #220.
43 lines
2.4 KiB
PL/PgSQL
43 lines
2.4 KiB
PL/PgSQL
-- 98_nspd_quarter_dumps_opportunity_flag.sql
|
||
-- Context : TIER 4 opportunity denorm columns for nspd_quarter_dumps.
|
||
-- Enables fast map filter "find quarters with auction parcels" without
|
||
-- unpacking features_json JSONB on every query.
|
||
-- Part of issue #94 PR 2 (TIER 4 opportunity layers).
|
||
-- Dependencies: 88_nspd_quarter_dumps.sql (table must exist)
|
||
-- Deploy order: after 88_nspd_quarter_dumps.sql
|
||
-- Idempotent: yes — ADD COLUMN IF NOT EXISTS + CREATE INDEX IF NOT EXISTS
|
||
-- Related:
|
||
-- - backend/app/services/scrapers/nspd_client.py :: LAYERS dict (TIER 4)
|
||
-- - backend/app/services/site_finder/quarter_dump_lookup.py :: _get_opportunity_parcels
|
||
-- - backend/app/workers/tasks/nspd_sync.py :: _build_opportunity_count, _build_has_auction_parcels
|
||
-- Note: red_lines (layer 879243) is a TIER 1 core layer — always harvested,
|
||
-- counted in red_lines_count (88_*.sql). No separate v2 column needed.
|
||
|
||
BEGIN;
|
||
|
||
-- Denorm columns for fast filter on map (find quarters with opportunity)
|
||
ALTER TABLE nspd_quarter_dumps
|
||
ADD COLUMN IF NOT EXISTS has_auction_parcels BOOLEAN DEFAULT FALSE,
|
||
ADD COLUMN IF NOT EXISTS opportunity_count INTEGER DEFAULT 0;
|
||
|
||
COMMENT ON COLUMN nspd_quarter_dumps.has_auction_parcels IS
|
||
'TRUE если квартал содержит >= 1 feature слоя auction_parcels (NSPD layer 37299). '
|
||
'Заполняется при harvest (include_opportunity=True). Используется для быстрого '
|
||
'поиска кварталов с аукционными ЗУ на карте. (#94 PR2)';
|
||
|
||
COMMENT ON COLUMN nspd_quarter_dumps.opportunity_count IS
|
||
'Сумма features TIER 4 opportunity layers: '
|
||
'auction_parcels (37299) + scheme_parcels (37294) + free_parcels (37298) + '
|
||
'future_parcels (36473) + protected_areas/oopt (875845). '
|
||
'Заполняется при harvest (include_opportunity=True). (#94 PR2)';
|
||
|
||
-- Partial index for quick "find quarters with auction parcels" map filter
|
||
CREATE INDEX IF NOT EXISTS idx_nspd_quarter_dumps_auction
|
||
ON nspd_quarter_dumps (quarter_cad)
|
||
WHERE has_auction_parcels = TRUE;
|
||
|
||
COMMENT ON INDEX idx_nspd_quarter_dumps_auction IS
|
||
'Partial index: быстрый поиск кварталов с аукционными ЗУ для UI-фильтра карты. '
|
||
'Малая кардинальность (только TRUE rows) — очень компактный. (#94 PR2)';
|
||
|
||
COMMIT;
|