feat(tradein): Phase 2-3 of #582 — Yandex backfill + canonical audit #591

Merged

Light1YT merged 1 commit from feat/tradein-house-coords-backfill into main

2026-05-27 06:30:55 +00:00

Light1YT commented

2026-05-27 06:30:52 +00:00

Owner

Summary

Builds on Phase 1 audit infra (PR #583). Forward-geocode 4141 houses без coords + reverse audit 4452 already-geocoded через Yandex Geocoder API.

Реальный mismatch найден на «Малышева 125»: наш point 56.838977, 60.640233 vs Yandex 56.840872, 60.654077 — ~870 метров, точка в другом квартале. Backfill это починит.

Files (5 staged, +1386 / -58)

File	LOC	Purpose
`scripts/backfill_house_coords.py` (new)	619	Forward + audit driver (per-row SAVEPOINT, resumable)
`scripts/_yandex_reverse.py` (mod)	+123	`forward_via_api()`, precision/kind в result
`scripts/audit_address_sample.sql` (mod)	+40/-7	Fix: убрана зависимость от FDW gendesign_ekb_districts_geom (нет на проде)
`tests/test_backfill_house_coords.py` (new)	510	20 tests — все pass
`scripts/README.md` (new)	116	Runbook

Test plan

pytest tests/test_backfill_house_coords.py tests/test_audit_address_mismatch.py → 39 pass
ruff check scripts/ tests/ → clean
Yandex API key verified ACTIVE (Геокодер активирован 11:29 после ~30 мин)
Canary 100 rows: --limit 100 --batch canary_2026-05-27 → inspect audit_status split
Full forward backfill: ~4141 rows (~20 мин)
Full reverse audit: ~4452 rows (~22 мин)

Decisions

Precision filter = {exact, number} only — street/range/near/other → audit-imprecise (не пишем неточные обратно в houses)
Mismatch threshold = 50м (consistent с Phase 1 report)
API-only (no Playwright fallback) — exit если key missing
EKB locality bias ll=60.6122,56.8389, spn=0.6,0.4 — иначе «ул Малышева 51» матчит Москва
Random sample (без district stratification) — district column отсутствует в houses, FDW spatial-join убран

Deployment

Merge → deploy.yml triggers (path matches tradein-mvp/backend/**)
После deploy — sed -i s/^# YANDEX/YANDEX/g .env.runtime (или add line) на VPS
docker restart tradein-backend для pick up env var
Запуск canary: docker exec tradein-backend python -m scripts.backfill_house_coords --limit 100 --batch canary_2026-05-27
Inspect: ожидаем backfill 70%+, imprecise 15-20%, no_match 5-10%, error <2%

Closes #582 (Phase 2-3 portion; Phase 1 was #583)

## Summary Builds on Phase 1 audit infra (PR #583). Forward-geocode **4141 houses без coords** + reverse audit **4452 already-geocoded** через Yandex Geocoder API. **Реальный mismatch найден на «Малышева 125»**: наш point `56.838977, 60.640233` vs Yandex `56.840872, 60.654077` — **~870 метров**, точка в другом квартале. Backfill это починит. ## Files (5 staged, +1386 / -58) | File | LOC | Purpose | |---|---|---| | `scripts/backfill_house_coords.py` (new) | 619 | Forward + audit driver (per-row SAVEPOINT, resumable) | | `scripts/_yandex_reverse.py` (mod) | +123 | `forward_via_api()`, precision/kind в result | | `scripts/audit_address_sample.sql` (mod) | +40/-7 | Fix: убрана зависимость от FDW gendesign_ekb_districts_geom (нет на проде) | | `tests/test_backfill_house_coords.py` (new) | 510 | 20 tests — все pass | | `scripts/README.md` (new) | 116 | Runbook | ## Test plan - [x] `pytest tests/test_backfill_house_coords.py tests/test_audit_address_mismatch.py` → 39 pass - [x] `ruff check scripts/ tests/` → clean - [x] Yandex API key verified ACTIVE (Геокодер активирован 11:29 после ~30 мин) - [ ] Canary 100 rows: `--limit 100 --batch canary_2026-05-27` → inspect audit_status split - [ ] Full forward backfill: ~4141 rows (~20 мин) - [ ] Full reverse audit: ~4452 rows (~22 мин) ## Decisions - Precision filter = {exact, number} only — `street/range/near/other` → audit-imprecise (не пишем неточные обратно в houses) - Mismatch threshold = 50м (consistent с Phase 1 report) - API-only (no Playwright fallback) — exit если key missing - EKB locality bias `ll=60.6122,56.8389, spn=0.6,0.4` — иначе «ул Малышева 51» матчит Москва - Random sample (без district stratification) — district column отсутствует в houses, FDW spatial-join убран ## Deployment 1. Merge → deploy.yml triggers (path matches `tradein-mvp/backend/**`) 2. После deploy — `sed -i s/^# YANDEX/YANDEX/g .env.runtime` (или add line) на VPS 3. `docker restart tradein-backend` для pick up env var 4. Запуск canary: `docker exec tradein-backend python -m scripts.backfill_house_coords --limit 100 --batch canary_2026-05-27` 5. Inspect: ожидаем backfill 70%+, imprecise 15-20%, no_match 5-10%, error <2% Closes #582 (Phase 2-3 portion; Phase 1 was #583)

Light1YT added 1 commit 2026-05-27 06:30:53 +00:00

feat(tradein): Phase 2-3 of #582 — backfill houses + canonical reverse audit 20b4a195b3

Builds on Phase 1 audit infra (PR #583). Forward-geocode 4141 houses
без coords + reverse audit 4452 already-geocoded houses через Yandex
Geocoder API. Verified mismatch 870м для «Малышева 125» — наш point
в другом квартале от реального здания.

Scripts (new):
- `backfill_house_coords.py` (619 LOC) — driver:
  * Forward mode: UPDATE houses SET lat/lon когда precision ∈ {exact, number}
  * `--audit-only`: reverse геокод + distance check, flag >50м как mismatch
  * Per-row SAVEPOINT, idempotent через UNIQUE (house_id, audit_batch)
  * EKB locality bias (ll=60.6122,56.8389, spn=0.6,0.4)
  * Rate-limit 50ms между запросами, 25k/день квота Yandex покрывает 8.5k houses

- `_yandex_reverse.py` (+123 LOC) — added `forward_via_api()`,
  precision/kind в YandexReverseResult dataclass

- `audit_address_sample.sql` (-7 +47) — FIX: убрана зависимость от FDW
  `gendesign_ekb_districts_geom` (нет на проде). Простой
  `ORDER BY random()` без district stratification. Bind-param контракт
  сохранён (`:limit_per_district` * 8 = 200 default).

Tests:
- `test_backfill_house_coords.py` (510 LOC) — 20 tests: request shape,
  precision filter, UPDATE shape, backfill happy/imprecise/no_match/error,
  audit ok/mismatch/no_match, resumability, missing-key exit, mode routing
- Existing `test_audit_address_mismatch.py` still green
- 39/39 pass, ruff clean

Decisions (см. PR body):
- Precision filter = {exact, number} only (street/range/near → audit-imprecise)
- Mismatch threshold 50м (consistent с Phase 1 report)
- API-only (no Playwright fallback) — exit если key missing

Closes #582 (Phase 2-3 portion; Phase 1 was #583)