← back to Nationalrealestate
Add NYC Manhattan commercial (6131) via authoritative NYC DOF building-class letter map (O=office,K=retail,E/F=industrial,H=hotel) + reusable ingestCommercialFromParcelCoded for coded counties. Verified: office+retail heavy split matches real Manhattan. 6th commercial county
b53f231b50c41fb13305c33b187ef7193b0c3dea · 2026-07-31 00:04:28 -0700 · steve@designerwallcoverings.com
Files touched
M src/ingest/commercial/engine.tsM src/ingest/commercial/from_parcel.ts
Diff
commit b53f231b50c41fb13305c33b187ef7193b0c3dea
Author: steve@designerwallcoverings.com <steve@designerwallcoverings.com>
Date: Fri Jul 31 00:04:28 2026 -0700
Add NYC Manhattan commercial (6131) via authoritative NYC DOF building-class letter map (O=office,K=retail,E/F=industrial,H=hotel) + reusable ingestCommercialFromParcelCoded for coded counties. Verified: office+retail heavy split matches real Manhattan. 6th commercial county
---
src/ingest/commercial/engine.ts | 8 ++++++
src/ingest/commercial/from_parcel.ts | 52 +++++++++++++++++++++++++++++++++++-
2 files changed, 59 insertions(+), 1 deletion(-)
diff --git a/src/ingest/commercial/engine.ts b/src/ingest/commercial/engine.ts
index 1c9aa02..5b30057 100644
--- a/src/ingest/commercial/engine.ts
+++ b/src/ingest/commercial/engine.ts
@@ -14,6 +14,14 @@ const ADAPTERS: Record<string, () => Promise<{ run: () => Promise<{ upserted: nu
saltlake: async () => ({ run: (await import('./from_parcel.ts')).ingestCommercialFromParcel('49035', 'Salt Lake County UT') }),
sandiego: async () => ({ run: (await import('./from_parcel.ts')).ingestCommercialFromParcel('06073', 'San Diego County CA') }),
wake: async () => ({ run: (await import('./from_parcel.ts')).ingestCommercialFromParcel('37183', 'Wake County NC (Raleigh)') }),
+ // NYC (Manhattan) building-class letter is authoritative (NYC DOF): O=office, K=store/retail,
+ // E=warehouse+F=factory=industrial, H=hotel, G/J/L/P=other commercial; A-D/R/S/V=residential/vacant.
+ nyc: async () => {
+ const m = await import('./from_parcel.ts');
+ const MAP: Record<string, any> = { O: 'office', K: 'retail', E: 'industrial', F: 'industrial', H: 'hospitality', G: 'other', J: 'other', L: 'other', P: 'other' };
+ const classify = (u: string) => { const mm = u.match(/Class\s+([A-Za-z])/); return mm ? (MAP[mm[1].toUpperCase()] ?? null) : null; };
+ return { run: m.ingestCommercialFromParcelCoded('36061', 'New York County NY (Manhattan)', 'Class [EFGHJKLOP]', classify) };
+ },
// NOTE: Cook(17031)/NY(36061)/Bexar-TX(48029) use CLASS CODES ('Class 5-93','C7','F1')
// not descriptive text, so from_parcel can't classify them (verified: Cook came back
// 100% industrial — implausible). They need a per-county class-code map (future task).
diff --git a/src/ingest/commercial/from_parcel.ts b/src/ingest/commercial/from_parcel.ts
index 654282c..48ad8b0 100644
--- a/src/ingest/commercial/from_parcel.ts
+++ b/src/ingest/commercial/from_parcel.ts
@@ -10,7 +10,7 @@
*/
import { query } from '../../../db/pool.ts';
import { openRun, closeRun } from '../run.ts';
-import { classifyCommercialByDesc } from '../../lib/commercial_types.ts';
+import { classifyCommercialByDesc, type CommercialType } from '../../lib/commercial_types.ts';
const digits = (v: unknown): string | null => { const t = (v == null ? '' : String(v)).replace(/\D/g, ''); return t || null; };
@@ -60,3 +60,53 @@ export function ingestCommercialFromParcel(fips: string, label: string) {
}
};
}
+
+/**
+ * Coded-county variant: for counties whose parcel.use_desc is a CODE (e.g. NYC
+ * 'Class O6', building-class letter O = Office). Pass an authoritative `classify`
+ * (code string -> CommercialType|null) and a SQL `filter` regex that narrows to the
+ * commercial-relevant codes so we don't scan the whole county. No guessing — the
+ * classify fn must come from the county's published code table (Cook lesson).
+ */
+export function ingestCommercialFromParcelCoded(
+ fips: string, label: string, filter: string, classify: (useDesc: string) => CommercialType | null,
+) {
+ return async (): Promise<{ upserted: number }> => {
+ const runId = await openRun(`commercial_${fips}_coded`, 'parcel-table');
+ try {
+ const r = await query<any>(
+ `SELECT county_fips, source_id, address, city, zip, use_desc, total_value, sqft, year_built, units
+ FROM parcel WHERE county_fips = $1 AND use_desc ~* $2`, [fips, filter]);
+ const byAin = new Map<string, any>();
+ for (const p of r.rows) {
+ const ctype = classify(p.use_desc || '');
+ if (!ctype) continue;
+ const ain = digits(p.source_id); if (!ain) continue;
+ byAin.set(ain, {
+ county_fips: p.county_fips, ain, address: p.address, city: p.city, zip: p.zip,
+ ctype, use_desc: p.use_desc, use_class: null,
+ assessed_total: p.total_value, assessed_land: null, assessed_imp: null,
+ roll_year: null, recording_date: null, sqft: p.sqft, year_built: p.year_built, units: p.units,
+ });
+ }
+ const rows = [...byAin.values()];
+ let up = 0;
+ for (let i = 0; i < rows.length; i += 1000) {
+ const chunk = rows.slice(i, i + 1000);
+ const params: unknown[] = [];
+ const vals = chunk.map((row, j) => { const b = j * COLS.length; COLS.forEach(c => params.push(row[c] ?? null)); return '(' + COLS.map((_, k) => `$${b + k + 1}`).join(',') + ')'; });
+ await query(
+ `INSERT INTO commercial_parcel (${COLS.join(',')}) VALUES ${vals.join(',')}
+ ON CONFLICT (county_fips, ain) DO UPDATE SET ${COLS.filter(c => c !== 'county_fips' && c !== 'ain').map(c => `${c}=EXCLUDED.${c}`).join(', ')}, updated_at=NOW()`,
+ params);
+ up += chunk.length;
+ }
+ await closeRun(runId, 'ok', { upserted: up, notes: `${label}: ${up} commercial parcels (coded, ${r.rows.length} candidates)` });
+ console.log(`[commercial:${label}] done: ${up} commercial parcels from ${r.rows.length} coded candidates`);
+ return { upserted: up };
+ } catch (e: any) {
+ await closeRun(runId, 'failed', { notes: e.message });
+ throw e;
+ }
+ };
+}
← 275ff1e Add Wake County NC commercial (3764, Raleigh) via safe word-
·
back to Nationalrealestate
·
Extend NYC commercial to all 5 boroughs (41511 total: Manhat 92af9db →