← back to Nationalrealestate
Add live ArcGIS commercial ETL (arcgis_commercial la-arcgis) — West-Coast successor to stale LA sqlite; UseType-gated, classifyCommercial-typed, cursor-resumable, reusable per county. LA 152910 commercial parcels
0b1366053bb2dbff69fc3c9500cf78dda5ddd8ce · 2026-07-30 19:57:40 -0700 · steve@designerwallcoverings.com
Files touched
A src/ingest/commercial/arcgis_commercial.tsM src/ingest/commercial/engine.ts
Diff
commit 0b1366053bb2dbff69fc3c9500cf78dda5ddd8ce
Author: steve@designerwallcoverings.com <steve@designerwallcoverings.com>
Date: Thu Jul 30 19:57:40 2026 -0700
Add live ArcGIS commercial ETL (arcgis_commercial la-arcgis) — West-Coast successor to stale LA sqlite; UseType-gated, classifyCommercial-typed, cursor-resumable, reusable per county. LA 152910 commercial parcels
---
src/ingest/commercial/arcgis_commercial.ts | 100 +++++++++++++++++++++++++++++
src/ingest/commercial/engine.ts | 2 +
2 files changed, 102 insertions(+)
diff --git a/src/ingest/commercial/arcgis_commercial.ts b/src/ingest/commercial/arcgis_commercial.ts
new file mode 100644
index 0000000..619b025
--- /dev/null
+++ b/src/ingest/commercial/arcgis_commercial.ts
@@ -0,0 +1,100 @@
+/**
+ * Live commercial-parcel ETL from a county's public ArcGIS assessor layer.
+ * The West-Coast-first successor to the LA-sqlite ETL (la_assessor.ts, stale 2024):
+ * pages the layer WHERE UseType IN ('Commercial','Industrial'), classifies each via the
+ * shared classifyCommercial taxonomy, and materializes into `commercial_parcel`.
+ * Reusable across WC metros — add a config entry per county.
+ *
+ * Cursor-resumable + MAX_PER_RUN cap (like arcgis_sales) so the hourly loop paginates
+ * politely. $0 (free county open data).
+ *
+ * npm run ingest:commercial-arcgis -- la
+ * SALES_MAX_PER_RUN=200000 npm run ingest:commercial-arcgis -- la # bulk backfill
+ */
+import { query } from '../../../db/pool.ts';
+import { openRun, closeRun } from '../run.ts';
+import { classifyCommercial } from '../../lib/commercial_types.ts';
+
+const MAX_PER_RUN = Number(process.env.SALES_MAX_PER_RUN || 6000);
+const PAGE = 1000;
+
+const s = (v: unknown): string | null => { const t = v == null ? '' : String(v).trim(); return t ? t : null; };
+const num = (v: unknown): number | null => { const x = Number(v); return Number.isFinite(x) && x !== 0 ? x : null; };
+
+interface CommercialConfig {
+ key: string; label: string; fips: string; layer: string; maxRecordCount: number;
+}
+
+const CONFIGS: Record<string, CommercialConfig> = {
+ la: {
+ key: 'la', label: 'Los Angeles County CA (live ArcGIS)', fips: '06037',
+ layer: 'https://public.gis.lacounty.gov/public/rest/services/LACounty_Cache/LACounty_Parcel/MapServer/0',
+ maxRecordCount: 1000,
+ },
+};
+
+const OUT = 'APN,SitusFullAddress,SitusCity,SitusZIP,UseType,UseDescription,YearBuilt1,SQFTmain1,Units1,Roll_LandValue,Roll_ImpValue';
+
+export function ingestArcgisCommercial(key: string) {
+ const cfg = CONFIGS[key];
+ if (!cfg) throw new Error(`unknown commercial arcgis source: ${key}`);
+ return async (): Promise<{ upserted: number }> => {
+ const runId = await openRun(`commercial_${cfg.key}_arcgis`, cfg.layer);
+ try {
+ const cur = await query<{ next_offset: number }>(`SELECT next_offset FROM ingest_cursor WHERE source=$1`, [`commercial_${cfg.key}`]);
+ let offset = cur.rows[0]?.next_offset ?? 0;
+ const where = encodeURIComponent("UseType IN ('Commercial','Industrial')");
+ const totalR = await fetch(`${cfg.layer}/query?where=${where}&returnCountOnly=true&f=json`).then(r => r.json() as any);
+ const total = totalR.count ?? 0;
+
+ const rows: any[] = [];
+ let fetched = 0;
+ while (fetched < MAX_PER_RUN) {
+ const url = `${cfg.layer}/query?where=${where}&outFields=${OUT}&orderByFields=OBJECTID&resultOffset=${offset}&resultRecordCount=${Math.min(PAGE, cfg.maxRecordCount)}&f=json`;
+ const j = await fetch(url).then(r => r.json() as any);
+ const feats = j.features || [];
+ if (!feats.length) { offset = 0; break; } // wrapped past the end → reset cursor
+ for (const f of feats) {
+ const a = f.attributes; const ain = s(a.APN); if (!ain) continue;
+ const ctype = classifyCommercial(s(a.UseType), s(a.UseDescription));
+ if (!ctype) continue; // not commercial after classification
+ const land = num(a.Roll_LandValue), imp = num(a.Roll_ImpValue);
+ rows.push({
+ county_fips: cfg.fips, ain, address: s(a.SitusFullAddress), city: s(a.SitusCity), zip: s(a.SitusZIP),
+ ctype, use_desc: s(a.UseDescription), use_class: s(a.UseType),
+ assessed_total: (land || imp) ? (land || 0) + (imp || 0) : null, assessed_land: land, assessed_imp: imp,
+ roll_year: null, recording_date: null, sqft: num(a.SQFTmain1), year_built: num(a.YearBuilt1), units: num(a.Units1),
+ });
+ }
+ fetched += feats.length; offset += feats.length;
+ if (feats.length < Math.min(PAGE, cfg.maxRecordCount)) { offset = 0; break; }
+ }
+
+ // Dedup within batch by ain (same guard as the residential upsert).
+ const byAin = new Map<string, any>();
+ for (const r of rows) byAin.set(r.ain, r);
+ const dedup = [...byAin.values()];
+ 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'];
+ let up = 0;
+ for (let i = 0; i < dedup.length; i += 1000) {
+ const chunk = dedup.slice(i, i + 1000);
+ const params: unknown[] = [];
+ const vals = chunk.map((r, j) => { const b = j * COLS.length; COLS.forEach(c => params.push(r[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 query(`INSERT INTO ingest_cursor (source, next_offset, total) VALUES ($1,$2,$3)
+ ON CONFLICT (source) DO UPDATE SET next_offset=$2, total=$3, updated_at=NOW()`, [`commercial_${cfg.key}`, offset, total]);
+ await closeRun(runId, 'ok', { upserted: up, notes: `${cfg.label}: ${up} commercial parcels (offset ${offset}/${total})` });
+ console.log(`[commercial:${cfg.key}] done: ${up} commercial parcels (offset ${offset}/${total})`);
+ return { upserted: up };
+ } catch (e: any) {
+ await closeRun(runId, 'failed', { notes: e.message });
+ throw e;
+ }
+ };
+}
diff --git a/src/ingest/commercial/engine.ts b/src/ingest/commercial/engine.ts
index c6039eb..216ae47 100644
--- a/src/ingest/commercial/engine.ts
+++ b/src/ingest/commercial/engine.ts
@@ -7,6 +7,8 @@ import { pool } from '../../../db/pool.ts';
const ADAPTERS: Record<string, () => Promise<{ run: () => Promise<{ upserted: number }> }>> = {
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') }),
};
async function main() {
← a77a687 auto-save: 2026-07-30T19:50:45 (2 files) — src/ingest/parcel
·
back to Nationalrealestate
·
Fix commercial ETL APN normalization: strip non-digits so Ar ba1528f →