← back to Commercialrealestate
Assessor parcel roll: streaming Socrata/eGIS fetcher + cre.assessor_parcel schema + NDJSON ingest (2025 roll, all parcels, disk-aware capped to current snapshot)
aafcd09bf88f5023c12d64ec0ac269d2a4ca79fb · 2026-06-30 13:24:01 -0700 · Steve Abrams
Files touched
A scripts/db/assessor-schema.sqlA scripts/fetch-assessor.jsA scripts/ingest-assessor.js
Diff
commit aafcd09bf88f5023c12d64ec0ac269d2a4ca79fb
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Tue Jun 30 13:24:01 2026 -0700
Assessor parcel roll: streaming Socrata/eGIS fetcher + cre.assessor_parcel schema + NDJSON ingest (2025 roll, all parcels, disk-aware capped to current snapshot)
---
scripts/db/assessor-schema.sql | 44 +++++++++++++++++
scripts/fetch-assessor.js | 108 +++++++++++++++++++++++++++++++++++++++++
scripts/ingest-assessor.js | 73 ++++++++++++++++++++++++++++
3 files changed, 225 insertions(+)
diff --git a/scripts/db/assessor-schema.sql b/scripts/db/assessor-schema.sql
new file mode 100644
index 0000000..2d6f80b
--- /dev/null
+++ b/scripts/db/assessor-schema.sql
@@ -0,0 +1,44 @@
+-- assessor-schema.sql — LA County Assessor public parcel roll, ingested into the local "cre" DB.
+-- Public open data (Assessor Parcel Data Rolls 2021-2025 feature layer). PROPERTY/PARCEL records only —
+-- assessor roll carries NO owner-name PII in this public layer (situs address + valuation + physical attrs).
+-- One row per parcel for the loaded roll year (default 2025 snapshot).
+
+CREATE TABLE IF NOT EXISTS assessor_parcel (
+ ain text PRIMARY KEY, -- Assessor Identification Number (parcel id)
+ roll_year text,
+ property_location text, -- full situs address string
+ situs_house_no integer,
+ situs_street text,
+ situs_unit text,
+ situs_city text,
+ situs_zip5 text,
+ use_type text, -- SFR / CND / COM / etc.
+ use_code text,
+ use_desc1 text, -- Residential / Commercial / ...
+ use_desc2 text, -- Single Family Residence / ...
+ year_built integer,
+ effective_year_built integer,
+ sqft_main integer,
+ bedrooms integer,
+ bathrooms integer,
+ units integer,
+ recording_date date,
+ roll_land_value bigint,
+ roll_imp_value bigint,
+ roll_tot_land_imp bigint,
+ roll_total_value bigint,
+ net_taxable_value bigint,
+ is_taxable text,
+ parcel_classification text,
+ admin_region text,
+ lat double precision,
+ lng double precision,
+ loaded_at timestamptz DEFAULT now()
+);
+
+CREATE INDEX IF NOT EXISTS idx_assessor_zip ON assessor_parcel (situs_zip5);
+CREATE INDEX IF NOT EXISTS idx_assessor_city ON assessor_parcel (situs_city);
+CREATE INDEX IF NOT EXISTS idx_assessor_use ON assessor_parcel (use_type);
+CREATE INDEX IF NOT EXISTS idx_assessor_street ON assessor_parcel (situs_street);
+-- normalized house# + street for address joins (broker->listing->parcel by situs).
+CREATE INDEX IF NOT EXISTS idx_assessor_addr ON assessor_parcel (situs_house_no, situs_street, situs_zip5);
diff --git a/scripts/fetch-assessor.js b/scripts/fetch-assessor.js
new file mode 100644
index 0000000..4f4a904
--- /dev/null
+++ b/scripts/fetch-assessor.js
@@ -0,0 +1,108 @@
+#!/usr/bin/env node
+// fetch-assessor.js — stream the LA County Assessor public parcel roll (all homes/parcels) to disk.
+//
+// Source: LA County eGIS "Assessor Parcel Data (Rolls 2021-2025)" hosted feature layer (public, FREE, $0).
+// https://services.arcgis.com/RmCCgQtiZLDCtblq/arcgis/rest/services/Parcel_Data_2021_Table/FeatureServer/0
+// The full layer is 12.1M rows = ~2.4M parcels × 5 roll years (2021-2025). We pull ONLY the latest roll
+// (RollYear=2025, ~2.43M rows = one record per parcel — the current snapshot of "all homes"). This is the
+// disk-aware scoped subset; pulling all 5 years would 5× the size for redundant historical valuations we
+// don't need. The cap is LOGGED, never silent. Override with CC_ROLL_YEAR= or CC_ALL_YEARS=1.
+//
+// Streams NDJSON to data/raw/assessor-parcels-<year>.ndjson (one parcel per line) — never holds the whole
+// roll in RAM. Reports running row count + on-disk size. data/raw/ is gitignored (big raw file).
+//
+// Run: node scripts/fetch-assessor.js # 2025 roll, all parcels
+// CC_LIMIT=5000 node scripts/fetch-assessor.js # smoke test (first 5k)
+'use strict';
+const fs = require('fs');
+const path = require('path');
+const https = require('https');
+
+const BASE = 'https://services.arcgis.com/RmCCgQtiZLDCtblq/arcgis/rest/services/Parcel_Data_2021_Table/FeatureServer/0';
+const PAGE = 2000; // = layer maxRecordCount
+const ROLL_YEAR = process.env.CC_ROLL_YEAR || '2025';
+const ALL_YEARS = process.env.CC_ALL_YEARS === '1';
+const LIMIT = +(process.env.CC_LIMIT || 0) || 0; // 0 = no cap (full roll)
+const WHERE = ALL_YEARS ? '1=1' : `RollYear='${ROLL_YEAR}'`;
+
+// Field subset: identity + location + the valuation/physical attrs that matter for CRE + broker work.
+// (* would pull ~60 cols incl. redundant base-year strings; this keeps the file lean but complete.)
+const FIELDS = [
+ 'AIN', 'RollYear', 'PropertyLocation', 'SitusHouseNo', 'SitusStreet', 'SitusUnit',
+ 'SitusCity', 'SitusZIP5', 'UseType', 'UseCode', 'UseCodeDescChar1', 'UseCodeDescChar2',
+ 'YearBuilt', 'EffectiveYearBuilt', 'SQFTmain', 'Bedrooms', 'Bathrooms', 'Units',
+ 'RecordingDate', 'Roll_LandValue', 'Roll_ImpValue', 'Roll_totLandImp', 'Roll_TotalValue',
+ 'netTaxableValue', 'isTaxableParcel', 'ParcelClassification', 'AdminRegion',
+ 'CENTER_LAT', 'CENTER_LON'
+].join(',');
+
+const RAW_DIR = path.join(__dirname, '..', 'data', 'raw');
+fs.mkdirSync(RAW_DIR, { recursive: true });
+const OUT = path.join(RAW_DIR, `assessor-parcels-${ALL_YEARS ? 'all' : ROLL_YEAR}.ndjson`);
+
+function getJSON(url) {
+ return new Promise((resolve, reject) => {
+ const req = https.get(url, { timeout: 60000 }, res => {
+ if (res.statusCode !== 200) { res.resume(); return reject(new Error('HTTP ' + res.statusCode)); }
+ let buf = '';
+ res.setEncoding('utf8');
+ res.on('data', d => buf += d);
+ res.on('end', () => { try { resolve(JSON.parse(buf)); } catch (e) { reject(new Error('bad JSON: ' + e.message)); } });
+ });
+ req.on('timeout', () => { req.destroy(new Error('timeout')); });
+ req.on('error', reject);
+ });
+}
+
+function fmtBytes(n) { return n > 1e9 ? (n / 1e9).toFixed(2) + ' GB' : (n / 1e6).toFixed(1) + ' MB'; }
+
+async function main() {
+ const t0 = Date.now();
+ // total count for progress
+ const cnt = await getJSON(`${BASE}/query?where=${encodeURIComponent(WHERE)}&returnCountOnly=true&f=json`);
+ const total = cnt.count || 0;
+ const target = LIMIT ? Math.min(LIMIT, total) : total;
+ console.log(`[assessor] LA County Assessor Parcel Roll — WHERE ${WHERE} → ${total.toLocaleString()} parcels` +
+ (LIMIT ? ` (CAPPED to ${LIMIT.toLocaleString()} via CC_LIMIT)` : '') +
+ (!ALL_YEARS ? `\n[assessor] SCOPE NOTE: pulling RollYear=${ROLL_YEAR} only (current snapshot, 1 row/parcel). Historical rolls 2021-2024 intentionally skipped (5× redundant). Set CC_ALL_YEARS=1 to pull all.` : ''));
+ console.log(`[assessor] streaming NDJSON → ${OUT} ($0 — public open data)`);
+
+ const ws = fs.createWriteStream(OUT, { flags: 'w' });
+ let offset = 0, written = 0, pages = 0;
+ while (written < target) {
+ const url = `${BASE}/query?where=${encodeURIComponent(WHERE)}&outFields=${FIELDS}` +
+ `&returnGeometry=false&orderByFields=AIN&resultOffset=${offset}&resultRecordCount=${PAGE}&f=json`;
+ let data;
+ for (let attempt = 1; ; attempt++) {
+ try { data = await getJSON(url); break; }
+ catch (e) {
+ if (attempt >= 4) throw new Error(`page @offset ${offset} failed after ${attempt} tries: ${e.message}`);
+ await new Promise(r => setTimeout(r, 1500 * attempt));
+ }
+ }
+ const feats = data.features || [];
+ if (!feats.length) break;
+ for (const f of feats) {
+ if (written >= target) break;
+ ws.write(JSON.stringify(f.attributes) + '\n');
+ written++;
+ }
+ offset += feats.length;
+ pages++;
+ if (pages % 25 === 0 || written >= target) {
+ let sz = 0; try { sz = fs.statSync(OUT).size; } catch (_) {}
+ const pct = total ? ((written / target) * 100).toFixed(1) : '?';
+ process.stdout.write(`[assessor] ${written.toLocaleString()}/${target.toLocaleString()} (${pct}%) · ${fmtBytes(sz)} · ${pages} pages\n`);
+ }
+ if (feats.length < PAGE) break; // last page
+ }
+ await new Promise(r => ws.end(r));
+ const sz = fs.statSync(OUT).size;
+ const secs = ((Date.now() - t0) / 1000).toFixed(0);
+ console.log(`[assessor] DONE — wrote ${written.toLocaleString()} parcels · ${fmtBytes(sz)} · ${pages} pages · ${secs}s · $0 (public open data)`);
+ console.log(`[assessor] file: ${OUT}`);
+ // emit a small machine-readable result line (last line) for callers/ingest
+ console.log(JSON.stringify({ ok: true, rows: written, bytes: sz, file: OUT, where: WHERE, rollYear: ALL_YEARS ? 'all' : ROLL_YEAR }));
+}
+
+main().catch(e => { console.error('[assessor] FAILED:', e.message); process.exit(1); });
diff --git a/scripts/ingest-assessor.js b/scripts/ingest-assessor.js
new file mode 100644
index 0000000..f670939
--- /dev/null
+++ b/scripts/ingest-assessor.js
@@ -0,0 +1,73 @@
+#!/usr/bin/env node
+// ingest-assessor.js — apply assessor-schema.sql then COPY-stream the NDJSON parcel roll into cre.assessor_parcel.
+// Streams the file line-by-line (never loads the whole roll in RAM), batch-upserts via multi-row INSERT
+// ON CONFLICT. Idempotent: re-running re-upserts by AIN. $0 (local Postgres).
+//
+// Run: node scripts/ingest-assessor.js # ingests data/raw/assessor-parcels-2025.ndjson
+// node scripts/ingest-assessor.js path/to/file.ndjson # explicit file
+'use strict';
+const fs = require('fs');
+const path = require('path');
+const readline = require('readline');
+const { pool } = require('./db/brokers-db');
+
+const FILE = process.argv[2] || path.join(__dirname, '..', 'data', 'raw', 'assessor-parcels-2025.ndjson');
+const BATCH = 1000;
+
+function toDate(ms) { return (ms == null || ms === '') ? null : new Date(+ms).toISOString().slice(0, 10); }
+function toInt(v) { const n = parseInt(v, 10); return Number.isFinite(n) ? n : null; }
+function s(v) { v = (v == null ? '' : String(v)).trim(); return v === '' ? null : v; }
+
+const COLS = ['ain', 'roll_year', 'property_location', 'situs_house_no', 'situs_street', 'situs_unit',
+ 'situs_city', 'situs_zip5', 'use_type', 'use_code', 'use_desc1', 'use_desc2', 'year_built',
+ 'effective_year_built', 'sqft_main', 'bedrooms', 'bathrooms', 'units', 'recording_date',
+ 'roll_land_value', 'roll_imp_value', 'roll_tot_land_imp', 'roll_total_value', 'net_taxable_value',
+ 'is_taxable', 'parcel_classification', 'admin_region', 'lat', 'lng'];
+
+function row(a) {
+ return [
+ s(a.AIN), s(a.RollYear), s(a.PropertyLocation), toInt(a.SitusHouseNo), s(a.SitusStreet), s(a.SitusUnit),
+ s(a.SitusCity), s(a.SitusZIP5), s(a.UseType), s(a.UseCode), s(a.UseCodeDescChar1), s(a.UseCodeDescChar2),
+ toInt(a.YearBuilt), toInt(a.EffectiveYearBuilt), toInt(a.SQFTmain), toInt(a.Bedrooms), toInt(a.Bathrooms),
+ toInt(a.Units), toDate(a.RecordingDate), toInt(a.Roll_LandValue), toInt(a.Roll_ImpValue),
+ toInt(a.Roll_totLandImp), toInt(a.Roll_TotalValue), toInt(a.netTaxableValue), s(a.isTaxableParcel),
+ s(a.ParcelClassification), s(a.AdminRegion),
+ (typeof a.CENTER_LAT === 'number' ? a.CENTER_LAT : null), (typeof a.CENTER_LON === 'number' ? a.CENTER_LON : null)
+ ];
+}
+
+async function flush(batch) {
+ if (!batch.length) return;
+ const vals = [], params = [];
+ let p = 0;
+ for (const r of batch) {
+ vals.push('(' + COLS.map(() => '$' + (++p)).join(',') + ')');
+ params.push(...r);
+ }
+ const upd = COLS.filter(c => c !== 'ain').map(c => `${c}=EXCLUDED.${c}`).join(',');
+ await pool.query(
+ `INSERT INTO assessor_parcel(${COLS.join(',')}) VALUES ${vals.join(',')}
+ ON CONFLICT(ain) DO UPDATE SET ${upd}, loaded_at=now()`, params);
+}
+
+async function main() {
+ if (!fs.existsSync(FILE)) { console.error('[ingest] file not found:', FILE); process.exit(1); }
+ const ddl = fs.readFileSync(path.join(__dirname, 'db', 'assessor-schema.sql'), 'utf8');
+ await pool.query(ddl);
+ console.log('[ingest] schema applied (cre.assessor_parcel). reading', FILE);
+
+ const rl = readline.createInterface({ input: fs.createReadStream(FILE), crlfDelay: Infinity });
+ let batch = [], n = 0, bad = 0;
+ for await (const line of rl) {
+ if (!line.trim()) continue;
+ let a; try { a = JSON.parse(line); } catch { bad++; continue; }
+ if (!a.AIN) { bad++; continue; }
+ batch.push(row(a));
+ if (batch.length >= BATCH) { await flush(batch); n += batch.length; batch = []; if (n % 50000 === 0) console.log(`[ingest] ${n.toLocaleString()} parcels…`); }
+ }
+ await flush(batch); n += batch.length;
+ const cnt = (await pool.query('SELECT count(*)::int c FROM assessor_parcel')).rows[0].c;
+ console.log(`[ingest] DONE — ${n.toLocaleString()} rows ingested (${bad} skipped) · table now ${cnt.toLocaleString()} parcels · $0 (local)`);
+ await pool.end();
+}
+main().catch(e => { console.error('[ingest] FAILED:', e.message); process.exit(1); });
← a45ddb9 afternoon CRE update 2026-06-30
·
back to Commercialrealestate
·
CRE :9911 — front-page broker contacts (name/company/phone/e ee71adf →