fix(cadastre): auto-heal cad_quarters_geom from CAT_QUARTER_STATS #181

Merged

lekss361 merged 1 commit from fix/cadastre-quarters-geom-autoheal into main

2026-05-15 14:16:39 +00:00

lekss361 commented

2026-05-15 14:08:33 +00:00

(Migrated from github.com)

Summary

Auto-heal broken cad_quarters_geom rows during regular cadastre harvest. Data-quality follow-up to #179.

Add upsert_quarter_geom_from_feature in bulk_harvest.py: CTE-based UPDATE that takes NSPD CAT_QUARTER_STATS (36381) polygon, transforms 3857 → 4326, writes only when existing geom is broken (width <100m or >10000m) and new geom is valid.
Wire into existing Phase 4 after upsert_quarter_stats with db.begin_nested() savepoint — malformed NSPD GeoJSON rolls back only geom UPDATE, preserves successful stats upsert.
7 new unit tests cover skip-on-invalid-geometry, scalar=None→False, scalar=cad_number→True, MultiPolygon coercion, CAST(:geom AS text) anti-double-colon, custom width bounds, integration test for exception path (heal raises → harvest_quarter does NOT propagate).

Why

Job #8 (full ЕКБ) revealed cad_quarters_geom: 1735/2408 (72%) rows have micro-precision width <0.01m vs expected 200-4000m. PR #179 introduced sanity check skipping grid-walk for broken quarters — but that's masking, not fixing. NSPD returns valid quarter polygon in CAT_QUARTER_STATS feature, we just discard it.

After this PR: every harvest_quarter run that returns CAT_QUARTER_STATS feature will heal the row IF current geom is broken. ~1735 broken quarters should be healed at next ekb_full run.

SQL safety guards

WHERE includes both sanity gates: new geom width ∈ [100m, 10000m] AND existing geom broken (NOT in same range).
RETURNING cqg.cad_number + scalar() signals row-touched.
ST_Multi() coerces Polygon→MultiPolygon (matches geometry(MultiPolygon, 4326) schema).
raw_props NOT updated — original provenance preserved.
Single-row UPDATE by PK, no table lock.

Test plan

pytest tests/services/test_cadastre_bulk.py → 35 passed locally

After merge + deploy → trigger pilot v8 → check cad_quarters_geom healed rows count via:

SELECT COUNT(*) FROM cad_quarters_geom
WHERE ST_XMax(ST_Transform(geom, 3857)) - ST_XMin(ST_Transform(geom, 3857)) BETWEEN 100 AND 10000;

After full ekb_full run → expect previously-skipped quarters now have grid-walk discovered_count > 0.

Closes part of #168, depends on conceptual fix from #179.

## Summary Auto-heal broken `cad_quarters_geom` rows during regular cadastre harvest. Data-quality follow-up to #179. - Add `upsert_quarter_geom_from_feature` in `bulk_harvest.py`: CTE-based UPDATE that takes NSPD CAT_QUARTER_STATS (36381) polygon, transforms 3857 → 4326, writes only when existing geom is broken (width <100m or >10000m) and new geom is valid. - Wire into existing **Phase 4** after `upsert_quarter_stats` with `db.begin_nested()` savepoint — malformed NSPD GeoJSON rolls back only geom UPDATE, preserves successful stats upsert. - 7 new unit tests cover skip-on-invalid-geometry, scalar=None→False, scalar=cad_number→True, MultiPolygon coercion, CAST(:geom AS text) anti-double-colon, custom width bounds, **integration test for exception path** (heal raises → harvest_quarter does NOT propagate). ## Why Job #8 (full ЕКБ) revealed `cad_quarters_geom`: 1735/2408 (72%) rows have micro-precision width <0.01m vs expected 200-4000m. PR #179 introduced sanity check skipping grid-walk for broken quarters — but that's masking, not fixing. NSPD returns valid quarter polygon in CAT_QUARTER_STATS feature, we just discard it. After this PR: every harvest_quarter run that returns CAT_QUARTER_STATS feature will heal the row IF current geom is broken. ~1735 broken quarters should be healed at next `ekb_full` run. ## SQL safety guards - WHERE includes both sanity gates: new geom width ∈ [100m, 10000m] AND existing geom broken (NOT in same range). - `RETURNING cqg.cad_number` + `scalar()` signals row-touched. - `ST_Multi()` coerces Polygon→MultiPolygon (matches `geometry(MultiPolygon, 4326)` schema). - `raw_props` NOT updated — original provenance preserved. - Single-row UPDATE by PK, no table lock. ## Test plan - [x] `pytest tests/services/test_cadastre_bulk.py` → 35 passed locally - [ ] After merge + deploy → trigger pilot v8 → check `cad_quarters_geom` healed rows count via: ```sql SELECT COUNT(*) FROM cad_quarters_geom WHERE ST_XMax(ST_Transform(geom, 3857)) - ST_XMin(ST_Transform(geom, 3857)) BETWEEN 100 AND 10000; ``` - [ ] After full ekb_full run → expect previously-skipped quarters now have grid-walk discovered_count > 0. Closes part of #168, depends on conceptual fix from #179.

lekss361 commented

2026-05-15 14:16:28 +00:00

(Migrated from github.com)

Review

CI зелёный (backend 1m17s + frontend 1m33s) + cross-check (agent):

