← back to La Socrata Ingester
GIS layers (NavigateLA ArcGIS) + CSLB contractor loader
cc1280543420dc05ab5b32f680a5fdf2eebe2697 · 2026-08-11 09:12:06 -0700 · steve
ArcGIS adapter: OBJECTID-cursor pagination + geometry support. GIS schema
(la_parcel_geom, la_gis_features). Loaded zoning/land-use/council districts/
neighborhood councils/HPOZ/community plan areas + fire/flood/fault/liquefaction
hazard layers. CSLB free-portal ASP.NET session downloader -> 243,555 CA
contractors in cslb_raw. Assessor switched to OID-cursor (fixes deep-paging 400s).
Files touched
M src/adapters/arcgis.jsA src/cslb/download.jsM src/db.jsM src/sources.js
Diff
commit cc1280543420dc05ab5b32f680a5fdf2eebe2697
Author: steve <steve@designerwallcoverings.com>
Date: Tue Aug 11 09:12:06 2026 -0700
GIS layers (NavigateLA ArcGIS) + CSLB contractor loader
ArcGIS adapter: OBJECTID-cursor pagination + geometry support. GIS schema
(la_parcel_geom, la_gis_features). Loaded zoning/land-use/council districts/
neighborhood councils/HPOZ/community plan areas + fire/flood/fault/liquefaction
hazard layers. CSLB free-portal ASP.NET session downloader -> 243,555 CA
contractors in cslb_raw. Assessor switched to OID-cursor (fixes deep-paging 400s).
---
src/adapters/arcgis.js | 94 ++++++++++++++++++++++++++++++--------------------
src/cslb/download.js | 82 +++++++++++++++++++++++++++++++++++++++++++
src/db.js | 5 +--
src/sources.js | 42 +++++++++++++++++++++-
4 files changed, 183 insertions(+), 40 deletions(-)
diff --git a/src/adapters/arcgis.js b/src/adapters/arcgis.js
index 45940d9..32177ed 100644
--- a/src/adapters/arcgis.js
+++ b/src/adapters/arcgis.js
@@ -1,54 +1,74 @@
import { fetchJson } from './http.js';
-// ArcGIS FeatureServer paginator. Yields { rows, url } per page of `.attributes`.
-// Pages via resultOffset/resultRecordCount, ordered by a stable field. Honors
-// exceededTransferLimit to know when more pages remain.
+// ArcGIS FeatureServer/MapServer paginator. Yields { rows, url } per page, where
+// each row is the feature's attributes (with __geometry attached when src.geometry).
//
-// src fields used: endpoint (…/FeatureServer/0), orderBy, cursorField?, defaultWhere?
-// opts: since (string | null), pageSize (default 2000), maxRows
+// Two pagination modes:
+// • 'oid' (src.paginate==='oid') — OBJECTID-cursor: where <oid> > lastMax, ordered
+// by oid, no resultOffset. Robust for large/deep extracts (avoids the deep-offset
+// 400 "Invalid query parameters" that offset paging hits past ~800 pages).
+// • 'offset' (default) — resultOffset/resultRecordCount. Fine for small layers.
+//
+// src fields: endpoint (…/{n}), orderBy?, oidField? (default OBJECTID), cursorField?,
+// defaultWhere?, paginate? ('oid'|'offset'), geometry? (bool)
+// opts: since, pageSize, maxRows, fullScan, whereOverride
export async function* arcgisPages(src, opts = {}) {
const { since = null, pageSize = 2000, maxRows = Infinity, fullScan = false, whereOverride = null } = opts;
const base = `${src.endpoint}/query`;
+ const oidField = src.oidField || 'OBJECTID';
+ const geom = !!src.geometry;
+
+ // base filters (everything except the oid cursor)
+ const filters = [];
+ if (whereOverride) filters.push(whereOverride);
+ else if (fullScan) { /* all rows */ }
+ else if (since && src.cursorField) filters.push(`${src.cursorField} >= '${since}'`);
+ else if (src.defaultWhere) filters.push(src.defaultWhere);
+
+ const common = {
+ outFields: '*',
+ returnGeometry: geom ? 'true' : 'false',
+ f: 'json',
+ };
+ if (geom) common.outSR = '4326'; // WGS84 lat/lon
+
+ const mapRows = (feats) =>
+ feats.map((ft) => (geom ? { ...ft.attributes, __geometry: ft.geometry } : ft.attributes));
- // whereOverride (--year) wins outright: pull exactly that slice.
- // fullScan (--full) = every row, ignore the latest-year defaultWhere.
- // Otherwise: incremental cursor if we have one, else the cheap defaultWhere.
- const clauses = [];
- if (whereOverride) {
- clauses.push(whereOverride);
- } else if (fullScan) {
- // no filter — all roll years
- } else if (since && src.cursorField) {
- clauses.push(`${src.cursorField} >= '${since}'`);
- } else if (src.defaultWhere) {
- clauses.push(src.defaultWhere);
+ if (src.paginate === 'oid') {
+ let lastOid = -1, fetched = 0;
+ for (;;) {
+ if (fetched >= maxRows) break;
+ const where = [...filters, `${oidField} > ${lastOid}`].join(' AND ');
+ const count = Math.min(pageSize, maxRows - fetched);
+ const p = new URLSearchParams({ ...common, where, orderByFields: oidField, resultRecordCount: String(count) });
+ const url = `${base}?${p}`;
+ const data = await fetchJson(url);
+ if (data.error) throw new Error(`ArcGIS error: ${JSON.stringify(data.error).slice(0, 200)}`);
+ const feats = data.features || [];
+ if (!feats.length) break;
+ yield { rows: mapRows(feats), url };
+ lastOid = Math.max(...feats.map((ft) => ft.attributes[oidField]));
+ fetched += feats.length;
+ if (feats.length < count) break;
+ }
+ return;
}
- const where = clauses.length ? clauses.join(' AND ') : '1=1';
+ // offset mode
let offset = 0;
+ const where = filters.length ? filters.join(' AND ') : '1=1';
for (;;) {
const count = Math.min(pageSize, maxRows - offset);
if (count <= 0) break;
-
- const p = new URLSearchParams({
- where,
- outFields: '*',
- returnGeometry: 'false',
- orderByFields: src.orderBy || 'AIN',
- resultOffset: String(offset),
- resultRecordCount: String(count),
- f: 'json',
- });
+ const p = new URLSearchParams({ ...common, where, orderByFields: src.orderBy || oidField, resultOffset: String(offset), resultRecordCount: String(count) });
const url = `${base}?${p}`;
const data = await fetchJson(url);
- if (data.error) throw new Error(`ArcGIS error: ${JSON.stringify(data.error).slice(0, 300)}`);
-
- const rows = (data.features || []).map((f) => f.attributes);
- if (!rows.length) break;
-
- yield { rows, url };
-
- offset += rows.length;
- if (!data.exceededTransferLimit && rows.length < count) break;
+ if (data.error) throw new Error(`ArcGIS error: ${JSON.stringify(data.error).slice(0, 200)}`);
+ const feats = data.features || [];
+ if (!feats.length) break;
+ yield { rows: mapRows(feats), url };
+ offset += feats.length;
+ if (!data.exceededTransferLimit && feats.length < count) break;
}
}
diff --git a/src/cslb/download.js b/src/cslb/download.js
new file mode 100644
index 0000000..44502f2
--- /dev/null
+++ b/src/cslb/download.js
@@ -0,0 +1,82 @@
+// CSLB License Master downloader — the free public portal is an ASP.NET
+// session/postback flow (no static URL). Flow:
+// 1. GET the page -> session cookie + viewstate tokens
+// 2. POST ddlStatus=M (dropdown-change postback) -> page with the CSV button + new tokens
+// 3. POST the CSV linkbutton -> streams the License Master CSV
+// Writes the CSV to tmp/cslb-master.csv.
+import fs from 'fs';
+import path from 'path';
+import { fileURLToPath } from 'url';
+
+const URL = 'https://www.cslb.ca.gov/onlineservices/dataportal/ContractorList';
+const UA = 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/120 Safari/537.36';
+const __dirname = path.dirname(fileURLToPath(import.meta.url));
+const OUT = path.resolve(__dirname, '../../tmp/cslb-master.csv');
+
+const pick = (html, name) => {
+ const m = html.match(new RegExp(`id="${name}"[^>]*value="([^"]*)"`)) ||
+ html.match(new RegExp(`name="${name}"[^>]*value="([^"]*)"`));
+ return m ? m[1] : '';
+};
+const tokens = (html) => ({
+ __VIEWSTATE: pick(html, '__VIEWSTATE'),
+ __VIEWSTATEGENERATOR: pick(html, '__VIEWSTATEGENERATOR'),
+ __EVENTVALIDATION: pick(html, '__EVENTVALIDATION'),
+});
+// find the CSV download linkbutton's postback target from the page
+function findCsvTarget(html) {
+ const targets = [...html.matchAll(/__doPostBack\(&?#?39?;?'?([^'&]*(?:CSV|Csv)[^'&]*)/g)].map((m) => m[1]);
+ // prefer a Master CSV button
+ return targets.find((t) => /master/i.test(t)) || targets.find((t) => /csv/i.test(t)) || 'ctl00$MainContent$lbMasterCSV';
+}
+
+async function post(cookie, fields) {
+ const body = new URLSearchParams(fields).toString();
+ return fetch(URL, {
+ method: 'POST',
+ headers: { 'User-Agent': UA, 'Content-Type': 'application/x-www-form-urlencoded', Cookie: cookie, Referer: URL },
+ body,
+ });
+}
+
+async function main() {
+ // 1. GET
+ const r1 = await fetch(URL, { headers: { 'User-Agent': UA } });
+ const cookie = (r1.headers.get('set-cookie') || '').split(',').map((c) => c.split(';')[0].trim()).filter((c) => /=/.test(c)).join('; ');
+ const html1 = await r1.text();
+ const t1 = tokens(html1);
+ console.log('step1: session', cookie ? 'ok' : 'MISSING', '| viewstate', t1.__VIEWSTATE ? t1.__VIEWSTATE.length + 'b' : 'MISSING');
+
+ // 2. select License Master (dropdown-change postback)
+ const r2 = await post(cookie, {
+ __EVENTTARGET: 'ctl00$MainContent$ddlStatus', __EVENTARGUMENT: '', __LASTFOCUS: '',
+ ...t1, 'ctl00$MainContent$ddlStatus': 'M',
+ });
+ const html2 = await r2.text();
+ const t2 = tokens(html2);
+ const csvTarget = findCsvTarget(html2);
+ console.log('step2: master selected | csv target =', csvTarget, '| viewstate', t2.__VIEWSTATE ? t2.__VIEWSTATE.length + 'b' : 'MISSING');
+
+ // 3. click the CSV download button -> stream to file
+ const r3 = await post(cookie, {
+ __EVENTTARGET: csvTarget, __EVENTARGUMENT: '', __LASTFOCUS: '',
+ ...t2, 'ctl00$MainContent$ddlStatus': 'M',
+ });
+ const ct = r3.headers.get('content-type') || '';
+ const cd = r3.headers.get('content-disposition') || '';
+ console.log('step3: status', r3.status, '| content-type', ct, '| disposition', cd.slice(0, 60));
+
+ if (!/csv|octet-stream|text\/plain|application\/vnd/i.test(ct) && !/attachment/i.test(cd)) {
+ const txt = await r3.text();
+ fs.writeFileSync(OUT.replace('.csv', '-debug.html'), txt);
+ throw new Error(`step3 did not return a file (got ${ct}); wrote debug HTML. First 200: ${txt.slice(0, 200)}`);
+ }
+ const buf = Buffer.from(await r3.arrayBuffer());
+ fs.mkdirSync(path.dirname(OUT), { recursive: true });
+ fs.writeFileSync(OUT, buf);
+ const lines = buf.toString('utf8', 0, Math.min(buf.length, 400)).split('\n')[0];
+ console.log(`✔ wrote ${OUT} — ${(buf.length / 1e6).toFixed(1)} MB`);
+ console.log(' header:', lines.slice(0, 180));
+}
+
+main().catch((e) => { console.error('✖', e.message); process.exit(1); });
diff --git a/src/db.js b/src/db.js
index c334957..3db064e 100644
--- a/src/db.js
+++ b/src/db.js
@@ -41,6 +41,7 @@ export async function upsert(table, conflict, rows) {
? 'DO UPDATE SET ' + updatable.map((c) => `"${c}" = EXCLUDED."${c}"`).join(', ')
: 'DO NOTHING';
+ const JSONB = new Set(['raw', 'geom', 'attrs']); // columns stored as jsonb
const CHUNK = 500;
let sent = 0;
for (let i = 0; i < rows.length; i += CHUNK) {
@@ -49,9 +50,9 @@ export async function upsert(table, conflict, rows) {
const tuples = slice.map((row) => {
const ph = cols.map((c) => {
let v = row[c];
- if (c === 'raw' && v != null && typeof v !== 'string') v = JSON.stringify(v);
+ if (JSONB.has(c) && v != null && typeof v !== 'string') v = JSON.stringify(v);
values.push(v === undefined ? null : v);
- return `$${values.length}${c === 'raw' ? '::jsonb' : ''}`;
+ return `$${values.length}${JSONB.has(c) ? '::jsonb' : ''}`;
});
return `(${ph.join(',')})`;
});
diff --git a/src/sources.js b/src/sources.js
index 2a4fbce..0890723 100644
--- a/src/sources.js
+++ b/src/sources.js
@@ -81,7 +81,9 @@ export const SOURCES = {
'https://services.arcgis.com/RmCCgQtiZLDCtblq/arcgis/rest/services/Parcel_Data_2021_Table/FeatureServer/0',
table: 'la_assessor_parcels_raw',
conflict: ['ain', 'roll_year'],
- orderBy: 'AIN',
+ orderBy: 'OBJECTID',
+ oidField: 'OBJECTID',
+ paginate: 'oid', // OBJECTID-cursor — robust vs deep-offset 400s
cursorField: 'RollYear',
// Default (non-full) refresh: newest roll year only (~2.4M vs 12.1M total).
defaultWhere: "RollYear = '2025'",
@@ -169,6 +171,44 @@ export const SOURCES = {
},
};
+// ========================= GIS LAYERS (LA City NavigateLA ArcGIS) ==========
+const NAV = 'https://maps.lacity.org/arcgis/rest/services/Mapping/NavigateLA/MapServer';
+// generic feature -> la_gis_features row
+const gisFeat = (layer, nameFields = []) => (r) => ({
+ layer, oid: r.OBJECTID,
+ name: nameFields.map((f) => r[f]).find((v) => v != null && v !== '') ?? str(r.TOOLTIP),
+ geom: r.__geometry,
+});
+const gisSrc = (layer, layerId, nameFields, extra = {}) => ({
+ platform: 'arcgis', endpoint: `${NAV}/${layerId}`, geometry: true,
+ table: 'la_gis_features', conflict: ['layer', 'oid'],
+ map: gisFeat(layer, nameFields), ...extra,
+});
+
+Object.assign(SOURCES, {
+ // Parcel polygons (2.4M) — join to permits/assessor by ain. OID-cursor + geometry.
+ gis_parcels: {
+ platform: 'arcgis', endpoint: `${NAV}/397`, geometry: true, paginate: 'oid', oidField: 'OBJECTID',
+ table: 'la_parcel_geom', conflict: ['oid'],
+ map: (r) => ({ oid: r.OBJECTID, ain: str(r.AIN), geom: r.__geometry }),
+ },
+ // Zoning + land use (~50–59k each; offset paging, 20k/page).
+ // geometry-heavy polygon layers: small pages + OID-cursor (20k/page 500s or truncates)
+ gis_zoning: gisSrc('zoning', 71, ['ZONE_CMPLT', 'ZONE_CLASS'], { pageSize: 3000, paginate: 'oid' }),
+ gis_landuse: gisSrc('landuse', 70, ['GPLU_Legend', 'GPLU'], { pageSize: 3000, paginate: 'oid' }),
+ // Boundaries (small).
+ gis_council_districts: gisSrc('council_district', 418, ['District_Name', 'NAME', 'District'], { pageSize: 20000 }),
+ gis_neighborhood_councils: gisSrc('neighborhood_council', 439, ['NAME'], { pageSize: 20000 }),
+ gis_hpoz: gisSrc('hpoz', 75, ['NAME'], { pageSize: 20000 }),
+ gis_community_plan_areas: gisSrc('community_plan_area', 414, ['NAME'], { pageSize: 20000 }),
+ // Hazards (small).
+ gis_fault_zones: gisSrc('fault_zone', 119, ['HAZ_TYPE'], { pageSize: 20000 }),
+ gis_liquefaction: gisSrc('liquefaction', 124, [], { pageSize: 20000 }),
+ gis_flood: gisSrc('flood', 254, ['FLD_ZONE'], { pageSize: 20000, oidField: 'FLD_AR_ID' }),
+ gis_fire_vhfhsz: gisSrc('fire_vhfhsz', 358, [], { pageSize: 20000 }),
+ // NOTE: methane (layer 354) has NO pagination support — needs spatial tiling; deferred.
+});
+
// Resolve a source's effective mapper (supports mapFrom inheritance + mapOverride).
export function resolveMap(name) {
const src = SOURCES[name];
← bc01699 auto-data-snapshot: 2026-08-11T09:10:52 (1 data files) — db/
·
back to La Socrata Ingester
·
GIS: parcel polygons via hosted FeatureServer (421,684 w/ ge 0ccaea8 →