← back to Commercialrealestate

CRCP gov data: CA luxury cities (Atherton/Beverly Hills/Malibu/Newport Beach/Montecito/Hillsborough/Woodside) city-level from DRE CurrList

988e9897cdd4869f73e7561ed2dc30a7a584adbe · 2026-07-12 08:49:12 -0700 · Steve Abrams

Files touched

• A scripts/ingest-gov-ca-cities.js

Diff

commit 988e9897cdd4869f73e7561ed2dc30a7a584adbe
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Sun Jul 12 08:49:12 2026 -0700

    CRCP gov data: CA luxury cities (Atherton/Beverly Hills/Malibu/Newport Beach/Montecito/Hillsborough/Woodside) city-level from DRE CurrList
---
 scripts/ingest-gov-ca-cities.js | 71 +++++++++++++++++++++++++++++++++++++++++
 1 file changed, 71 insertions(+)

diff --git a/scripts/ingest-gov-ca-cities.js b/scripts/ingest-gov-ca-cities.js
new file mode 100644
index 0000000..199dabe
--- /dev/null
+++ b/scripts/ingest-gov-ca-cities.js
@@ -0,0 +1,71 @@
+#!/usr/bin/env node
+/*
+ * ingest-gov-ca-cities.js — load CALIFORNIA luxury CITIES (city-level) from the CA DRE
+ * CurrList bulk file into gov_licensed_agent, one metro per city. Same file/columns as
+ * ingest-gov-ca.js but filtered by CITY (field 17) instead of county, for the top-expensive
+ * California cities. $0, authoritative. Expects /tmp/CurrList.csv already extracted.
+ */
+'use strict';
+const fs = require('fs');
+const readline = require('readline');
+const { pool } = require('./db/brokers-db');
+const FILE = '/tmp/CurrList.csv';
+const CITY_METRO = {
+  'ATHERTON': 'atherton', 'BEVERLY HILLS': 'beverly-hills', 'MALIBU': 'malibu',
+  'PALO ALTO': 'palo-alto', 'LOS ALTOS': 'los-altos', 'HILLSBOROUGH': 'hillsborough',
+  'WOODSIDE': 'woodside', 'NEWPORT BEACH': 'newport-beach', 'MONTECITO': 'montecito',
+};
+const caDate = s => /^\d{8}$/.test(s || '') ? `${s.slice(0,4)}-${s.slice(4,6)}-${s.slice(6,8)}` : null;
+
+async function flush(batch) {
+  if (!batch.length) return 0;
+  const seen = new Set(); const vals = []; const ph = [];
+  for (const r of batch) {
+    const key = r.lic + '|' + r.firm; if (seen.has(key)) continue; seen.add(key);
+    const b = vals.length;
+    ph.push(`('CA',$${b+1},$${b+2},$${b+3},$${b+4},$${b+5},$${b+6},$${b+7},$${b+8},$${b+9},$${b+10},'ca-dre')`);
+    vals.push(r.metro, r.name, r.firm, r.lic, r.type, r.city, r.addr, r.zip, r.county, r.exp);
+  }
+  if (!ph.length) return 0;
+  await pool.query(
+    `INSERT INTO gov_licensed_agent (state,metro,name,firm,license_number,license_type,city,addr,zip,county,expiration,source)
+     VALUES ${ph.join(',')}
+     ON CONFLICT (source,license_number,firm) DO UPDATE
+       SET license_type=EXCLUDED.license_type, expiration=EXCLUDED.expiration, city=EXCLUDED.city, fetched_at=now()`, vals);
+  return ph.length;
+}
+
+async function main() {
+  if (!fs.existsSync(FILE)) { console.error(`missing ${FILE}`); process.exit(1); }
+  const cityKeys = Object.keys(CITY_METRO);
+  const re = new RegExp(cityKeys.join('|'), 'i');
+  const rl = readline.createInterface({ input: fs.createReadStream(FILE), crlfDelay: Infinity });
+  let n = 0, loaded = 0, batch = [], first = true;
+  for await (const line of rl) {
+    if (first) { first = false; continue; }
+    n++;
+    if (!re.test(line)) continue;
+    const f = line.split(',');
+    if (f.length < 23) continue;
+    const city = (f[17] || '').trim().toUpperCase();
+    const metro = CITY_METRO[city]; if (!metro) continue;
+    const type = (f[5] || '').trim();
+    if (type !== 'Salesperson' && type !== 'Broker') continue;
+    if ((f[6] || '').trim() !== 'Licensed') continue;
+    const last = (f[1] || '').trim(), fn = (f[2] || '').trim();
+    const name = (fn ? fn + ' ' : '') + last;
+    if (!name || !(f[4] || '').trim()) continue;
+    batch.push({ metro, name, firm: (f[11] || '').trim(), lic: (f[4] || '').trim(), type,
+      city, addr: (f[15] || '').trim(), zip: (f[19] || '').trim(),
+      county: (f[22] || '').trim(), exp: caDate((f[8] || '').trim()) });
+    if (batch.length >= 500) { loaded += await flush(batch); batch = []; }
+  }
+  loaded += await flush(batch);
+  const { rows } = await pool.query(
+    `SELECT metro, count(*) n FROM gov_licensed_agent WHERE source='ca-dre' AND metro = ANY($1) GROUP BY metro ORDER BY n DESC`,
+    [Object.values(CITY_METRO)]);
+  rows.forEach(r => console.log(`  ${r.metro.padEnd(15)} ${r.n}`));
+  console.log('  source: CA DRE CurrList. cost: $0');
+  await pool.end();
+}
+if (require.main === module) main().catch(e => { console.error(e); process.exit(1); });

← 32e449e CRCP gov data: 7 metros / 337,725 agents live-ready (add CA  ·  back to Commercialrealestate  ·  CRCP gov data: CO Aspen/Telluride/Vail (777, DORA API) + FL 86793a4 →