← back to Commercialrealestate
CRCP gov data: CO Aspen/Telluride/Vail (777, DORA API) + FL luxury cities Palm Beach/Naples/Miami Beach (DBPR)
86793a47a95c8e04eec48bcb637e4b1978160475 · 2026-07-12 08:50:46 -0700 · Steve Abrams
Files touched
A scripts/ingest-gov-co.jsA scripts/ingest-gov-fl-cities.js
Diff
commit 86793a47a95c8e04eec48bcb637e4b1978160475
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Sun Jul 12 08:50:46 2026 -0700
CRCP gov data: CO Aspen/Telluride/Vail (777, DORA API) + FL luxury cities Palm Beach/Naples/Miami Beach (DBPR)
---
scripts/ingest-gov-co.js | 47 +++++++++++++++++++++++++++++++++
scripts/ingest-gov-fl-cities.js | 58 +++++++++++++++++++++++++++++++++++++++++
2 files changed, 105 insertions(+)
diff --git a/scripts/ingest-gov-co.js b/scripts/ingest-gov-co.js
new file mode 100644
index 0000000..98a296a
--- /dev/null
+++ b/scripts/ingest-gov-co.js
@@ -0,0 +1,47 @@
+#!/usr/bin/env node
+/*
+ * ingest-gov-co.js — load Aspen, Telluride, Vail licensed real-estate brokers from the
+ * COLORADO open-data API into gov_licensed_agent. $0, authoritative. (Colorado issues a single
+ * "Broker" real-estate license — no salesperson tier — so all agents are brokers.)
+ *
+ * Source: CO DORA / Division of Real Estate via data.colorado.gov dataset 4zse-6bnw
+ * SODA API https://data.colorado.gov/resource/4zse-6bnw.json
+ * Usage: node scripts/ingest-gov-co.js
+ */
+'use strict';
+const { pool } = require('./db/brokers-db');
+const BASE = 'https://data.colorado.gov/resource/4zse-6bnw.json';
+const CITY_METRO = { ASPEN: 'aspen', TELLURIDE: 'telluride', VAIL: 'vail' };
+const coDate = s => { const m = /^(\d{2})\/(\d{2})\/(\d{4})$/.exec(s || ''); return m ? `${m[3]}-${m[1]}-${m[2]}` : null; };
+
+async function main() {
+ let loaded = 0;
+ for (const [CITY, metro] of Object.entries(CITY_METRO)) {
+ const where = `upper(city)='${CITY}' AND licensestatus='Active' AND licensetype like '%Broker%'`;
+ const url = `${BASE}?%24where=${encodeURIComponent(where)}&%24limit=5000`;
+ const r = await fetch(url, { headers: { 'User-Agent': 'crcp-gov-ingest' } });
+ if (!r.ok) { console.error(`${metro}: SODA ${r.status}`); continue; }
+ const rows = await r.json();
+ const seen = new Set(); const vals = []; const ph = [];
+ for (const x of rows) {
+ const lic = ((x.licenseprefix || '') + (x.licensenumber || '')).trim();
+ const name = [x.firstname, x.middlename, x.lastname].filter(Boolean).join(' ').trim();
+ const firm = (x.employer || '').trim(); // usually absent; kept for schema parity
+ const key = lic + '|' + firm; if (seen.has(key)) continue; seen.add(key);
+ const b = vals.length;
+ ph.push(`('CO',$${b+1},$${b+2},$${b+3},$${b+4},$${b+5},$${b+6},NULL,$${b+7},NULL,$${b+8},'co-dora')`);
+ vals.push(metro, name, firm, lic, (x.licensetype || '').trim(), (x.city || '').trim(), (x.zipcode || '').trim(), coDate(x.licenseexpirationdate));
+ }
+ if (ph.length) {
+ 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);
+ loaded += ph.length;
+ }
+ console.log(` ${metro.padEnd(12)} ${ph.length}`);
+ }
+ console.log(`✔ CO loaded ${loaded}. source: CO DORA public data. cost: $0`);
+ await pool.end();
+}
+if (require.main === module) main().catch(e => { console.error(e); process.exit(1); });
diff --git a/scripts/ingest-gov-fl-cities.js b/scripts/ingest-gov-fl-cities.js
new file mode 100644
index 0000000..3a048cf
--- /dev/null
+++ b/scripts/ingest-gov-fl-cities.js
@@ -0,0 +1,58 @@
+#!/usr/bin/env node
+/*
+ * ingest-gov-fl-cities.js — load FL luxury CITIES (Palm Beach, Naples, Miami Beach) city-level
+ * from the FL DBPR CSV into gov_licensed_agent, one metro per city. Same file/columns as
+ * ingest-gov-fl.js but filtered by CITY (field 7) instead of county. Expects /tmp/fl_realestate.csv.
+ */
+'use strict';
+const fs = require('fs');
+const readline = require('readline');
+const { pool } = require('./db/brokers-db');
+const FILE = '/tmp/fl_realestate.csv';
+const CITY_METRO = { 'PALM BEACH': 'palm-beach', 'NAPLES': 'naples', 'MIAMI BEACH': 'miami-beach' };
+
+function parseLine(line) { const out = []; const re = /"((?:[^"]|"")*)"/g; let m; while ((m = re.exec(line))) out.push(m[1].replace(/""/g, '"')); return out; }
+const flDate = s => { const M = { JAN:'01',FEB:'02',MAR:'03',APR:'04',MAY:'05',JUN:'06',JUL:'07',AUG:'08',SEP:'09',OCT:'10',NOV:'11',DEC:'12' };
+ const m = /^(\d{2})-([A-Z]{3})-(\d{2})$/.exec((s||'').trim()); if (!m) return null; const yr = +m[3] > 50 ? '19'+m[3] : '20'+m[3]; return `${yr}-${M[m[2]]||'01'}-${m[1]}`; };
+
+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(`('FL',$${b+1},$${b+2},$${b+3},$${b+4},$${b+5},$${b+6},$${b+7},$${b+8},$${b+9},$${b+10},'fl-dbpr')`);
+ 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 cityRe = new RegExp('"(' + Object.keys(CITY_METRO).join('|') + ')"', 'i');
+ const rl = readline.createInterface({ input: fs.createReadStream(FILE), crlfDelay: Infinity });
+ let loaded = 0, batch = [];
+ for await (const line of rl) {
+ if (!cityRe.test(line)) continue;
+ const f = parseLine(line); if (f.length < 17) continue;
+ const city = (f[7] || '').trim().toUpperCase();
+ const metro = CITY_METRO[city]; if (!metro) continue;
+ if (!/active/i.test((f[13] || '').trim())) continue;
+ const rank = (f[3] || '').trim();
+ if (!/(broker|sales)/i.test(rank) || /corp|officer|school|instructor/i.test(rank)) continue;
+ batch.push({ metro, name: (f[1]||'').trim(), firm: (f[19]||'').trim(), lic: (f[11]||'').trim(), type: rank,
+ city, addr: (f[4]||'').trim(), zip: (f[9]||'').trim(), county: (f[10]||'').trim(), exp: flDate(f[16]) });
+ 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 metro = ANY($1) GROUP BY metro ORDER BY n DESC`, [Object.values(CITY_METRO)]);
+ rows.forEach(r => console.log(` ${r.metro.padEnd(14)} ${r.n}`));
+ console.log(`✔ FL cities loaded ${loaded}. source: FL DBPR. cost: $0`);
+ await pool.end();
+}
+if (require.main === module) main().catch(e => { console.error(e); process.exit(1); });
← 988e989 CRCP gov data: CA luxury cities (Atherton/Beverly Hills/Mali
·
back to Commercialrealestate
·
CRCP gov data: CT Greenwich (648, DCP eLicense API). Top-exp 561fd54 →