Geocode the final 38 stragglers via Nominatim — map now 100% covered

6ca9edb24aa64f0e2d5aae325c8a2ad2a00b7961 · 2026-05-07 15:05:52 -0700 · Steve Abrams

scripts/geocode-nominatim.js hits OSM Nominatim (free, 1 req/sec policy,
real User-Agent) for the rows the corridor cross-reference couldn't
match. First pass got 32 of 38; the 6 misses had unusual address
suffixes ('Office #20', 'Space #203', 'Front', '17047 1/2'). Tightened
the cleanStreet regex to strip those and got the last 6.

Map markers: 1,811 → 1,849 (every active business is now pinned).
Zero corridor-seeded rows without lat/lng. Idempotent — only updates
where latitude IS NULL.

Took ~75 seconds total to geocode all 38 (rate-limited).

Files touched

A scripts/geocode-nominatim.js

Diff

commit 6ca9edb24aa64f0e2d5aae325c8a2ad2a00b7961
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Thu May 7 15:05:52 2026 -0700

    Geocode the final 38 stragglers via Nominatim — map now 100% covered
    
    scripts/geocode-nominatim.js hits OSM Nominatim (free, 1 req/sec policy,
    real User-Agent) for the rows the corridor cross-reference couldn't
    match. First pass got 32 of 38; the 6 misses had unusual address
    suffixes ('Office #20', 'Space #203', 'Front', '17047 1/2'). Tightened
    the cleanStreet regex to strip those and got the last 6.
    
    Map markers: 1,811 → 1,849 (every active business is now pinned).
    Zero corridor-seeded rows without lat/lng. Idempotent — only updates
    where latitude IS NULL.
    
    Took ~75 seconds total to geocode all 38 (rate-limited).
---
 scripts/geocode-nominatim.js | 109 +++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 109 insertions(+)

diff --git a/scripts/geocode-nominatim.js b/scripts/geocode-nominatim.js
new file mode 100644
index 0000000..49e9523
--- /dev/null
+++ b/scripts/geocode-nominatim.js
@@ -0,0 +1,109 @@
+#!/usr/bin/env node
+/**
+ * geocode-nominatim.js
+ *
+ * Backfill lat/lng for the 38 corridor-seeded rows the cross-reference
+ * pass couldn't match. Hits OSM Nominatim (free, 1 req/sec policy). Idempotent
+ * — only updates rows where latitude IS NULL.
+ *
+ * Nominatim policy: max 1 req/sec, real User-Agent required, no bulk geocoding.
+ * 38 rows × 1.2s/req ≈ 50s total — well within fair use.
+ */
+
+require('dotenv').config({ path: require('path').resolve(__dirname, '..', '.env') });
+const db = require('../lib/db');
+
+const UA = 'ventura-claw-leads/0.1 (steve@designerwallcoverings.com)';
+const SLEEP_MS = 1100; // > 1 req/sec
+
+function sleep(ms) { return new Promise(r => setTimeout(r, ms)); }
+
+// Strip unit/suite/space/office tokens — Nominatim wants
+// "<number> <street>" only. LA BTRC addresses include things like
+// "21021 Ventura Blvd Office #20" or "17047 1/2 Ventura Blvd" that
+// confuse the geocoder.
+function cleanStreet(s) {
+  if (!s) return s;
+  return s
+    // Drop suite/unit-style suffixes anywhere in the string.
+    .replace(/\s*\b(?:suite|ste|unit|apt|space|office|front|rear)\b\s*#?\s*\S*/gi, '')
+    // Drop bare # tokens (e.g. "#P9", "#20", "#203").
+    .replace(/\s*#\s*\S+/g, '')
+    // "17047 1/2 Ventura Blvd" → "17047 Ventura Blvd"
+    .replace(/(\d+)\s+\d+\/\d+\b/, '$1')
+    .replace(/\s+,/g, ',')
+    .replace(/\s+/g, ' ')
+    .trim();
+}
+
+async function geocode(query) {
+  const url = `https://nominatim.openstreetmap.org/search?q=${encodeURIComponent(query)}&format=json&limit=1&countrycodes=us`;
+  const r = await fetch(url, { headers: { 'User-Agent': UA } });
+  if (!r.ok) throw new Error(`nominatim ${r.status}`);
+  const arr = await r.json();
+  if (!arr.length) return null;
+  return { lat: parseFloat(arr[0].lat), lng: parseFloat(arr[0].lon), display: arr[0].display_name };
+}
+
+(async () => {
+  const rows = await db.many(`
+    SELECT id, slug, business_name, street, city, zip
+      FROM businesses
+     WHERE source='public_records' AND status='active' AND latitude IS NULL
+     ORDER BY id
+  `);
+  console.log(`[nominatim] ${rows.length} stragglers to geocode`);
+
+  let ok = 0, miss = 0, err = 0;
+  for (let i = 0; i < rows.length; i++) {
+    const r = rows[i];
+    const street = cleanStreet(r.street);
+    // Try 2-step: (1) clean street + city + state + zip, (2) clean street + city + state.
+    const queries = [
+      `${street}, ${r.city}, CA ${r.zip || ''}`.trim(),
+      `${street}, ${r.city}, CA`
+    ];
+
+    let result = null;
+    for (const q of queries) {
+      try {
+        result = await geocode(q);
+        if (result) break;
+        await sleep(SLEEP_MS);
+      } catch (e) {
+        err++;
+        console.error(`[${r.slug}] ${e.message}`);
+      }
+    }
+    if (!result) {
+      miss++;
+      console.log(`  miss  ${r.slug}  (${street}, ${r.city})`);
+      await sleep(SLEEP_MS);
+      continue;
+    }
+
+    // Sanity-check: result must be inside SoCal bounding box. Otherwise reject.
+    if (result.lat < 33.5 || result.lat > 34.5 || result.lng < -119.5 || result.lng > -117.5) {
+      miss++;
+      console.log(`  out-of-box  ${r.slug}  (${result.lat},${result.lng})`);
+      await sleep(SLEEP_MS);
+      continue;
+    }
+
+    await db.query(
+      `UPDATE businesses SET latitude=$1, longitude=$2 WHERE id=$3 AND latitude IS NULL`,
+      [result.lat, result.lng, r.id]
+    );
+    ok++;
+    console.log(`  ok    ${r.slug}  (${result.lat.toFixed(5)}, ${result.lng.toFixed(5)})`);
+    await sleep(SLEEP_MS);
+  }
+
+  console.log(`[nominatim] ok=${ok} miss=${miss} err=${err}`);
+  await db.pool.end();
+  process.exit(0);
+})().catch(async (err) => {
+  console.error('[nominatim] fatal', err);
+  try { await db.pool.end(); } catch (_) {}
+  process.exit(1);
+});

← ab2ce52 /for-businesses: Find-your-business CTA + claim-flow narrati · back to Ventura Claw Leads · Auto-fire codex-3way on every commit (hook + viewer) bdc2730 →