← back to Nationalrealestate
TK-16: add Parcel Explorer web viewer (/parcels.html + /api/parcels)
d6f64ff7abc3aad66899fb7d0b5bcd48767b7b72 · 2026-08-10 17:04:30 -0700 · Steve Abrams
Unified map+table viewer over the parcel/parcel_event tables the county ingesters fill.
- mountParcels API: /api/parcels/coverage (county facets+counts, cached), /api/parcels
(county-scoped filtered/sorted/paged grid off the pkey), /api/parcels/map (capped lat/lng
points), /api/parcels/:fips/:sid (detail + recorded-deed history from parcel_event).
- public/parcels.html: dark/gold house theme, Leaflet dark map (points colored by value,
gold=priced), county facet sidebar w/ totals, search (addr/APN/city/owner), sort dropdown
+ density slider + resizable/toggleable columns (col-resize.js, persisted) per house rules,
drill-down detail drawer w/ deed history (OR) or 'coverage-only AB-1785' note (CA), deep-link
?county=&sid=. Every datum drills to detail.
Verified vs local usre (11.5M parcels/51 counties/3.28M priced): all 4 endpoints return real
data, page 200, inline JS node --check clean. tsc clean. Local pm2 restarted. NOT pushed to prod.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Files touched
A public/parcels.htmlM src/server/index.tsA src/server/parcels.ts
Diff
commit d6f64ff7abc3aad66899fb7d0b5bcd48767b7b72
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Mon Aug 10 17:04:30 2026 -0700
TK-16: add Parcel Explorer web viewer (/parcels.html + /api/parcels)
Unified map+table viewer over the parcel/parcel_event tables the county ingesters fill.
- mountParcels API: /api/parcels/coverage (county facets+counts, cached), /api/parcels
(county-scoped filtered/sorted/paged grid off the pkey), /api/parcels/map (capped lat/lng
points), /api/parcels/:fips/:sid (detail + recorded-deed history from parcel_event).
- public/parcels.html: dark/gold house theme, Leaflet dark map (points colored by value,
gold=priced), county facet sidebar w/ totals, search (addr/APN/city/owner), sort dropdown
+ density slider + resizable/toggleable columns (col-resize.js, persisted) per house rules,
drill-down detail drawer w/ deed history (OR) or 'coverage-only AB-1785' note (CA), deep-link
?county=&sid=. Every datum drills to detail.
Verified vs local usre (11.5M parcels/51 counties/3.28M priced): all 4 endpoints return real
data, page 200, inline JS node --check clean. tsc clean. Local pm2 restarted. NOT pushed to prod.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
---
public/parcels.html | 314 ++++++++++++++++++++++++++++++++++++++++++++++++++
src/server/index.ts | 2 +
src/server/parcels.ts | 123 ++++++++++++++++++++
3 files changed, 439 insertions(+)
diff --git a/public/parcels.html b/public/parcels.html
new file mode 100644
index 0000000..42c9fd3
--- /dev/null
+++ b/public/parcels.html
@@ -0,0 +1,314 @@
+<!doctype html>
+<html lang="en">
+<head>
+<meta charset="utf-8">
+<meta name="viewport" content="width=device-width,initial-scale=1">
+<title>Parcel Explorer · USRealEstate</title>
+<link rel="stylesheet" href="https://unpkg.com/leaflet@1.9.4/dist/leaflet.css">
+<style>
+ :root{--bg:#0a0d13;--panel:#11151d;--line:#222936;--fg:#e8ecf2;--muted:#8a93a3;--gold:#c8a24b;--rowh:30px;--fs:13px;}
+ *{box-sizing:border-box;}
+ html,body{margin:0;height:100%;background:var(--bg);color:var(--fg);font:14px/1.5 -apple-system,Segoe UI,Roboto,sans-serif;overflow:hidden;}
+ a{color:var(--gold);text-decoration:none;} a:hover{text-decoration:underline;}
+ /* topbar */
+ #topbar{position:fixed;inset:0 0 auto 0;z-index:1200;display:flex;align-items:center;gap:16px;
+ padding:9px 16px;background:rgba(17,21,29,.96);border-bottom:1px solid var(--line);backdrop-filter:blur(6px);}
+ .brand{font-size:15px;font-weight:700;white-space:nowrap;} .brand b{color:var(--gold);}
+ nav{display:flex;gap:13px;font-size:13px;} nav a{color:var(--muted);} nav a:hover,nav a.active{color:var(--gold);text-decoration:none;}
+ #controls{display:flex;gap:8px;align-items:center;margin-left:auto;flex-wrap:wrap;}
+ #controls input,#controls select{background:var(--bg);border:1px solid var(--line);color:var(--fg);border-radius:8px;
+ padding:6px 10px;font:12px inherit;}
+ #controls input:focus,#controls select:focus{outline:none;border-color:var(--gold);}
+ #q{width:190px;}
+ .toggle{display:flex;align-items:center;gap:5px;font-size:12px;color:var(--muted);cursor:pointer;user-select:none;}
+ #density{width:90px;accent-color:var(--gold);}
+ .lbl{font-size:11px;color:var(--muted);}
+ /* layout */
+ #wrap{position:fixed;inset:49px 0 0 0;display:flex;}
+ #side{width:250px;flex:none;border-right:1px solid var(--line);background:var(--panel);overflow-y:auto;padding:10px;}
+ #side h3{margin:4px 0 8px;font-size:11px;letter-spacing:.08em;text-transform:uppercase;color:var(--gold);}
+ #totals{font-size:12px;color:var(--muted);margin-bottom:10px;font-variant-numeric:tabular-nums;line-height:1.7;}
+ #totals b{color:var(--fg);}
+ .cty{display:flex;justify-content:space-between;gap:6px;padding:5px 7px;border-radius:7px;cursor:pointer;font-size:12.5px;
+ border:1px solid transparent;}
+ .cty:hover{background:var(--bg);border-color:var(--line);}
+ .cty.sel{background:var(--bg);border-color:var(--gold);}
+ .cty .nm{white-space:nowrap;overflow:hidden;text-overflow:ellipsis;}
+ .cty .st{color:var(--muted);font-size:10px;}
+ .cty .ct{color:var(--gold);font-variant-numeric:tabular-nums;flex:none;}
+ .cty.priced .nm::after{content:" ◆";color:var(--gold);font-size:9px;}
+ #main{flex:1;display:flex;flex-direction:column;min-width:0;}
+ #map{height:44%;min-height:120px;background:var(--bg);border-bottom:1px solid var(--line);}
+ .leaflet-container{background:var(--bg);font:inherit;}
+ .basemap-dark{filter:brightness(.55) contrast(1.05) saturate(.5);}
+ #tablewrap{flex:1;overflow:auto;position:relative;}
+ table{border-collapse:collapse;width:100%;table-layout:fixed;font-size:var(--fs);font-variant-numeric:tabular-nums;}
+ thead th{position:sticky;top:0;z-index:2;background:#0d1119;border-bottom:1px solid var(--line);color:var(--muted);
+ text-align:left;padding:7px 9px;font-weight:600;white-space:nowrap;cursor:pointer;}
+ thead th:hover{color:var(--gold);}
+ tbody td{border-bottom:1px solid var(--line);padding:0 9px;height:var(--rowh);white-space:nowrap;overflow:hidden;
+ text-overflow:ellipsis;}
+ tbody tr{cursor:pointer;} tbody tr:hover{background:#141a24;} tbody tr.sel{background:#1a2130;}
+ .num{text-align:right;} .gold{color:var(--gold);}
+ #status{padding:6px 12px;border-top:1px solid var(--line);font-size:11px;color:var(--muted);background:var(--panel);
+ display:flex;gap:14px;align-items:center;}
+ #status button{background:var(--bg);border:1px solid var(--line);color:var(--muted);border-radius:6px;padding:3px 10px;cursor:pointer;font:11px inherit;}
+ #status button:hover:not(:disabled){color:var(--fg);border-color:var(--muted);} #status button:disabled{opacity:.4;cursor:default;}
+ #colmenu{position:relative;}
+ #colpop{display:none;position:absolute;bottom:26px;left:0;background:var(--panel);border:1px solid var(--line);border-radius:8px;
+ padding:8px;min-width:150px;box-shadow:0 6px 20px rgba(0,0,0,.5);z-index:20;}
+ #colpop.open{display:block;} #colpop label{display:flex;gap:7px;align-items:center;font-size:12px;padding:3px 4px;color:var(--fg);cursor:pointer;}
+ /* detail drawer */
+ #detail{position:fixed;top:49px;right:0;bottom:0;width:380px;max-width:92vw;background:var(--panel);border-left:1px solid var(--gold);
+ z-index:1100;transform:translateX(100%);transition:transform .18s ease;overflow-y:auto;padding:16px 18px;}
+ #detail.open{transform:translateX(0);}
+ #detail .x{position:absolute;top:12px;right:14px;color:var(--muted);cursor:pointer;font-size:18px;}
+ #detail h2{margin:0 6px 2px 0;font-size:17px;color:var(--gold);} #detail .sub{color:var(--muted);font-size:12px;margin-bottom:14px;}
+ .kv{display:grid;grid-template-columns:110px 1fr;gap:3px 10px;font-size:13px;margin-bottom:14px;}
+ .kv .k{color:var(--muted);} .kv .v{color:var(--fg);word-break:break-word;} .kv .v.gold{color:var(--gold);}
+ .deed{border:1px solid var(--line);border-radius:8px;padding:9px 11px;margin-bottom:8px;font-size:12.5px;}
+ .deed .amt{color:var(--gold);font-weight:700;font-size:14px;} .deed .dt{color:var(--muted);}
+ .deed .row{display:flex;justify-content:space-between;gap:8px;margin-top:3px;} .deed .row span{color:var(--muted);}
+ .badge{display:inline-block;font-size:10px;padding:1px 7px;border-radius:10px;border:1px solid var(--line);color:var(--muted);}
+ .badge.p{color:var(--gold);border-color:var(--gold);}
+ .empty{color:var(--muted);font-size:12px;padding:30px;text-align:center;}
+</style>
+</head>
+<body>
+<div id="topbar">
+ <div class="brand">US<b>RealEstate</b> · Parcel Explorer</div>
+ <nav><a href="/index.html">Map</a><a href="/markets.html">Markets</a><a href="/parcels.html" class="active">Parcels</a><a href="/brokers.html">Brokers</a></nav>
+ <div id="controls">
+ <input id="q" type="search" placeholder="Search address / APN / city / owner…" autocomplete="off">
+ <select id="sort" title="Sort">
+ <option value="">SKU / APN</option>
+ <option value="value_desc">Value ↓</option>
+ <option value="value_asc">Value ↑</option>
+ <option value="price_desc">Sale price ↓</option>
+ <option value="recent">Most recent sale</option>
+ <option value="address">Address A→Z</option>
+ <option value="added">Recently added</option>
+ </select>
+ <label class="toggle"><input type="checkbox" id="pricedOnly"> priced only</label>
+ <span class="lbl">density</span><input id="density" type="range" min="22" max="44" value="30">
+ </div>
+</div>
+
+<div id="wrap">
+ <aside id="side">
+ <div id="totals">Loading coverage…</div>
+ <h3>Counties <span class="lbl">(◆ = has sale prices)</span></h3>
+ <div id="counties"></div>
+ </aside>
+ <div id="main">
+ <div id="map"></div>
+ <div id="tablewrap"><div class="empty">Pick a county on the left to explore its parcels.</div></div>
+ <div id="status">
+ <span id="count">—</span>
+ <div id="colmenu"><button id="colbtn">Columns ▾</button><div id="colpop"></div></div>
+ <button id="prev" disabled>‹ Prev</button><button id="next" disabled>Next ›</button>
+ <span id="mapnote" style="margin-left:auto"></span>
+ </div>
+ </div>
+</div>
+
+<div id="detail"><span class="x" onclick="closeDetail()">✕</span><div id="detailBody"></div></div>
+
+<script src="https://unpkg.com/leaflet@1.9.4/dist/leaflet.js"></script>
+<script src="/col-resize.js" defer></script>
+<script>
+const $ = s => document.querySelector(s);
+const fmt = n => n==null?'—':Number(n).toLocaleString();
+const money = n => n==null?'—':'$'+Number(n).toLocaleString(undefined,{maximumFractionDigits:0});
+const esc = s => (s==null?'':String(s)).replace(/[&<>"]/g,c=>({'&':'&','<':'<','>':'>','"':'"'}[c]));
+
+// columns: key, label, render, numeric, defaultOn
+const COLS = [
+ {k:'source_id', t:'APN / SKU', on:1, r:x=>esc(x.source_id)},
+ {k:'address', t:'Address', on:1, r:x=>esc(x.address)||'<span class="lbl">—</span>'},
+ {k:'city', t:'City', on:1, r:x=>esc(x.city)},
+ {k:'total_value', t:'Assessed', on:1, num:1, r:x=>x.total_value==null?'':'<span class="gold">'+money(x.total_value)+'</span>'},
+ {k:'last_sale_price', t:'Sale $', on:1, num:1, r:x=>x.last_sale_price==null?'':'<span class="gold">'+money(x.last_sale_price)+'</span>'},
+ {k:'last_sale_date', t:'Sale date', on:1, r:x=>esc(x.last_sale_date)},
+ {k:'use_desc', t:'Use', on:1, r:x=>esc(x.use_desc)},
+ {k:'owner_name', t:'Owner', on:0, r:x=>esc(x.owner_name)},
+ {k:'year_built', t:'Yr', on:0, num:1, r:x=>x.year_built||''},
+ {k:'sqft', t:'SqFt', on:0, num:1, r:x=>x.sqft==null?'':fmt(x.sqft)},
+ {k:'beds', t:'Bd', on:0, num:1, r:x=>x.beds==null?'':x.beds},
+ {k:'baths', t:'Ba', on:0, num:1, r:x=>x.baths==null?'':x.baths},
+ {k:'zip', t:'ZIP', on:0, r:x=>esc(x.zip)},
+ {k:'added', t:'Added', on:0, r:x=>esc(x.added)},
+];
+const LSK='parcels:cols';
+let colOn = JSON.parse(localStorage.getItem(LSK)||'null') || Object.fromEntries(COLS.map(c=>[c.k,!!c.on]));
+
+let state = { county:null, sort:'', q:'', priced:false, offset:0, limit:100, rows:[], selSid:null };
+let map, ptLayer;
+
+// ── map ──
+function initMap(){
+ map = L.map('map',{preferCanvas:true,zoomControl:true,attributionControl:true}).setView([36.2,-119.3],6);
+ L.tileLayer('https://{s}.basemaps.cartocdn.com/dark_all/{z}/{x}/{y}{r}.png',{
+ className:'basemap-dark',subdomains:'abcd',maxZoom:19,
+ attribution:'© OpenStreetMap © CARTO'}).addTo(map);
+ ptLayer = L.layerGroup().addTo(map);
+}
+function valColor(v,priced){
+ if(priced) return '#c8a24b';
+ if(v==null) return '#3a4658';
+ if(v>2e6) return '#e05a5a'; if(v>1e6) return '#e0965a'; if(v>5e5) return '#d8c85a';
+ if(v>2.5e5) return '#7ec86b'; return '#5a9be0';
+}
+async function loadMap(){
+ ptLayer.clearLayers();
+ if(!state.county) return;
+ const u = `/api/parcels/map?county=${state.county}&cap=6000${state.priced?'&priced=1':''}`;
+ const d = await (await fetch(u)).json();
+ const pts=[];
+ for(const p of d.points||[]){
+ if(p.lat==null||p.lng==null) continue;
+ const priced=p.last_sale_price!=null;
+ const m=L.circleMarker([p.lat,p.lng],{radius:priced?4:3,weight:priced?1:0,color:'#0a0d13',
+ fillColor:valColor(p.total_value,priced),fillOpacity:.8});
+ m.on('click',()=>openDetail(state.county,p.source_id));
+ m.bindTooltip(`${esc(p.address||p.source_id)}${p.total_value!=null?' · '+money(p.total_value):''}${priced?' · '+money(p.last_sale_price):''}`,{className:'county-tip'});
+ m.addTo(ptLayer); pts.push([p.lat,p.lng]);
+ }
+ $('#mapnote').textContent = d.points.length?`${fmt(d.points.length)} plotted${d.capped?' (capped)':''}`:'no coordinates';
+ if(pts.length){ try{ map.fitBounds(L.latLngBounds(pts).pad(.05),{maxZoom:13}); }catch(e){} }
+}
+
+// ── sidebar coverage ──
+async function loadCoverage(){
+ const d = await (await fetch('/api/parcels/coverage')).json();
+ const t=d.totals||{};
+ $('#totals').innerHTML = `<b>${fmt(t.parcels)}</b> parcels · <b>${fmt(t.counties)}</b> counties<br>`+
+ `<b>${fmt(t.priced)}</b> with recorded sale prices`;
+ const box=$('#counties'); box.innerHTML='';
+ for(const c of d.counties){
+ const el=document.createElement('div');
+ el.className='cty'+(c.priced>0?' priced':'');
+ el.dataset.fips=c.fips;
+ el.innerHTML=`<span class="nm">${esc(c.name)} <span class="st">${esc(c.state||'')}</span></span><span class="ct">${fmt(c.parcels)}</span>`;
+ el.title=`${c.parcels.toLocaleString()} parcels · ${c.priced.toLocaleString()} priced · ${c.mapped.toLocaleString()} mapped · ${c.valued.toLocaleString()} valued`;
+ el.onclick=()=>selectCounty(c.fips,el);
+ box.appendChild(el);
+ }
+}
+function selectCounty(fips,el){
+ document.querySelectorAll('.cty.sel').forEach(x=>x.classList.remove('sel'));
+ (el||document.querySelector(`.cty[data-fips="${fips}"]`))?.classList.add('sel');
+ state.county=fips; state.offset=0; loadTable(); loadMap();
+}
+
+// ── table ──
+function renderCols(){
+ const pop=$('#colpop'); pop.innerHTML='';
+ for(const c of COLS){
+ const l=document.createElement('label');
+ l.innerHTML=`<input type="checkbox" ${colOn[c.k]?'checked':''}> ${esc(c.t)}`;
+ l.querySelector('input').onchange=e=>{colOn[c.k]=e.target.checked;localStorage.setItem(LSK,JSON.stringify(colOn));draw();};
+ pop.appendChild(l);
+ }
+}
+async function loadTable(){
+ if(!state.county){return;}
+ const u=`/api/parcels?county=${state.county}&limit=${state.limit}&offset=${state.offset}`+
+ (state.sort?`&sort=${state.sort}`:'')+(state.q?`&q=${encodeURIComponent(state.q)}`:'')+(state.priced?'&priced=1':'');
+ $('#count').textContent='loading…';
+ const d=await (await fetch(u)).json();
+ state.rows=d.rows||[]; state.hasMore=d.hasMore;
+ draw();
+ $('#prev').disabled=state.offset<=0; $('#next').disabled=!state.hasMore;
+ const from=state.offset+1, to=state.offset+state.rows.length;
+ $('#count').textContent=state.rows.length?`rows ${fmt(from)}–${fmt(to)}${d.hasMore?'+':''}`:'no matches';
+}
+function draw(){
+ const cols=COLS.filter(c=>colOn[c.k]);
+ if(!state.rows.length){ $('#tablewrap').innerHTML='<div class="empty">'+(state.county?'No parcels match.':'Pick a county.')+'</div>'; return; }
+ let h='<table><thead><tr>';
+ for(const c of cols) h+=`<th data-sort="${c.k}" class="${c.num?'num':''}">${esc(c.t)}</th>`;
+ h+='</tr></thead><tbody>';
+ for(const r of state.rows){
+ h+=`<tr data-sid="${esc(r.source_id)}"${r.source_id===state.selSid?' class="sel"':''}>`;
+ for(const c of cols) h+=`<td class="${c.num?'num':''}">${c.r(r)}</td>`;
+ h+='</tr>';
+ }
+ h+='</tbody></table>';
+ $('#tablewrap').innerHTML=h;
+ $('#tablewrap').querySelectorAll('tbody tr').forEach(tr=>tr.onclick=()=>openDetail(state.county,tr.dataset.sid));
+ $('#tablewrap').querySelectorAll('thead th').forEach(th=>th.onclick=()=>{ headerSort(th.dataset.sort); });
+ if(window.ColResize) ColResize.refresh();
+}
+function headerSort(k){
+ const map={total_value:'value_desc',last_sale_price:'price_desc',last_sale_date:'recent',address:'address',source_id:'',added:'added'};
+ if(k in map){ state.sort=map[k]; $('#sort').value=map[k]; state.offset=0; loadTable(); }
+}
+
+// ── detail drawer ──
+async function openDetail(fips,sid){
+ state.selSid=sid; document.querySelectorAll('tbody tr.sel').forEach(t=>t.classList.remove('sel'));
+ document.querySelector(`tbody tr[data-sid="${CSS.escape(sid)}"]`)?.classList.add('sel');
+ $('#detail').classList.add('open'); $('#detailBody').innerHTML='<div class="empty">Loading…</div>';
+ const d=await (await fetch(`/api/parcels/${fips}/${encodeURIComponent(sid)}`)).json();
+ if(d.error){ $('#detailBody').innerHTML='<div class="empty">'+esc(d.error)+'</div>'; return; }
+ const p=d.parcel;
+ const row=(k,v,gold)=>v==null||v===''?'':`<div class="k">${k}</div><div class="v${gold?' gold':''}">${v}</div>`;
+ let kv='<div class="kv">';
+ kv+=row('APN / SKU',esc(p.source_id));
+ kv+=row('Address',esc(p.address));
+ kv+=row('City / ZIP',[esc(p.city),esc(p.zip)].filter(Boolean).join(' · '));
+ kv+=row('Use',esc(p.use_desc));
+ kv+=row('Assessed value',money(p.total_value),1);
+ kv+=row('Owner',esc(p.owner_name));
+ kv+=row('Year built',p.year_built);
+ kv+=row('SqFt / Bed / Bath',[p.sqft&&fmt(p.sqft),p.beds&&p.beds+'bd',p.baths&&p.baths+'ba'].filter(Boolean).join(' · '));
+ kv+=row('Coordinates',p.lat!=null?`${(+p.lat).toFixed(5)}, ${(+p.lng).toFixed(5)}`:'');
+ kv+=row('Last sale',p.last_sale_price!=null?money(p.last_sale_price)+(p.last_sale_date?' · '+esc(p.last_sale_date):''):'');
+ kv+=row('Added',esc(p.fetched_at?String(p.fetched_at).slice(0,16).replace('T',' '):''));
+ kv+='</div>';
+ let deeds='';
+ if(d.events&&d.events.length){
+ deeds='<h3 style="color:var(--gold);font-size:11px;letter-spacing:.06em;text-transform:uppercase;margin:4px 0 8px">Recorded deeds ('+d.events.length+')</h3>';
+ for(const e of d.events){
+ const det=e.detail||{};
+ deeds+='<div class="deed"><div><span class="amt">'+money(e.amount)+'</span> <span class="dt">'+esc(e.event_date||'')+'</span> '+
+ (e.doc_type?'<span class="badge">'+esc(e.doc_type)+'</span>':'')+'</div>'+
+ (det.grantor?'<div class="row"><span>Grantor</span>'+esc(det.grantor)+'</div>':'')+
+ (det.grantee?'<div class="row"><span>Grantee</span>'+esc(det.grantee)+'</div>':'')+
+ (e.doc_number?'<div class="row"><span>Doc #</span>'+esc(e.doc_number)+'</div>':'')+
+ (e.source_url?'<div class="row"><span>Source</span><a href="'+esc(e.source_url)+'" target="_blank" rel="noopener">record ↗</a></div>':'')+
+ '</div>';
+ }
+ } else {
+ deeds='<div class="badge">Coverage-only — no recorded sale price (CA AB-1785)</div>';
+ }
+ let links='';
+ if(d.links&&d.links.length){ links='<div style="margin-top:12px">'+d.links.map(l=>'<a href="'+esc(l.url)+'" target="_blank" rel="noopener">'+esc(l.label||l.kind)+' ↗</a>').join(' · ')+'</div>'; }
+ $('#detailBody').innerHTML=`<h2>${esc(p.address||p.source_id)}</h2><div class="sub">${esc(p.county_name||p.county_fips)} ${esc(p.state_code||'')} · ${esc(p.source_id)}</div>${kv}${deeds}${links}`;
+}
+function closeDetail(){ $('#detail').classList.remove('open'); state.selSid=null; document.querySelectorAll('tbody tr.sel').forEach(t=>t.classList.remove('sel')); }
+
+// ── wire controls ──
+let qT;
+$('#q').oninput=e=>{ clearTimeout(qT); qT=setTimeout(()=>{ state.q=e.target.value.trim(); state.offset=0; loadTable(); },300); };
+$('#sort').onchange=e=>{ state.sort=e.target.value; state.offset=0; loadTable(); };
+$('#pricedOnly').onchange=e=>{ state.priced=e.target.checked; state.offset=0; loadTable(); loadMap(); };
+$('#density').oninput=e=>{ const v=+e.target.value; document.documentElement.style.setProperty('--rowh',v+'px'); document.documentElement.style.setProperty('--fs',(10+v*0.11).toFixed(1)+'px'); localStorage.setItem('parcels:density',v); };
+$('#prev').onclick=()=>{ state.offset=Math.max(0,state.offset-state.limit); loadTable(); $('#tablewrap').scrollTop=0; };
+$('#next').onclick=()=>{ state.offset+=state.limit; loadTable(); $('#tablewrap').scrollTop=0; };
+$('#colbtn').onclick=()=>$('#colpop').classList.toggle('open');
+document.addEventListener('click',e=>{ if(!$('#colmenu').contains(e.target)) $('#colpop').classList.remove('open'); });
+document.addEventListener('keydown',e=>{ if(e.key==='Escape') closeDetail(); });
+
+// ── boot ──
+(function(){
+ const d=localStorage.getItem('parcels:density'); if(d){ $('#density').value=d; $('#density').oninput({target:{value:d}}); }
+ initMap(); renderCols(); loadCoverage();
+ // deep-link ?county=06059&sid=...
+ const p=new URLSearchParams(location.search);
+ if(p.get('county')){ state.county=p.get('county'); loadCoverage().then(()=>selectCounty(state.county)); if(p.get('sid')) setTimeout(()=>openDetail(state.county,p.get('sid')),600); }
+})();
+</script>
+</body>
+</html>
diff --git a/src/server/index.ts b/src/server/index.ts
index 076f9ac..9ce239b 100644
--- a/src/server/index.ts
+++ b/src/server/index.ts
@@ -12,6 +12,7 @@ import { mountProperty } from './property.ts';
import { mountListings } from './listings.ts';
import { mountCommercial } from './commercial.ts';
import { mountSublease } from './sublease.ts';
+import { mountParcels } from './parcels.ts';
const __dirname = dirname(fileURLToPath(import.meta.url));
const ROOT = join(__dirname, '..', '..');
@@ -513,6 +514,7 @@ mountProperty(app);
mountListings(app);
mountCommercial(app);
mountSublease(app);
+mountParcels(app);
app.use(express.static(join(ROOT, 'public')));
diff --git a/src/server/parcels.ts b/src/server/parcels.ts
new file mode 100644
index 0000000..3fb9670
--- /dev/null
+++ b/src/server/parcels.ts
@@ -0,0 +1,123 @@
+/**
+ * Parcel Explorer API — a unified viewer over the `parcel` + `parcel_event` PG tables
+ * that the arcgis_sales/dedicated county ingesters fill (TK-16). Distinct from
+ * property.ts (per-address LA-sqlite lookup): this browses the whole multi-county
+ * roll — coverage facets, a county-scoped grid (map + table), and per-parcel detail
+ * with recorded-deed history where a county publishes sale price (Oregon; CA is
+ * AB-1785 coverage-only).
+ *
+ * All queries are county-scoped off the (county_fips, source_id) pkey so they stay
+ * fast on a multi-million-row table; the one full-scan (coverage) is cached.
+ */
+import type { Express, Request, Response } from 'express';
+import { query } from '../../db/pool.ts';
+
+const num = (v: unknown, d: number, max: number) => {
+ const n = Math.floor(Number(v)); return Number.isFinite(n) && n > 0 ? Math.min(n, max) : d;
+};
+const fips = (v: unknown) => { const t = String(v ?? '').trim(); return /^\d{5}$/.test(t) ? t : null; };
+
+// coverage is a GROUP BY over the whole parcel table — cache it (cheap staleness for a viewer).
+let coverageCache: { at: number; rows: any[]; totals: any } | null = null;
+const COVERAGE_TTL = 60_000;
+
+export function mountParcels(app: Express) {
+ // ── coverage facets: every county with ingested parcels + counts ──────────
+ app.get('/api/parcels/coverage', async (_req: Request, res: Response) => {
+ try {
+ if (coverageCache && Date.now() - coverageCache.at < COVERAGE_TTL) return res.json({ counties: coverageCache.rows, totals: coverageCache.totals });
+ const r = await query<any>(
+ `SELECT p.county_fips,
+ COUNT(*)::bigint AS parcels,
+ COUNT(p.last_sale_price)::bigint AS priced,
+ COUNT(p.lat)::bigint AS mapped,
+ COUNT(p.total_value)::bigint AS valued,
+ r.name, r.state_code
+ FROM parcel p
+ LEFT JOIN region r ON r.fips = p.county_fips AND r.region_type = 'county'
+ GROUP BY p.county_fips, r.name, r.state_code
+ ORDER BY COUNT(*) DESC`);
+ const counties = r.rows.map((x) => ({
+ fips: x.county_fips, name: x.name || x.county_fips, state: x.state_code || null,
+ parcels: Number(x.parcels), priced: Number(x.priced), mapped: Number(x.mapped), valued: Number(x.valued),
+ }));
+ const totals = counties.reduce((a, c) => ({ parcels: a.parcels + c.parcels, priced: a.priced + c.priced, counties: a.counties + 1 }), { parcels: 0, priced: 0, counties: 0 });
+ coverageCache = { at: Date.now(), rows: counties, totals };
+ res.json({ counties, totals });
+ } catch (e: any) { res.status(500).json({ error: String(e.message || e) }); }
+ });
+
+ // ── county-scoped grid: filtered + sorted + paged rows ────────────────────
+ app.get('/api/parcels', async (req: Request, res: Response) => {
+ try {
+ const cf = fips(req.query.county);
+ if (!cf) return res.status(400).json({ error: 'county (5-digit FIPS) required' });
+ const limit = num(req.query.limit, 100, 500);
+ const off = Math.max(0, Math.floor(Number(req.query.offset) || 0));
+ const q = String(req.query.q || '').trim().slice(0, 80);
+ const pricedOnly = req.query.priced === '1';
+
+ const where: string[] = ['p.county_fips = $1']; const params: any[] = [cf];
+ if (pricedOnly) where.push('p.last_sale_price IS NOT NULL');
+ if (q) {
+ const esc = q.replace(/[%_\\]/g, '\\$&');
+ params.push('%' + esc + '%');
+ where.push(`(p.address ILIKE $${params.length} ESCAPE '\\' OR p.source_id ILIKE $${params.length} ESCAPE '\\' OR p.city ILIKE $${params.length} ESCAPE '\\' OR p.owner_name ILIKE $${params.length} ESCAPE '\\')`);
+ }
+ const SORTS: Record<string, string> = {
+ value_desc: 'p.total_value DESC NULLS LAST', value_asc: 'p.total_value ASC NULLS LAST',
+ price_desc: 'p.last_sale_price DESC NULLS LAST', recent: 'p.last_sale_date DESC NULLS LAST',
+ sku: 'p.source_id ASC', address: 'p.address ASC NULLS LAST', added: 'p.fetched_at DESC NULLS LAST',
+ };
+ const orderBy = SORTS[String(req.query.sort || '')] || 'p.source_id ASC';
+ params.push(limit + 1, off);
+ const r = await query<any>(
+ `SELECT p.county_fips, p.source_id, p.address, p.city, p.zip, p.lat, p.lng, p.use_desc,
+ p.total_value, p.owner_name, p.year_built, p.sqft, p.beds, p.baths,
+ p.last_sale_date, p.last_sale_price,
+ to_char(p.fetched_at,'YYYY-MM-DD HH24:MI') AS added
+ FROM parcel p
+ WHERE ${where.join(' AND ')}
+ ORDER BY ${orderBy}
+ LIMIT $${params.length - 1} OFFSET $${params.length}`, params);
+ const hasMore = r.rows.length > limit;
+ res.json({ county: cf, offset: off, limit, hasMore, rows: r.rows.slice(0, limit) });
+ } catch (e: any) { res.status(500).json({ error: String(e.message || e) }); }
+ });
+
+ // ── map points for a county (capped sample of lat/lng) ────────────────────
+ app.get('/api/parcels/map', async (req: Request, res: Response) => {
+ try {
+ const cf = fips(req.query.county);
+ if (!cf) return res.status(400).json({ error: 'county required' });
+ const cap = num(req.query.cap, 6000, 20000);
+ const pricedOnly = req.query.priced === '1';
+ const r = await query<any>(
+ `SELECT p.source_id, p.lat, p.lng, p.total_value, p.last_sale_price, p.address
+ FROM parcel p
+ WHERE p.county_fips = $1 AND p.lat IS NOT NULL AND p.lng IS NOT NULL
+ ${pricedOnly ? 'AND p.last_sale_price IS NOT NULL' : ''}
+ ORDER BY p.total_value DESC NULLS LAST
+ LIMIT $2`, [cf, cap]);
+ res.json({ county: cf, capped: r.rows.length >= cap, points: r.rows });
+ } catch (e: any) { res.status(500).json({ error: String(e.message || e) }); }
+ });
+
+ // ── single-parcel detail + recorded-deed history ──────────────────────────
+ app.get('/api/parcels/:fips/:sid', async (req: Request, res: Response) => {
+ try {
+ const cf = fips(req.params.fips); if (!cf) return res.status(400).json({ error: 'bad fips' });
+ const sid = String(req.params.sid).slice(0, 64);
+ const p = await query<any>(
+ `SELECT p.*, r.name AS county_name, r.state_code
+ FROM parcel p LEFT JOIN region r ON r.fips = p.county_fips AND r.region_type='county'
+ WHERE p.county_fips = $1 AND p.source_id = $2`, [cf, sid]);
+ if (!p.rows.length) return res.status(404).json({ error: 'not found' });
+ const ev = await query<any>(
+ `SELECT event_type, to_char(event_date,'YYYY-MM-DD') AS event_date, amount, doc_type, doc_number, source_url, detail
+ FROM parcel_event WHERE county_fips = $1 AND source_id = $2 ORDER BY event_date DESC NULLS LAST LIMIT 100`, [cf, sid]);
+ const lk = await query<any>(`SELECT kind, url, label FROM parcel_links WHERE county_fips=$1 AND source_id=$2`, [cf, sid]);
+ res.json({ parcel: p.rows[0], events: ev.rows, links: lk.rows });
+ } catch (e: any) { res.status(500).json({ error: String(e.message || e) }); }
+ });
+}
← 834684a TK-16: add CA Ring 5 — Napa + Contra Costa + El Dorado + Nev
·
back to Nationalrealestate
·
feat: Parcel Explorer national coverage overview map + lint/ 956d719 →