`scripts/cr-osm-match.js`

99 lines
'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: within 60km of the region centroid. A missing centroid is
    // NOT a pass — matches without geo confirmation get '-nogeo' provenance
    // so the UI/audits can treat them as weaker evidence.
    let geoTag = '';
    if (p.rlat && c.lat) {
      if (km(Number(p.rlat), Number(p.rlng), c.lat, c.lon) > 60) { geoRejected++; continue; }
    } else {
      geoTag = '-nogeo';
    }
    const socialTag = /facebook\.com|instagram\.com/i.test(c.website) ? '-social' : '';
    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 + socialTag + geoTag, p.id]);
    matched++;
  }
  console.log(`DONE matched=${matched} geo_rejected=${geoRejected} ambiguous_skipped=${ambiguous}`);
  await pool.end();
})();