gui.do
Sifa professional network API (Fastify, AT Protocol, Jetstream)
sifa.id/
1/**
2 * One-time backfill: geocode existing profiles that have location data but no coordinates.
3 *
4 * Usage: DATABASE_URL=... GEONAMES_USERNAME=... npx tsx scripts/backfill-coordinates.ts
5 *
6 * GeoNames free tier: ~1000 requests/hour. Script adds a 4s delay between API calls.
7 */
8import { drizzle } from 'drizzle-orm/node-postgres';
9import pg from 'pg';
10import { profiles } from '../src/db/schema/index.js';
11import { sql } from 'drizzle-orm';
12import { geocodeCity } from '../src/lib/geocode.js';
13import { resolveCountryCode } from '../src/lib/country-centroids.js';
14
15const DATABASE_URL = process.env.DATABASE_URL;
16const GEONAMES_USERNAME = process.env.GEONAMES_USERNAME ?? 'gxjansen';
17
18if (!DATABASE_URL) {
19 console.error('DATABASE_URL is required');
20 process.exit(1);
21}
22
23const pool = new pg.Pool({ connectionString: DATABASE_URL });
24const db = drizzle(pool);
25
26async function main() {
27 const rows = await db
28 .select({
29 did: profiles.did,
30 locationCity: profiles.locationCity,
31 locationCountry: profiles.locationCountry,
32 countryCode: profiles.countryCode,
33 })
34 .from(profiles)
35 .where(
36 sql`${profiles.latitude} IS NULL AND (${profiles.countryCode} IS NOT NULL OR ${profiles.locationCountry} IS NOT NULL)`,
37 );
38
39 console.log(`Found ${rows.length} profiles to backfill`);
40
41 let updated = 0;
42 let skipped = 0;
43
44 for (const row of rows) {
45 const code = resolveCountryCode(row.countryCode, row.locationCountry);
46 if (!code) {
47 skipped++;
48 continue;
49 }
50
51 const city = row.locationCity?.trim() ?? '';
52 const coords = await geocodeCity(city, code, GEONAMES_USERNAME);
53
54 if (coords) {
55 await db
56 .update(profiles)
57 .set({ latitude: coords.lat, longitude: coords.lng })
58 .where(sql`${profiles.did} = ${row.did}`);
59 updated++;
60 console.log(
61 ` ${row.did}: ${city || '(country only)'}, ${code} -> ${coords.lat}, ${coords.lng}`,
62 );
63 } else {
64 skipped++;
65 }
66
67 // Rate limit: only delay for city geocodes (API calls), not country-only (local)
68 if (city) {
69 await new Promise((r) => setTimeout(r, 4000));
70 }
71 }
72
73 console.log(`Done: ${updated} updated, ${skipped} skipped`);
74 await pool.end();
75}
76
77main().catch((err) => {
78 console.error(err);
79 process.exit(1);
80});