`scripts/ingest-la-historic-monuments.ts`

141 lines
/**
 * ingest-la-historic-monuments.ts
 *
 * LA City Historic-Cultural Monuments via NavigateLA layer 74.
 * Verified 2026-04-30: 7,836 monuments + survey records.
 *
 * Each monument gets:
 *   - A listing (matched to existing if address matches LADBS data, else stub)
 *   - A place_event row (kind='historic_designation') tied to that listing
 *   - source_url linking back to NavigateLA's HCM detail report
 *
 * Tier: A (LA City Office of Historic Resources primary record).
 */
import { Pool } from 'pg';

const pool = new Pool({
  host: process.env.PGHOST ?? '/tmp',
  database: process.env.PGDATABASE ?? 'stayclaim',
  user: process.env.PGUSER ?? process.env.USER,
  password: process.env.PGPASSWORD,
  port: parseInt(process.env.PGPORT ?? '5432', 10),
  max: 4,
});

const URL = 'https://maps.lacity.org/arcgis/rest/services/Mapping/NavigateLA/MapServer/74/query';
const PAGE = 1000;

function canonicalize(addr: string): string {
  return addr.toLowerCase().replace(/[^\w\s-]/g, '').replace(/\s+/g, '-').replace(/-+/g, '-').replace(/^-|-$/g, '').slice(0, 90);
}

async function fetchPage(offset: number) {
  const u = new URL_(URL);
  u.searchParams.set('where', '1=1');
  u.searchParams.set('outFields', '*');
  u.searchParams.set('returnGeometry', 'true');
  u.searchParams.set('outSR', '4326');
  u.searchParams.set('resultOffset', String(offset));
  u.searchParams.set('resultRecordCount', String(PAGE));
  u.searchParams.set('f', 'json');
  u.searchParams.set('orderByFields', 'OBJECTID');
  const res = await fetch(u);
  if (!res.ok) throw new Error(`HTTP ${res.status}`);
  const j = await res.json() as { features?: { attributes: any; geometry?: { x: number; y: number } }[] };
  return j.features ?? [];
}

// Workaround: the URL constant shadows the global URL constructor inside fetchPage above.
const URL_ = globalThis.URL;

async function ensureSchema() {
  // Use existing place_event table for the historic designation event
  // (kind='historic_designation', source_label='LA City Office of Historic Resources').
}

function parseLocation(loc: string): string | null {
  // "200-240 Columbia Avenue; 200-252 Columbia Place" → take first segment, strip ranges
  if (!loc) return null;
  const first = loc.split(/[;,]/)[0]!.trim();
  // collapse "200-240 Columbia Avenue" → "200 Columbia Avenue"
  return first.replace(/^(\d+)-\d+\s+/, '$1 ');
}

async function upsertOne(f: any) {
  const a = f.attributes ?? {};
  const name = (a.NAME ?? '').trim();
  const location = (a.LOCATION ?? '').trim();
  const addr = parseLocation(location);
  if (!addr) return { skipped: true };
  const canon = canonicalize(addr);
  if (!canon) return { skipped: true };
  const lat = f.geometry?.y ?? null;
  const lon = f.geometry?.x ?? null;
  const dateActive = a.DATE_ACTIVE ? new Date(a.DATE_ACTIVE).toISOString().slice(0, 10) : null;
  const objectId = a.OBJECTID;

  // Try to match an existing listing first (LADBS / BH / etc). Otherwise create a stub.
  const exists = await pool.query<{ id: string }>(
    `SELECT id FROM listing
     WHERE source IN ('ladbs_permit','bh_permit','manual')
       AND lower(regexp_replace(coalesce(address_line1,''), '[^\\w\\s-]', '', 'g')) ILIKE $1
     LIMIT 1`,
    [`%${canon.replace(/-/g, '%')}%`]
  );
  let listingId: string;
  if (exists.rows[0]) {
    listingId = exists.rows[0].id;
  } else {
    const r = await pool.query<{ id: string }>(
      `INSERT INTO listing (slug, source, source_id, title, address_line1, city, state, country, latitude, longitude, is_public, tier)
       VALUES ($1,'la_hcm',$2,$3,$4,'Los Angeles','CA','US',$5,$6,true,'free')
       ON CONFLICT (slug) DO UPDATE SET
         latitude = COALESCE(listing.latitude, EXCLUDED.latitude),
         longitude = COALESCE(listing.longitude, EXCLUDED.longitude)
       RETURNING id`,
      [`${canon}-hcm`, `hcm:${objectId}`, name || addr, addr, lat, lon]
    );
    listingId = r.rows[0]!.id;
  }

  await pool.query(
    `INSERT INTO place_event
       (listing_id, kind, summary, valid_from, source_tier, source_label, source_url, source_id, public_visible)
     VALUES ($1,'historic_designation',$2,$3::date,'A','LA City Office of Historic Resources',$4,$5,true)
     ON CONFLICT DO NOTHING`,
    [
      listingId,
      `Designated Historic-Cultural Monument: ${name} (HCM #${a.MNT_NUM || objectId} · ${a.HIST_TYPE || 'HCM'})`,
      dateActive,
      `https://maps.lacity.org/${a.NLA_URL ?? `navigatela/reports/historic_cultural_monuments.cfm?PK=${objectId}`}`,
      `hcm:${objectId}`,
    ]
  );
  return { ok: true };
}

async function main() {
  await ensureSchema();
  let offset = 0, total = 0, ok = 0, skipped = 0;
  const t0 = Date.now();
  while (true) {
    const features = await fetchPage(offset);
    if (!features.length) break;
    for (const f of features) {
      const r = await upsertOne(f);
      if (r.ok) ok++;
      else skipped++;
    }
    total += features.length;
    const dt = (Date.now() - t0) / 1000;
    console.log(`  hcm: ${total} processed (${ok} ok, ${skipped} skipped) — ${(total/dt).toFixed(0)}/s`);
    if (features.length < PAGE) break;
    offset += features.length;
    if (offset > 50000) break;
  }
  console.log(`✓ HCM: ${ok} monuments tied to listings`);
  await pool.end();
}

main().catch(e => { console.error('FATAL', e); process.exit(1); });