← back to Commercialrealestate
scrapers: capture LATITUDE/LONGITUDE from gis-csv -> lat/lng (was discarded)
92df97330657ed1a81346dab1b444ee801b21718 · 2026-07-10 08:13:57 -0700 · Steve Abrams
~6,100 active residential listings (5,081 SFR + 1,031 condo) were invisible on the map for lack of
coordinates. The feed carries LATITUDE/LONGITUDE; now parsed + upserted (COALESCE so an existing
geocode is never nulled). Map coverage climbs to ~100% as sweeps run. Verified: SFR 215/215,
condo 68/68 on freshly-swept regions.
Files touched
M scripts/fetch-condos-redfin.jsM scripts/fetch-sfr-redfin.js
Diff
commit 92df97330657ed1a81346dab1b444ee801b21718
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Fri Jul 10 08:13:57 2026 -0700
scrapers: capture LATITUDE/LONGITUDE from gis-csv -> lat/lng (was discarded)
~6,100 active residential listings (5,081 SFR + 1,031 condo) were invisible on the map for lack of
coordinates. The feed carries LATITUDE/LONGITUDE; now parsed + upserted (COALESCE so an existing
geocode is never nulled). Map coverage climbs to ~100% as sweeps run. Verified: SFR 215/215,
condo 68/68 on freshly-swept regions.
---
scripts/fetch-condos-redfin.js | 12 ++++++++----
scripts/fetch-sfr-redfin.js | 12 ++++++++----
2 files changed, 16 insertions(+), 8 deletions(-)
diff --git a/scripts/fetch-condos-redfin.js b/scripts/fetch-condos-redfin.js
index 5d40bff..046e88a 100644
--- a/scripts/fetch-condos-redfin.js
+++ b/scripts/fetch-condos-redfin.js
@@ -96,6 +96,7 @@ function condosFromCSV(text, regionName) {
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'), hoa: col('HOA/MONTH'), status: col('STATUS'), dom: col('DAYS ON MARKET'),
+ lat: col('LATITUDE'), lng: col('LONGITUDE'),
url: H.findIndex(h => h.toUpperCase().startsWith('URL')), source: col('SOURCE'), mls: col('MLS#')
};
const out = [];
@@ -126,6 +127,8 @@ function condosFromCSV(text, regionName) {
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,
property_type: ptype,
project_name: null, // CSV has no HOA/project name; classifier uses address+zip vs FHA list
listing_text: ptype, // minimal text; richer description not in the feed
@@ -234,8 +237,8 @@ function condosFromCSV(text, regionName) {
const firmId = c.firm_name ? await brokerdb.upsertFirm(c.firm_name) : null;
await brokerdb.pool.query(
`INSERT INTO condo(id,address,city,zip,price,hoa,beds,baths,sqft,year_built,project_name,
- listing_text,warrantable_status,warrant_source,warrant_signals,firm_id,firm_name,broker_name,source,last_seen,status,days_on_market,market_status,listed_date)
- VALUES($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,now(),'active',$20::int,$21, CASE WHEN $20::int IS NOT NULL THEN CURRENT_DATE - $20::int ELSE NULL END)
+ listing_text,warrantable_status,warrant_source,warrant_signals,firm_id,firm_name,broker_name,source,last_seen,status,days_on_market,market_status,listed_date,lat,lng)
+ VALUES($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,now(),'active',$20::int,$21, CASE WHEN $20::int IS NOT NULL THEN CURRENT_DATE - $20::int ELSE NULL END,$22::float8,$23::float8)
ON CONFLICT(id) DO UPDATE SET price=EXCLUDED.price, hoa=EXCLUDED.hoa,
warrantable_status=EXCLUDED.warrantable_status, warrant_source=EXCLUDED.warrant_source,
warrant_signals=EXCLUDED.warrant_signals,
@@ -243,10 +246,11 @@ function condosFromCSV(text, regionName) {
days_on_market=EXCLUDED.days_on_market, listed_date=EXCLUDED.listed_date,
prev_market_status=condo.market_status, market_status=EXCLUDED.market_status,
prev_price=CASE WHEN EXCLUDED.price IS DISTINCT FROM condo.price THEN condo.price ELSE condo.prev_price END,
- price_changed_at=CASE WHEN EXCLUDED.price IS DISTINCT FROM condo.price THEN now() ELSE condo.price_changed_at END`,
+ price_changed_at=CASE WHEN EXCLUDED.price IS DISTINCT FROM condo.price THEN now() ELSE condo.price_changed_at END,
+ lat=COALESCE(EXCLUDED.lat, condo.lat), lng=COALESCE(EXCLUDED.lng, condo.lng)`,
[c.id, c.address, c.city, c.zip, c.price, c.hoa, c.beds, c.baths, c.sqft, c.year_built,
c.project_name, c.listing_text, c.warrantable_status, c.warrant_source,
- JSON.stringify(c.warrant_signals), firmId, c.firm_name, c.broker_name, c.source, c.days_on_market ?? null, c.market_status ?? null]);
+ JSON.stringify(c.warrant_signals), firmId, c.firm_name, c.broker_name, c.source, c.days_on_market ?? null, c.market_status ?? null, c.lat ?? null, c.lng ?? null]);
}
process.stderr.write(`Upserted ${condos.length} into cre.condo\n`);
} catch (e) { process.stderr.write('DB upsert err: ' + e.message + '\n'); }
diff --git a/scripts/fetch-sfr-redfin.js b/scripts/fetch-sfr-redfin.js
index 95d026a..415368b 100644
--- a/scripts/fetch-sfr-redfin.js
+++ b/scripts/fetch-sfr-redfin.js
@@ -82,6 +82,7 @@ function sfrsFromCSV(text) {
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 = [];
@@ -111,6 +112,8 @@ function sfrsFromCSV(text) {
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,
listing_text: ptype,
source: url ? (url.startsWith('http') ? url : 'https://www.redfin.com' + url) : null
});
@@ -148,17 +151,18 @@ function loadRegions() {
const upsert = async (s) => {
if (!s.source) return;
const ins = await pool.query(
- `INSERT INTO sfr(id,address,city,zip,price,beds,baths,sqft,year_built,listing_text,source,last_seen,status,days_on_market,market_status,listed_date)
- VALUES($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,now(),'active',$12::int,$13, CASE WHEN $12::int IS NOT NULL THEN CURRENT_DATE - $12::int ELSE NULL END)
+ `INSERT INTO sfr(id,address,city,zip,price,beds,baths,sqft,year_built,listing_text,source,last_seen,status,days_on_market,market_status,listed_date,lat,lng)
+ VALUES($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,now(),'active',$12::int,$13, CASE WHEN $12::int IS NOT NULL THEN CURRENT_DATE - $12::int ELSE NULL END,$14::float8,$15::float8)
ON CONFLICT(id) DO UPDATE SET price=EXCLUDED.price, beds=EXCLUDED.beds, baths=EXCLUDED.baths,
sqft=EXCLUDED.sqft, year_built=EXCLUDED.year_built,
last_seen=now(), status='active', off_market_at=NULL, disposition=NULL, sold_price=NULL, sold_date=NULL,
days_on_market=EXCLUDED.days_on_market, listed_date=EXCLUDED.listed_date,
prev_market_status=sfr.market_status, market_status=EXCLUDED.market_status,
prev_price=CASE WHEN EXCLUDED.price IS DISTINCT FROM sfr.price THEN sfr.price ELSE sfr.prev_price END,
- price_changed_at=CASE WHEN EXCLUDED.price IS DISTINCT FROM sfr.price THEN now() ELSE sfr.price_changed_at END
+ price_changed_at=CASE WHEN EXCLUDED.price IS DISTINCT FROM sfr.price THEN now() ELSE sfr.price_changed_at END,
+ lat=COALESCE(EXCLUDED.lat, sfr.lat), lng=COALESCE(EXCLUDED.lng, sfr.lng)
RETURNING (xmax = 0) AS inserted`,
- [s.id, s.address, s.city, s.zip, s.price, s.beds, s.baths, s.sqft, s.year_built, s.listing_text, s.source, s.days_on_market ?? null, s.market_status ?? null])
+ [s.id, s.address, s.city, s.zip, s.price, s.beds, s.baths, s.sqft, s.year_built, s.listing_text, s.source, s.days_on_market ?? null, s.market_status ?? null, s.lat ?? null, s.lng ?? null])
.catch(() => null);
if (ins && ins.rows[0] && ins.rows[0].inserted) added++;
};
← b11c287 auto-save: 2026-07-10T06:44:22 (4 files) — data/condos-redfi
·
back to Commercialrealestate
·
auto-save: 2026-07-10T08:14:41 (1 files) — data/condos-redfi a8e7f8a →