Free website enrichment via OpenStreetMap/Overpass: 89 places matched (word-boundary name match, geo-gated, provenance in website_source)

2f8854cb49de59adcb953d5ef3d6f52dd881ab7d · 2026-07-22 21:13:59 -0700 · Steve

Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>

Files touched

A scripts/cr-osm-match.js

Diff

commit 2f8854cb49de59adcb953d5ef3d6f52dd881ab7d
Author: Steve <steve@designerwallcoverings.com>
Date:   Wed Jul 22 21:13:59 2026 -0700

    Free website enrichment via OpenStreetMap/Overpass: 89 places matched (word-boundary name match, geo-gated, provenance in website_source)
    
    Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
---
 scripts/cr-osm-match.js | 90 +++++++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 90 insertions(+)

diff --git a/scripts/cr-osm-match.js b/scripts/cr-osm-match.js
new file mode 100644
index 0000000..8f08193
--- /dev/null
+++ b/scripts/cr-osm-match.js
@@ -0,0 +1,90 @@
+'use strict';
+// Match OSM CR POIs (with website/phone/email tags) to our places by
+// normalized name, geo-sanity-checked against the place's region centroid.
+// CONSERVATIVE: exact normalized-name match, unique candidate only, fills
+// NULL fields only, provenance in website_source='osm'. $0, ODbL-compliant
+// (attribution note added to about page separately if desired).
+const fs = require('fs');
+const { Pool } = require('pg');
+const pool = new Pool({ connectionString: process.env.DATABASE_URL });
+
+const norm = (s) => (s || '')
+  .toLowerCase()
+  .normalize('NFD').replace(/[̀-ͯ]/g, '')
+  .replace(/\b(sociedad anonima|s\.?a\.?|ltda\.?|s\.?r\.?l\.?|inc\.?|cia\.?)\b/g, '')
+  .replace(/[^a-z0-9 ]+/g, ' ')
+  .replace(/\s+/g, ' ').trim();
+
+const km = (a, b, c, d) => {
+  const R = 6371, dLat = (c - a) * Math.PI / 180, dLon = (d - b) * Math.PI / 180;
+  const x = Math.sin(dLat / 2) ** 2 + Math.cos(a * Math.PI / 180) * Math.cos(c * Math.PI / 180) * Math.sin(dLon / 2) ** 2;
+  return 2 * R * Math.asin(Math.sqrt(x));
+};
+
+(async () => {
+  const osm = JSON.parse(fs.readFileSync(__dirname + '/data/cache/osm-cr-websites.json', 'utf8')).elements;
+  const byName = new Map();
+  for (const e of osm) {
+    const t = e.tags || {};
+    const n = norm(t.name);
+    if (n.length < 4) continue;
+    const lat = e.lat ?? e.center?.lat, lon = e.lon ?? e.center?.lon;
+    const rec = {
+      website: t.website || t['contact:website'] || null,
+      phone: t.phone || t['contact:phone'] || null,
+      email: t.email || t['contact:email'] || null,
+      lat, lon,
+    };
+    if (!byName.has(n)) byName.set(n, []);
+    byName.get(n).push(rec);
+  }
+  console.log(`osm index: ${byName.size} distinct names`);
+
+  await pool.query(`ALTER TABLE places ADD COLUMN IF NOT EXISTS website_source TEXT`);
+  const { rows: places } = await pool.query(`
+    SELECT p.id, p.name, p.website, p.phone, p.email,
+           r.lat AS rlat, r.lng AS rlng
+    FROM places p LEFT JOIN regions r ON r.id = p.region_id
+    WHERE p.website IS NULL`);
+  console.log(`places without website: ${places.length}`);
+
+  const osmNames = [...byName.keys()];
+  let matched = 0, geoRejected = 0, ambiguous = 0;
+  for (const p of places) {
+    const n = norm(p.name);
+    if (n.length < 4) continue;
+    let cands = byName.get(n);
+    let via = 'exact';
+    if (!cands && n.length >= 8) {
+      // containment as a CONTIGUOUS WORD subsequence (not raw substring —
+      // "miguel angEL CASTILLO diaz" must NOT match "el castillo").
+      // Requires a UNIQUE containing candidate; min length 8 avoids junk.
+      const wordsContain = (long, short) => {
+        const L = long.split(' '), S = short.split(' ');
+        if (S.length < 2 && S[0].length < 8) return false;
+        for (let i = 0; i + S.length <= L.length; i++)
+          if (S.every((w, j) => L[i + j] === w)) return true;
+        return false;
+      };
+      const hits = osmNames.filter(o => o.length >= 8 && (wordsContain(o, n) || wordsContain(n, o)));
+      if (hits.length === 1) { cands = byName.get(hits[0]); via = 'contain'; }
+      else if (hits.length > 1) { ambiguous++; continue; }
+    }
+    if (!cands) continue;
+    if (cands.length > 1) { ambiguous++; continue; }
+    const c = cands[0];
+    if (!c.website) continue;
+    // geo sanity: if both sides have coords, must be within 60km of the region
+    if (p.rlat && c.lat && km(Number(p.rlat), Number(p.rlng), c.lat, c.lon) > 60) { geoRejected++; continue; }
+    await pool.query(
+      `UPDATE places SET website = $1,
+              phone = COALESCE(phone, $2),
+              email = COALESCE(email, $3),
+              website_source = $4
+       WHERE id = $5`,
+      [c.website, c.phone, c.email, 'osm-' + via, p.id]);
+    matched++;
+  }
+  console.log(`DONE matched=${matched} geo_rejected=${geoRejected} ambiguous_skipped=${ambiguous}`);
+  await pool.end();
+})();

← 4ad9f20 Localize all region/place images (Wikimedia burst-429 fix) + · back to Costa Rica · Portal detail-page crawler: 79 websites + 61 phones + 14 ema 7bcb527 →