"""2026 triennial reappraisal — current vs proposed market value per parcel. Source is the public layer behind the Auditor's "2026 Proposed Value Viewer" (experience.arcgis.com/experience/acf7da4828484168a55a5c3f4b78eddc): ParcelsProposedValue/FeatureServer/0 on the county's ArcGIS Online org. No key. Writes one table, parcel_reval_2026, keyed on parcel_no (same 14-digit id as parcels.id / parcels_full.parcel_no). delta and pct are generated columns so the ask agent and the popup read the same arithmetic. The new table is built under a temp name and swapped in, so askapi never sees it empty mid-refresh. Values are *proposed* until the county finalizes them; rerun this to pick up any revisions (informal reviews / BOR changes land in the same layer). Also writes wv_reval_2026.json to the docroot — the map's "Increase %/$" color modes, the reappraisal KPI tiles and the popup block all read it at page load (the popup is topped up again from /api/parcel-residents on open). One run refreshes both, so they can't drift apart the way the voter overlays do. """ from __future__ import annotations import concurrent.futures as cf import datetime as dt import json import os import sys from pathlib import Path import psycopg2 import psycopg2.extras import requests sys.path.insert(0, str(Path(__file__).resolve().parent)) from tile_views import ensure_views # noqa: E402 DATA_DIR = Path("/home/m3ac/genoa-entwuerfe.com") LAYER = ("https://services2.arcgis.com/ziXVKVy3BiopMCCU/arcgis/rest/services/" "ParcelsProposedValue/FeatureServer/0") PAGE = 2000 # the layer's maxRecordCount FIELDS = "PARCEL_NO,CurrentValue,ProposedValue,ProposedCAUVValue,ClassCode,UseCode" TABLE = "parcel_reval_2026" def _read_gispass() -> str: for line in (DATA_DIR / ".env.gis").read_text().splitlines(): if line.startswith("GISPASS="): return line.split("=", 1)[1].strip() raise SystemExit("GISPASS missing") def _fetch_page(offset: int) -> list[dict]: r = requests.get( f"{LAYER}/query", params={ "where": "1=1", "outFields": FIELDS, "returnGeometry": "false", "f": "json", "resultOffset": offset, "resultRecordCount": PAGE, "orderByFields": "OBJECTID", }, timeout=90, ) r.raise_for_status() d = r.json() if "error" in d: raise RuntimeError(f"offset {offset}: {d['error']}") return [f["attributes"] for f in d.get("features", [])] def main() -> None: count = requests.get( f"{LAYER}/query", params={"where": "1=1", "returnCountOnly": "true", "f": "json"}, timeout=30, ).json()["count"] print(f"[arcgis] {count} rows to fetch in pages of {PAGE}") rows: list[dict] = [] with cf.ThreadPoolExecutor(max_workers=4) as ex: for batch in ex.map(_fetch_page, range(0, count, PAGE)): rows.extend(batch) if len(rows) != count: raise SystemExit(f"fetched {len(rows)} rows, layer reports {count} — not swapping") # Multipart parcels appear once per polygon with the same values; keep one. by_id: dict[str, tuple] = {} conflicts = 0 for a in rows: pid = (a.get("PARCEL_NO") or "").strip() if not pid: continue rec = (pid, a.get("CurrentValue"), a.get("ProposedValue"), a.get("ProposedCAUVValue") or None, (a.get("ClassCode") or "").strip() or None, (a.get("UseCode") or "").strip() or None) if pid in by_id and by_id[pid][1:3] != rec[1:3]: conflicts += 1 by_id.setdefault(pid, rec) print(f"[dedupe] {len(by_id)} parcels ({len(rows) - len(by_id)} duplicate rows, " f"{conflicts} with differing values — kept first)") conn = psycopg2.connect( f"postgresql://gisapp:{_read_gispass()}@127.0.0.1:5432/westerville") # Refreshed in place (truncate + insert in one transaction) rather than # built beside and renamed: parcel_reval_geom depends on this table, and a # dependent view makes DROP TABLE fail. Change the columns below and you # have to drop both by hand first. with conn, conn.cursor() as cur: cur.execute(f""" CREATE TABLE IF NOT EXISTS {TABLE} ( parcel_no text PRIMARY KEY, current_value integer, proposed_value integer, proposed_cauv integer, class_code text, use_code text, delta integer GENERATED ALWAYS AS (proposed_value - current_value) STORED, pct numeric(8,2) GENERATED ALWAYS AS ( CASE WHEN current_value > 0 THEN round(100.0 * (proposed_value - current_value) / current_value, 2) END) STORED, fetched_at timestamptz NOT NULL DEFAULT now() )""") cur.execute(f"TRUNCATE {TABLE}") psycopg2.extras.execute_values( cur, f"INSERT INTO {TABLE} (parcel_no, current_value, proposed_value," f" proposed_cauv, class_code, use_code) VALUES %s", list(by_id.values()), page_size=5000) cur.execute(f"COMMENT ON TABLE {TABLE} IS " f"'Delaware Co 2026 proposed values; source {LAYER}'") # Like-for-like totals only: ~2,200 new splits/builds have a proposed # value and no current one, and counting them inflates the county # increase from +15.8% to +17.0%. cur.execute(f""" SELECT count(*), count(*) FILTER (WHERE r.parcel_no IN (SELECT id FROM parcels)), sum(current_value) FILTER (WHERE current_value > 0)::bigint, sum(proposed_value) FILTER (WHERE current_value > 0)::bigint, percentile_cont(0.5) WITHIN GROUP (ORDER BY pct), count(*) FILTER (WHERE current_value IS NULL AND proposed_value > 0) FROM {TABLE} r""") n, matched, cur_sum, prop_sum, med, n_new = cur.fetchone() # Same n >= 30 floor as askapi's class median, so the popup reads the # same benchmark whether it came from this file or the live API. cur.execute(f""" SELECT class_code, percentile_cont(0.5) WITHIN GROUP (ORDER BY pct) FROM {TABLE} WHERE pct IS NOT NULL GROUP BY class_code HAVING count(*) >= 30""") cls_med = {c: round(float(m), 1) for c, m in cur.fetchall() if c} # $0 → $0 parcels (exempt / right-of-way) have nothing to show; leave them out. cur.execute(f""" SELECT parcel_no, current_value, proposed_value, class_code, proposed_cauv FROM {TABLE} WHERE coalesce(current_value, 0) > 0 OR coalesce(proposed_value, 0) > 0 ORDER BY parcel_no""") out_rows = {} for pid, cv, pv, cls, cauv in cur.fetchall(): out_rows[pid] = [cv or 0, pv or 0, cls or ""] + ([cauv] if cauv else []) # Parcel polygons carrying the change, published by Martin as # /tiles/parcel_reval_geom — the map's "Increase" modes fill shapes from # this rather than drawing 100k dots. Separate transaction so the REFRESH's # lock doesn't hold tile queries for the whole run. with conn, conn.cursor() as cur: # parcel_reval_geom LEFT JOINs the value-history views, so make sure # they exist before building — otherwise a rebuild on a box where the # history was never crawled fails outright. Empty is fine here (the year # columns come out NULL and the map greys those parcels); # build_growth_6yr.py fills them. Shared DDL so the two scripts can't # build different versions of the same view. restart_martin = ensure_views(cur) cur.execute("REFRESH MATERIALIZED VIEW parcel_reval_geom") cur.execute("SELECT count(*) FROM parcel_reval_geom") n_geom = cur.fetchone()[0] conn.close() out = DATA_DIR / "wv_reval_2026.json" tmp = out.with_suffix(".json.tmp") tmp.write_text(json.dumps({ "format": 1, "source": LAYER, "fetched_at": dt.datetime.now(dt.timezone.utc).isoformat(timespec="seconds"), "cols": ["current", "proposed", "class_code", "cauv?"], "cls_med": cls_med, "rows": out_rows, }, separators=(",", ":"))) tmp.chmod(0o644) os.replace(tmp, out) print(f"[done] {n} rows · {matched} match parcels · {n_new} new (no current value) · " f"like-for-like ${cur_sum:,} → ${prop_sum:,} " f"(+{100 * (prop_sum - cur_sum) / cur_sum:.1f}%) · median parcel +{med:.1f}%") print(f"[json] {out.name}: {len(out_rows)} parcels, {out.stat().st_size / 1e6:.1f} MB") print(f"[tiles] parcel_reval_geom: {n_geom} polygons refreshed") if restart_martin: print("[martin] tile columns changed — run: systemctl restart martin") if __name__ == "__main__": main()