← back to Nationalrealestate
src/server/parcels.ts
128 lines
/**
* 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,
AVG(p.lat) FILTER (WHERE p.lat IS NOT NULL) AS clat,
AVG(p.lng) FILTER (WHERE p.lng IS NOT NULL) AS clng,
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),
clat: x.clat != null ? +(+x.clat).toFixed(4) : null, clng: x.clng != null ? +(+x.clng).toFixed(4) : null,
}));
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) }); }
});
}