CRCP: NY metros loaded from gov data — NYC 47,019 + Hamptons 2,261 licensees (brokers+salespersons); batched upsert + office/branch filter

7fe7597a83b5678d5db47b1656b158a74254d1c1 · 2026-07-12 08:01:34 -0700 · Steve Abrams

Files touched

M scripts/ingest-gov-ny.js

Diff

commit 7fe7597a83b5678d5db47b1656b158a74254d1c1
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Sun Jul 12 08:01:34 2026 -0700

    CRCP: NY metros loaded from gov data — NYC 47,019 + Hamptons 2,261 licensees (brokers+salespersons); batched upsert + office/branch filter
---
 scripts/ingest-gov-ny.js | 27 ++++++++++++++++++---------
 1 file changed, 18 insertions(+), 9 deletions(-)

diff --git a/scripts/ingest-gov-ny.js b/scripts/ingest-gov-ny.js
index d63452e..c376741 100644
--- a/scripts/ingest-gov-ny.js
+++ b/scripts/ingest-gov-ny.js
@@ -23,7 +23,9 @@ const METROS = {
 
 async function fetchPage(cities, offset, limit) {
   const inList = cities.map(c => `'${c.replace(/'/g, "''")}'`).join(',');
-  const where = encodeURIComponent(`business_city in(${inList})`);
+  // brokers + salespersons only — exclude pure office/branch records (Steve's scope)
+  const where = encodeURIComponent(
+    `business_city in(${inList}) AND license_type not like '%OFFICE%' AND license_type not like '%BRANCH%'`);
   const url = `${BASE}?%24where=${where}&%24limit=${limit}&%24offset=${offset}&%24order=license_number`;
   const r = await fetch(url, { headers: { 'User-Agent': 'crcp-gov-ingest' } });
   if (!r.ok) throw new Error(`SODA ${r.status}`);
@@ -39,19 +41,26 @@ async function main() {
   for (;;) {
     const rows = await fetchPage(cities, offset, LIMIT);
     if (!rows.length) break;
+    // batch upsert: one multi-row INSERT per page. Dedup within-page on (license_number,firm)
+    // so a page can't trip "ON CONFLICT ... cannot affect row a second time".
+    const seen = new Set(); const vals = []; const ph = [];
     for (const r of rows) {
+      const lic = (r.license_number || '').trim(), firm = (r.business_name || '').trim();
+      const key = lic + '|' + firm; if (seen.has(key)) continue; seen.add(key);
       const exp = r.license_expiration_date ? r.license_expiration_date.slice(0, 10) : null;
+      const b = vals.length;
+      ph.push(`('NY',$${b+1},$${b+2},$${b+3},$${b+4},$${b+5},$${b+6},$${b+7},$${b+8},$${b+9},$${b+10},'ny-dos')`);
+      vals.push(metro, (r.license_holder_name || '').trim(), firm, lic, (r.license_type || '').trim(),
+        (r.business_city || '').trim(), (r.business_address_1 || '').trim(),
+        (r.business_zip || '').trim(), (r.county && r.county !== 'NONE' ? r.county : null), exp);
+    }
+    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 ('NY',$1,$2,$3,$4,$5,$6,$7,$8,$9,$10,'ny-dos')
+         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()`,
-        [metro,
-         (r.license_holder_name || '').trim(), (r.business_name || '').trim(),
-         (r.license_number || '').trim(), (r.license_type || '').trim(),
-         (r.business_city || '').trim(), (r.business_address_1 || '').trim(),
-         (r.business_zip || '').trim(), (r.county && r.county !== 'NONE' ? r.county : null), exp]);
-      upserted++;
+           SET license_type=EXCLUDED.license_type, expiration=EXCLUDED.expiration, city=EXCLUDED.city, fetched_at=now()`, vals);
+      upserted += ph.length;
     }
     total += rows.length; offset += LIMIT;
     process.stdout.write(`\r  fetched ${total}  upserted ${upserted}   `);

← cdf2871 CRCP: multi-metro gov-license ingest — gov_licensed_agent ta · back to Commercialrealestate · CRCP: FL DBPR CSV adapter — Miami (Dade county) 67,776 licen 3d61200 →