← back to Nationalrealestate
parcels: add Franklin County OH (39049) via .gdb + GDAL — 122k parcels w/ owner/address/sqft/sale
4c2251e9ecc4861c3e5414c0741ca1554a501b96 · 2026-07-26 08:48:46 -0700 · Steve Abrams
DTD verdict B: rich CAMA lives only in the auditor's 603MB monthly FileGeoDatabase (GDAL-only); polygon shapefile is geometry-only, www data pages WAF-walled. Adapter extracts the TaxParcelSale point layer (dedupe to latest sale/parcel) -> 122,047 parcels: site address (98%), owner=latest grantee (100%), use class, res sqft (85%), last sale date+price (66% arms-length), lat/lng (100% in-county). Honest sale-window label 2020-01..2023-05 in parcel_source. Documented GDAL exception + deploy path in FRANKLIN-RUNBOOK.md.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Files touched
A FRANKLIN-RUNBOOK.mdM PARCEL-COVERAGE.mdM src/ingest/parcels/engine.tsA src/ingest/parcels/franklin_oh.ts
Diff
commit 4c2251e9ecc4861c3e5414c0741ca1554a501b96
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Sun Jul 26 08:48:46 2026 -0700
parcels: add Franklin County OH (39049) via .gdb + GDAL — 122k parcels w/ owner/address/sqft/sale
DTD verdict B: rich CAMA lives only in the auditor's 603MB monthly FileGeoDatabase (GDAL-only); polygon shapefile is geometry-only, www data pages WAF-walled. Adapter extracts the TaxParcelSale point layer (dedupe to latest sale/parcel) -> 122,047 parcels: site address (98%), owner=latest grantee (100%), use class, res sqft (85%), last sale date+price (66% arms-length), lat/lng (100% in-county). Honest sale-window label 2020-01..2023-05 in parcel_source. Documented GDAL exception + deploy path in FRANKLIN-RUNBOOK.md.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
---
FRANKLIN-RUNBOOK.md | 57 +++++++++++++
PARCEL-COVERAGE.md | 3 +-
src/ingest/parcels/engine.ts | 1 +
src/ingest/parcels/franklin_oh.ts | 173 ++++++++++++++++++++++++++++++++++++++
4 files changed, 233 insertions(+), 1 deletion(-)
diff --git a/FRANKLIN-RUNBOOK.md b/FRANKLIN-RUNBOOK.md
new file mode 100644
index 0000000..dbfef7f
--- /dev/null
+++ b/FRANKLIN-RUNBOOK.md
@@ -0,0 +1,57 @@
+# Franklin County OH (39049) — parcel ingest & deploy runbook
+
+Franklin is the ONE documented exception to the "re-run the adapter on the remote"
+model. Its rich CAMA attributes live only inside the auditor's 603 MB monthly
+**FileGeoDatabase (.gdb)**, which requires **GDAL/ogr2ogr** to read. GDAL is
+installed on the local Mac (`/opt/homebrew/bin/ogr2ogr`, 3.13) but **NOT** on the
+Kamatera prod box. So Franklin is parsed locally and its rows are pushed to prod.
+(DTD verdict B, 2026-07-26; the C-dissent's guardrail = this exception must be
+documented + reproducible, not a snowflake.)
+
+## Why not the other sources
+- The parcel POLYGON shapefile at `apps.franklincountyauditor.com/GIS_Shapefiles/YYYY/MM/*_Parcel_Polygons.zip`
+ is **geometry-only** (`.dbf` = PARCELID, Shape_Length, Shape_Area). No attributes.
+- The auditor's `www.franklincountyauditor.com` data pages are **WAF-walled** (403 to curl).
+- The `.gdb`'s parcel polygon layer is *also* attribute-stripped; the rich data is in
+ the **`TaxParcelSale`** point layer (159,988 sale rows / 122,047 distinct parcels).
+
+## What we ingest
+`TaxParcelSale`, deduped to the **latest sale per parcel** → 122,047 parcels with:
+site address, owner (latest grantee), use class, res sqft, last sale date+price,
+lat/lng (point reprojected to WGS84). **Sale window is a fixed 2020-01..2023-05**
+range in the open layer — owner is "latest sale in window", not current-day; this
+is recorded in the `parcel_source` note. No assessed value / year-built / beds-baths
+in this layer. The ~341k geometry-only parcels are a documented full-coverage TODO.
+
+## Refresh (run on a GDAL machine — local Mac)
+```sh
+cd ~/Projects/nationalrealestate
+# option A: let the adapter download the latest monthly .gdb (~603 MB)
+npm run ingest:parcels franklin
+# option B: reuse an already-extracted .gdb (skips the 603 MB download)
+FRANKLIN_GDB=/path/to/FCA_SDE_Web_Prod_MonthEnd.gdb npm run ingest:parcels franklin
+```
+The adapter fails loud if `ogr2ogr` is missing, and closes the `ingest_runs` row
+`failed` on any error. Idempotent (upsert on `(county_fips, source_id)`).
+
+## Deploy to prod (rows, not code re-run)
+Prod has no GDAL, so push the parsed rows straight to remote Postgres:
+```sh
+# 1. dump just the Franklin rows from local
+pg_dump -h /tmp -d usre --data-only --table=parcel \
+ --where="county_fips='39049'" 2>/dev/null > /tmp/franklin_parcels.sql # (see note)
+# pg_dump has no --where; use a COPY instead:
+psql -h /tmp -d usre -c "\copy (SELECT * FROM parcel WHERE county_fips='39049') TO '/tmp/franklin.csv' CSV"
+# 2. ship + load on remote (delete-then-load for a clean rebuild of this county)
+scp /tmp/franklin.csv root@45.61.58.125:/tmp/
+ssh root@45.61.58.125 "cd /root/public-projects/nationalrealestate
+ DB=\$(grep -E '^DATABASE_URL=' .env | cut -d= -f2- | tr -d '\"')
+ psql \"\$DB\" -c \"DELETE FROM parcel WHERE county_fips='39049'\"
+ psql \"\$DB\" -c \"\\copy parcel FROM '/tmp/franklin.csv' CSV\"
+ # re-register the parcel_source row (data rows don't carry the registry)
+ npx tsx -e \"import('./src/ingest/parcels/upsert.ts').then(m=>m.registerParcelSource('39049','franklin .gdb','...'))\" "
+```
+Also rsync the code (`src/ingest/parcels/franklin_oh.ts`, `engine.ts`) so the adapter
+exists on prod for reference, even though it can't run there without GDAL.
+
+**Cadence:** monthly, aligned to the auditor's month-end .gdb publish. Cost $0 (public records).
diff --git a/PARCEL-COVERAGE.md b/PARCEL-COVERAGE.md
index ea68da9..726506e 100644
--- a/PARCEL-COVERAGE.md
+++ b/PARCEL-COVERAGE.md
@@ -14,6 +14,7 @@ migration 004, adapters in `src/ingest/parcels/`).
| Los Angeles, CA (06037) | 2,427,010 | CRCP SQLite export of the 2025 secured roll (read in place) | via CRCP | Details (beds/baths/sqft/yr), Tax (1 roll yr), recording date; **no owner / zoning / sale price** (see LA probe below) |
| NYC — Manhattan 36061 (42,544), Bronx 36005 (89,496), Brooklyn 36047 (276,311), Queens 36081 (324,559), Staten Island 36085 (125,692) = **858,602 lots** | | MapPLUTO via NYC Open Data Socrata `64uk-42ks` (JSON paging; `ingest:parcels nyc`) | re-run adapter (PLUTO versioned, currently 26v1) | Details (lot-level: sqft/yr/units — no beds/baths), Tax (assessed land/imp/total), **Ownership (owner of record)**, **Zoning (zonedist1)**, Map (parcel lat/lng) |
| King, WA — Seattle (53033) | 628,417 | Assessor EXTR bulk zips, `aqua.kingcounty.gov/extranet/assessor` (Parcel / ResBldg / RPAcct / RPSale / Lookup; `ingest:parcels king`) | re-run adapter (extracts refresh weekly) | Details (beds/baths/sqft/yr — 525k w/ address), Tax (2026 appraised land+imp), **Property History: REAL recorded deed sales w/ price+buyer+seller (536k parcels)**, Zoning (627k), Owner = latest deed buyer (names file is `_NoName` by policy) |
+| Franklin, OH — Columbus (39049) | 122,047 | Auditor monthly FileGeoDatabase, `TaxParcelSale` layer (GDAL-extracted; `ingest:parcels franklin`) — see FRANKLIN-RUNBOOK.md | re-run adapter on a GDAL machine, push rows to prod | Details (site address, res sqft, use class), **Ownership (latest grantee)**, **Property History: real sale date+price**, Map (point lat/lng). **Sale window fixed 2020-01..2023-05** (owner = latest-in-window, not current-day); no assessed value/year-built/beds-baths; ~341k geometry-only parcels are a full-coverage TODO |
| Cook, IL — Chicago (17031) | 1,863,530 | Assessor Socrata: universe `pabr-t5kh` (2026, lat/lng+class) + addresses/owner `3723-97qp` (2026) + certified ASSESSED values `uzyt-m557` (2025) + res characteristics `x54s-btds` (2026) (`ingest:parcels cook`) | re-run adapter (annual cycle) | Details (beds/baths/sqft/yr for 1.1M res pins), Tax (2025 certified **assessed** value — 10%/25% of market, labeled), **Ownership (owner/taxpayer name + mailing)**, Map (parcel lat/lng); no zoning (municipal), no sale feed |
## Probed — gated or absent
@@ -38,7 +39,7 @@ migration 004, adapters in `src/ingest/parcels/`).
| County | Verdict | Detail |
|---|---|---|
-| Franklin, OH | **OPEN but shapefile-only** | `GIS_Shapefiles/` is live (200) but organized `YYYY/MM/` deep, and the leaf assets are **zipped ESRI shapefiles** (shp+dbf), not flat CSV/Socrata. Ingesting needs a streaming `shapefile`/`.dbf` reader dep (NOT currently in package.json) — the auditor's parcel *attributes* live in the `.dbf`; geometry can be dropped. Tractable but a multi-step adapter build, unlike the Socrata one-shot counties (cook). **Next parcel county to land — start here.** |
+| Franklin, OH | **DONE 2026-07-26 (via .gdb + GDAL)** | The polygon shapefile turned out geometry-only (PARCELID + Shape). The rich CAMA data is inside the 603MB monthly **FileGeoDatabase**; parsed with GDAL/ogr2ogr (local-only dep) → 122,047 parcels from the `TaxParcelSale` layer. Landed via `ingest:parcels franklin`. See the covered-counties table above + FRANKLIN-RUNBOOK.md. |
| Wake, NC | **URL drift** | The documented data-files page 404s now (site reorg). Wake still publishes open RE data files; entry point needs re-discovery before an adapter. |
| Miami-Dade, FL | **off-Socrata** | `opendata.miamidade.gov` no longer serves the standard Socrata `/api/catalog/v1` or `/resource/` endpoints (302/404) — migrated like several state portals. Property-appraiser **ArcGIS** open-data is the remaining open path; re-probe that surface, not Socrata. |
diff --git a/src/ingest/parcels/engine.ts b/src/ingest/parcels/engine.ts
index 2e89c4f..7fa2c96 100644
--- a/src/ingest/parcels/engine.ts
+++ b/src/ingest/parcels/engine.ts
@@ -11,6 +11,7 @@ const ADAPTERS: Record<string, () => Promise<{ run: () => Promise<{ upserted: nu
'nyc-acris': async () => ({ run: (await import('./nyc_acris.ts')).ingestNycAcris }),
king: async () => ({ run: (await import('./king_wa.ts')).ingestKing }),
cook: async () => ({ run: (await import('./cook_il.ts')).ingestCook }),
+ franklin: async () => ({ run: (await import('./franklin_oh.ts')).ingestFranklin }),
sandiego: async () => { const m = await import('./sandiego_ca.ts'); return { run: () => m.ingestSanDiego('poway') }; },
'sandiego-county': async () => { const m = await import('./sandiego_ca.ts'); return { run: () => m.ingestSanDiego('county') }; },
'oregon-rlis': async () => { const m = await import('./arcgis_sales.ts'); return { run: m.ingestArcgisSales('oregon-rlis') }; },
diff --git a/src/ingest/parcels/franklin_oh.ts b/src/ingest/parcels/franklin_oh.ts
new file mode 100644
index 0000000..3a688c2
--- /dev/null
+++ b/src/ingest/parcels/franklin_oh.ts
@@ -0,0 +1,173 @@
+/**
+ * Franklin County OH (Columbus, FIPS 39049) parcel ingest.
+ *
+ * ── DTD verdict B (2026-07-26) + its dissent's guardrails ──────────────────
+ * Franklin's ONLY open bulk surface is the auditor's GIS directory
+ * (apps.franklincountyauditor.com/GIS_Shapefiles/YYYY/MM/) — the www data pages
+ * are WAF-walled. The parcel POLYGON shapefile there is geometry-only (PARCELID +
+ * Shape only). The rich CAMA attributes live ONLY inside the 603MB monthly
+ * FileGeoDatabase (.gdb), which requires GDAL/ogr2ogr to read.
+ *
+ * This adapter is therefore the ONE documented exception to the "re-run on the
+ * remote" model: it depends on GDAL (ogr2ogr), which is installed locally but
+ * NOT on the Kamatera prod box. Run it on a GDAL-having machine; deploy the rows
+ * to prod via pg_dump-subset -> restore (see FRANKLIN-RUNBOOK.md). Everything
+ * downstream of the GDAL extract (dedupe, upsert, parcel_source registry) uses
+ * the identical shared contract as every other county, so the "snowflake" is
+ * confined to the single extraction step.
+ *
+ * Attribute source = the TaxParcelSale POINT layer (159,988 sale rows across
+ * 122,047 distinct parcels — one row per recorded sale). We keep the LATEST sale
+ * per parcel (current owner = its grantee) and lead with these 122k RICH parcels
+ * (address / owner / use-class / res sqft / last sale + lat-lng) rather than
+ * diluting the county with the ~341k geometry-only parcels (that full-coverage
+ * pass is a documented follow-up). No assessed value / year-built / beds-baths in
+ * this layer — recorded null, noted in extra.
+ *
+ * Run: FRANKLIN_GDB=/path/to/xxx.gdb npm run ingest:parcels franklin
+ * (if FRANKLIN_GDB unset, downloads the latest monthly .gdb zip — 603MB.)
+ */
+import { execFile } from 'node:child_process';
+import { promisify } from 'node:util';
+import { createReadStream } from 'node:fs';
+import { mkdtemp, rm } from 'node:fs/promises';
+import { createInterface } from 'node:readline';
+import { tmpdir } from 'node:os';
+import { join } from 'node:path';
+import { pool } from '../../../db/pool.ts';
+import { openRun, closeRun } from '../run.ts';
+import { upsertParcels, registerParcelSource, normAddress, type ParcelUpsertRow } from './upsert.ts';
+
+const execFileP = promisify(execFile);
+const FIPS = '39049';
+const GIS_BASE = 'https://apps.franklincountyauditor.com/GIS_Shapefiles';
+const LAYER = 'TaxParcelSale';
+
+const num = (v: unknown) => { const n = Number(v); return Number.isFinite(n) && n !== 0 ? n : null; };
+/** "2021/12/02 00:00:00+00" -> "2021-12-02"; anything unparseable -> null */
+const toIsoDate = (v: unknown): string | null => {
+ const m = String(v ?? '').match(/(\d{4})[/-](\d{2})[/-](\d{2})/);
+ return m ? `${m[1]}-${m[2]}-${m[3]}` : null;
+};
+
+async function ogr2ogrExists(): Promise<boolean> {
+ try { await execFileP('ogr2ogr', ['--version']); return true; } catch { return false; }
+}
+
+/** Find the newest YYYY/MM/*_FileGeoDataBase.zip in the auditor GIS directory. */
+async function latestGdbUrl(): Promise<string> {
+ const links = async (path: string): Promise<string[]> => {
+ const html = await (await fetch(`${GIS_BASE}/${path}`)).text();
+ return [...html.matchAll(/href="([^"]+)"/gi)].map(m => m[1]);
+ };
+ const years = (await links('')).map(h => h.match(/\/(\d{4})\/$/)?.[1]).filter(Boolean).sort().reverse();
+ for (const y of years) {
+ const months = (await links(`${y}/`)).map(h => h.match(new RegExp(`/${y}/(\\d{2})/$`))?.[1]).filter(Boolean).sort().reverse();
+ for (const mo of months) {
+ const zip = (await links(`${y}/${mo}/`)).find(h => /FileGeoDataBase\.zip$/i.test(h));
+ if (zip) return zip.startsWith('http') ? zip : `https://apps.franklincountyauditor.com${zip}`;
+ }
+ }
+ throw new Error('no FileGeoDataBase.zip found in Franklin GIS directory');
+}
+
+/** Resolve a local .gdb dir: env override, else download+unzip the latest monthly zip. */
+async function resolveGdb(work: string): Promise<{ gdb: string; provenance: string }> {
+ if (process.env.FRANKLIN_GDB) return { gdb: process.env.FRANKLIN_GDB, provenance: `local .gdb (${process.env.FRANKLIN_GDB})` };
+ const url = await latestGdbUrl();
+ console.log(`[franklin] downloading ${url} (~603MB)…`);
+ const zip = join(work, 'fcgdb.zip');
+ await execFileP('curl', ['-s', '-m', '900', '-o', zip, url]);
+ await execFileP('unzip', ['-o', '-q', zip, '-d', join(work, 'gdb')]);
+ const { stdout } = await execFileP('bash', ['-lc', `find ${join(work, 'gdb')} -maxdepth 4 -name '*.gdb' -type d | head -1`]);
+ const gdb = stdout.trim();
+ if (!gdb) throw new Error('unzip produced no .gdb');
+ return { gdb, provenance: url };
+}
+
+export async function ingestFranklin(): Promise<{ upserted: number }> {
+ if (!(await ogr2ogrExists())) {
+ throw new Error('ogr2ogr (GDAL) not found — Franklin needs GDAL to read the .gdb. Run on a GDAL machine; see FRANKLIN-RUNBOOK.md.');
+ }
+ const work = await mkdtemp(join(tmpdir(), 'franklin-'));
+ const runId = await openRun('parcel_franklin_oh', `${GIS_BASE}/ (TaxParcelSale)`);
+ try {
+ const { gdb, provenance } = await resolveGdb(work);
+ // GDAL extract: TaxParcelSale -> WGS84 NDJSON (point geom carries lat/lng)
+ const ndjson = join(work, 'sales.ndjson');
+ console.log(`[franklin] ogr2ogr extracting ${LAYER} -> WGS84 NDJSON`);
+ await execFileP('ogr2ogr', ['-f', 'GeoJSONSeq', ndjson, gdb, LAYER, '-t_srs', 'EPSG:4326',
+ '-select', 'PARCELID,SITEADDRESS,ZIPCD,CLASSDSCRP,GranteeName1,GrantorName1,SALEDATE,SalePrice,SaleType,RESFLRAREA_AG,RESFLRAREA_BG',
+ ], { maxBuffer: 1 << 30 });
+
+ // Stream NDJSON, keep the LATEST sale per parcel (current owner = latest grantee)
+ const best = new Map<string, ParcelUpsertRow>();
+ const bestDate = new Map<string, string>();
+ let seen = 0, minSale = '9999', maxSale = '0000';
+ const rl = createInterface({ input: createReadStream(ndjson), crlfDelay: Infinity });
+ for await (const line of rl) {
+ if (!line.trim()) continue;
+ let f: any; try { f = JSON.parse(line); } catch { continue; }
+ const p = f.properties || {};
+ const pid = (p.PARCELID || '').trim();
+ if (!pid) continue;
+ seen++;
+ const saleDate = toIsoDate(p.SALEDATE);
+ if (saleDate) { if (saleDate < minSale) minSale = saleDate; if (saleDate > maxSale) maxSale = saleDate; }
+ const prevDate = bestDate.get(pid);
+ // keep the row with the newest sale date (undated rows never beat a dated one)
+ if (prevDate !== undefined && (saleDate ?? '') <= prevDate) continue;
+ const coords = f.geometry?.coordinates;
+ const lng = Array.isArray(coords) ? num(coords[0]) : null;
+ const lat = Array.isArray(coords) ? num(coords[1]) : null;
+ const addr = (p.SITEADDRESS || '').trim();
+ const norm = addr ? normAddress(addr) : null;
+ const sqft = num(p.RESFLRAREA_AG);
+ best.set(pid, {
+ county_fips: FIPS, source_id: pid,
+ address: norm, norm_address: norm,
+ city: null, zip: (p.ZIPCD || '').trim() || null,
+ lat, lng,
+ year_built: null, sqft, beds: null, baths: null, units: null,
+ use_desc: (p.CLASSDSCRP || '').trim() || null,
+ land_value: null, improvement_value: null, total_value: null, tax_year: null,
+ owner_name: (p.GranteeName1 || '').trim() || null,
+ zoning: null,
+ last_sale_date: saleDate, last_sale_price: num(p.SalePrice),
+ extra: JSON.stringify({
+ class_desc: (p.CLASSDSCRP || '').trim() || undefined,
+ res_sqft_below_grade: num(p.RESFLRAREA_BG) || undefined,
+ seller: (p.GrantorName1 || '').trim() || undefined,
+ sale_type: (p.SaleType || '').trim() || undefined,
+ owner_source: p.GranteeName1 ? 'grantee of latest recorded sale (see sale_window in coverage), Franklin County Auditor TaxParcelSale' : undefined,
+ note: 'owner/sale from the open TaxParcelSale window (see coverage notes); no assessed value / year-built / beds-baths in this layer',
+ }),
+ });
+ bestDate.set(pid, saleDate ?? '');
+ }
+ rl.close();
+
+ const rows = [...best.values()];
+ if (rows.length < 90000) throw new Error(`only ${rows.length} Franklin parcels parsed (expected ~122k) — layer/schema drift?`);
+ let upserted = 0;
+ for (let i = 0; i < rows.length; i += 5000) {
+ upserted += await upsertParcels(rows.slice(i, i + 5000));
+ console.log(`[franklin] ${upserted}/${rows.length} upserted`);
+ }
+
+ const n = await registerParcelSource(FIPS, provenance,
+ `Franklin County OH Auditor FileGeoDatabase (TaxParcelSale layer, GDAL-extracted) — ${rows.length} parcels with latest-sale attributes: site address, owner (latest grantee), use class, res sqft, last sale date+price, lat/lng from ${seen} sale rows. SALE WINDOW ${minSale}..${maxSale}: owner reflects the latest sale in that window (the open layer's fixed range), NOT current-day ownership. No assessed value/year-built/beds-baths in this open layer; ~341k geometry-only parcels not yet ingested (full-coverage follow-up).`);
+ await closeRun(runId, 'ok', { upserted, notes: `Franklin OH: ${upserted} parcels from ${seen} sale rows` });
+ console.log(`[franklin] ok: ${upserted} parcels (registry ${n})`);
+ return { upserted };
+ } catch (e: any) {
+ await closeRun(runId, 'failed', { notes: String(e.message || e).slice(0, 500) });
+ throw e;
+ } finally {
+ await rm(work, { recursive: true, force: true }).catch(() => {});
+ }
+}
+
+if (import.meta.url === `file://${process.argv[1]}`) {
+ ingestFranklin().then(() => pool.end()).catch(e => { console.error(e); process.exit(1); });
+}
← 32c8646 Deschutes pageSize=1000 (server page cap) so pagination cont
·
back to Nationalrealestate
·
franklin: contrarian gate fixes — full-timestamp dedupe + bu aa4b31c →