spb/datacenterindex

1/**2 * Backfill facilities.country_iso2 from coordinates (offline Natural Earth polygons) for rows ingested before3 * ingest/country-lookup.ts existed, then assign metros where missing. Idempotent; writes provenance rows.4 *   DATABASE_URL=… node node_modules/tsx/dist/cli.mjs scripts/backfill-country.ts [--dry-run]5 */6import { getSql, closeDb } from "@dci/db";7import { stableId } from "@dci/core";8import { countryFromPoint } from "../apps/worker/src/ingest/country-lookup.js";910const dry = process.argv.includes("--dry-run");11const sql = getSql();12const rows = await sql<{ id: string; lat: number; lng: number; slug: string }[]>`select id, lat, lng, slug from facilities where country_iso2 is null and lat is not null and merged_into is null`;13const known = new Set((await sql<{ iso2: string }[]>`select iso2 from countries`).map((r) => r.iso2));14let fixed = 0, unresolved = 0;15const byCountry = new Map<string, number>();16for (const r of rows) {17  const iso = countryFromPoint(Number(r.lat), Number(r.lng));18  if (!iso || !known.has(iso)) { unresolved++; continue; }19  byCountry.set(iso, (byCountry.get(iso) ?? 0) + 1);20  if (!dry) {21    await sql`update facilities set country_iso2 = ${iso}, updated_at = now() where id = ${r.id}`;22    await sql`insert into provenance (id, entity_type, entity_id, field, value, source_id, connector_id, url, confidence, is_estimate, method, note)23      values (${stableId("provenance", `facility|${r.id}|country_iso2|derived-geo`)}, 'facility', ${r.id}, 'country_iso2', ${sql.json(iso)}, 'src_derived', 'derived', ${"https://www.datacenterindex.io/methodology#geolocation"}, 'moderate', false, 'derived:point-in-country', 'Natural Earth 1:50m admin-0 polygons (public domain)')24      on conflict do nothing`;25  }26  fixed++;27}28if (!dry) {29  await sql`insert into sources (id, connector_id, name, domain, kind, priority, license, attribution) values ('src_derived', 'derived', 'Derived (DataCenterIndex)', 'www.datacenterindex.io', 'registry', 4, 'Derived from indexed coordinates + Natural Earth (public domain)', 'DataCenterIndex') on conflict do nothing`;30  // metro assignment for rows without metro: nearest metro within its radius31  const m = await sql`update facilities f set metro_id = (32      select mm.id from metros mm where mm.country_iso2 = f.country_iso233        and 6371.0088 * 2 * asin(sqrt(power(sin(radians(mm.lat - f.lat) / 2), 2) + cos(radians(f.lat)) * cos(radians(mm.lat)) * power(sin(radians(mm.lng - f.lng) / 2), 2))) <= mm.radius_km34      order by 6371.0088 * 2 * asin(sqrt(power(sin(radians(mm.lat - f.lat) / 2), 2) + cos(radians(f.lat)) * cos(radians(mm.lat)) * power(sin(radians(mm.lng - f.lng) / 2), 2))) limit 1)35    where f.metro_id is null and f.lat is not null and f.country_iso2 is not null and f.merged_into is null36      and exists (select 1 from metros mm where mm.country_iso2 = f.country_iso2)`;37  console.log(`metros assigned: ${m.count}`);38}39console.log(`candidates ${rows.length} · fixed ${fixed} · unresolved ${unresolved}${dry ? " (dry run)" : ""}`);40console.log([...byCountry.entries()].sort((a, b) => b[1] - a[1]).slice(0, 12).map(([k, v]) => `${k}:${v}`).join(" "));41await closeDb();42