← back to Stayclaim
scripts/ingest-bh-permits-arcgis.ts
185 lines
/**
* ingest-bh-permits-arcgis.ts
*
* Beverly Hills Issued Permits via ArcGIS FeatureServer.
* Verified 2026-04-30: ~41,409 rows in "Issued Permits All Current" feed.
*
* Tier: A (Beverly Hills city government primary record).
*/
import { Pool } from 'pg';
const pool = new Pool({
host: process.env.PGHOST ?? '/tmp',
database: process.env.PGDATABASE ?? 'stayclaim',
user: process.env.PGUSER ?? process.env.USER,
password: process.env.PGPASSWORD,
port: parseInt(process.env.PGPORT ?? '5432', 10),
max: 6,
});
const FS_URL = 'https://services5.arcgis.com/7CXE3aevo18HlHBC/arcgis/rest/services/Permit_Issued_All/FeatureServer/0/query';
const PAGE = 1000; // BH server caps at 1000 per request
const BATCH = 500;
type Feature = { attributes: Record<string, any>; geometry?: { x?: number; y?: number } };
function canonicalize(addr: string): string {
return addr.toLowerCase().replace(/[^\w\s-]/g, '').replace(/\s+/g, '-').replace(/-+/g, '-').replace(/^-|-$/g, '').slice(0, 90);
}
async function fetchPage(offset: number): Promise<Feature[]> {
const u = new URL(FS_URL);
u.searchParams.set('where', '1=1');
u.searchParams.set('outFields', '*');
u.searchParams.set('returnGeometry', 'true');
u.searchParams.set('outSR', '4326');
u.searchParams.set('resultOffset', String(offset));
u.searchParams.set('resultRecordCount', String(PAGE));
u.searchParams.set('f', 'json');
u.searchParams.set('orderByFields', 'OBJECTID');
for (let attempt = 1; attempt <= 4; attempt++) {
try {
const res = await fetch(u);
if (!res.ok) throw new Error(`HTTP ${res.status}`);
const j = await res.json() as { features?: Feature[] };
return j.features ?? [];
} catch (e) {
if (attempt >= 4) throw e;
await new Promise(r => setTimeout(r, 1500 * attempt));
}
}
return [];
}
async function processBatch(features: Feature[]) {
// dedup canonical — BH actual field is `ADDRESS` (verified 2026-04-30)
const canonMap = new Map<string, { addr: string; lat: number | null; lon: number | null }>();
for (const f of features) {
const a = f.attributes ?? {};
const addr = (a.ADDRESS ?? a.SiteAddress ?? a.SITE_ADDRESS ?? a.Address ?? '').toString().trim();
if (!addr) continue;
const canon = canonicalize(addr);
if (!canon || canonMap.has(canon)) continue;
canonMap.set(canon, { addr, lat: f.geometry?.y ?? null, lon: f.geometry?.x ?? null });
}
if (canonMap.size === 0) return;
// upsert listings batch
const listingTuples: string[] = [];
const listingValues: any[] = [];
let i = 0;
for (const [canon, r] of canonMap) {
const base = i * 6;
listingTuples.push(`($${base+1},$${base+2},$${base+3},$${base+4},$${base+5},$${base+6})`);
listingValues.push(`${canon}-bh`, `bh:${canon}`, r.addr, r.addr, r.lat, r.lon);
i++;
}
const lr = await pool.query<{ id: string; source_id: string }>(
`INSERT INTO listing (slug, source, source_id, title, address_line1, city, state, country, latitude, longitude, is_public, tier)
VALUES ${listingTuples.map((t, k) => {
const b = k * 6;
return `($${b+1},'bh_permit',$${b+2},$${b+3},$${b+4},'Beverly Hills','CA','US',$${b+5},$${b+6},true,'free')`;
}).join(',')}
ON CONFLICT (slug) DO UPDATE SET
latitude = COALESCE(listing.latitude, EXCLUDED.latitude),
longitude = COALESCE(listing.longitude, EXCLUDED.longitude),
updated_at = now()
RETURNING id, source_id`,
listingValues
);
const canonToLid = new Map<string, string>();
for (const row of lr.rows) {
// source_id may be from a colliding-slug existing listing; tolerate by stripping any prefix
canonToLid.set(row.source_id.replace(/^[a-z_]+:/i, ''), row.id);
}
// also map by canonical (in case source_id stripped doesn't match)
for (const [canon] of canonMap) {
if (!canonToLid.has(canon)) {
// try lookup by slug
const slugMatch = await pool.query<{ id: string }>(
`SELECT id FROM listing WHERE slug = $1 LIMIT 1`,
[`${canon}-bh`]
);
if (slugMatch.rows[0]) canonToLid.set(canon, slugMatch.rows[0].id);
}
}
// permits batch — dedup by permit_number to avoid ON CONFLICT collisions within a batch
const seenPermits = new Set<string>();
const permitTuples: string[] = [];
const permitValues: any[] = [];
let pi = 0;
for (const f of features) {
const a = f.attributes ?? {};
const addr = (a.ADDRESS ?? a.SiteAddress ?? a.Address ?? '').toString().trim();
if (!addr) continue;
const canon = canonicalize(addr);
const lid = canonToLid.get(canon);
if (!lid) continue;
const permitNbr = (a.PERMIT_NUMBER ?? a.PermitNumber ?? a.OBJECTID ?? '').toString().trim();
if (!permitNbr) continue;
if (seenPermits.has(permitNbr)) continue;
seenPermits.add(permitNbr);
// Construct issue_date from year+month
const yr = parseInt(String(a.ISSUED_YEAR ?? ''), 10);
const mo = parseInt(String(a.ISSUED_MONTH ?? '1'), 10);
const issueDate = (isFinite(yr) && yr >= 1900 && yr <= 2030 && mo >= 1 && mo <= 12)
? `${yr}-${String(mo).padStart(2, '0')}-01` : null;
const valStr = String(a.VALUATION ?? a.Valuation ?? '').replace(/[$,]/g, '');
const valuation = parseFloat(valStr) || null;
const base = pi * 11;
permitTuples.push(`(${Array.from({length: 11}, (_, k) => `$${base+k+1}`).join(',')})`);
permitValues.push(
permitNbr, // 1 permit_number
lid, // 2 listing_id
addr, // 3 primary_address
a.PERMIT_TYPE ?? a.PermitType ?? null, // 4 permit_type
null, // 5 permit_sub_type
null, // 6 status_desc — BH doesn't expose
issueDate, // 7 issue_date
valuation, // 8 valuation
a.PERMIT_DESCRIPTION ?? null, // 9 work_desc
f.geometry?.y ?? null, // 10 latitude
f.geometry?.x ?? null, // 11 longitude
);
pi++;
}
if (permitTuples.length === 0) return;
await pool.query(
`INSERT INTO permit (source, source_dataset, permit_number, listing_id, primary_address, permit_type, permit_sub_type, status_desc, issue_date, valuation, work_desc, latitude, longitude, source_label, source_url)
VALUES ${permitTuples.map((t, k) => {
const b = k * 11;
return `('bh','bh-issued-permits-all',$${b+1},$${b+2},$${b+3},$${b+4},$${b+5},$${b+6},$${b+7},$${b+8},$${b+9},$${b+10},$${b+11},'City of Beverly Hills','https://opendata-hub.beverlyhills.org/datasets/155382a864da4f10afb7d0d5eab7b68d_0')`;
}).join(',')}
ON CONFLICT (source_dataset, permit_number) DO UPDATE SET
status_desc = COALESCE(EXCLUDED.status_desc, permit.status_desc),
retrieved_at = now()`,
permitValues
);
}
async function main() {
let offset = 0;
let total = 0;
const t0 = Date.now();
while (true) {
const features = await fetchPage(offset);
if (!features.length) break;
for (let i = 0; i < features.length; i += BATCH) {
await processBatch(features.slice(i, i + BATCH));
}
total += features.length;
const dt = (Date.now() - t0) / 1000;
console.log(` bh: ${total} (${(total/dt).toFixed(0)}/s)`);
if (features.length === 0) break;
offset += features.length;
if (offset > 100000) break; // safety bound
}
console.log(`✓ BH permits: ${total} total`);
await pool.end();
}
main().catch(e => { console.error('FATAL', e); process.exit(1); });