✅ SAVEPOINT pattern textbook-correct

try: with db.begin_nested(): ... except: ... — канонический паттерн SQLAlchemy 2.0. На raise внутри savepoint → auto-rollback savepoint, outer tx остаётся usable, outer except ловит → последующий db.commit() сохраняет успешный upsert_quarter_stats. В отличие от #155 (где db.rollback() положил outer tx), здесь semantics правильные.

✅ SQL safety — двойные gates

WHERE width_check.w BETWEEN :min_w AND :max_w
  AND (cqg.geom IS NULL
       OR (ST_XMax(...) - ST_XMin(...) NOT BETWEEN :min_w AND :max_w))

Новая geom обязана быть в [100, 10000]м.
Existing geom обязана быть NULL или сломанной.
Не перезаписывает healthy + не пишет broken back. RETURNING cad_number + scalar() сигналит touched.

✅ CAST compliance

Тест test_upsert_quarter_geom_sql_uses_cast_not_double_colon — CAST(:geom AS text) присутствует, ::jsonb/::text отсутствуют. Соответствует #178 паттерну.

✅ Schema match

ST_Multi(ST_Transform(..., 4326)) — coerce Polygon → MultiPolygon для geometry(MultiPolygon, 4326) column. ✓

✅ Provenance

source = 'quarter_stats_autoheal' — ops смогут считать сколько healed rows. raw_props не перезаписывается. Single-row UPDATE by PK — no table lock.

✅ Test coverage

7 unit tests: invalid geometry skip, scalar=None→False, scalar=cad_number→True, MultiPolygon coerce, CAST compliance, custom bounds, integration exception path.

Минор: test_harvest_quarter_geom_heal_failure_does_not_propagate мокает db.begin_nested как MagicMock — это не exercise real savepoint rollback, только Python-уровневый try/except. Acceptable test-fidelity gap: SAVEPOINT semantics гарантируются самим SQLAlchemy (subject of fetched docs), не нашим кодом.

✅ Phase 4 ordering safe

upsert_quarter_stats OUTSIDE savepoint → если fail, raise до открытия savepoint. Geom heal INSIDE → fail rollback'нёт только savepoint. Затем db.commit() фиксирует stats (или stats+heal если оба ок).

✅ No conflict с PR #182

Touch разные секции bulk_harvest.py — #181 Phase 4 (~L237) + новая функция (~L946); #182 dataclass + Phase 1/1.5. Zero overlap.

✅ Drift отсутствует

Server-side only, no TS callers.

approve merge

## Review CI зелёный (backend 1m17s + frontend 1m33s) + cross-check (agent): ### ✅ SAVEPOINT pattern textbook-correct `try: with db.begin_nested(): ... except: ...` — канонический паттерн SQLAlchemy 2.0. На raise внутри savepoint → auto-rollback savepoint, outer tx остаётся usable, outer `except` ловит → последующий `db.commit()` сохраняет успешный `upsert_quarter_stats`. В отличие от #155 (где `db.rollback()` положил outer tx), здесь semantics правильные. ### ✅ SQL safety — двойные gates ```sql WHERE width_check.w BETWEEN :min_w AND :max_w AND (cqg.geom IS NULL OR (ST_XMax(...) - ST_XMin(...) NOT BETWEEN :min_w AND :max_w)) ``` - Новая geom **обязана** быть в [100, 10000]м. - Existing geom **обязана** быть NULL или сломанной. - Не перезаписывает healthy + не пишет broken back. `RETURNING cad_number` + `scalar()` сигналит touched. ### ✅ CAST compliance Тест `test_upsert_quarter_geom_sql_uses_cast_not_double_colon` — `CAST(:geom AS text)` присутствует, `::jsonb`/`::text` отсутствуют. Соответствует #178 паттерну. ### ✅ Schema match `ST_Multi(ST_Transform(..., 4326))` — coerce Polygon → MultiPolygon для `geometry(MultiPolygon, 4326)` column. ✓ ### ✅ Provenance `source = 'quarter_stats_autoheal'` — ops смогут считать сколько healed rows. `raw_props` не перезаписывается. Single-row UPDATE by PK — no table lock. ### ✅ Test coverage 7 unit tests: invalid geometry skip, scalar=None→False, scalar=cad_number→True, MultiPolygon coerce, CAST compliance, custom bounds, integration exception path. **Минор**: `test_harvest_quarter_geom_heal_failure_does_not_propagate` мокает `db.begin_nested` как MagicMock — это **не** exercise real savepoint rollback, только Python-уровневый try/except. Acceptable test-fidelity gap: SAVEPOINT semantics гарантируются самим SQLAlchemy (subject of fetched docs), не нашим кодом. ### ✅ Phase 4 ordering safe `upsert_quarter_stats` OUTSIDE savepoint → если fail, raise до открытия savepoint. Geom heal INSIDE → fail rollback'нёт только savepoint. Затем `db.commit()` фиксирует stats (или stats+heal если оба ок). ### ✅ No conflict с PR #182 Touch разные секции `bulk_harvest.py` — #181 Phase 4 (~L237) + новая функция (~L946); #182 dataclass + Phase 1/1.5. Zero overlap. ### ✅ Drift отсутствует Server-side only, no TS callers. approve merge