← back to Nationalrealestate
LA commercial RE layer: taxonomy + materialized 153.9k parcels + explorer & news feed
613b10654eb3dee17df4a7dd331eb51044514805 · 2026-07-22 07:02:26 -0700 · Steve
Repositions the platform toward local commercial real estate news, LA first.
- src/lib/commercial_types.ts: use_desc2 -> canonical taxonomy (office/retail/
industrial/hospitality/studio/parking/other); Studio broken out for LA media assets.
- 005_commercial.sql + src/ingest/commercial/la_assessor.ts: materialize the LA
assessor sqlite's 153,923 commercial+industrial parcels into pg commercial_parcel
(classified, city/zip parsed, indexed; multi-county-ready). LA has assessed value +
recording date, NO sale price/owner (roll limit) — never fabricated.
- src/server/commercial.ts: /api/commercial/{types,cities,search,feed,marquee,property}.
- public/commercial.html (explorer: marquee + filters + sort + density) and
public/commercial-feed.html (recent recorded activity, the local news feed).
- Verified headless: 120 cards + 6 marquee + 8 filters / 80 feed items, 0 console errors.
Marquee surfaces real trophy assets (Beverly Center $995M, Bunker Hill towers).
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Files touched
A db/migrations/005_commercial.sqlM package.jsonA public/commercial-feed.htmlA public/commercial.htmlA src/ingest/commercial/engine.tsA src/ingest/commercial/la_assessor.tsA src/lib/commercial_types.tsA src/server/commercial.tsM src/server/index.ts
Diff
commit 613b10654eb3dee17df4a7dd331eb51044514805
Author: Steve <steve@designerwallcoverings.com>
Date: Wed Jul 22 07:02:26 2026 -0700
LA commercial RE layer: taxonomy + materialized 153.9k parcels + explorer & news feed
Repositions the platform toward local commercial real estate news, LA first.
- src/lib/commercial_types.ts: use_desc2 -> canonical taxonomy (office/retail/
industrial/hospitality/studio/parking/other); Studio broken out for LA media assets.
- 005_commercial.sql + src/ingest/commercial/la_assessor.ts: materialize the LA
assessor sqlite's 153,923 commercial+industrial parcels into pg commercial_parcel
(classified, city/zip parsed, indexed; multi-county-ready). LA has assessed value +
recording date, NO sale price/owner (roll limit) — never fabricated.
- src/server/commercial.ts: /api/commercial/{types,cities,search,feed,marquee,property}.
- public/commercial.html (explorer: marquee + filters + sort + density) and
public/commercial-feed.html (recent recorded activity, the local news feed).
- Verified headless: 120 cards + 6 marquee + 8 filters / 80 feed items, 0 console errors.
Marquee surfaces real trophy assets (Beverly Center $995M, Bunker Hill towers).
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
---
db/migrations/005_commercial.sql | 31 ++++++++
package.json | 3 +-
public/commercial-feed.html | 94 +++++++++++++++++++++++++
public/commercial.html | 133 +++++++++++++++++++++++++++++++++++
src/ingest/commercial/engine.ts | 24 +++++++
src/ingest/commercial/la_assessor.ts | 104 +++++++++++++++++++++++++++
src/lib/commercial_types.ts | 55 +++++++++++++++
src/server/commercial.ts | 132 ++++++++++++++++++++++++++++++++++
src/server/index.ts | 2 +
9 files changed, 577 insertions(+), 1 deletion(-)
diff --git a/db/migrations/005_commercial.sql b/db/migrations/005_commercial.sql
new file mode 100644
index 0000000..eaa602f
--- /dev/null
+++ b/db/migrations/005_commercial.sql
@@ -0,0 +1,31 @@
+-- Commercial-property layer for the LA-region commercial-RE-news product.
+-- Materialized from each county's parcel source (LA: read-only assessor sqlite;
+-- future CA counties: their own assessor feeds) so the feed / explorer / rankings
+-- query fast pg indexes instead of scanning a 2.4M-row read-only sqlite per request.
+-- Multi-county by design: (county_fips, ain) is the key; LA (06037) is populated first.
+
+CREATE TABLE IF NOT EXISTS commercial_parcel (
+ county_fips text NOT NULL,
+ ain text NOT NULL, -- parcel id (LA AIN; other counties their pid)
+ address text,
+ city text,
+ zip text,
+ ctype text NOT NULL, -- canonical type: office|retail|industrial|hospitality|studio|parking|other
+ use_desc text, -- raw assessor sub-type ("Office Building", ...)
+ use_class text, -- Commercial | Industrial
+ assessed_total numeric,
+ assessed_land numeric,
+ assessed_imp numeric,
+ roll_year text,
+ recording_date date, -- last recorded transfer (NO price in LA roll)
+ sqft integer,
+ year_built integer,
+ units integer,
+ updated_at timestamptz NOT NULL DEFAULT now(),
+ PRIMARY KEY (county_fips, ain)
+);
+
+CREATE INDEX IF NOT EXISTS idx_commercial_type ON commercial_parcel (county_fips, ctype);
+CREATE INDEX IF NOT EXISTS idx_commercial_recdate ON commercial_parcel (county_fips, recording_date DESC NULLS LAST);
+CREATE INDEX IF NOT EXISTS idx_commercial_value ON commercial_parcel (county_fips, assessed_total DESC NULLS LAST);
+CREATE INDEX IF NOT EXISTS idx_commercial_city ON commercial_parcel (county_fips, city);
diff --git a/package.json b/package.json
index 24ee8f2..910a541 100644
--- a/package.json
+++ b/package.json
@@ -22,7 +22,8 @@
"alerts": "tsx src/jobs/alerts_check.ts",
"ingest:nri": "tsx src/ingest/fema_nri.ts",
"ingest:fmr": "tsx src/ingest/hud_fmr.ts",
- "ingest:parcels": "tsx src/ingest/parcels/engine.ts"
+ "ingest:parcels": "tsx src/ingest/parcels/engine.ts",
+ "ingest:commercial": "tsx src/ingest/commercial/engine.ts"
},
"dependencies": {
"better-sqlite3": "^13.0.1",
diff --git a/public/commercial-feed.html b/public/commercial-feed.html
new file mode 100644
index 0000000..4ed4b85
--- /dev/null
+++ b/public/commercial-feed.html
@@ -0,0 +1,94 @@
+<!doctype html>
+<html lang="en">
+<head>
+<meta charset="utf-8">
+<meta name="viewport" content="width=device-width, initial-scale=1">
+<link rel="icon" href="data:image/svg+xml,%3Csvg xmlns='http://www.w3.org/2000/svg' viewBox='0 0 16 16'%3E%3Crect width='16' height='16' rx='3' fill='%230a0d13'/%3E%3Ctext x='8' y='12' font-size='10' text-anchor='middle' fill='%23c8a24b'%3ECR%3C/text%3E%3C/svg%3E">
+<title>USRealEstate — LA Commercial Feed</title>
+<style>
+ :root{--bg:#0a0d13;--panel:#11151d;--line:#222936;--fg:#e8ecf2;--muted:#8a93a3;--gold:#c8a24b;}
+ *{box-sizing:border-box;}
+ html,body{margin:0;background:var(--bg);color:var(--fg);font-family:-apple-system,BlinkMacSystemFont,'Segoe UI',Roboto,sans-serif;}
+ #topbar{display:flex;align-items:center;gap:14px;padding:10px 18px;background:rgba(17,21,29,.94);border-bottom:1px solid var(--line);position:sticky;top:0;z-index:20;backdrop-filter:blur(6px);}
+ .brand{font-size:16px;font-weight:700;white-space:nowrap;} .brand b{color:var(--gold);}
+ nav{display:flex;gap:14px;font-size:13px;} nav a{color:var(--muted);text-decoration:none;} nav a:hover,nav a.active{color:var(--gold);}
+ main{padding:16px 18px 60px;max-width:960px;margin:0 auto;}
+ .lede{color:var(--muted);font-size:13px;line-height:1.5;margin:6px 0 16px;}
+ #controls{display:flex;flex-wrap:wrap;gap:10px;align-items:center;margin-bottom:14px;}
+ #seg{display:inline-flex;flex-wrap:wrap;border:1px solid var(--line);border-radius:8px;overflow:hidden;}
+ #seg button{background:var(--panel);color:var(--muted);border:0;border-right:1px solid var(--line);padding:7px 11px;font-size:12px;cursor:pointer;}
+ #seg button:last-child{border-right:0;} #seg button.active{background:var(--gold);color:var(--bg);font-weight:700;}
+ select{background:var(--panel);color:var(--fg);border:1px solid var(--line);border-radius:8px;padding:7px 10px;font-size:13px;}
+ .item{display:flex;gap:14px;padding:14px 12px;border-bottom:1px solid var(--line);}
+ .item .date{min-width:92px;font-size:12px;color:var(--gold);font-weight:600;padding-top:2px;}
+ .item .body{flex:1;}
+ .item .hl{font-size:15px;font-weight:600;line-height:1.3;}
+ .item .meta{font-size:12px;color:var(--muted);margin-top:4px;display:flex;flex-wrap:wrap;gap:10px;}
+ .tag{font-size:10px;letter-spacing:.05em;text-transform:uppercase;color:var(--gold);border:1px solid var(--line);border-radius:5px;padding:1px 6px;}
+ .val{color:var(--fg);font-weight:600;}
+ #loading{padding:40px;text-align:center;color:var(--muted);}
+</style>
+</head>
+<body>
+<div id="topbar">
+ <div class="brand"><b>USRealEstate</b> — LA Commercial Feed</div>
+ <nav>
+ <a href="/">Map</a><a href="/markets.html">Markets</a>
+ <a href="/commercial.html">Commercial</a>
+ <a href="/commercial-feed.html" class="active">Feed</a>
+ <a href="/brokers.html">Brokers</a>
+ </nav>
+</div>
+<main>
+ <p class="lede">Recently recorded commercial & industrial activity across Los Angeles County, newest first —
+ sourced from the LA County Assessor roll. Figures shown are <b>assessed value</b> and the <b>last recorded
+ transfer date</b>; market sale prices are not in the public roll (they arrive with the commercial listings feed).</p>
+ <div id="controls">
+ <div id="seg"></div>
+ <select id="city"><option value="">All LA cities</option></select>
+ </div>
+ <div id="feed"></div>
+ <div id="loading">Loading…</div>
+</main>
+<script>
+const $=s=>document.querySelector(s), usd=n=>n==null?'—':'$'+Number(n).toLocaleString();
+const state={type:'',city:''};
+function dstr(d){const dt=new Date(d);return dt.toLocaleDateString(undefined,{year:'numeric',month:'short',day:'numeric'});}
+async function j(u){const r=await fetch(u);return r.json();}
+async function loadTypes(){
+ const d=await j('/api/commercial/types');
+ const seg=$('#seg');
+ seg.innerHTML=`<button data-t="" class="active">All</button>`+
+ d.types.filter(t=>t.count).map(t=>`<button data-t="${t.key}">${t.label}</button>`).join('');
+ seg.querySelectorAll('button').forEach(b=>b.onclick=()=>{
+ seg.querySelectorAll('button').forEach(x=>x.classList.remove('active'));
+ b.classList.add('active'); state.type=b.dataset.t; load();});
+}
+async function loadCities(){
+ const d=await j('/api/commercial/cities');
+ $('#city').innerHTML='<option value="">All LA cities</option>'+
+ d.cities.map(c=>`<option value="${c.city}">${c.city} (${c.count.toLocaleString()})</option>`).join('');
+ $('#city').onchange=()=>{state.city=$('#city').value;load();};
+}
+async function load(){
+ $('#loading').style.display='block';
+ const p=new URLSearchParams({limit:80});
+ if(state.type)p.set('type',state.type); if(state.city)p.set('city',state.city);
+ const d=await j('/api/commercial/feed?'+p);
+ $('#feed').innerHTML=d.results.map(r=>`<div class="item">
+ <div class="date">${dstr(r.recording_date)}</div>
+ <div class="body">
+ <div class="hl">${r.address||'—'}${r.city?`, ${r.city}`:''}</div>
+ <div class="meta">
+ <span class="tag">${r.type_label}</span>
+ <span>${r.use_desc||''}</span>
+ <span class="val">Assessed ${usd(r.assessed_total)}</span>
+ ${r.sqft?`<span>${Number(r.sqft).toLocaleString()} sqft</span>`:''}
+ </div>
+ </div></div>`).join('') || '<div id="loading">No recorded activity for this filter.</div>';
+ $('#loading').style.display='none';
+}
+(async()=>{ await loadTypes(); await loadCities(); await load(); })();
+</script>
+</body>
+</html>
diff --git a/public/commercial.html b/public/commercial.html
new file mode 100644
index 0000000..80cbc4e
--- /dev/null
+++ b/public/commercial.html
@@ -0,0 +1,133 @@
+<!doctype html>
+<html lang="en">
+<head>
+<meta charset="utf-8">
+<meta name="viewport" content="width=device-width, initial-scale=1">
+<link rel="icon" href="data:image/svg+xml,%3Csvg xmlns='http://www.w3.org/2000/svg' viewBox='0 0 16 16'%3E%3Crect width='16' height='16' rx='3' fill='%230a0d13'/%3E%3Ctext x='8' y='12' font-size='10' text-anchor='middle' fill='%23c8a24b'%3ECR%3C/text%3E%3C/svg%3E">
+<title>USRealEstate — LA Commercial</title>
+<style>
+ :root{--bg:#0a0d13;--panel:#11151d;--line:#222936;--fg:#e8ecf2;--muted:#8a93a3;--gold:#c8a24b;--cols:4;}
+ *{box-sizing:border-box;}
+ html,body{margin:0;background:var(--bg);color:var(--fg);font-family:-apple-system,BlinkMacSystemFont,'Segoe UI',Roboto,sans-serif;}
+ #topbar{display:flex;align-items:center;gap:14px;padding:10px 18px;background:rgba(17,21,29,.94);border-bottom:1px solid var(--line);position:sticky;top:0;z-index:20;backdrop-filter:blur(6px);}
+ .brand{font-size:16px;font-weight:700;white-space:nowrap;} .brand b{color:var(--gold);}
+ nav{display:flex;gap:14px;font-size:13px;} nav a{color:var(--muted);text-decoration:none;} nav a:hover,nav a.active{color:var(--gold);}
+ main{padding:16px 18px 60px;max-width:1500px;margin:0 auto;}
+ h2{font-size:13px;letter-spacing:.08em;text-transform:uppercase;color:var(--muted);margin:22px 0 10px;font-weight:600;}
+ /* marquee */
+ #marquee{display:grid;grid-template-columns:repeat(auto-fill,minmax(230px,1fr));gap:10px;}
+ .mq{background:linear-gradient(160deg,#141a24,#0d1119);border:1px solid var(--line);border-radius:10px;padding:14px;}
+ .mq .val{font-size:20px;font-weight:700;color:var(--gold);}
+ .mq .addr{font-size:13px;margin-top:4px;} .mq .sub{font-size:11px;color:var(--muted);margin-top:6px;}
+ /* controls */
+ #controls{display:flex;flex-wrap:wrap;gap:10px;align-items:center;margin:6px 0 4px;}
+ #seg{display:inline-flex;flex-wrap:wrap;border:1px solid var(--line);border-radius:8px;overflow:hidden;}
+ #seg button{background:var(--panel);color:var(--muted);border:0;border-right:1px solid var(--line);padding:7px 11px;font-size:12px;cursor:pointer;}
+ #seg button:last-child{border-right:0;} #seg button.active{background:var(--gold);color:var(--bg);font-weight:700;}
+ input,select{background:var(--panel);color:var(--fg);border:1px solid var(--line);border-radius:8px;padding:7px 10px;font-size:13px;}
+ #controls label{font-size:12px;color:var(--muted);display:flex;align-items:center;gap:6px;}
+ #count{font-size:12px;color:var(--muted);margin-left:auto;}
+ /* grid */
+ #grid{display:grid;grid-template-columns:repeat(var(--cols),1fr);gap:10px;margin-top:8px;}
+ .card{background:var(--panel);border:1px solid var(--line);border-radius:10px;padding:12px;cursor:pointer;transition:border-color .12s;}
+ .card:hover{border-color:var(--gold);}
+ .card .t{font-size:10px;letter-spacing:.06em;text-transform:uppercase;color:var(--gold);}
+ .card .a{font-size:14px;font-weight:600;margin:4px 0 2px;line-height:1.25;}
+ .card .c{font-size:12px;color:var(--muted);}
+ .card .v{font-size:16px;font-weight:700;margin-top:8px;}
+ .card .m{font-size:11px;color:var(--muted);margin-top:6px;display:flex;flex-wrap:wrap;gap:8px;}
+ .when{color:var(--muted);}
+ #loading{padding:50px;text-align:center;color:var(--muted);}
+</style>
+</head>
+<body>
+<div id="topbar">
+ <div class="brand"><b>USRealEstate</b> — LA Commercial</div>
+ <nav>
+ <a href="/">Map</a><a href="/markets.html">Markets</a>
+ <a href="/commercial.html" class="active">Commercial</a>
+ <a href="/commercial-feed.html">Feed</a>
+ <a href="/brokers.html">Brokers</a>
+ </nav>
+</div>
+<main>
+ <h2>Marquee LA Commercial Assets</h2>
+ <div id="marquee"></div>
+
+ <h2>Explore</h2>
+ <div id="controls">
+ <div id="seg"></div>
+ <input id="q" placeholder="Search address / city…" style="width:200px">
+ <select id="city"><option value="">All cities</option></select>
+ <select id="sort">
+ <option value="value_desc">Assessed ↓</option>
+ <option value="value_asc">Assessed ↑</option>
+ <option value="recent">Recently recorded</option>
+ <option value="sqft_desc">Size ↓</option>
+ </select>
+ <label>Density <input id="density" type="range" min="2" max="7" value="4"></label>
+ <span id="count"></span>
+ </div>
+ <div id="grid"></div>
+ <div id="loading">Loading…</div>
+</main>
+<script>
+const $=s=>document.querySelector(s), usd=n=>n==null?'—':'$'+Number(n).toLocaleString();
+const state={type:'',city:'',q:'',sort:localStorage.getItem('cre_sort')||'value_desc'};
+function whenStr(d){ if(!d) return ''; const dt=new Date(d);
+ return '🕓 '+dt.toLocaleString(undefined,{year:'numeric',month:'short',day:'numeric'}); }
+$('#sort').value=state.sort;
+const dens=$('#density'); dens.value=localStorage.getItem('cre_density')||4;
+document.documentElement.style.setProperty('--cols',dens.value);
+dens.oninput=()=>{document.documentElement.style.setProperty('--cols',dens.value);localStorage.setItem('cre_density',dens.value);};
+
+async function j(u){const r=await fetch(u);return r.json();}
+
+async function loadTypes(){
+ const d=await j('/api/commercial/types');
+ const seg=$('#seg');
+ seg.innerHTML=`<button data-t="" class="active">All ${d.total.toLocaleString()}</button>`+
+ d.types.filter(t=>t.count).map(t=>`<button data-t="${t.key}">${t.label} ${t.count.toLocaleString()}</button>`).join('');
+ seg.querySelectorAll('button').forEach(b=>b.onclick=()=>{
+ seg.querySelectorAll('button').forEach(x=>x.classList.remove('active'));
+ b.classList.add('active'); state.type=b.dataset.t; load();
+ });
+}
+async function loadCities(){
+ const d=await j('/api/commercial/cities');
+ $('#city').innerHTML='<option value="">All cities</option>'+
+ d.cities.map(c=>`<option value="${c.city}">${c.city} (${c.count.toLocaleString()})</option>`).join('');
+ $('#city').onchange=()=>{state.city=$('#city').value;load();};
+}
+async function loadMarquee(){
+ const d=await j('/api/commercial/marquee?limit=6'+(state.type?`&type=${state.type}`:''));
+ $('#marquee').innerHTML=d.results.map(r=>`<div class="mq">
+ <div class="val">${usd(r.assessed_total)}</div>
+ <div class="addr">${r.address||'—'}</div>
+ <div class="sub">${r.type_label} · ${r.city||''} · ${r.use_desc||''}</div></div>`).join('');
+}
+async function load(){
+ $('#loading').style.display='block';
+ const p=new URLSearchParams({sort:state.sort,limit:120});
+ if(state.type)p.set('type',state.type); if(state.city)p.set('city',state.city); if(state.q)p.set('q',state.q);
+ const d=await j('/api/commercial/search?'+p);
+ $('#count').textContent=d.total.toLocaleString()+' properties';
+ $('#grid').innerHTML=d.results.map(r=>`<div class="card">
+ <div class="t">${r.type_label}</div>
+ <div class="a">${r.address||'—'}</div>
+ <div class="c">${r.city||''} ${r.zip||''}</div>
+ <div class="v">${usd(r.assessed_total)}</div>
+ <div class="m">
+ ${r.sqft?`<span>${Number(r.sqft).toLocaleString()} sqft</span>`:''}
+ ${r.year_built?`<span>built ${r.year_built}</span>`:''}
+ ${r.recording_date?`<span class="when" title="${r.recording_date}">${whenStr(r.recording_date)}</span>`:''}
+ </div></div>`).join('');
+ loadMarquee();
+ $('#loading').style.display='none';
+}
+$('#q').oninput=()=>{state.q=$('#q').value;clearTimeout(window._t);window._t=setTimeout(load,250);};
+$('#sort').onchange=()=>{state.sort=$('#sort').value;localStorage.setItem('cre_sort',state.sort);load();};
+(async()=>{ await loadTypes(); await loadCities(); await load(); })();
+</script>
+</body>
+</html>
diff --git a/src/ingest/commercial/engine.ts b/src/ingest/commercial/engine.ts
new file mode 100644
index 0000000..c6039eb
--- /dev/null
+++ b/src/ingest/commercial/engine.ts
@@ -0,0 +1,24 @@
+/**
+ * Commercial-layer ingest dispatcher: npm run ingest:commercial -- <county>
+ * Counties: la (LA County assessor sqlite -> commercial_parcel). More CA counties
+ * (san-diego, orange, sf, ...) register here as their adapters are added.
+ */
+import { pool } from '../../../db/pool.ts';
+
+const ADAPTERS: Record<string, () => Promise<{ run: () => Promise<{ upserted: number }> }>> = {
+ la: async () => ({ run: (await import('./la_assessor.ts')).ingestLaCommercial }),
+};
+
+async function main() {
+ const which = (process.argv[2] || '').toLowerCase();
+ if (!ADAPTERS[which]) {
+ console.error(`usage: npm run ingest:commercial -- <${Object.keys(ADAPTERS).join('|')}>`);
+ process.exit(2);
+ }
+ const { run } = await ADAPTERS[which]();
+ const { upserted } = await run();
+ console.log(`[commercial:${which}] done, ${upserted} upserted`);
+ await pool.end();
+}
+
+main().catch(e => { console.error(e); process.exit(1); });
diff --git a/src/ingest/commercial/la_assessor.ts b/src/ingest/commercial/la_assessor.ts
new file mode 100644
index 0000000..021f2b8
--- /dev/null
+++ b/src/ingest/commercial/la_assessor.ts
@@ -0,0 +1,104 @@
+/**
+ * LA commercial layer ETL: read the CRCP assessor sqlite (read-only, 2.4M rows),
+ * keep the Commercial + Industrial parcels (~154k), classify each into a canonical
+ * commercial type, parse city/zip out of property_location, and materialize into the
+ * pg `commercial_parcel` table so the feed / explorer / rankings query fast indexes.
+ *
+ * LA carries NO sale price and NO owner (assessor roll limits) — only assessed values
+ * + last recording_date. We never fabricate those; prices come from the listings crawl.
+ *
+ * Run: npm run ingest:commercial -- la
+ */
+import { join } from 'node:path';
+import { homedir } from 'node:os';
+import { existsSync } from 'node:fs';
+import { createRequire } from 'node:module';
+import { pool, query } from '../../../db/pool.ts';
+import { openRun, closeRun } from '../run.ts';
+import { classifyCommercial } from '../../lib/commercial_types.ts';
+
+const require = createRequire(import.meta.url);
+const FIPS = '06037';
+const SQLITE = process.env.ASSESSOR_SQLITE || join(homedir(), 'Projects', 'commercialrealestate', 'data', 'assessor.sqlite');
+
+interface Row {
+ ain: string; property_location: string | null; year_built: number | null;
+ sqft_main: number | null; units: number | null; use_desc1: string | null; use_desc2: string | null;
+ roll_total_value: number | null; roll_land_value: number | null; roll_imp_value: number | null;
+ roll_year: string | null; recording_date: string | null;
+}
+
+/** "8407 FALLBROOK AVE WEST HILLS CA 91304" -> {street, city, zip}; nulls if unparseable. */
+function parseLoc(loc: string | null): { street: string | null; city: string | null; zip: string | null } {
+ if (!loc) return { street: null, city: null, zip: null };
+ const m = loc.match(/^(.*?)\s{2,}(.+?)\s+CA\s+(\d{5})/);
+ if (m) return { street: m[1].trim() || null, city: m[2].trim() || null, zip: m[3] };
+ return { street: loc.trim() || null, city: null, zip: null };
+}
+
+const isoDate = (d: string | null) => (d && /^\d{4}-\d{2}-\d{2}$/.test(d.slice(0, 10)) ? d.slice(0, 10) : null);
+const COLS = ['county_fips', 'ain', 'address', 'city', 'zip', 'ctype', 'use_desc', 'use_class',
+ 'assessed_total', 'assessed_land', 'assessed_imp', 'roll_year', 'recording_date', 'sqft', 'year_built', 'units'];
+
+export async function ingestLaCommercial(): Promise<{ upserted: number }> {
+ const runId = await openRun('commercial_la_assessor', SQLITE);
+ try {
+ if (!existsSync(SQLITE)) throw new Error(`LA assessor sqlite not found at ${SQLITE}`);
+ const Database = require('better-sqlite3');
+ const db = new Database(SQLITE, { readonly: true, fileMustExist: true });
+
+ const rows = db.prepare(
+ `SELECT ain, property_location, year_built, sqft_main, units, use_desc1, use_desc2,
+ roll_total_value, roll_land_value, roll_imp_value, roll_year, recording_date
+ FROM assessor_parcel WHERE use_desc1 IN ('Commercial','Industrial')`,
+ ).all() as Row[];
+ console.log(`[la-commercial] read ${rows.length} commercial+industrial parcels from sqlite`);
+
+ // fresh authoritative load for this county
+ await query(`DELETE FROM commercial_parcel WHERE county_fips = $1`, [FIPS]);
+
+ let upserted = 0, skipped = 0;
+ const batch: unknown[][] = [];
+ const flush = async () => {
+ if (!batch.length) return;
+ const params: unknown[] = [];
+ const values = batch.map((r, j) => {
+ const b = j * COLS.length;
+ params.push(...r);
+ return '(' + COLS.map((_, k) => `$${b + k + 1}`).join(',') + ')';
+ });
+ await query(
+ `INSERT INTO commercial_parcel (${COLS.join(',')}) VALUES ${values.join(',')}
+ ON CONFLICT (county_fips, ain) DO UPDATE SET
+ ${COLS.filter(c => c !== 'county_fips' && c !== 'ain').map(c => `${c}=EXCLUDED.${c}`).join(', ')},
+ updated_at = now()`,
+ params,
+ );
+ upserted += batch.length;
+ batch.length = 0;
+ };
+
+ for (const r of rows) {
+ const ctype = classifyCommercial(r.use_desc1, r.use_desc2);
+ if (!ctype) { skipped++; continue; }
+ const { street, city, zip } = parseLoc(r.property_location);
+ batch.push([FIPS, r.ain, street, city, zip, ctype, (r.use_desc2 || r.use_desc1), r.use_desc1,
+ r.roll_total_value, r.roll_land_value, r.roll_imp_value, r.roll_year, isoDate(r.recording_date),
+ r.sqft_main, r.year_built, r.units]);
+ if (batch.length >= 1000) await flush();
+ }
+ await flush();
+ if (upserted < 100000) throw new Error(`only ${upserted} commercial parcels loaded — expected ~154k, sqlite drift?`);
+
+ await closeRun(runId, 'ok', { upserted, skipped, notes: `LA commercial: ${upserted} parcels materialized (${skipped} unclassified)` });
+ console.log(`[la-commercial] ok: ${upserted} parcels into commercial_parcel (${skipped} skipped)`);
+ return { upserted };
+ } catch (e: any) {
+ await closeRun(runId, 'failed', { notes: String(e.message || e) });
+ throw e;
+ }
+}
+
+if (import.meta.url === `file://${process.argv[1]}`) {
+ ingestLaCommercial().then(() => pool.end()).catch(e => { console.error(e); process.exit(1); });
+}
diff --git a/src/lib/commercial_types.ts b/src/lib/commercial_types.ts
new file mode 100644
index 0000000..f2246e5
--- /dev/null
+++ b/src/lib/commercial_types.ts
@@ -0,0 +1,55 @@
+/**
+ * Canonical commercial property-type taxonomy for the LA-region commercial layer.
+ *
+ * LA County assessor rows carry a coarse `use_desc1` (Commercial | Industrial) and a
+ * finer free-text `use_desc2` (e.g. "Office Building", "Shopping Center (Regional)").
+ * This maps those to a small canonical set the UI/API filter on, using ordered
+ * substring rules so string variants across counties still classify. "Studio" is
+ * broken out on purpose — Motion Picture/Radio/TV is an LA-distinctive commercial
+ * category worth surfacing in a local commercial-RE-news product.
+ *
+ * Derived from the full LA use_desc2 distribution (154k commercial+industrial parcels).
+ */
+
+export type CommercialType =
+ | 'office' | 'retail' | 'industrial' | 'hospitality'
+ | 'studio' | 'parking' | 'other';
+
+export interface CommercialCategory { key: CommercialType; label: string }
+
+export const COMMERCIAL_CATEGORIES: CommercialCategory[] = [
+ { key: 'office', label: 'Office' },
+ { key: 'retail', label: 'Retail' },
+ { key: 'industrial', label: 'Industrial' },
+ { key: 'hospitality', label: 'Hospitality' },
+ { key: 'studio', label: 'Studio / Media' },
+ { key: 'parking', label: 'Parking' },
+ { key: 'other', label: 'Other Commercial' },
+];
+
+/** Ordered [regex, type] rules — first match wins (most specific first). */
+const RULES: [RegExp, CommercialType][] = [
+ [/motion picture|radio|television|studio/i, 'studio'],
+ [/hotel|motel|hospitality|lodging/i, 'hospitality'],
+ [/parking/i, 'parking'],
+ [/office|professional building|bank|savings/i, 'office'],
+ [/warehous|distribution|storage|manufactur|industrial|food processing|mineral|lumber|open storage/i, 'industrial'],
+ [/store|shopping|supermarket|department|restaurant|cocktail|service station|repair shop|paint shop|laundry|auto|recreation equipment|wholesale|outlet|nursery|greenhouse|kennel|retail/i, 'retail'],
+];
+
+/**
+ * Classify an LA assessor row into a canonical commercial type.
+ * Returns null when the row is not commercial/industrial (residential, farm, etc.).
+ */
+export function classifyCommercial(useDesc1: string | null, useDesc2: string | null): CommercialType | null {
+ const d1 = (useDesc1 || '').trim();
+ if (d1 !== 'Commercial' && d1 !== 'Industrial') return null;
+ const d2 = (useDesc2 || '').trim();
+ for (const [re, type] of RULES) if (re.test(d2)) return type;
+ // Industrial-bucket rows with an unmatched/blank sub-type default to industrial; else other.
+ return d1 === 'Industrial' ? 'industrial' : 'other';
+}
+
+export function categoryLabel(type: CommercialType): string {
+ return COMMERCIAL_CATEGORIES.find(c => c.key === type)?.label ?? 'Other Commercial';
+}
diff --git a/src/server/commercial.ts b/src/server/commercial.ts
new file mode 100644
index 0000000..084af09
--- /dev/null
+++ b/src/server/commercial.ts
@@ -0,0 +1,132 @@
+/**
+ * LA-region commercial real-estate API — the "local commercial RE news" layer.
+ * Reads the materialized `commercial_parcel` table (see 005_commercial.sql +
+ * src/ingest/commercial/la_assessor.ts). LA carries assessed values + last
+ * recording date but NO sale price / owner (assessor-roll limits), so the feed is
+ * "recorded activity + assessed value" — never fabricated prices.
+ *
+ * GET /api/commercial/types -> taxonomy + per-type counts
+ * GET /api/commercial/search?type&city&q&min&max&sort&limit&offset
+ * GET /api/commercial/feed?type&city&limit -> recent recorded activity
+ * GET /api/commercial/marquee?type&city&limit -> top assets by assessed value
+ * GET /api/commercial/property/:ain -> single-parcel detail + context
+ */
+import type { Express, Request, Response } from 'express';
+import { query } from '../../db/pool.ts';
+import { COMMERCIAL_CATEGORIES, categoryLabel, type CommercialType } from '../lib/commercial_types.ts';
+
+const COUNTY = '06037'; // LA County (first region; multi-county-ready via ?county)
+const likeEscape = (s: string) => s.replace(/[\\%_]/g, c => '\\' + c);
+const TYPE_KEYS = new Set(COMMERCIAL_CATEGORIES.map(c => c.key));
+const clampLimit = (v: unknown, def: number, max: number) => Math.min(max, Math.max(1, Number(v) || def));
+
+/** shared WHERE builder for type/city/q/value filters */
+function buildFilter(q: Request['query']): { where: string; params: unknown[] } {
+ const county = String(q.county || COUNTY);
+ const params: unknown[] = [county];
+ const parts = ['county_fips = $1'];
+ if (q.type && TYPE_KEYS.has(String(q.type) as CommercialType)) { params.push(String(q.type)); parts.push(`ctype = $${params.length}`); }
+ if (q.city) { params.push(String(q.city).toUpperCase()); parts.push(`city = $${params.length}`); }
+ if (q.q) { params.push('%' + likeEscape(String(q.q).toUpperCase()) + '%'); parts.push(`(upper(address) LIKE $${params.length} ESCAPE '\\' OR upper(city) LIKE $${params.length} ESCAPE '\\')`); }
+ if (q.min) { params.push(Number(q.min)); parts.push(`assessed_total >= $${params.length}`); }
+ if (q.max) { params.push(Number(q.max)); parts.push(`assessed_total <= $${params.length}`); }
+ return { where: parts.join(' AND '), params };
+}
+
+const SORTS: Record<string, string> = {
+ value_desc: 'assessed_total DESC NULLS LAST',
+ value_asc: 'assessed_total ASC NULLS LAST',
+ recent: 'recording_date DESC NULLS LAST',
+ sqft_desc: 'sqft DESC NULLS LAST',
+};
+
+function shape(r: any) {
+ return {
+ ain: r.ain, address: r.address, city: r.city, zip: r.zip,
+ type: r.ctype, type_label: categoryLabel(r.ctype), use_desc: r.use_desc,
+ assessed_total: r.assessed_total != null ? Number(r.assessed_total) : null,
+ assessed_land: r.assessed_land != null ? Number(r.assessed_land) : null,
+ assessed_improvement: r.assessed_imp != null ? Number(r.assessed_imp) : null,
+ roll_year: r.roll_year, recording_date: r.recording_date,
+ sqft: r.sqft, year_built: r.year_built, units: r.units,
+ };
+}
+
+export function mountCommercial(app: Express) {
+ // taxonomy + per-type counts (drives the filter UI)
+ app.get('/api/commercial/types', async (req: Request, res: Response) => {
+ try {
+ const county = String(req.query.county || COUNTY);
+ const r = await query<{ ctype: string; n: string }>(
+ `SELECT ctype, count(*)::text n FROM commercial_parcel WHERE county_fips=$1 GROUP BY ctype`, [county]);
+ const counts = Object.fromEntries(r.rows.map(x => [x.ctype, Number(x.n)]));
+ const total = r.rows.reduce((s, x) => s + Number(x.n), 0);
+ res.json({ county, total, types: COMMERCIAL_CATEGORIES.map(c => ({ ...c, count: counts[c.key] || 0 })) });
+ } catch (e: any) { res.status(500).json({ error: String(e.message || e) }); }
+ });
+
+ // top cities by commercial count (for the filter dropdown)
+ app.get('/api/commercial/cities', async (req: Request, res: Response) => {
+ try {
+ const county = String(req.query.county || COUNTY);
+ const r = await query<{ city: string; n: string }>(
+ `SELECT city, count(*)::text n FROM commercial_parcel WHERE county_fips=$1 AND city IS NOT NULL
+ GROUP BY city ORDER BY count(*) DESC LIMIT 60`, [county]);
+ res.json({ cities: r.rows.map(x => ({ city: x.city, count: Number(x.n) })) });
+ } catch (e: any) { res.status(500).json({ error: String(e.message || e) }); }
+ });
+
+ // filtered browse/search
+ app.get('/api/commercial/search', async (req: Request, res: Response) => {
+ try {
+ const { where, params } = buildFilter(req.query);
+ const sort = SORTS[String(req.query.sort || 'value_desc')] || SORTS.value_desc;
+ const limit = clampLimit(req.query.limit, 60, 200);
+ const offset = Math.max(0, Number(req.query.offset) || 0);
+ const total = await query<{ n: string }>(`SELECT count(*)::text n FROM commercial_parcel WHERE ${where}`, params);
+ const rows = await query(
+ `SELECT * FROM commercial_parcel WHERE ${where} ORDER BY ${sort}, ain LIMIT $${params.length + 1} OFFSET $${params.length + 2}`,
+ [...params, limit, offset]);
+ res.json({ total: Number(total.rows[0].n), count: rows.rows.length, offset, results: rows.rows.map(shape) });
+ } catch (e: any) { res.status(500).json({ error: String(e.message || e) }); }
+ });
+
+ // recent recorded activity — the "news feed" (recording date, no price for LA)
+ app.get('/api/commercial/feed', async (req: Request, res: Response) => {
+ try {
+ const { where, params } = buildFilter(req.query);
+ const limit = clampLimit(req.query.limit, 40, 100);
+ const rows = await query(
+ `SELECT * FROM commercial_parcel WHERE ${where} AND recording_date IS NOT NULL
+ ORDER BY recording_date DESC, assessed_total DESC NULLS LAST LIMIT $${params.length + 1}`,
+ [...params, limit]);
+ res.json({ count: rows.rows.length, results: rows.rows.map(shape) });
+ } catch (e: any) { res.status(500).json({ error: String(e.message || e) }); }
+ });
+
+ // marquee assets — top by assessed value (the newsworthy trophy hook)
+ app.get('/api/commercial/marquee', async (req: Request, res: Response) => {
+ try {
+ const { where, params } = buildFilter(req.query);
+ const limit = clampLimit(req.query.limit, 12, 50);
+ const rows = await query(
+ `SELECT * FROM commercial_parcel WHERE ${where} AND assessed_total IS NOT NULL
+ ORDER BY assessed_total DESC LIMIT $${params.length + 1}`, [...params, limit]);
+ res.json({ count: rows.rows.length, results: rows.rows.map(shape) });
+ } catch (e: any) { res.status(500).json({ error: String(e.message || e) }); }
+ });
+
+ // single-parcel detail + county market context
+ app.get('/api/commercial/property/:ain', async (req: Request, res: Response) => {
+ try {
+ const county = String(req.query.county || COUNTY);
+ const r = await query(`SELECT * FROM commercial_parcel WHERE county_fips=$1 AND ain=$2`, [county, req.params.ain]);
+ if (!r.rows.length) return res.status(404).json({ error: 'not found' });
+ const p = shape(r.rows[0]);
+ res.json({
+ ...p,
+ pricing_note: 'LA County assessor roll — assessed values + last recording date only. Sale price and owner are not in the public roll; market listing prices come from the commercial listings feed.',
+ });
+ } catch (e: any) { res.status(500).json({ error: String(e.message || e) }); }
+ });
+}
diff --git a/src/server/index.ts b/src/server/index.ts
index 3be95d3..6f031d9 100644
--- a/src/server/index.ts
+++ b/src/server/index.ts
@@ -10,6 +10,7 @@ import { query } from '../../db/pool.ts';
import { mountWatchlistAdmin } from './watchlist_admin.ts';
import { mountProperty } from './property.ts';
import { mountListings } from './listings.ts';
+import { mountCommercial } from './commercial.ts';
const __dirname = dirname(fileURLToPath(import.meta.url));
const ROOT = join(__dirname, '..', '..');
@@ -342,6 +343,7 @@ app.get('/api/markets.csv', async (req, res) => {
mountWatchlistAdmin(app);
mountProperty(app);
mountListings(app);
+mountCommercial(app);
app.use(express.static(join(ROOT, 'public')));
← e3663e8 NYC ACRIS recorded sales -> PLUTO parcels (188k lots, 5 boro
·
back to Nationalrealestate
·
Wire Commercial into main site nav across all pages (discove 0b1ee1d →