← back to Stayclaim
scripts/seed-walk-of-fame-full.mjs
194 lines
#!/usr/bin/env node
/**
* Bulk-ingest the FULL Hollywood Walk of Fame (~2,718 honorees, ~2,875 stars
* including multi-category) from Wikipedia.
*
* Source: https://en.wikipedia.org/wiki/List_of_stars_on_the_Hollywood_Walk_of_Fame
* — sortable table with columns: Name, Category, Address, Date
* — public-domain data (Wikipedia CC-BY-SA), tier C citation
*
* Geocoding: linear interpolation along Hollywood Blvd (lat≈34.1018) and Vine St
* (lng≈-118.3268) between known endpoints. Accurate to ~50ft per block — fine
* for sidewalk-pin display. Refine with Census geocoder later if needed.
*
* Idempotent: ON CONFLICT (honoree_slug) DO UPDATE. Re-run safe.
*/
import pg from 'pg';
import { randomUUID } from 'crypto';
const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL, max: 4 });
const UA = 'pastdoor/1.0-wof-full (https://wholivedthere.com)';
// Hollywood Blvd: numbers run east (low) → west (high) in 5800–7100 block.
// Anchors verified via Google Maps:
// 6233 Hollywood Blvd ≈ 34.10166, -118.3284 (Vine intersection)
// 7065 Hollywood Blvd ≈ 34.10174, -118.3458 (La Brea intersection)
const HBLVD_LAT = 34.10166;
const HBLVD_NUM_LO = 6233;
const HBLVD_LNG_LO = -118.3284;
const HBLVD_NUM_HI = 7065;
const HBLVD_LNG_HI = -118.3458;
// Vine St: numbers run south (low) → north (high) in 1500–1800 block.
// 1500 Vine St ≈ 34.0975, -118.3268 (Sunset intersection)
// 1750 Vine St ≈ 34.1014, -118.3268 (Hollywood Blvd intersection)
const VINE_LNG = -118.3268;
const VINE_NUM_LO = 1500;
const VINE_LAT_LO = 34.0975;
const VINE_NUM_HI = 1750;
const VINE_LAT_HI = 34.1014;
function geocode(address) {
const m = address.match(/^(\d+)\s+(.+?)$/);
if (!m) return null;
const num = parseInt(m[1], 10);
const street = m[2].toLowerCase();
if (street.includes('hollywood')) {
const t = (num - HBLVD_NUM_LO) / (HBLVD_NUM_HI - HBLVD_NUM_LO);
return { lat: HBLVD_LAT, lng: HBLVD_LNG_LO + t * (HBLVD_LNG_HI - HBLVD_LNG_LO) };
}
if (street.includes('vine')) {
const t = (num - VINE_NUM_LO) / (VINE_NUM_HI - VINE_NUM_LO);
return { lat: VINE_LAT_LO + t * (VINE_LAT_HI - VINE_LAT_LO), lng: VINE_LNG };
}
return null;
}
const slugify = s => s.toLowerCase().normalize('NFKD')
.replace(/[̀-ͯ]/g, '') // strip combining diacritics (escape form survives encoding)
.replace(/[^a-z0-9]+/g, '-').replace(/^-+|-+$/g, '');
// Wikipedia category → walk_of_fame.category enum
// (DB constraint: motion_pictures | television | recording | radio | theater | sports | special)
const CATEGORY_MAP = {
'Motion pictures': 'motion_pictures',
'Television': 'television',
'Radio': 'radio',
'Recording': 'recording',
'Live performance': 'theater',
'Live theatre': 'theater',
'Live theater': 'theater',
'Sports entertainment': 'sports',
'Sports': 'sports',
};
async function fetchTable() {
const url = 'https://en.wikipedia.org/w/api.php?action=parse&page=List_of_stars_on_the_Hollywood_Walk_of_Fame&format=json&prop=text&disablelimitreport=1&disableeditsection=1';
const r = await fetch(url, { headers: { 'User-Agent': UA, Accept: 'application/json' } });
const j = await r.json();
return j.parse.text['*'];
}
function parseRows(html) {
// Page has multiple wikitables (one per letter group or section).
// Concatenate every wikitable's rows in document order.
const tables = [...html.matchAll(/<table[^>]*class="[^"]*wikitable[^"]*"[^>]*>([\s\S]*?)<\/table>/g)];
if (!tables.length) throw new Error('no wikitable found');
console.log(` found ${tables.length} wikitables on page`);
const rows = [];
let lastName = null, lastSlug = null, lastWiki = null;
const allTrs = tables.flatMap(t => [...t[1].matchAll(/<tr[^>]*>([\s\S]*?)<\/tr>/g)]);
for (const m of allTrs) {
const tr = m[1];
const cells = [...tr.matchAll(/<t[hd][^>]*>([\s\S]*?)<\/t[hd]>/g)].map(c => c[1]);
if (cells.length === 0) continue;
let name, category, address, dateText;
if (cells.length === 4) {
// Name cell present (first row for an honoree)
name = cleanText(cells[0]);
const wikiHref = cells[0].match(/<a[^>]+href="\/wiki\/([^"#]+)"/);
lastWiki = wikiHref ? `https://en.wikipedia.org/wiki/${wikiHref[1]}` : null;
category = cleanText(cells[1]);
address = cleanText(cells[2]);
dateText = cleanText(cells[3]);
lastName = name;
lastSlug = slugify(name);
} else if (cells.length === 3) {
// Continuation row for the same honoree — name omitted via rowspan
if (!lastName) continue;
name = lastName;
category = cleanText(cells[0]);
address = cleanText(cells[1]);
dateText = cleanText(cells[2]);
} else {
continue;
}
if (!name || name.length === 0 || name === 'Name') continue;
if (!address || !address.match(/^\d+/)) continue;
rows.push({
name, slug: lastSlug, wiki_url: lastWiki,
category, address,
date: parseDate(dateText),
});
}
return rows;
}
function cleanText(html) {
return html.replace(/<[^>]+>/g, '').replace(/ /g, ' ').replace(/&/g, '&')
.replace(/ /g, ' ').replace(/&[a-z]+;/g, ' ')
.replace(/\([^)]*\)/g, '').replace(/\s+/g, ' ').trim();
}
function parseDate(t) {
if (!t) return null;
const m = t.match(/(January|February|March|April|May|June|July|August|September|October|November|December)\s+(\d{1,2}),\s+(\d{4})/);
if (!m) return null;
const months = { January:1, February:2, March:3, April:4, May:5, June:6,
July:7, August:8, September:9, October:10, November:11, December:12 };
return `${m[3]}-${String(months[m[1]]).padStart(2,'0')}-${String(m[2]).padStart(2,'0')}`;
}
(async () => {
console.log('Fetching Wikipedia WoF list…');
const html = await fetchTable();
console.log(`HTML size: ${(html.length/1024).toFixed(1)}KB`);
const rows = parseRows(html);
console.log(`Parsed ${rows.length} star rows`);
// Dedupe to one row per honoree (keep first/primary star) for the table.
// Multi-category honorees still get their primary star pin; secondary stars
// are tracked in honoree_count for future "shows N stars" enrichment.
const seen = new Map();
for (const r of rows) {
if (!seen.has(r.slug)) seen.set(r.slug, { ...r, extra_categories: [] });
else seen.get(r.slug).extra_categories.push(r.category);
}
const honorees = [...seen.values()];
console.log(`Unique honorees: ${honorees.length}`);
let ok = 0, geoFail = 0;
for (const h of honorees) {
const g = geocode(h.address);
if (!g) {
console.log(`SKIP-GEO ${h.name.padEnd(30)} @ ${h.address}`);
geoFail++;
continue;
}
const cat = CATEGORY_MAP[h.category] ?? 'motion_pictures';
try {
await pool.query(
`INSERT INTO walk_of_fame
(id, honoree_slug, honoree_name, category, address, lat, lng, dedicated_date, source_url)
VALUES (gen_random_uuid(), $1, $2, $3, $4, $5, $6, $7::date, $8)
ON CONFLICT (honoree_slug) DO UPDATE SET
honoree_name = EXCLUDED.honoree_name,
category = EXCLUDED.category,
address = EXCLUDED.address,
lat = EXCLUDED.lat, lng = EXCLUDED.lng,
dedicated_date = EXCLUDED.dedicated_date,
source_url = EXCLUDED.source_url`,
[h.slug, h.name, cat, h.address, g.lat, g.lng, h.date, h.wiki_url]
);
ok++;
} catch (e) {
console.log(`SKIP ${h.name}: ${e.message.slice(0, 80)}`);
}
}
console.log(`\ndone: ${ok} inserted/updated · ${geoFail} no-geocode (non-Hwd-Blvd/Vine address)`);
await pool.end();
})();