← back to Nationalrealestate
auto-save: 2026-07-21T17:08:42 (8 files) — package.json public/property.html src/enrich/firm_website_discovery.ts src/server/property.ts data/screenshots/
1c7f851fff371529d0c19536dbf12dbf2eb853ba · 2026-07-21 17:08:43 -0700 · Steve Abrams
Files touched
A data/screenshots/firm-1830.jpgA data/screenshots/firm-27261.jpgA data/screenshots/firm-27268.jpgM package.jsonM public/property.htmlM src/enrich/firm_website_discovery.tsA src/ingest/parcels/engine.tsA src/ingest/parcels/king_wa.tsA src/ingest/parcels/upsert.tsM src/server/property.ts
Diff
commit 1c7f851fff371529d0c19536dbf12dbf2eb853ba
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Tue Jul 21 17:08:43 2026 -0700
auto-save: 2026-07-21T17:08:42 (8 files) — package.json public/property.html src/enrich/firm_website_discovery.ts src/server/property.ts data/screenshots/
---
data/screenshots/firm-1830.jpg | Bin 0 -> 124546 bytes
data/screenshots/firm-27261.jpg | Bin 0 -> 125203 bytes
data/screenshots/firm-27268.jpg | Bin 0 -> 117280 bytes
package.json | 5 +-
public/property.html | 14 ++-
src/enrich/firm_website_discovery.ts | 129 ++++++++++++-------------
src/ingest/parcels/engine.ts | 26 +++++
src/ingest/parcels/king_wa.ts | 181 +++++++++++++++++++++++++++++++++++
src/ingest/parcels/upsert.ts | 70 ++++++++++++++
src/server/property.ts | 83 ++++++++++++----
10 files changed, 416 insertions(+), 92 deletions(-)
diff --git a/data/screenshots/firm-1830.jpg b/data/screenshots/firm-1830.jpg
new file mode 100644
index 0000000..b60f762
Binary files /dev/null and b/data/screenshots/firm-1830.jpg differ
diff --git a/data/screenshots/firm-27261.jpg b/data/screenshots/firm-27261.jpg
new file mode 100644
index 0000000..e1bb6f8
Binary files /dev/null and b/data/screenshots/firm-27261.jpg differ
diff --git a/data/screenshots/firm-27268.jpg b/data/screenshots/firm-27268.jpg
new file mode 100644
index 0000000..4c1b862
Binary files /dev/null and b/data/screenshots/firm-27268.jpg differ
diff --git a/package.json b/package.json
index 490d005..bfd7d77 100644
--- a/package.json
+++ b/package.json
@@ -3,7 +3,7 @@
"version": "0.1.0",
"private": true,
"type": "module",
- "description": "USRealEstate — national U.S. residential market explorer. Free-data v1 (Redfin Data Center, Zillow Research, Census ACS, FHFA). Internal analyst tool behind basic auth.",
+ "description": "USRealEstate \u2014 national U.S. residential market explorer. Free-data v1 (Redfin Data Center, Zillow Research, Census ACS, FHFA). Internal analyst tool behind basic auth.",
"scripts": {
"server": "tsx src/server/index.ts",
"migrate": "tsx db/migrate.ts",
@@ -20,7 +20,8 @@
"refresh": "tsx src/jobs/refresh_all.ts",
"alerts": "tsx src/jobs/alerts_check.ts",
"ingest:nri": "tsx src/ingest/fema_nri.ts",
- "ingest:fmr": "tsx src/ingest/hud_fmr.ts"
+ "ingest:fmr": "tsx src/ingest/hud_fmr.ts",
+ "ingest:parcels": "tsx src/ingest/parcels/engine.ts"
},
"dependencies": {
"better-sqlite3": "^13.0.1",
diff --git a/public/property.html b/public/property.html
index f6d35d2..85527b1 100644
--- a/public/property.html
+++ b/public/property.html
@@ -115,8 +115,10 @@ const COUNTY_LABELS = {
'06037': 'Los Angeles County, CA', '36061': 'Manhattan (New York County), NY',
'36047': 'Brooklyn (Kings County), NY', '36081': 'Queens County, NY',
'36005': 'Bronx County, NY', '36085': 'Staten Island (Richmond County), NY',
+ '53033': 'King County (Seattle), WA', '17031': 'Cook County (Chicago), IL',
};
-const PLACEHOLDER = { '06037': 'e.g. 9321 Ventura Way', dflt: 'e.g. 1 5 Avenue' };
+const PLACEHOLDER = { '06037': 'e.g. 9321 Ventura Way', '53033': 'e.g. 400 Broad St',
+ '17031': 'e.g. 1412 N Kingsbury St', dflt: 'e.g. 1 5 Avenue' };
const usd0 = v => v == null ? '—' : '$' + Math.round(v).toLocaleString();
const int0 = v => v == null ? '—' : Math.round(v).toLocaleString();
@@ -172,8 +174,14 @@ async function load(loc){
`AIN ${p.ain} · county ${d.county} · ${p.bedrooms ?? '—'} bd / ${p.bathrooms ?? '—'} ba · ` +
`${int0(p.sqft)} sqft · built ${p.year_built || '—'}`;
- // 1 Property History
- body('s_history', d.property_history.last_recording_date
+ // 1 Property History — full sale records table when the county source has them (King WA)
+ const sales = d.property_history.sales || [];
+ body('s_history', sales.length
+ ? `<table><tr><th>Date</th><th>Price</th><th>Buyer</th><th>Seller</th></tr>` +
+ sales.map(s => `<tr><td>${esc(s.date)}</td><td>${usd0(s.price)}</td>
+ <td>${esc(s.buyer || '—')}</td><td>${esc(s.seller || '—')}</td></tr>`).join('') +
+ `</table><div class="note">${esc(d.property_history.note)}</div>`
+ : d.property_history.last_recording_date
? stats([['Last Recording Date', esc(d.property_history.last_recording_date)],
['Sale Price', usd0(d.property_history.sale_price)]]) +
`<div class="note">${esc(d.property_history.note)}</div>`
diff --git a/src/enrich/firm_website_discovery.ts b/src/enrich/firm_website_discovery.ts
index d33d645..26e2b2c 100644
--- a/src/enrich/firm_website_discovery.ts
+++ b/src/enrich/firm_website_discovery.ts
@@ -1,24 +1,27 @@
/**
- * Firm website discovery via DuckDuckGo HTML search (free, no API key).
+ * Firm website discovery via free HTML search — Brave Search primary,
+ * DuckDuckGo html endpoint fallback. No API keys, $0.
*
* For firms with no firm_site row yet (biggest by agent_count first), query
- * html.duckduckgo.com for "<name> <city> <state> real estate", take the first
- * organic result whose domain isn't an aggregator (prefer one whose domain
- * contains a firm-name token), and store it in firm_site (discovery_method='ddg').
- * Misses are recorded too (url NULL, crawl_status='no_url') so reruns advance
- * down the list instead of re-querying the same firms.
+ * "<name> <city> <state> real estate", take the first organic result whose
+ * domain isn't an aggregator/portal/MLS-board (preferring one whose domain
+ * echoes a firm-name token), and store it in firm_site (discovery_method =
+ * engine that answered). Misses are recorded too (url NULL,
+ * crawl_status='no_url') so reruns advance down the list.
*
* Run: npm run discover:firms # default 200
* tsx src/enrich/firm_website_discovery.ts -- --limit=500
*
- * Polite: 2.5-4s jittered gap per query (1-2s trips DDG anomaly detection), 10s
- * timeout, custom UA, escalating backoff + abort on persistent throttle. $0.
+ * Polite: 2.5-4s jittered gap per query, 10s timeout, real-browser UA.
+ * Both engines bot-gate: Brave 429s, DDG serves an HTTP-202 "anomaly" page
+ * (which must NOT be recorded as a legitimate no-result miss — that bug
+ * poisoned the first run). Escalating backoff, abort after 3 consecutive
+ * throttled firms.
*/
import 'dotenv/config';
-import { chromium, type Browser, type Page } from 'playwright';
import { pool, query } from '../../db/pool.ts';
-const UA = 'Mozilla/5.0 (Macintosh; Intel Mac OS X 14_0) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/126.0.0.0 Safari/537.36 usrealestate/0.1 (firm-website-discovery; +local)';
+const UA = 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/126.0.0.0 Safari/537.36';
const TIMEOUT_MS = 10_000;
const BLOCK = new Set([
@@ -29,6 +32,8 @@ const BLOCK = new Set([
'coldwellbankerhomes.com', // brand-level portal, not the local firm's site
'har.com', 'onekeymls.com', 'streeteasy.com', 'homefinder.com',
'landwatch.com', 'land.com', 'loopnet.com', 'crexi.com',
+ // Realtor-board / MLS office directories
+ 'pbbor.com', 'cpar.us', 'miamire.com', 'onehome.com', 'realtyna.com',
// Social
'facebook.com', 'instagram.com', 'linkedin.com', 'twitter.com', 'x.com',
'youtube.com', 'tiktok.com', 'pinterest.com', 'reddit.com',
@@ -36,7 +41,7 @@ const BLOCK = new Set([
'yelp.com', 'bbb.org', 'yellowpages.com', 'whitepages.com', 'manta.com',
'bizapedia.com', 'opencorporates.com', 'dnb.com', 'zoominfo.com',
'crunchbase.com', 'glassdoor.com', 'indeed.com', 'mapquest.com',
- 'wikipedia.org', 'wikidata.org', 'google.com', 'duckduckgo.com',
+ 'wikipedia.org', 'wikidata.org', 'google.com', 'duckduckgo.com', 'brave.com',
'ratemyagent.com', 'fastexpert.com', 'realtyrates.com',
]);
@@ -47,6 +52,7 @@ function host(u: string): string | null {
function blocked(h: string): boolean {
for (const root of BLOCK) if (h === root || h.endsWith('.' + root)) return true;
if (h.endsWith('.gov')) return true;
+ if (h.includes('mls')) return true; // nystatemls.com, floridakeysmls.com, stellarmls.com, …
return false;
}
@@ -64,21 +70,34 @@ function domainMatchesName(h: string, name: string): boolean {
class ThrottleError extends Error {}
-async function ddgSearch(q: string): Promise<string[]> {
- const url = 'https://html.duckduckgo.com/html/?q=' + encodeURIComponent(q);
+async function fetchHtml(url: string): Promise<string> {
const res = await fetch(url, {
- headers: { 'User-Agent': UA, 'Accept': 'text/html', 'Accept-Language': 'en-US,en;q=0.9' },
+ headers: { 'User-Agent': UA, 'Accept': 'text/html,application/xhtml+xml', 'Accept-Language': 'en-US,en;q=0.9' },
redirect: 'follow',
signal: AbortSignal.timeout(TIMEOUT_MS),
});
- // DDG's bot-challenge page comes back as HTTP 202 with an "anomaly" body and
- // zero results — must NOT be treated as a legitimate no-result miss.
- if (res.status === 202) throw new ThrottleError('ddg 202 anomaly');
- if (!res.ok) throw new Error(`ddg ${res.status}`);
- const html = await res.text();
+ if (res.status === 429 || res.status === 403 || res.status === 202) throw new ThrottleError(`http ${res.status}`);
+ if (!res.ok) throw new Error(`http ${res.status}`);
+ return res.text();
+}
+
+// Brave SERP: each organic hit is a block flagged data-type="web"; the first
+// href inside the block is the target URL.
+async function braveSearch(q: string): Promise<string[]> {
+ const html = await fetchHtml('https://search.brave.com/search?q=' + encodeURIComponent(q));
+ if (/verifying you are human|challenge-platform/i.test(html)) throw new ThrottleError('brave challenge');
+ const out: string[] = [];
+ for (const blk of html.split('data-type="web"').slice(1)) {
+ const m = blk.match(/href="(https?:\/\/[^"]+)"/);
+ if (m) out.push(m[1].replace(/&/g, '&'));
+ }
+ return out.slice(0, 10);
+}
+
+// DDG html endpoint: <a class="result__a" href="/l/?uddg=<encoded>">.
+async function ddgSearch(q: string): Promise<string[]> {
+ const html = await fetchHtml('https://html.duckduckgo.com/html/?q=' + encodeURIComponent(q));
if (/anomaly-modal|anomaly\.js|challenge-form/i.test(html)) throw new ThrottleError('ddg anomaly page');
- // Organic results: <a rel="nofollow" class="result__a" href="...">. Targets are
- // usually wrapped as /l/?uddg=<encoded>. Regex parse — no cheerio dep here.
const out: string[] = [];
const tagRe = /<a[^>]+class="[^"]*result__a[^"]*"[^>]*>/g;
let m: RegExpExecArray | null;
@@ -96,23 +115,13 @@ async function ddgSearch(q: string): Promise<string[]> {
return out.slice(0, 10);
}
-// Browser-mode search: DDG's JS site via real Chromium. The plain-fetch html
-// endpoint anomaly-flags this IP quickly (HTTP 202 challenge); a real browser
-// passes. One shared page, new search per firm.
-async function ddgBrowserSearch(page: Page, q: string): Promise<string[]> {
- await page.goto('https://duckduckgo.com/?q=' + encodeURIComponent(q) + '&ia=web', {
- waitUntil: 'domcontentloaded', timeout: 20_000,
- });
+async function searchWeb(q: string): Promise<{ urls: string[]; engine: string }> {
try {
- await page.waitForSelector('[data-testid="result-title-a"], article a[href^="http"]', { timeout: 8000 });
- } catch {
- const body = (await page.textContent('body')) || '';
- if (/unfortunately|bots|anomaly|browser check/i.test(body)) throw new ThrottleError('ddg browser challenge');
- return []; // genuinely no results
+ return { urls: await braveSearch(q), engine: 'brave' };
+ } catch (e) {
+ if (!(e instanceof ThrottleError)) throw e;
}
- const hrefs: string[] = await page.$$eval('[data-testid="result-title-a"]',
- els => els.map(e => (e as HTMLAnchorElement).href).filter(h => /^https?:\/\//.test(h)));
- return hrefs.slice(0, 10);
+ return { urls: await ddgSearch(q), engine: 'ddg' };
}
function pickWinner(urls: string[], firmName: string): string | null {
@@ -132,7 +141,7 @@ async function main() {
const run = await query<{ id: number }>(
`INSERT INTO ingest_runs (source, notes) VALUES ('firm_discovery', $1) RETURNING id`,
- [`ddg website discovery, batch limit ${limit}`],
+ [`brave+ddg website discovery, batch limit ${limit}`],
);
const runId = run.rows[0].id;
@@ -148,35 +157,18 @@ async function main() {
console.log(`[discover] run #${runId} · ${queue.length} firms · jittered 2.5-4s/query`);
let ok = 0, miss = 0, err = 0, consecThrottle = 0;
- let engine: 'fetch' | 'browser' = process.argv.includes('--engine=browser') ? 'browser' : 'fetch';
- let browser: Browser | null = null;
- let page: Page | null = null;
- const ensureBrowser = async () => {
- if (!page) {
- browser = await chromium.launch({ headless: true });
- page = await (await browser.newContext({ userAgent: UA.replace(/ usrealestate.*$/, ''), viewport: { width: 1280, height: 900 } })).newPage();
- }
- return page;
- };
-
for (let i = 0; i < queue.length; i++) {
const f = queue[i];
const q = `${f.name} ${f.hq_city || ''} ${f.license_state} real estate`.replace(/\s+/g, ' ').trim();
- let urls: string[] | null = null;
- // Up to 3 attempts; a plain-fetch throttle escalates to browser mode first,
- // then to timed backoff. A firm is only recorded as a miss when DDG actually
- // returned a results page.
- for (let attempt = 0; attempt < 3 && urls === null; attempt++) {
+ let result: { urls: string[]; engine: string } | null = null;
+ // Up to 3 attempts with escalating backoff on throttle; a firm is only
+ // recorded as a miss when an engine actually returned a results page.
+ for (let attempt = 0; attempt < 3 && result === null; attempt++) {
try {
- urls = engine === 'fetch' ? await ddgSearch(q) : await ddgBrowserSearch(await ensureBrowser(), q);
+ result = await searchWeb(q);
consecThrottle = 0;
} catch (e: any) {
- if (e instanceof ThrottleError || /ddg (403|429)/.test(e.message || '')) {
- if (engine === 'fetch') {
- console.log(` [throttle] ${e.message} — switching to browser engine`);
- engine = 'browser';
- continue;
- }
+ if (e instanceof ThrottleError) {
const wait = [30_000, 90_000, 180_000][attempt];
console.log(` [throttle] ${e.message} — backing off ${wait / 1000}s (attempt ${attempt + 1}/3)`);
await new Promise(res => setTimeout(res, wait));
@@ -186,30 +178,30 @@ async function main() {
}
}
}
- if (urls === null) {
+ if (result === null) {
err++;
consecThrottle++;
console.log(` [${i + 1}/${queue.length}] ⚠ ${f.name.slice(0, 44)} (search failed, not recorded)`);
if (consecThrottle >= 3) {
- console.log('[discover] 3 consecutive throttled firms — aborting run to avoid hammering DDG');
+ console.log('[discover] 3 consecutive throttled firms — aborting run');
break;
}
} else {
- const winner = pickWinner(urls, f.name);
+ const winner = pickWinner(result.urls, f.name);
if (winner) {
ok++;
await query(
- `INSERT INTO firm_site (firm_id, url, discovery_method) VALUES ($1, $2, 'ddg')
+ `INSERT INTO firm_site (firm_id, url, discovery_method) VALUES ($1, $2, $3)
ON CONFLICT (firm_id) DO NOTHING`,
- [f.id, winner],
+ [f.id, winner, result.engine],
);
console.log(` [${i + 1}/${queue.length}] ✓ ${f.name.padEnd(44).slice(0, 44)} → ${winner}`);
} else {
miss++;
await query(
- `INSERT INTO firm_site (firm_id, url, discovery_method, crawl_status) VALUES ($1, NULL, 'ddg', 'no_url')
+ `INSERT INTO firm_site (firm_id, url, discovery_method, crawl_status) VALUES ($1, NULL, $2, 'no_url')
ON CONFLICT (firm_id) DO NOTHING`,
- [f.id],
+ [f.id, result.engine],
);
console.log(` [${i + 1}/${queue.length}] · ${f.name.slice(0, 44)} (no credible result)`);
}
@@ -222,8 +214,7 @@ async function main() {
notes = notes || $4 WHERE id = $1`,
[runId, ok, miss, ` · ${ok} found, ${miss} no-result, ${err} search-errors`],
);
- console.log(`[discover] done · ${ok} found · ${miss} no-result · ${err} errors · engine=${engine}`);
- if (browser) await (browser as Browser).close().catch(() => {});
+ console.log(`[discover] done · ${ok} found · ${miss} no-result · ${err} errors`);
await pool.end();
}
diff --git a/src/ingest/parcels/engine.ts b/src/ingest/parcels/engine.ts
new file mode 100644
index 0000000..8e68df5
--- /dev/null
+++ b/src/ingest/parcels/engine.ts
@@ -0,0 +1,26 @@
+/**
+ * Parcel-ingest dispatcher: npm run ingest:parcels <county>
+ * Counties: nyc (five boroughs, PLUTO) | king (King WA EXTR) | cook (Cook IL Socrata)
+ * Per-county adapters own download + parse + upsert + parcel_source registration.
+ */
+import { pool } from '../../../db/pool.ts';
+
+const ADAPTERS: Record<string, () => Promise<{ run: () => Promise<{ upserted: number }> }>> = {
+ nyc: async () => ({ run: (await import('./nyc_pluto.ts')).ingestNycPluto }),
+ king: async () => ({ run: (await import('./king_wa.ts')).ingestKing }),
+ cook: async () => ({ run: (await import('./cook_il.ts')).ingestCook }),
+};
+
+async function main() {
+ const which = (process.argv[2] || '').toLowerCase();
+ if (!ADAPTERS[which]) {
+ console.error(`usage: npm run ingest:parcels -- <${Object.keys(ADAPTERS).join('|')}>`);
+ process.exit(2);
+ }
+ const { run } = await ADAPTERS[which]();
+ const { upserted } = await run();
+ console.log(`[parcels:${which}] done, ${upserted} upserted`);
+ await pool.end();
+}
+
+main().catch(e => { console.error(e); process.exit(1); });
diff --git a/src/ingest/parcels/king_wa.ts b/src/ingest/parcels/king_wa.ts
new file mode 100644
index 0000000..a9a60f1
--- /dev/null
+++ b/src/ingest/parcels/king_wa.ts
@@ -0,0 +1,181 @@
+/**
+ * King County WA (Seattle, FIPS 53033) parcel ingest from the assessor's open
+ * EXTR bulk extracts (aqua.kingcounty.gov/extranet/assessor — no auth, $0):
+ * Parcel.zip -> EXTR_Parcel.csv (628k universe: zoning, district, present use)
+ * Residential Building.zip -> EXTR_ResBldg.csv (533k: address, beds/baths, sqft, yr built)
+ * Real Property Account.zip -> EXTR_RPAcct_NoName.csv (740k: appraised land/imps values, tax year)
+ * Real Property Sales.zip -> EXTR_RPSale.csv (2.4M: full deed history w/ prices — REAL sale records)
+ * Lookup.zip -> EXTR_LookUp.csv (LUType 102 = PresentUse descriptions)
+ *
+ * The account extract is the "_NoName" variant (taxpayer names withheld), so
+ * owner_name is the BUYER on the most recent recorded deed (public record),
+ * labeled as such in extra.owner_source.
+ *
+ * Run: npm run ingest:parcels king (NODE_OPTIONS=--max-old-space-size=6144 recommended)
+ */
+import { existsSync } from 'node:fs';
+import { join } from 'node:path';
+import { execFileSync } from 'node:child_process';
+import { pool } from '../../../db/pool.ts';
+import { openRun, closeRun, DOWNLOADS, download } from '../run.ts';
+import { csvObjects } from './csv.ts';
+import { upsertParcels, registerParcelSource, normAddress, type ParcelUpsertRow } from './upsert.ts';
+
+const FIPS = '53033';
+const BASE = 'https://aqua.kingcounty.gov/extranet/assessor/';
+const DIR = join(DOWNLOADS, 'king');
+const FILES: [zip: string, csv: string][] = [
+ ['Parcel.zip', 'EXTR_Parcel.csv'],
+ ['Residential%20Building.zip', 'EXTR_ResBldg.csv'],
+ ['Real%20Property%20Account.zip', 'EXTR_RPAcct_NoName.csv'],
+ ['Real%20Property%20Sales.zip', 'EXTR_RPSale.csv'],
+ ['Lookup.zip', 'EXTR_LookUp.csv'],
+];
+
+const num = (v: string | undefined) => { const n = Number(v); return Number.isFinite(n) && n !== 0 ? n : null; };
+const num0 = (v: string | undefined) => { const n = Number(v); return Number.isFinite(n) ? n : 0; };
+const pinOf = (o: Record<string, string>) => o.major.padStart(6, '0') + o.minor.padStart(4, '0');
+
+/** MM/DD/YYYY -> YYYY-MM-DD (null if unparseable) */
+function isoDate(d: string): string | null {
+ const m = d.match(/^(\d{2})\/(\d{2})\/(\d{4})$/);
+ return m ? `${m[3]}-${m[1]}-${m[2]}` : null;
+}
+
+interface Sale { date: string; price: number; buyer: string; seller: string; instrument: string }
+
+async function ensureFiles() {
+ for (const [zip, csv] of FILES) {
+ if (existsSync(join(DIR, csv))) continue;
+ const { path } = await download(BASE + zip, 'king/' + zip.replace(/%20/g, '_'), { reuseCached: true });
+ execFileSync('unzip', ['-o', '-q', path, '-d', DIR]);
+ }
+}
+
+export async function ingestKing(): Promise<{ upserted: number }> {
+ const runId = await openRun('parcel_king_wa', BASE);
+ try {
+ await ensureFiles();
+
+ // PresentUse code -> description
+ const useDesc = new Map<string, string>();
+ for await (const o of csvObjects(join(DIR, 'EXTR_LookUp.csv'), 'latin1')) {
+ if (o.lutype.trim() === '102') useDesc.set(o.luitem.trim(), o.ludescription.trim());
+ }
+
+ // Residential buildings: address + physical facts (bldg 1 wins; units summed)
+ interface Res { address: string; zip: string; beds: number | null; baths: number | null;
+ sqft: number | null; year_built: number | null; units: number; stories: number | null; bldg: number }
+ const res = new Map<string, Res>();
+ for await (const o of csvObjects(join(DIR, 'EXTR_ResBldg.csv'), 'latin1')) {
+ const pin = pinOf(o);
+ const bldg = num0(o.bldgnbr);
+ const addr = [o.buildingnumber, o.fraction, o.directionprefix, o.streetname, o.streettype, o.directionsuffix]
+ .map(s => (s || '').trim()).filter(Boolean).join(' ');
+ const units = num0(o.nbrlivingunits);
+ const prev = res.get(pin);
+ if (prev) {
+ prev.units += units;
+ if (bldg < prev.bldg) Object.assign(prev, { bldg, address: addr || prev.address });
+ continue;
+ }
+ res.set(pin, {
+ address: addr, zip: (o.zipcode || '').trim() || '', bldg,
+ beds: num(o.bedrooms),
+ baths: (num0(o.bathfullcount) + 0.75 * num0(o.bath3qtrcount) + 0.5 * num0(o.bathhalfcount)) || null,
+ sqft: num(o.sqfttotliving), year_built: num(o.yrbuilt),
+ units: units || 1, stories: num(o.stories),
+ });
+ }
+ console.log(`[king] resbldg: ${res.size} pins`);
+
+ // Account values: latest bill year per pin (+ taxpayer mailing address, name withheld)
+ interface Acct { land: number | null; imps: number | null; year: number; mailing: string | null }
+ const acct = new Map<string, Acct>();
+ for await (const o of csvObjects(join(DIR, 'EXTR_RPAcct_NoName.csv'), 'latin1')) {
+ const pin = pinOf(o);
+ const year = num0(o.billyr);
+ const prev = acct.get(pin);
+ if (prev && prev.year >= year) continue;
+ const mailing = [o.addrline, o.citystate, o.zipcode].map(s => (s || '').trim()).filter(Boolean).join(', ');
+ acct.set(pin, { land: num(o.apprlandval), imps: num(o.apprimpsval), year, mailing: mailing || null });
+ }
+ console.log(`[king] rpacct: ${acct.size} pins`);
+
+ // Sales: keep 10 most recent priced sales per pin + latest deed buyer (any price)
+ const sales = new Map<string, Sale[]>();
+ const lastDeed = new Map<string, { date: string; buyer: string }>();
+ let saleRows = 0;
+ for await (const o of csvObjects(join(DIR, 'EXTR_RPSale.csv'), 'latin1')) {
+ saleRows++;
+ const pin = pinOf(o);
+ const date = isoDate(o.documentdate.trim());
+ if (!date) continue;
+ const buyer = o.buyername.trim();
+ if (buyer) {
+ const prev = lastDeed.get(pin);
+ if (!prev || date > prev.date) lastDeed.set(pin, { date, buyer });
+ }
+ const price = num(o.saleprice);
+ if (!price) continue;
+ const s: Sale = { date, price, buyer, seller: o.sellername.trim(), instrument: o.saleinstrument.trim() };
+ const list = sales.get(pin);
+ if (!list) { sales.set(pin, [s]); continue; }
+ list.push(s);
+ if (list.length > 14) { list.sort((a, b) => b.date.localeCompare(a.date)); list.length = 10; }
+ }
+ for (const list of sales.values()) list.sort((a, b) => b.date.localeCompare(a.date));
+ console.log(`[king] rpsale: ${saleRows} rows, ${sales.size} pins with priced sales`);
+
+ // Universe pass: EXTR_Parcel drives; join everything, batch upsert
+ let upserted = 0;
+ let batch: ParcelUpsertRow[] = [];
+ for await (const o of csvObjects(join(DIR, 'EXTR_Parcel.csv'), 'latin1')) {
+ const pin = pinOf(o);
+ const r = res.get(pin);
+ const a = acct.get(pin);
+ const sl = (sales.get(pin) || []).slice(0, 10);
+ const deed = lastDeed.get(pin);
+ const land = a?.land ?? null, imps = a?.imps ?? null;
+ const city = (o.districtname || '').trim() || null;
+ const address = r?.address ? normAddress(r.address) : null;
+ batch.push({
+ county_fips: FIPS, source_id: pin,
+ address, norm_address: address, city, zip: r?.zip || null,
+ lat: null, lng: null, // EXTR carries no coordinates; county centroid at render
+ year_built: r?.year_built ?? null, sqft: r?.sqft ?? null,
+ beds: r?.beds ?? null, baths: r?.baths ?? null, units: r?.units ?? null,
+ use_desc: useDesc.get((o.presentuse || '').trim()) || (o.presentuse ? `Use ${o.presentuse.trim()}` : null),
+ land_value: land, improvement_value: imps,
+ total_value: land != null || imps != null ? (land ?? 0) + (imps ?? 0) : null,
+ tax_year: a ? String(a.year) : null,
+ owner_name: deed?.buyer ?? null,
+ zoning: (o.currentzoning || '').trim() || null,
+ last_sale_date: sl[0]?.date ?? null, last_sale_price: sl[0]?.price ?? null,
+ extra: JSON.stringify({
+ sales: sl, prop_type: o.proptype, prop_name: (o.propname || '').trim() || undefined,
+ sqft_lot: num(o.sqftlot), stories: r?.stories ?? undefined,
+ taxpayer_mailing: a?.mailing ?? undefined,
+ owner_source: deed ? 'buyer on most recent recorded deed (EXTR_RPSale)' : undefined,
+ }),
+ });
+ if (batch.length >= 2000) { upserted += await upsertParcels(batch); batch = []; }
+ if (upserted % 100000 < 2000 && upserted) console.log(`[king] ${upserted} upserted`);
+ }
+ upserted += await upsertParcels(batch);
+ if (upserted < 400000) throw new Error(`only ${upserted} King parcels — expected ~628k, extract drift?`);
+
+ const n = await registerParcelSource(FIPS, BASE,
+ 'King County WA EXTR extracts — address/beds/baths/sqft (ResBldg), appraised values (RPAcct), zoning+use (Parcel), REAL sale history w/ prices (RPSale; owner = latest deed buyer, names file withheld)');
+ await closeRun(runId, 'ok', { upserted, notes: `King WA: ${upserted} parcels, ${sales.size} with priced sale history` });
+ console.log(`[king] ok: ${upserted} parcels (registry ${n})`);
+ return { upserted };
+ } catch (e: any) {
+ await closeRun(runId, 'failed', { notes: String(e.message || e) });
+ throw e;
+ }
+}
+
+if (import.meta.url === `file://${process.argv[1]}`) {
+ ingestKing().then(() => pool.end()).catch(e => { console.error(e); process.exit(1); });
+}
diff --git a/src/ingest/parcels/upsert.ts b/src/ingest/parcels/upsert.ts
new file mode 100644
index 0000000..f4cf3ff
--- /dev/null
+++ b/src/ingest/parcels/upsert.ts
@@ -0,0 +1,70 @@
+/**
+ * Shared chunked upsert into `parcel` — full column set (incl. beds/baths/sales,
+ * unlike the NYC-era subset). PG caps a statement at 65535 bind params;
+ * 23 cols -> chunks of 2000 rows = 46k params.
+ */
+import { query } from '../../../db/pool.ts';
+
+export interface ParcelUpsertRow {
+ county_fips: string; source_id: string;
+ address: string | null; norm_address: string | null; city: string | null; zip: string | null;
+ lat: number | null; lng: number | null;
+ year_built: number | null; sqft: number | null; beds: number | null; baths: number | null;
+ units: number | null; use_desc: string | null;
+ land_value: number | null; improvement_value: number | null; total_value: number | null;
+ tax_year: string | null; owner_name: string | null; zoning: string | null;
+ last_sale_date: string | null; last_sale_price: number | null;
+ extra: string | null; // JSON string
+}
+
+const COLS: (keyof ParcelUpsertRow)[] = ['county_fips', 'source_id', 'address', 'norm_address', 'city', 'zip',
+ 'lat', 'lng', 'year_built', 'sqft', 'beds', 'baths', 'units', 'use_desc',
+ 'land_value', 'improvement_value', 'total_value', 'tax_year', 'owner_name', 'zoning',
+ 'last_sale_date', 'last_sale_price', 'extra'];
+
+export async function upsertParcels(rows: ParcelUpsertRow[]): Promise<number> {
+ let done = 0;
+ for (let i = 0; i < rows.length; i += 2000) {
+ const chunk = rows.slice(i, i + 2000);
+ const params: unknown[] = [];
+ const values = chunk.map((r, j) => {
+ const b = j * COLS.length;
+ for (const c of COLS) params.push(r[c] ?? null);
+ return '(' + COLS.map((_, k) => `$${b + k + 1}`).join(',') + ')';
+ });
+ await query(
+ `INSERT INTO parcel (${COLS.join(',')}) VALUES ${values.join(',')}
+ ON CONFLICT (county_fips, source_id) DO UPDATE SET
+ ${COLS.filter(c => c !== 'county_fips' && c !== 'source_id').map(c => `${c}=EXCLUDED.${c}`).join(', ')}`,
+ params,
+ );
+ done += chunk.length;
+ }
+ return done;
+}
+
+/** Uppercase, collapse whitespace, strip ordinal suffixes so queries and stored
+ * addresses normalize the same way ("61ST AVE S" -> "61 AVE S"). Must mirror
+ * normAddressQuery in src/server/property.ts. */
+export function normAddress(addr: string): string {
+ return addr.trim().toUpperCase()
+ .replace(/[.,#]/g, ' ')
+ .replace(/\b(\d+)(?:ST|ND|RD|TH)\b/g, '$1')
+ .replace(/\s+/g, ' ')
+ .trim();
+}
+
+export async function registerParcelSource(
+ countyFips: string, pathOrUrl: string, notes: string,
+): Promise<number> {
+ const cnt = await query<{ n: string }>(`SELECT COUNT(*)::text AS n FROM parcel WHERE county_fips=$1`, [countyFips]);
+ const n = Number(cnt.rows[0].n);
+ await query(
+ `INSERT INTO parcel_source (county_fips, source_kind, path_or_url, parcel_count, notes, loaded_at)
+ VALUES ($1,'pg',$2,$3,$4,NOW())
+ ON CONFLICT (county_fips) DO UPDATE SET source_kind='pg', path_or_url=EXCLUDED.path_or_url,
+ parcel_count=EXCLUDED.parcel_count, notes=EXCLUDED.notes, loaded_at=NOW()`,
+ [countyFips, pathOrUrl, n, notes],
+ );
+ return n;
+}
diff --git a/src/server/property.ts b/src/server/property.ts
index 9fa4466..f65e323 100644
--- a/src/server/property.ts
+++ b/src/server/property.ts
@@ -170,6 +170,47 @@ const asMatch = (p: any) => ({
bedrooms: p.beds, bathrooms: p.baths, units: p.units, use: p.use_desc,
assessed_value: p.total_value, owner: p.owner_name, zoning: p.zoning,
});
+interface PgCountyMeta {
+ assessor: { name: string; phone: string; website: string };
+ detailsNote: string; historyNote: string; taxNote: string; ownerNote: string; zoningNote: string;
+}
+const NYC_META: PgCountyMeta = {
+ assessor: { name: 'NYC Department of Finance', phone: '311',
+ website: 'https://www.nyc.gov/site/finance/property/property.page' },
+ detailsNote: 'NYC MapPLUTO is lot-level — building sqft shown; unit-level beds/baths not in PLUTO.',
+ historyNote: 'Sale/deed chain from ACRIS pending; PLUTO carries current attributes only.',
+ taxNote: 'NYC assessed values from MapPLUTO (current version).',
+ ownerNote: 'Owner of record from NYC MapPLUTO.',
+ zoningNote: 'Primary zoning district (zonedist1) from NYC MapPLUTO.',
+};
+const PG_COUNTY_META: Record<string, PgCountyMeta> = {
+ '36005': NYC_META, '36047': NYC_META, '36061': NYC_META, '36081': NYC_META, '36085': NYC_META,
+ '53033': {
+ assessor: { name: 'King County Department of Assessments', phone: '(206) 296-7300',
+ website: 'https://kingcounty.gov/en/dept/assessments' },
+ detailsNote: 'King County assessor EXTR extracts (residential building record).',
+ historyNote: 'Recorded deed history with prices from King County EXTR_RPSale (10 most recent priced sales).',
+ taxNote: 'Appraised land + improvement values from King County real property accounts (latest bill year).',
+ ownerNote: 'Owner from King County records.',
+ zoningNote: 'Current zoning from the King County parcel record.',
+ },
+ '17031': {
+ assessor: { name: 'Cook County Assessor', phone: '(312) 443-7550',
+ website: 'https://www.cookcountyassessor.com' },
+ detailsNote: 'Cook County Assessor open data (parcel universe + parcel addresses).',
+ historyNote: 'Deed/sale history pending (Cook County Recorder feed); assessed values shown under Tax History.',
+ taxNote: 'Cook County certified/mailed assessed values (latest tax year on the open-data portal).',
+ ownerNote: 'Owner/taxpayer of record from Cook County parcel addresses dataset.',
+ zoningNote: 'Zoning from the Cook County parcel record.',
+ },
+ default: {
+ assessor: { name: 'County Assessor', phone: '', website: '' },
+ detailsNote: 'County parcel record.', historyNote: 'Sale history pending for this county.',
+ taxNote: 'Latest assessed values on file.', ownerNote: 'Owner of record from the county roll.',
+ zoningNote: 'Zoning from the county parcel record.',
+ },
+};
+
async function pgSearchRows(county: string, q: string): Promise<any[]> {
const norm = normAddressQuery(q);
const exact = await query<PgParcel>(
@@ -273,49 +314,55 @@ export function mountProperty(app: Express) {
const src0 = await parcelSourceFor(county);
if (!src0) return res.status(404).json({ error: `no parcel source loaded for county ${county}` });
- // ── PG-backed counties (NYC PLUTO: owner + zoning + assessed values present) ──
+ // ── PG-backed counties (NYC PLUTO, King WA, Cook IL: owner/zoning/values/sales where present) ──
if (src0.source_kind === 'pg') {
const found = await pgProperty(county, loc);
if (!found) return res.status(404).json({ error: 'parcel not found: ' + loc });
const { p, comps } = found;
const ctx = await countyContext(county);
+ const meta = PG_COUNTY_META[county] ?? PG_COUNTY_META.default;
const total = p.total_value != null ? +p.total_value : null;
+ const extra = p.extra ?? {};
+ const sales: any[] = Array.isArray(extra.sales) ? extra.sales : [];
return res.json({
county, region_key: ctx.region?.canonical_key ?? null,
property_details: {
ain: p.source_id, address: p.address, use: p.use_desc, use_category: p.use_desc,
year_built: p.year_built || null, effective_year_built: null,
- sqft: p.sqft || null, bedrooms: null, bathrooms: null, units: p.units,
- note: 'NYC MapPLUTO is lot-level — building sqft shown; unit-level beds/baths not in PLUTO.',
- },
- property_history: {
- last_recording_date: null, sale_price: null,
- note: 'Sale/deed chain from ACRIS pending (M-P2 next); PLUTO carries current attributes only.',
+ sqft: p.sqft || null, bedrooms: p.beds, bathrooms: p.baths, units: p.units,
+ note: meta.detailsNote,
},
+ property_history: sales.length
+ ? {
+ last_recording_date: p.last_sale_date, sale_price: p.last_sale_price != null ? +p.last_sale_price : null,
+ sales: sales.map(s => ({ date: s.date, price: s.price, buyer: s.buyer || null, seller: s.seller || null })),
+ note: meta.historyNote,
+ }
+ : { last_recording_date: p.last_sale_date, sale_price: p.last_sale_price != null ? +p.last_sale_price : null,
+ note: meta.historyNote },
tax_history: {
rolls: [{ roll_year: p.tax_year, total_value: total,
land_value: p.land_value != null ? +p.land_value : null,
improvement_value: p.improvement_value != null ? +p.improvement_value : null }],
- note: 'NYC assessed values from MapPLUTO (' + (p.tax_year || 'current') + ' version).',
+ note: meta.taxNote,
},
contact_information: { phone: null, email: null,
- note: 'Owner mailing/contact via NYC ACRIS pending; owner name shown below.' },
+ note: 'Owner phone/email are not in public rolls; owner name/mailing under Ownership.' },
ownership_information: {
- owner_name: p.owner_name, mailing_address: null, owner_occupied: null,
- note: p.owner_name ? 'Owner of record from NYC MapPLUTO.' : PENDING,
+ owner_name: p.owner_name, mailing_address: extra.taxpayer_mailing ?? extra.mailing_address ?? null,
+ owner_occupied: null,
+ note: p.owner_name ? (extra.owner_source ? `Owner: ${extra.owner_source}.` : meta.ownerNote) : PENDING,
},
- zoning: { code: p.zoning,
- note: p.zoning ? 'Primary zoning district (zonedist1) from NYC MapPLUTO.' : PENDING },
+ zoning: { code: p.zoning, note: p.zoning ? meta.zoningNote : PENDING },
contacts: {
- county_assessor: { name: 'NYC Department of Finance', phone: '311',
- website: 'https://www.nyc.gov/site/finance/property/property.page' },
- brokers_link: '/brokers.html?state=NY',
- note: 'Local brokers from the NY licensing registry; per-parcel listing agent pending.',
+ county_assessor: meta.assessor,
+ brokers_link: '/brokers.html?state=' + (ctx.region?.state_code ?? ''),
+ note: 'Local brokers from the state licensing registry; per-parcel listing agent pending.',
},
map: {
lat: p.lat ?? ctx.region?.lat ?? null, lng: p.lng ?? ctx.region?.lng ?? null,
precision: p.lat ? 'parcel' : 'county_centroid',
- note: p.lat ? 'Parcel coordinates from MapPLUTO.' : 'County centroid (parcel geocode missing).',
+ note: p.lat ? 'Parcel coordinates from the county source.' : 'County centroid (no parcel coordinates in this extract).',
},
comps,
...buildContextSections(county, ctx),
← 2d46b43 NYC search hardening: ordinal-stripping address normalizatio
·
back to Nationalrealestate
·
Broker wave-2: DE DPR adapter ingested (29,895 brokers + 997 93eff11 →