← back to Commercialrealestate
scripts/fetch-valley-pools.js
180 lines
#!/usr/bin/env node
// fetch-valley-pools.js — build data/valley-pools.json: ACTIVE single-family homes WITH A POOL in the
// San Fernando Valley under $1,200,000. Feed-first, $0: same Redfin gis-csv feed the SFR sweep uses,
// but with &pool=1 (Redfin's real "has pool" filter — verified: Reseda 39 homes -> 9 with pool) and
// &max_price=1200000. uipt=1 restricts to detached single-family. One plain fetch per SFV region.
//
// This is a NEW, self-contained artifact for the "new crcp / valley pools" landing — it does NOT
// touch the shared sfr table or serve.js, and does NOT edit the TK-10703-owned pages.
const fs = require('fs');
const path = require('path');
const ROOT = path.resolve(__dirname, '..');
// ---- reusable knobs (so anyone can re-run for different criteria, not just the SFV/pool default) ----
// MAX_PRICE=1500000 node scripts/fetch-valley-pools.js # raise the price ceiling
// POOL=0 node scripts/fetch-valley-pools.js # drop the has-pool filter (all SFV SFR)
// OUT=data/foo.json node scripts/fetch-valley-pools.js # write somewhere else
const WANT_POOL = process.env.POOL !== '0';
const OUT_FILE = process.env.OUT || path.join(ROOT, 'data', 'valley-pools.json');
// ---- CSV parser + SFR row extractor (lifted verbatim from fetch-sfr-redfin.js) ----
function parseCSV(text) {
const rows = []; let i = 0, field = '', row = [], inQ = false;
while (i < text.length) {
const c = text[i];
if (inQ) { if (c === '"') { if (text[i + 1] === '"') { field += '"'; i++; } else inQ = false; } else field += c; }
else {
if (c === '"') inQ = true;
else if (c === ',') { row.push(field); field = ''; }
else if (c === '\n') { row.push(field); rows.push(row); row = []; field = ''; }
else if (c === '\r') { /* skip */ }
else field += c;
}
i++;
}
if (field.length || row.length) { row.push(field); rows.push(row); }
return rows;
}
function sfrsFromCSV(text) {
const rows = parseCSV(text);
const hi = rows.findIndex(r => r.join(',').includes('PROPERTY TYPE') && r.join(',').includes('ADDRESS'));
if (hi < 0) return [];
const H = rows[hi].map(h => h.trim());
const col = name => H.findIndex(h => h.toUpperCase() === name || h.toUpperCase().startsWith(name));
const ci = {
type: col('PROPERTY TYPE'), addr: col('ADDRESS'), city: col('CITY'), zip: col('ZIP OR POSTAL CODE'),
price: col('PRICE'), beds: col('BEDS'), baths: col('BATHS'), sqft: col('SQUARE FEET'),
year: col('YEAR BUILT'), status: col('STATUS'), dom: col('DAYS ON MARKET'),
lat: col('LATITUDE'), lng: col('LONGITUDE'),
url: H.findIndex(h => h.toUpperCase().startsWith('URL')), mls: col('MLS#')
};
const out = [];
for (let r = hi + 1; r < rows.length; r++) {
const row = rows[r];
if (!row || row.length < 5) continue;
const ptype = (row[ci.type] || '').trim();
if (!/single.?family|^house$/i.test(ptype)) continue;
const price = Number((row[ci.price] || '').replace(/[^\d.]/g, ''));
const addr = (row[ci.addr] || '').trim();
const city = (row[ci.city] || '').trim();
if (!addr || !price) continue;
const url = (row[ci.url] || '').trim();
// Drop attached PUD/townhome units Redfin occasionally tags "Single Family" — a unit with a
// SHARED/community pool is not a detached single-family home with its own pool. Tells: a "#N"
// in the address or "/unit-N/" in the Redfin URL.
if (/\/unit-/i.test(url) || /#\s*\w/.test(addr)) continue;
const mls = (row[ci.mls] || '').trim();
const id = 'rdf' + (mls || (addr + (row[ci.zip] || '')).replace(/\s+/g, '').toLowerCase());
out.push({
id, address: addr.slice(0, 120), city, zip: (row[ci.zip] || '').trim(), price,
beds: ci.beds >= 0 && row[ci.beds] ? Number(row[ci.beds]) || null : null,
baths: ci.baths >= 0 && row[ci.baths] ? Number(row[ci.baths]) || null : null,
sqft: ci.sqft >= 0 && row[ci.sqft] ? Math.round(Number(String(row[ci.sqft]).replace(/[^\d.]/g, ''))) || null : null,
year_built: ci.year >= 0 && row[ci.year] ? Number(row[ci.year]) || null : null,
days_on_market: ci.dom >= 0 && row[ci.dom] !== '' && row[ci.dom] != null ? Number(String(row[ci.dom]).replace(/[^\d]/g, '')) : null,
market_status: ci.status >= 0 ? ((row[ci.status] || '').trim() || null) : null,
lat: ci.lat >= 0 && row[ci.lat] && isFinite(+row[ci.lat]) ? +row[ci.lat] : null,
lng: ci.lng >= 0 && row[ci.lng] && isFinite(+row[ci.lng]) ? +row[ci.lng] : null,
source: url ? (url.startsWith('http') ? url : 'https://www.redfin.com' + url) : null,
// pool feed (&pool=1) guarantees a pool; when POOL=0 the feed isn't pool-filtered so it's unknown
has_pool: WANT_POOL ? true : null
});
}
return out;
}
// ---- SFV region set (Redfin region_ids resolved from data/redfin-neighborhoods.json + regions.json) ----
const REGIONS = [
// neighborhoods (region_type=1)
['Van Nuys', 2859, 1], ['Reseda', 2257, 1], ['North Hollywood', 1905, 1], ['Sherman Oaks', 2440, 1],
['Encino', 899, 1], ['Woodland Hills', 10848, 1], ['Northridge', 481131, 1], ['Canoga Park', 355, 1],
['Studio City', 2629, 1], ['Panorama City', 10731, 1], ['Sun Valley', 10243, 1], ['Granada Hills', 1126, 1],
['Tarzana', 2692, 1], ['Winnetka', 3099, 1], ['West Hills', 3006, 1], ['Lake Balboa', 112192, 1],
['Valley Village', 2852, 1], ['Valley Glen', 2850, 1], ['Toluca Lake', 2749, 1], ['Arleta', 72, 1],
['Pacoima', 2079, 1], ['Mission Hills', 1734, 1], ['Sylmar', 2675, 1], ['Tujunga', 2642, 1],
['Sunland', 9719, 1], ['Porter Ranch', 2179, 1], ['Chatsworth', 460, 1], ['North Hills', 351587, 1],
['Sepulveda', 14610, 1], ['Shadow Hills', 11702, 1], ['Lake View Terrace', 10632, 1], ['Cahuenga Pass', 59009, 1],
// valley cities (region_type=6)
['Burbank', 2320, 6], ['Calabasas', 2471, 6], ['Hidden Hills', 8583, 6], ['San Fernando', 16938, 6]
];
const MAX_PRICE = +process.env.MAX_PRICE || 1200000;
async function fetchRegion(name, id, type) {
const url = `https://www.redfin.com/stingray/api/gis-csv?al=1®ion_id=${id}®ion_type=${type}` +
`&uipt=1&num_homes=350&status=9&sf=1,2,3,5,6,7&v=8${WANT_POOL ? '&pool=1' : ''}&max_price=${MAX_PRICE}`;
try {
const r = await fetch(url, { headers: { accept: 'text/csv', 'user-agent': 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36' } });
if (!r.ok) return { name, ok: false, http: r.status, homes: [] };
const text = await r.text();
if (!text.includes('PROPERTY TYPE')) return { name, ok: false, http: r.status, homes: [] };
const homes = sfrsFromCSV(text).filter(h => h.price < MAX_PRICE).map(h => ({ ...h, region: name }));
return { name, ok: true, http: r.status, homes };
} catch (e) { return { name, ok: false, err: String(e.message).split('\n')[0], homes: [] }; }
}
(async () => {
const seen = new Map();
const perRegion = [];
for (const [name, id, type] of REGIONS) {
const res = await fetchRegion(name, id, type);
perRegion.push({ region: name, ok: res.ok, http: res.http || null, count: res.homes.length, err: res.err || null });
for (const h of res.homes) { const k = h.source || h.id; if (!seen.has(k)) seen.set(k, h); }
process.stderr.write(` ${name.padEnd(20)} ${res.ok ? String(res.homes.length).padStart(3) + ' pool homes' : 'FAIL ' + (res.http || res.err)}\n`);
await new Promise(r => setTimeout(r, 400)); // polite pacing
}
let homes = [...seen.values()].sort((a, b) => a.price - b.price);
// ---- ENRICH 1: price history from the sfr table (repeated sweeps track prev_price/price_changed_at;
// the one-shot feed can't). Motivated-seller signal = a real price cut. Best-effort: skip silently if
// the local DB isn't reachable (prod/other machines) so the fetcher still works standalone. ----
try {
const { Pool } = require('pg');
const pg = new Pool({ host: '/tmp', port: 5432, database: 'cre', user: process.env.USER || 'stevestudio2' });
const urls = homes.map(h => h.source).filter(Boolean);
const r = await pg.query(
'SELECT source, prev_price, price_changed_at, listed_date, days_on_market FROM sfr WHERE source = ANY($1)', [urls]);
const byUrl = new Map(r.rows.map(x => [x.source, x]));
for (const h of homes) {
const m = byUrl.get(h.source); if (!m) continue;
h.prev_price = m.prev_price != null ? +m.prev_price : null;
h.price_changed_at = m.price_changed_at || null;
h.listed_date = m.listed_date || null;
if (h.days_on_market == null && m.days_on_market != null) h.days_on_market = +m.days_on_market;
h.price_cut = (h.prev_price && h.prev_price > h.price) ? (h.prev_price - h.price) : 0;
}
await pg.end();
} catch (e) { process.stderr.write(' (price-history enrich skipped: ' + String(e.message).split('\n')[0] + ')\n'); }
// ---- ENRICH 2: $/sqft + region-median deal score. 'value buy' = >=12% under its region's median $/sqft. ----
for (const h of homes) h.ppsf = h.sqft ? Math.round(h.price / h.sqft) : null;
const byRegion = {};
for (const h of homes) if (h.ppsf) (byRegion[h.region] || (byRegion[h.region] = [])).push(h.ppsf);
const regionMed = {};
for (const [reg, arr] of Object.entries(byRegion)) { arr.sort((a, b) => a - b); regionMed[reg] = arr[Math.floor(arr.length / 2)]; }
for (const h of homes) {
const med = regionMed[h.region];
h.region_ppsf_median = med || null;
h.ppsf_vs_region = (h.ppsf && med) ? Math.round((h.ppsf / med - 1) * 100) : null; // negative = cheaper than peers
h.value_buy = (h.ppsf_vs_region != null && h.ppsf_vs_region <= -12);
}
const failed = perRegion.filter(r => !r.ok).map(r => r.region);
const cuts = homes.filter(h => h.price_cut > 0).length;
const values = homes.filter(h => h.value_buy).length;
const out = {
generated: new Date().toISOString(),
label: `Active single-family homes${WANT_POOL ? ' WITH A POOL' : ''} in the San Fernando Valley, under $${MAX_PRICE.toLocaleString()}. Source: Redfin gis-csv feed${WANT_POOL ? ' (&pool=1 = Redfin\'s verified has-pool filter)' : ''}; price-history + $/sqft deal-score enriched from the cre.sfr sweep table. Feed-first, $0.`,
criteria: { property_type: 'Single-family (detached)', region: 'San Fernando Valley', pool: WANT_POOL, max_price: MAX_PRICE, status: 'active' },
count: homes.length,
price_cuts: cuts,
value_buys: values,
regions_ok: perRegion.length - failed.length,
regions_total: perRegion.length,
regions_failed: failed,
per_region: perRegion,
homes
};
fs.writeFileSync(OUT_FILE, JSON.stringify(out, null, 0));
console.log(JSON.stringify({ count: homes.length, price_cuts: cuts, value_buys: values, regions_ok: out.regions_ok, regions_total: out.regions_total, regions_failed: failed, out: OUT_FILE }, null, 2));
})();