← back to Stayclaim
scripts/extract-news-addresses.ts
295 lines
/**
* extract-news-addresses.ts
*
* Phase 3 of the LA newspaper ingest. Sweeps OCR'd issues in news_issue for
* US street-address patterns, captures a context snippet around each match,
* and tries to match each one to a row in `listing` (by normalized
* street_number + street_name, restricted to LA-county cities to suppress
* false positives).
*
* Idempotent at the issue level — re-running on the same issue wipes its
* prior mentions and re-extracts. Sets news_issue.addresses_extracted_at on
* success so subsequent runs skip already-processed issues.
*
* Designed to run in parallel with the Phase 2 OCR puller — only reads
* news_issue (no UPDATE conflict) and writes to a separate table.
*
* Usage: npx tsx scripts/extract-news-addresses.ts [--paper=LADailyHerald]
* [--rerun]
* [--limit=100]
*
* Tier: A (gov-tier source-of-source, but address-mention itself is a
* derived/inferred fact — pin Tier B downstream when displaying.)
*/
import { Pool } from 'pg';
const pool = new Pool({
host: process.env.PGHOST ?? '/tmp',
database: process.env.PGDATABASE ?? 'stayclaim',
user: process.env.PGUSER ?? process.env.USER,
password: process.env.PGPASSWORD,
port: parseInt(process.env.PGPORT ?? '5432', 10),
max: 4,
});
const args = Object.fromEntries(
process.argv.slice(2).map(a => {
const m = a.match(/^--([^=]+)(?:=(.*))?$/);
return m ? [m[1], m[2] ?? 'true'] : [a, 'true'];
})
);
const PAPER = args.paper as string | undefined;
const RERUN = args.rerun === 'true';
const LIMIT = args.limit ? parseInt(args.limit as string, 10) : undefined;
// LA-area cities — used to filter listing matches and reduce false positives
// when a number+street happens to match an address in another state.
const LA_CITIES = [
'Los Angeles', 'West Hollywood', 'Beverly Hills', 'Santa Monica',
'Pasadena', 'Culver City', 'Long Beach', 'Burbank', 'Glendale',
'Hollywood', 'Venice', 'Inglewood', 'Compton', 'Pomona',
'Manhattan Beach', 'Hermosa Beach', 'Redondo Beach', 'El Segundo',
'Torrance', 'San Pedro', 'Wilmington', 'Watts', 'South Gate',
'Huntington Park', 'Whittier', 'Anaheim', 'Santa Ana',
];
/** Suffix → normalized abbrev (used in both extraction normalization and listing match). */
const SUFFIX_MAP: Record<string, string> = {
street: 'st', st: 'st',
avenue: 'ave', ave: 'ave',
boulevard: 'blvd', blvd: 'blvd',
drive: 'dr', dr: 'dr',
road: 'rd', rd: 'rd',
way: 'way',
place: 'pl', pl: 'pl',
court: 'ct', ct: 'ct',
lane: 'ln', ln: 'ln',
highway: 'hwy', hwy: 'hwy',
parkway: 'pkwy', pkwy: 'pkwy',
circle: 'cir', cir: 'cir',
terrace: 'ter', ter: 'ter',
square: 'sq', sq: 'sq',
};
const SUFFIX_RE = Object.keys(SUFFIX_MAP).join('|');
// US street-address regex — case insensitive. Captures: number, optional dir, name, suffix.
// Requires capitalized first letter on the street name to suppress sentence-noise matches.
// Allows 1-4 word street names (e.g., "Wilshire", "San Vicente", "South La Brea", "Avenue of the Stars").
const ADDR_RE = new RegExp(
String.raw`\b(\d{2,5})` + // number
String.raw`\s+(N\.?|S\.?|E\.?|W\.?|North|South|East|West)?\s*` + // optional dir
String.raw`([A-Z][A-Za-z'.\-]+(?:\s+(?:de\s+|of\s+|the\s+|la\s+|las\s+|los\s+)?[A-Z][A-Za-z'.\-]+){0,3})` + // 1-4 cap words
String.raw`\s+(${SUFFIX_RE})\.?` + // suffix
String.raw`\b`,
'g'
);
function normalizeStreetName(name: string): string {
return name
.toLowerCase()
.replace(/\./g, '')
.replace(/[^a-z0-9\s]/g, ' ')
.replace(/\s+/g, ' ')
.trim();
}
function normalizeSuffix(suffix: string): string {
return SUFFIX_MAP[suffix.toLowerCase().replace(/\./g, '')] ?? suffix.toLowerCase();
}
function normalizeListingAddr(line1: string | null): { number: string; street: string } | null {
if (!line1) return null;
// Try to split into number + rest
const m = line1.trim().match(/^(\d{1,5})\s+(.+)$/);
if (!m) return null;
const number = m[1];
const rest = m[2];
// Find suffix at end
const suffixMatch = rest.match(new RegExp(String.raw`^(.+?)\s+(${SUFFIX_RE})\.?\s*$`, 'i'));
if (!suffixMatch) {
return { number, street: normalizeStreetName(rest) };
}
const namePart = suffixMatch[1];
const suffixNorm = normalizeSuffix(suffixMatch[2]);
return { number, street: `${normalizeStreetName(namePart)} ${suffixNorm}`.trim() };
}
type Mention = {
raw: string;
number: string;
street: string; // normalized name + suffix
context: string;
offset: number;
};
function extractMentions(text: string): Mention[] {
if (!text || text.length < 20) return [];
const found: Mention[] = [];
const seen = new Set<number>();
let m: RegExpExecArray | null;
ADDR_RE.lastIndex = 0;
while ((m = ADDR_RE.exec(text)) !== null) {
if (seen.has(m.index)) continue;
seen.add(m.index);
const [raw, number, dir, namePart, suffix] = m;
// Skip year-looking 4-digit numbers (1880, 1925) followed by month/day patterns
if (/^(18|19|20)\d{2}$/.test(number) && /^[A-Z][a-z]+ ?(?:\d|St|Ave|Blvd)/.test(text.slice(m.index + raw.length, m.index + raw.length + 30))) {
// probably a date "1925 March 15 Avenue ..." — skip
continue;
}
const dirNorm = dir ? dir[0].toUpperCase() + ' ' : '';
const streetNormalized = `${normalizeStreetName(namePart)} ${normalizeSuffix(suffix)}`.trim();
if (!streetNormalized || streetNormalized.length < 3) continue;
const start = Math.max(0, m.index - 80);
const end = Math.min(text.length, m.index + raw.length + 80);
const context = text.slice(start, end).replace(/\s+/g, ' ').trim();
found.push({
raw: raw.trim(),
number,
street: dirNorm ? `${dirNorm.trim().toLowerCase()} ${streetNormalized}` : streetNormalized,
context,
offset: m.index,
});
}
return found;
}
async function ensureSchema() {
await pool.query(`
CREATE TABLE IF NOT EXISTS news_address_mention (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
issue_id UUID NOT NULL REFERENCES news_issue(id) ON DELETE CASCADE,
listing_id UUID REFERENCES listing(id),
raw_address TEXT NOT NULL,
street_number TEXT NOT NULL,
street_name_normalized TEXT NOT NULL,
context TEXT,
char_offset INT NOT NULL,
match_method TEXT,
confidence NUMERIC,
extracted_at TIMESTAMPTZ DEFAULT now(),
UNIQUE (issue_id, char_offset)
);
CREATE INDEX IF NOT EXISTS idx_news_addr_issue ON news_address_mention(issue_id);
CREATE INDEX IF NOT EXISTS idx_news_addr_listing ON news_address_mention(listing_id) WHERE listing_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_news_addr_lookup ON news_address_mention(street_number, street_name_normalized);
ALTER TABLE news_issue ADD COLUMN IF NOT EXISTS addresses_extracted_at TIMESTAMPTZ;
CREATE INDEX IF NOT EXISTS idx_news_issue_extract_pending ON news_issue(paper_short)
WHERE ocr_text IS NOT NULL AND ocr_text <> '' AND addresses_extracted_at IS NULL;
`);
console.log('schema OK');
}
async function loadListingIndex(): Promise<Map<string, string>> {
// street_number+''+normalized_street → listing_id
const map = new Map<string, string>();
const { rows } = await pool.query<{ id: string; address_line1: string | null; city: string | null }>(
`SELECT id, address_line1, city FROM listing
WHERE address_line1 IS NOT NULL
AND ($1::text[] IS NULL OR city = ANY($1::text[]))`,
[LA_CITIES]
);
for (const r of rows) {
const norm = normalizeListingAddr(r.address_line1);
if (!norm) continue;
map.set(`${norm.number}${norm.street}`, r.id);
}
console.log(`loaded ${map.size} listings into match index`);
return map;
}
async function processIssue(
issue: { id: string; ocr_text: string },
index: Map<string, string>
): Promise<{ found: number; matched: number }> {
const mentions = extractMentions(issue.ocr_text);
if (mentions.length === 0) {
await pool.query(`UPDATE news_issue SET addresses_extracted_at = now() WHERE id = $1`, [issue.id]);
return { found: 0, matched: 0 };
}
// wipe prior (idempotent rerun)
await pool.query(`DELETE FROM news_address_mention WHERE issue_id = $1`, [issue.id]);
let matched = 0;
// Bulk insert
const tuples: string[] = [];
const values: any[] = [];
mentions.forEach((mn, i) => {
const key = `${mn.number}${mn.street}`;
const lid = index.get(key) ?? null;
if (lid) matched++;
const base = i * 8;
tuples.push(`($${base+1},$${base+2},$${base+3},$${base+4},$${base+5},$${base+6},$${base+7},$${base+8})`);
values.push(
issue.id,
lid,
mn.raw,
mn.number,
mn.street,
mn.context,
mn.offset,
lid ? 'exact_street' : 'unmatched'
);
});
await pool.query(
`INSERT INTO news_address_mention
(issue_id, listing_id, raw_address, street_number, street_name_normalized, context, char_offset, match_method)
VALUES ${tuples.join(',')}
ON CONFLICT (issue_id, char_offset) DO NOTHING`,
values
);
await pool.query(`UPDATE news_issue SET addresses_extracted_at = now() WHERE id = $1`, [issue.id]);
return { found: mentions.length, matched };
}
async function main() {
await ensureSchema();
const index = await loadListingIndex();
const params: any[] = [];
let where = `ocr_text IS NOT NULL AND ocr_text <> ''`;
if (!RERUN) where += ` AND addresses_extracted_at IS NULL`;
if (PAPER) { where += ` AND paper_short = $${params.length+1}`; params.push(PAPER); }
let sql = `SELECT id, ocr_text FROM news_issue WHERE ${where} ORDER BY issue_date NULLS LAST`;
if (LIMIT) sql += ` LIMIT ${LIMIT}`;
const { rows } = await pool.query<{ id: string; ocr_text: string }>(sql, params);
console.log(`extract-news-addresses paper=${PAPER ?? '(all)'} rerun=${RERUN} queue=${rows.length}`);
if (rows.length === 0) {
await pool.end();
return;
}
const t0 = Date.now();
let totalFound = 0, totalMatched = 0, processed = 0;
for (const issue of rows) {
const { found, matched } = await processIssue(issue, index);
totalFound += found;
totalMatched += matched;
processed++;
if (processed % 50 === 0) {
const dt = (Date.now() - t0) / 1000;
process.stdout.write(` ${processed}/${rows.length} processed — ${totalFound} mentions, ${totalMatched} matched — ${(processed/dt).toFixed(1)}/s\r`);
}
}
const dt = (Date.now() - t0) / 1000;
console.log('');
console.log('--- done ---');
console.log(` issues=${processed} mentions=${totalFound} listing-matched=${totalMatched} elapsed=${dt.toFixed(1)}s`);
// Top 10 unmatched streets to identify gaps
const { rows: topUnmatched } = await pool.query<{ street: string; n: string }>(
`SELECT street_name_normalized as street, count(*)::text as n
FROM news_address_mention WHERE listing_id IS NULL
GROUP BY street_name_normalized
ORDER BY count(*) DESC LIMIT 10`
);
console.log(' top 10 unmatched street names (listings to add for coverage):');
for (const r of topUnmatched) console.log(` ${r.n.padStart(6)} ${r.street}`);
await pool.end();
}
main().catch(e => {
console.error('FATAL', e);
process.exit(1);
});