← back to Nationalrealestate
NYC search hardening: ordinal-stripping address normalization + nearest-house-number fallback ('350 5th Ave' now resolves the Empire State lot at 338 5 AVENUE); streaming CSV parser for the King/Cook adapters
2d46b435fb4a7661a4ab9e7c23371cdfa52f92db · 2026-07-21 17:03:47 -0700 · Steve Abrams
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Files touched
A src/ingest/parcels/csv.tsM src/server/property.ts
Diff
commit 2d46b435fb4a7661a4ab9e7c23371cdfa52f92db
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Tue Jul 21 17:03:47 2026 -0700
NYC search hardening: ordinal-stripping address normalization + nearest-house-number fallback ('350 5th Ave' now resolves the Empire State lot at 338 5 AVENUE); streaming CSV parser for the King/Cook adapters
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
---
src/ingest/parcels/csv.ts | 58 +++++++++++++++++++++++++++++++++++++++++++++++
src/server/property.ts | 49 ++++++++++++++++++++++++++++++---------
2 files changed, 96 insertions(+), 11 deletions(-)
diff --git a/src/ingest/parcels/csv.ts b/src/ingest/parcels/csv.ts
new file mode 100644
index 0000000..f23a1dc
--- /dev/null
+++ b/src/ingest/parcels/csv.ts
@@ -0,0 +1,58 @@
+/**
+ * Streaming CSV parser — async generator of string[] records. Handles quoted
+ * fields (embedded commas/newlines/"" escapes), CRLF, and never buffers the
+ * whole file (PLUTO ~165MB, King RPSale ~500MB unzipped).
+ */
+import { createReadStream } from 'node:fs';
+
+export async function* csvRecords(path: string, encoding: BufferEncoding = 'utf8'): AsyncGenerator<string[]> {
+ const stream = createReadStream(path, { encoding, highWaterMark: 1 << 20 });
+ let field = '';
+ let record: string[] = [];
+ let inQuotes = false;
+ let sawQuote = false; // current field was quoted (so '' stays distinguishable)
+ let prevCR = false;
+
+ for await (const chunk of stream) {
+ const s = chunk as string;
+ for (let i = 0; i < s.length; i++) {
+ const c = s[i];
+ if (inQuotes) {
+ if (c === '"') {
+ if (s[i + 1] === '"') { field += '"'; i++; }
+ else inQuotes = false;
+ } else field += c;
+ continue;
+ }
+ if (c === '"') { inQuotes = true; sawQuote = true; continue; }
+ if (c === ',') { record.push(field); field = ''; sawQuote = false; prevCR = false; continue; }
+ if (c === '\r') { prevCR = true; continue; }
+ if (c === '\n') {
+ record.push(field); field = ''; sawQuote = false; prevCR = false;
+ if (record.length > 1 || record[0] !== '') yield record;
+ record = [];
+ continue;
+ }
+ if (prevCR) { field += '\r'; prevCR = false; } // lone CR inside a field
+ field += c;
+ }
+ }
+ if (field !== '' || sawQuote || record.length) {
+ record.push(field);
+ if (record.length > 1 || record[0] !== '') yield record;
+ }
+}
+
+/** Header-mapped variant: yields Record<lowercased header, value>. */
+export async function* csvObjects(path: string, encoding: BufferEncoding = 'utf8'): AsyncGenerator<Record<string, string>> {
+ let headers: string[] | null = null;
+ for await (const rec of csvRecords(path, encoding)) {
+ if (!headers) {
+ headers = rec.map(h => h.replace(/^/, '').trim().toLowerCase());
+ continue;
+ }
+ const obj: Record<string, string> = {};
+ for (let i = 0; i < headers.length; i++) obj[headers[i]] = (rec[i] ?? '').trim();
+ yield obj;
+ }
+}
diff --git a/src/server/property.ts b/src/server/property.ts
index 14a127f..9fa4466 100644
--- a/src/server/property.ts
+++ b/src/server/property.ts
@@ -149,29 +149,56 @@ function buildContextSections(county: string, ctx: Awaited<ReturnType<typeof cou
interface PgParcel {
source_id: string; address: string; norm_address: string; city: string; zip: string;
lat: number | null; lng: number | null; year_built: number | null; sqft: number | null;
+ beds: number | null; baths: number | null;
units: number | null; use_desc: string | null; land_value: number | null;
improvement_value: number | null; total_value: number | null; tax_year: string | null;
owner_name: string | null; zoning: string | null;
+ last_sale_date: string | null; last_sale_price: number | null; extra: any;
}
-async function pgSearch(county: string, q: string) {
- const norm = q.trim().toUpperCase();
- const r = await query<PgParcel>(
- `SELECT source_id, address, city, zip, year_built, sqft, units, use_desc, total_value, owner_name, zoning
- FROM parcel WHERE county_fips = $1 AND norm_address LIKE $2 ESCAPE '\\'
+/** "350 5th Ave." -> "350 5 AVE" — PLUTO/King store numeric streets without
+ * ordinals; abbreviations then match as LIKE prefixes (AVE% hits AVENUE). */
+function normAddressQuery(q: string): string {
+ return q.trim().toUpperCase()
+ .replace(/[.,#]/g, ' ')
+ .replace(/\b(\d+)(?:ST|ND|RD|TH)\b/g, '$1')
+ .replace(/\s+/g, ' ')
+ .trim();
+}
+const SEARCH_COLS = `source_id, address, city, zip, year_built, sqft, beds, baths, units, use_desc, total_value, owner_name, zoning`;
+const asMatch = (p: any) => ({
+ ain: p.source_id, address: p.address, year_built: p.year_built, sqft: p.sqft,
+ bedrooms: p.beds, bathrooms: p.baths, units: p.units, use: p.use_desc,
+ assessed_value: p.total_value, owner: p.owner_name, zoning: p.zoning,
+});
+async function pgSearchRows(county: string, q: string): Promise<any[]> {
+ const norm = normAddressQuery(q);
+ const exact = await query<PgParcel>(
+ `SELECT ${SEARCH_COLS} FROM parcel WHERE county_fips = $1 AND norm_address LIKE $2 ESCAPE '\\'
ORDER BY norm_address LIMIT 25`, [county, likeEscape(norm) + '%'],
);
- return r.rows.map((p: any) => ({
- ain: p.source_id, address: p.address, year_built: p.year_built, sqft: p.sqft,
- bedrooms: null, bathrooms: null, units: p.units, use: p.use_desc,
- assessed_value: p.total_value, owner: p.owner_name, zoning: p.zoning,
- }));
+ if (exact.rows.length) return exact.rows;
+ // Fallback: house number not on file (e.g. Empire State is "338 5 AVENUE" for
+ // the 338-350 range) — same street, nearest house number first.
+ const m = norm.match(/^(\d+)\s+(.+)$/);
+ if (!m) return [];
+ const houseNo = Number(m[1]);
+ const near = await query<PgParcel>(
+ `SELECT ${SEARCH_COLS} FROM parcel
+ WHERE county_fips = $1 AND norm_address LIKE $2 ESCAPE '\\'
+ ORDER BY ABS(COALESCE((regexp_match(norm_address, '^(\\d+)'))[1]::bigint, 0) - $3) LIMIT 25`,
+ [county, '%' + likeEscape(m[2]) + '%', houseNo],
+ );
+ return near.rows;
+}
+async function pgSearch(county: string, q: string) {
+ return (await pgSearchRows(county, q)).map(asMatch);
}
async function pgProperty(county: string, loc: string) {
let r = await query<PgParcel>(`SELECT * FROM parcel WHERE county_fips=$1 AND source_id=$2`, [county, loc]);
if (!r.rows.length) {
r = await query<PgParcel>(
`SELECT * FROM parcel WHERE county_fips=$1 AND norm_address LIKE $2 ESCAPE '\\' LIMIT 1`,
- [county, likeEscape(loc.trim().toUpperCase()) + '%'],
+ [county, likeEscape(normAddressQuery(loc)) + '%'],
);
}
const p = r.rows[0];
← da797f6 M-P2: NYC MapPLUTO parcels (858,602 lots, 5 boroughs) — prop
·
back to Nationalrealestate
·
auto-save: 2026-07-21T17:08:42 (8 files) — package.json publ 1c7f851 →