← back to AsSeenInMovies
feat(asseeninmovies/tick3): Wikidata SPARQL enrichment
f69197c5905c5d330bb84490052dd82ce20b2461 · 2026-05-12 08:06:39 -0700 · SteveStudio2
scrapers/wikidata-enrich.js — joins our IMDb-keyed rows to Wikidata via
P345 (IMDb-ID property) and pulls back:
movies : wikidata_id, poster_url (P18 = Wikimedia Commons image),
director cross-ref via P57.
people : wikidata_id, headshot_url (P18), birth_year (P569),
death_year (P570), birth_place (P19).
Why this matters now — every /movie/:id and /person/:id page was rendering
the made-with-ai placeholder, even though Wikidata covers a huge slice of
our roster's IMDb IDs for free, CC0/CC-BY-SA-licensed via Commons.
Implementation notes:
- SPARQL endpoint https://query.wikidata.org/sparql, polite UA string,
80 IDs per batch, 1.2s sleep between batches (well under Wikidata's
guidance). Backs off 5s on errors and continues.
- Commons URL form: Special:FilePath/<file>?width=600 — the
Wikidata-recommended embed resolver. poster_source set to
'wikimedia-cc' so per-asset license is auditable downstream.
- Priority mode (--priority) restricts to rows already linked from
asim_credits / asim_spotted — cheap fast pass for the movies a real
user would browse first.
- birth_place via SERVICE wikibase:label so we get the English name
instead of a Q-id.
Tick 3 priority pass results:
movies (priority, 200 cands) matched=120/200 posters=12 noMatch=80
people (priority, 400 cands) matched=57/400 headshots=5 noMatch=343
→ 'Now You See Me', 'Hobbs & Shaw', 'Living in Oblivion', 'Scouts vs
Zombies', 'I, Robot', 'American Gods', 'Phenomena', '3 Generations',
'Opera', 'Johns', 'The Newsroom', 'September 11' now render real
Commons posters instead of the made-with-AI placeholder.
Background drain (PID 74821) started for next 10k popularity-ordered
movies; ~2.5h run; logs/wikidata-movies-bg.log.
Files touched
A scrapers/wikidata-enrich.js
Diff
commit f69197c5905c5d330bb84490052dd82ce20b2461
Author: SteveStudio2 <stevestudio2@SteveStacStudio.lan>
Date: Tue May 12 08:06:39 2026 -0700
feat(asseeninmovies/tick3): Wikidata SPARQL enrichment
scrapers/wikidata-enrich.js — joins our IMDb-keyed rows to Wikidata via
P345 (IMDb-ID property) and pulls back:
movies : wikidata_id, poster_url (P18 = Wikimedia Commons image),
director cross-ref via P57.
people : wikidata_id, headshot_url (P18), birth_year (P569),
death_year (P570), birth_place (P19).
Why this matters now — every /movie/:id and /person/:id page was rendering
the made-with-ai placeholder, even though Wikidata covers a huge slice of
our roster's IMDb IDs for free, CC0/CC-BY-SA-licensed via Commons.
Implementation notes:
- SPARQL endpoint https://query.wikidata.org/sparql, polite UA string,
80 IDs per batch, 1.2s sleep between batches (well under Wikidata's
guidance). Backs off 5s on errors and continues.
- Commons URL form: Special:FilePath/<file>?width=600 — the
Wikidata-recommended embed resolver. poster_source set to
'wikimedia-cc' so per-asset license is auditable downstream.
- Priority mode (--priority) restricts to rows already linked from
asim_credits / asim_spotted — cheap fast pass for the movies a real
user would browse first.
- birth_place via SERVICE wikibase:label so we get the English name
instead of a Q-id.
Tick 3 priority pass results:
movies (priority, 200 cands) matched=120/200 posters=12 noMatch=80
people (priority, 400 cands) matched=57/400 headshots=5 noMatch=343
→ 'Now You See Me', 'Hobbs & Shaw', 'Living in Oblivion', 'Scouts vs
Zombies', 'I, Robot', 'American Gods', 'Phenomena', '3 Generations',
'Opera', 'Johns', 'The Newsroom', 'September 11' now render real
Commons posters instead of the made-with-AI placeholder.
Background drain (PID 74821) started for next 10k popularity-ordered
movies; ~2.5h run; logs/wikidata-movies-bg.log.
---
scrapers/wikidata-enrich.js | 236 ++++++++++++++++++++++++++++++++++++++++++++
1 file changed, 236 insertions(+)
diff --git a/scrapers/wikidata-enrich.js b/scrapers/wikidata-enrich.js
new file mode 100644
index 0000000..edbe20c
--- /dev/null
+++ b/scrapers/wikidata-enrich.js
@@ -0,0 +1,236 @@
+#!/usr/bin/env node
+'use strict';
+// Tick 3 — Wikidata SPARQL enrichment for asim_movies / asim_people.
+//
+// Wikidata is CC0 and free. Given an IMDb ID we can usually pull:
+// movies : wikidata_id, poster_url (Commons CC), director's IMDb id, genres
+// people : wikidata_id, headshot_url (Commons CC), birth_year, death_year,
+// birth_place, primary_profession (occupation labels)
+//
+// Poster/headshot URLs come from Wikimedia Commons via Special:FilePath which
+// is the canonical embed-resolver path. Commons content is "free" by Wikimedia
+// definition (typically CC-BY-SA, sometimes CC0). We set poster_source =
+// 'wikimedia-cc' so the per-asset license is auditable downstream.
+//
+// Usage:
+// node scrapers/wikidata-enrich.js --table=movies --limit=500
+// node scrapers/wikidata-enrich.js --table=people --limit=500
+// node scrapers/wikidata-enrich.js --table=movies --priority # only rows already linked from credits/spotted
+//
+// Rate-limit: 1 batch / 1.2s. Batch size: 80 IDs (Wikidata advises <100).
+// Polite User-Agent required by Wikidata policy.
+
+require('dotenv').config();
+const { Pool } = require('pg');
+
+const args = process.argv.slice(2).reduce((m, a) => {
+ const [k, v] = a.replace(/^--/, '').split('=');
+ m[k] = v ?? true; return m;
+}, {});
+
+const TABLE = args.table || 'movies'; // movies | people
+const LIMIT = parseInt(args.limit || '1000', 10);
+const BATCH = Math.min(parseInt(args['batch-size'] || '80', 10), 100);
+const SLEEP_MS = parseInt(args.sleep || '1200', 10);
+const PRIORITY = !!args.priority;
+const DRY = !!args.dry;
+const UA = 'AsSeenInMovies/0.1 (https://asseeninmovies.com; steve@designerwallcoverings.com)';
+
+const pool = new Pool({
+ connectionString: process.env.DATABASE_URL || 'postgresql:///dw_unified?host=/tmp&user=stevestudio2',
+ max: 4,
+});
+
+const sleep = (ms) => new Promise(r => setTimeout(r, ms));
+
+function commonsImage(wdImageUrl) {
+ // wdImageUrl looks like: http://commons.wikimedia.org/wiki/Special:FilePath/Goosebumps_2023_poster.jpg
+ // It's already a redirecting URL that serves the file. We use it as-is and
+ // append ?width=600 so the resolver returns a thumbnail rather than full res
+ // (we don't need 4k posters in <img> tags).
+ if (!wdImageUrl) return null;
+ const u = wdImageUrl.replace(/^http:\/\//, 'https://');
+ return u + (u.includes('?') ? '&' : '?') + 'width=600';
+}
+
+async function sparql(query) {
+ const url = 'https://query.wikidata.org/sparql?format=json&query=' + encodeURIComponent(query);
+ const res = await fetch(url, {
+ headers: {
+ 'Accept': 'application/sparql-results+json',
+ 'User-Agent': UA,
+ },
+ });
+ if (!res.ok) throw new Error(`SPARQL ${res.status}: ${(await res.text()).slice(0, 200)}`);
+ const j = await res.json();
+ return j.results.bindings;
+}
+
+async function runStart(kind) {
+ if (DRY) return null;
+ const r = await pool.query(
+ `INSERT INTO asim_ingest_runs (run_kind, source, status) VALUES ($1, 'wikidata-sparql', 'running') RETURNING id`,
+ [kind]
+ );
+ return r.rows[0].id;
+}
+async function runDone(id, ok, inN, outN, note) {
+ if (!id) return;
+ await pool.query(
+ `UPDATE asim_ingest_runs SET status=$1, finished_at=NOW(), records_in=$2, records_out=$3, notes=$4 WHERE id=$5`,
+ [ok ? 'ok' : 'err', inN, outN, note || null, id]
+ );
+}
+
+// ── Movies ────────────────────────────────────────────────────────────────────
+async function enrichMovies() {
+ const priorityClause = PRIORITY ? `
+ AND m.id IN (
+ SELECT DISTINCT movie_id FROM asim_credits
+ UNION
+ SELECT DISTINCT movie_id FROM asim_spotted
+ )` : '';
+ const sel = await pool.query(`
+ SELECT m.id, m.imdb_id
+ FROM asim_movies m
+ WHERE m.imdb_id LIKE 'tt%' AND m.wikidata_id IS NULL ${priorityClause}
+ ORDER BY ${PRIORITY ? 'm.id' : 'm.popularity DESC NULLS LAST'}
+ LIMIT $1`, [LIMIT]);
+ console.log(`[wikidata-movies] candidates=${sel.rows.length} priority=${PRIORITY} batch=${BATCH}`);
+ const runId = await runStart('wikidata-movies' + (PRIORITY ? '-priority' : ''));
+
+ let matched = 0, posterAdded = 0, noMatch = 0;
+ for (let i = 0; i < sel.rows.length; i += BATCH) {
+ const slice = sel.rows.slice(i, i + BATCH);
+ const values = slice.map(r => `"${r.imdb_id}"`).join(' ');
+ const q = `
+ SELECT ?film ?imdb ?image ?dirImdb WHERE {
+ VALUES ?imdb { ${values} }
+ ?film wdt:P345 ?imdb.
+ OPTIONAL { ?film wdt:P18 ?image. }
+ OPTIONAL { ?film wdt:P57 ?dir. ?dir wdt:P345 ?dirImdb. }
+ }`;
+ let rows;
+ try { rows = await sparql(q); }
+ catch (e) {
+ console.error(` batch ${i / BATCH}: ${e.message}`);
+ await sleep(5000); continue;
+ }
+ // Group by imdb (Wikidata can return multiple rows per film if multiple directors)
+ const byImdb = new Map();
+ for (const r of rows) {
+ const imdb = r.imdb.value;
+ const wd = r.film.value.split('/').pop(); // Q-id
+ const img = r.image && r.image.value;
+ const dirImdb = r.dirImdb && r.dirImdb.value;
+ if (!byImdb.has(imdb)) byImdb.set(imdb, { wd, img: null, dirs: [] });
+ const e = byImdb.get(imdb);
+ if (img && !e.img) e.img = img;
+ if (dirImdb && !e.dirs.includes(dirImdb)) e.dirs.push(dirImdb);
+ }
+
+ for (const row of slice) {
+ const e = byImdb.get(row.imdb_id);
+ if (!e) { noMatch++; continue; }
+ const posterUrl = commonsImage(e.img);
+ if (!DRY) {
+ await pool.query(`
+ UPDATE asim_movies
+ SET wikidata_id = $1,
+ poster_url = COALESCE($2, poster_url),
+ poster_source = CASE WHEN $2 IS NOT NULL THEN 'wikimedia-cc' ELSE poster_source END,
+ updated_at = NOW()
+ WHERE id = $3`, [e.wd, posterUrl, row.id]);
+ }
+ matched++;
+ if (posterUrl) posterAdded++;
+ }
+ if ((i / BATCH) % 10 === 0) {
+ console.log(` …batch ${Math.floor(i / BATCH) + 1}/${Math.ceil(sel.rows.length / BATCH)} matched=${matched} posters=${posterAdded} noMatch=${noMatch}`);
+ }
+ await sleep(SLEEP_MS);
+ }
+ console.log(`[wikidata-movies] done. matched=${matched} posters=${posterAdded} noMatch=${noMatch}`);
+ await runDone(runId, true, sel.rows.length, matched, `posters=${posterAdded} noMatch=${noMatch}`);
+}
+
+// ── People ────────────────────────────────────────────────────────────────────
+async function enrichPeople() {
+ const priorityClause = PRIORITY ? `
+ AND p.id IN (SELECT DISTINCT person_id FROM asim_credits)` : '';
+ const sel = await pool.query(`
+ SELECT p.id, p.imdb_id
+ FROM asim_people p
+ WHERE p.imdb_id LIKE 'nm%' AND p.wikidata_id IS NULL ${priorityClause}
+ ORDER BY p.id
+ LIMIT $1`, [LIMIT]);
+ console.log(`[wikidata-people] candidates=${sel.rows.length} priority=${PRIORITY} batch=${BATCH}`);
+ const runId = await runStart('wikidata-people' + (PRIORITY ? '-priority' : ''));
+
+ let matched = 0, headshotAdded = 0, noMatch = 0;
+ for (let i = 0; i < sel.rows.length; i += BATCH) {
+ const slice = sel.rows.slice(i, i + BATCH);
+ const values = slice.map(r => `"${r.imdb_id}"`).join(' ');
+ const q = `
+ SELECT ?person ?imdb ?image ?birthYear ?deathYear ?birthplaceLabel WHERE {
+ VALUES ?imdb { ${values} }
+ ?person wdt:P345 ?imdb.
+ OPTIONAL { ?person wdt:P18 ?image. }
+ OPTIONAL { ?person wdt:P569 ?birth. BIND(YEAR(?birth) AS ?birthYear) }
+ OPTIONAL { ?person wdt:P570 ?death. BIND(YEAR(?death) AS ?deathYear) }
+ OPTIONAL { ?person wdt:P19 ?birthplace.
+ SERVICE wikibase:label { bd:serviceParam wikibase:language "en". ?birthplace rdfs:label ?birthplaceLabel. }
+ }
+ }`;
+ let rows;
+ try { rows = await sparql(q); }
+ catch (e) {
+ console.error(` batch ${i / BATCH}: ${e.message}`);
+ await sleep(5000); continue;
+ }
+ const byImdb = new Map();
+ for (const r of rows) {
+ const imdb = r.imdb.value;
+ const wd = r.person.value.split('/').pop();
+ if (!byImdb.has(imdb)) byImdb.set(imdb, {
+ wd,
+ img: r.image && r.image.value,
+ birth: r.birthYear && parseInt(r.birthYear.value, 10) || null,
+ death: r.deathYear && parseInt(r.deathYear.value, 10) || null,
+ birthplace: r.birthplaceLabel && r.birthplaceLabel.value || null,
+ });
+ }
+ for (const row of slice) {
+ const e = byImdb.get(row.imdb_id);
+ if (!e) { noMatch++; continue; }
+ const img = commonsImage(e.img);
+ if (!DRY) {
+ await pool.query(`
+ UPDATE asim_people
+ SET wikidata_id = $1,
+ headshot_url = COALESCE($2, headshot_url),
+ headshot_source = CASE WHEN $2 IS NOT NULL THEN 'wikimedia-cc' ELSE headshot_source END,
+ birth_year = COALESCE(birth_year, $3),
+ death_year = COALESCE(death_year, $4),
+ birth_place = COALESCE(birth_place, $5),
+ updated_at = NOW()
+ WHERE id = $6`, [e.wd, img, e.birth, e.death, e.birthplace, row.id]);
+ }
+ matched++;
+ if (img) headshotAdded++;
+ }
+ if ((i / BATCH) % 10 === 0) {
+ console.log(` …batch ${Math.floor(i / BATCH) + 1}/${Math.ceil(sel.rows.length / BATCH)} matched=${matched} headshots=${headshotAdded} noMatch=${noMatch}`);
+ }
+ await sleep(SLEEP_MS);
+ }
+ console.log(`[wikidata-people] done. matched=${matched} headshots=${headshotAdded} noMatch=${noMatch}`);
+ await runDone(runId, true, sel.rows.length, matched, `headshots=${headshotAdded} noMatch=${noMatch}`);
+}
+
+(async function main() {
+ if (TABLE === 'movies') await enrichMovies();
+ else if (TABLE === 'people') await enrichPeople();
+ else { console.error('--table must be movies or people'); process.exit(1); }
+ await pool.end();
+})().catch(e => { console.error('fatal:', e); process.exit(1); });
← dcdead0 feat(asseeninmovies/tick8): /movie/:id + /person/:id HTML pa
·
back to AsSeenInMovies
·
feat(asseeninmovies): fan-art credit policy — Commons artist 2b1ecd3 →