`scripts/cr-geocode-regions.js`

80 lines
'use strict';
// Fill missing region centroids via Nominatim (free, 1req/s etiquette),
// then re-validate the '-nogeo' OSM website matches: re-derive each match
// from the cached OSM dataset and geo-check with the fresh centroid.
// >60km → purge the website (bad match); ≤60km → drop the -nogeo tag.
const fs = require('fs');
const { Pool } = require('pg');
const pool = new Pool({ connectionString: process.env.DATABASE_URL });
const UA = { 'User-Agent': 'CR-Directory/1.0 (costarica.agentabrams.com; info@agentabrams.com)' };
const sleep = (ms) => new Promise((r) => setTimeout(r, ms));

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 () => {
  // 1) geocode regions missing centroids
  const { rows: regions } = await pool.query(
    `SELECT id, slug, name, province FROM regions WHERE lat IS NULL`);
  console.log(`regions missing centroid: ${regions.length}`);
  let geocoded = 0;
  for (const r of regions) {
    const q = encodeURIComponent(`${r.name}, ${r.province || ''}, Costa Rica`);
    try {
      const res = await fetch(`https://nominatim.openstreetmap.org/search?format=json&limit=1&countrycodes=cr&q=${q}`, { headers: UA });
      if (res.ok) {
        const j = await res.json();
        if (j[0]) {
          await pool.query(`UPDATE regions SET lat=$1, lng=$2 WHERE id=$3`, [j[0].lat, j[0].lon, r.id]);
          geocoded++;
        } else console.log(`no hit: ${r.slug}`);
      } else console.log(`HTTP ${res.status}: ${r.slug}`);
    } catch (e) { console.log(`err ${r.slug}: ${e.message}`); }
    await sleep(1100);
  }
  console.log(`geocoded ${geocoded}/${regions.length}`);

  // 2) re-validate -nogeo matches with fresh centroids
  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 w = t.website || t['contact:website'];
    if (!byName.has(n)) byName.set(n, []);
    byName.get(n).push({ website: w, lat, lon });
  }
  const { rows: nogeo } = await pool.query(`
    SELECT p.id, p.name, p.website, p.website_source, r.lat AS rlat, r.lng AS rlng
    FROM places p LEFT JOIN regions r ON r.id = p.region_id
    WHERE p.website_source LIKE 'osm%nogeo%'`);
  console.log(`-nogeo matches to re-validate: ${nogeo.length}`);
  let confirmed = 0, purged = 0, still = 0;
  for (const p of nogeo) {
    if (!p.rlat) { still++; continue; }   // region still has no centroid
    // find the OSM record whose website matches what we stored
    let cand = null;
    for (const list of byName.values())
      for (const c of list) if (c.website === p.website && c.lat) { cand = c; break; }
    if (!cand) { still++; continue; }
    if (km(Number(p.rlat), Number(p.rlng), cand.lat, cand.lon) > 60) {
      await pool.query(
        `UPDATE places SET website=NULL, website_source=NULL WHERE id=$1`, [p.id]);
      purged++;
    } else {
      await pool.query(
        `UPDATE places SET website_source=replace(website_source,'-nogeo','') WHERE id=$1`, [p.id]);
      confirmed++;
    }
  }
  console.log(`REVALIDATED confirmed=${confirmed} purged=${purged} unresolved=${still}`);
  await pool.end();
})();