[object Object]

← back to Nationalrealestate

Add classifyCommercialByDesc + from_parcel materializer: extract commercial_parcel from ALREADY-LOADED parcels (descriptive use_desc) — $0, no new source. King County Seattle: 18352 commercial parcels. Pattern extends to Cook/NY/Wake/etc

d55600a66084d76171701295ccf810d94a5ab1e7 · 2026-07-30 21:10:52 -0700 · steve@designerwallcoverings.com

Files touched

Diff

commit d55600a66084d76171701295ccf810d94a5ab1e7
Author: steve@designerwallcoverings.com <steve@designerwallcoverings.com>
Date:   Thu Jul 30 21:10:52 2026 -0700

    Add classifyCommercialByDesc + from_parcel materializer: extract commercial_parcel from ALREADY-LOADED parcels (descriptive use_desc) — $0, no new source. King County Seattle: 18352 commercial parcels. Pattern extends to Cook/NY/Wake/etc
---
 src/ingest/commercial/engine.ts      |  2 ++
 src/ingest/commercial/from_parcel.ts | 62 ++++++++++++++++++++++++++++++++++++
 src/lib/commercial_types.ts          | 14 ++++++++
 3 files changed, 78 insertions(+)

diff --git a/src/ingest/commercial/engine.ts b/src/ingest/commercial/engine.ts
index 216ae47..835af0e 100644
--- a/src/ingest/commercial/engine.ts
+++ b/src/ingest/commercial/engine.ts
@@ -9,6 +9,8 @@ const ADAPTERS: Record<string, () => Promise<{ run: () => Promise<{ upserted: nu
   la: async () => ({ run: (await import('./la_assessor.ts')).ingestLaCommercial }),
   // Live ArcGIS commercial ETL (West-Coast-first; successor to the LA sqlite path).
   'la-arcgis': async () => ({ run: (await import('./arcgis_commercial.ts')).ingestArcgisCommercial('la') }),
+  // Materialize commercial from already-loaded parcels (use_desc is descriptive) — $0.
+  king: async () => ({ run: (await import('./from_parcel.ts')).ingestCommercialFromParcel('53033', 'King County WA (Seattle)') }),
 };
 
 async function main() {
diff --git a/src/ingest/commercial/from_parcel.ts b/src/ingest/commercial/from_parcel.ts
new file mode 100644
index 0000000..efd5338
--- /dev/null
+++ b/src/ingest/commercial/from_parcel.ts
@@ -0,0 +1,62 @@
+/**
+ * Materialize commercial_parcel from the EXISTING parcel table — $0, NO new source.
+ * For counties whose parcel.use_desc is already a plain description (King County WA:
+ * 'Office Building', 'Warehouse', 'Retail Store', …). A SQL pre-filter narrows to
+ * commercial-keyword candidates (so we don't load the whole county), then
+ * classifyCommercialByDesc types each precisely; non-commercial rows are skipped.
+ * APN normalized to digits. Idempotent upsert on (county_fips, ain).
+ *
+ *   npm run ingest:commercial -- king   # King County WA (53033) from loaded parcels
+ */
+import { query } from '../../../db/pool.ts';
+import { openRun, closeRun } from '../run.ts';
+import { classifyCommercialByDesc } from '../../lib/commercial_types.ts';
+
+const digits = (v: unknown): string | null => { const t = (v == null ? '' : String(v)).replace(/\D/g, ''); return t || null; };
+
+// Broad SQL pre-filter covering the classifier's keywords — precise typing happens in JS.
+const KEYWORDS = 'office|professional|bank|savings|warehous|distribution|storage|manufactur|industrial|store|shop|supermarket|department|restaurant|cocktail|hotel|motel|hospitality|lodging|parking|studio|motion picture|television|radio|retail|wholesale|outlet|nursery|greenhouse|kennel|laundry|repair|service station|auto|recreation';
+
+const COLS = ['county_fips', 'ain', 'address', 'city', 'zip', 'ctype', 'use_desc', 'use_class',
+  'assessed_total', 'assessed_land', 'assessed_imp', 'roll_year', 'recording_date', 'sqft', 'year_built', 'units'];
+
+export function ingestCommercialFromParcel(fips: string, label: string) {
+  return async (): Promise<{ upserted: number }> => {
+    const runId = await openRun(`commercial_${fips}_fromparcel`, '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, KEYWORDS]);
+      const byAin = new Map<string, any>();
+      for (const p of r.rows) {
+        const ctype = classifyCommercialByDesc(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 materialized from parcel table (${r.rows.length} candidates scanned)` });
+      console.log(`[commercial:${label}] done: ${up} commercial parcels from ${r.rows.length} candidates`);
+      return { upserted: up };
+    } catch (e: any) {
+      await closeRun(runId, 'failed', { notes: e.message });
+      throw e;
+    }
+  };
+}
diff --git a/src/lib/commercial_types.ts b/src/lib/commercial_types.ts
index f2246e5..8ed2f19 100644
--- a/src/lib/commercial_types.ts
+++ b/src/lib/commercial_types.ts
@@ -50,6 +50,20 @@ export function classifyCommercial(useDesc1: string | null, useDesc2: string | n
   return d1 === 'Industrial' ? 'industrial' : 'other';
 }
 
+/**
+ * Classify from a SINGLE free-text use description, WITHOUT the LA 'Commercial'/
+ * 'Industrial' UseType gate. For county parcel layers whose use_desc is already a
+ * plain description (e.g. King County WA: 'Office Building', 'Warehouse', 'Retail
+ * Store'). Returns null for anything not matching a commercial/industrial rule
+ * (single-family, apartment, vacant, church, etc. stay residential/non-commercial).
+ */
+export function classifyCommercialByDesc(desc: string | null): CommercialType | null {
+  const d = (desc || '').trim();
+  if (!d) return null;
+  for (const [re, type] of RULES) if (re.test(d)) return type;
+  return null;
+}
+
 export function categoryLabel(type: CommercialType): string {
   return COMMERCIAL_CATEGORIES.find(c => c.key === type)?.label ?? 'Other Commercial';
 }

← 1c2a95f Add Santa Clara County CA parcel source (493k, San Jose) — 2  ·  back to Nationalrealestate  ·  Commercial from_parcel: add bare-'Commercial' fallback class b3ca8a1 →