`src/ingest/la_btrc.ts`

172 lines
/**
 * LA City Business Tax Registrants → ventura-corridor
 * https://data.lacity.org/resource/6rrh-rzua.json — Socrata SODA API, no key needed.
 *
 * Filters: street_address contains "VENTURA BLVD" or "VENTURA BOULEVARD".
 * Source: la_btrc · source_id: location_account (UNIQUE).
 * Category derived from NAICS (primary_naics_description); raw NAICS preserved.
 *
 * No LLM. Pure data pull. Run once to backfill (17K+ records expected).
 */
import { query, pool } from '../../db/pool.ts';

const BASE = 'https://data.lacity.org/resource/6rrh-rzua.json';
const WHERE =
  "upper(street_address) like '%VENTURA BLVD%' OR upper(street_address) like '%VENTURA BOULEVARD%'";
const PAGE = 1000;

interface BTRCRecord {
  location_account: string;
  business_name?: string;
  dba_name?: string;
  street_address?: string;
  city?: string;
  zip_code?: string;
  naics?: string;
  primary_naics_description?: string;
  council_district?: string;
  location_start_date?: string;
  location_end_date?: string;
  location_1?: { latitude?: string; longitude?: string };
}

function parseAddr(addr: string | undefined): { number: string | null; street: string | null } {
  if (!addr) return { number: null, street: null };
  const m = addr.trim().match(/^(\d+[A-Z]?)\s+(.+)$/i);
  return { number: m ? m[1] : null, street: m ? m[2] : addr };
}

// Map a NAICS description to our `office:*` / `shop:*` / `amenity:*` taxonomy
// (matching what the OSM Overpass ingest produces — keeps category filter consistent).
function naicsToCategory(naicsDesc: string | undefined): string {
  if (!naicsDesc) return 'office: company';
  const n = naicsDesc.toLowerCase();
  if (/restaurant|food service|caterer|cafeteria/.test(n)) return 'amenity: restaurant';
  if (/coffee|tea|snack|bakery|donut/.test(n)) return 'amenity: cafe';
  if (/bar|drinking place|tavern/.test(n)) return 'amenity: bar';
  if (/hair|salon|barber|nail|beauty|spa/.test(n)) return 'shop: hairdresser';
  if (/grocery|supermarket|market/.test(n)) return 'shop: supermarket';
  if (/clothing|apparel|shoe|jewelry|accessories/.test(n)) return 'shop: clothes';
  if (/furniture|home furnish|interior|wallcover|paint|floor/.test(n)) return 'shop: furniture';
  if (/pharmacy|drug store/.test(n)) return 'shop: pharmacy';
  if (/dental|dentist/.test(n)) return 'amenity: dentist';
  if (/medical|physician|chiropract|optomet|veterin/.test(n)) return 'amenity: clinic';
  if (/law|legal|attorney/.test(n)) return 'office: lawyer';
  if (/accounting|tax prep|bookkeep/.test(n)) return 'office: accountant';
  if (/insurance/.test(n)) return 'office: insurance';
  if (/real estate|property manage/.test(n)) return 'office: estate_agent';
  if (/financial|invest|advisor|wealth/.test(n)) return 'office: financial advisor';
  if (/independent artist|writer|performer|musician|production/.test(n)) return 'office: company';
  if (/auto|car|tire|repair/.test(n)) return 'shop: car_repair';
  if (/dry cleaning|laundry/.test(n)) return 'shop: laundry';
  if (/fitness|gym|yoga|exercise/.test(n)) return 'leisure: fitness_centre';
  return 'office: company';
}

async function fetchPage(offset: number): Promise<BTRCRecord[]> {
  // $order is required for stable pagination on Socrata — without it, rows
  // can drop or duplicate across pages.
  const url = `${BASE}?$where=${encodeURIComponent(WHERE)}&$order=location_account&$limit=${PAGE}&$offset=${offset}`;
  const r = await fetch(url, {
    headers: { Accept: 'application/json' },
    signal: AbortSignal.timeout(60_000),
  });
  if (!r.ok) throw new Error(`BTRC ${r.status}: ${(await r.text()).slice(0, 200)}`);
  return (await r.json()) as BTRCRecord[];
}

async function upsert(rec: BTRCRecord): Promise<'new' | 'updated' | 'skipped'> {
  if (!rec.location_account) return 'skipped';
  const name = (rec.dba_name || rec.business_name || '').trim();
  if (!name) return 'skipped';
  // Skip ended/closed registrations.
  if (rec.location_end_date) return 'skipped';

  const { number, street } = parseAddr(rec.street_address);
  const lat = rec.location_1?.latitude ? parseFloat(rec.location_1.latitude) : null;
  const lng = rec.location_1?.longitude ? parseFloat(rec.location_1.longitude) : null;
  const category = naicsToCategory(rec.primary_naics_description);

  const r = await query(
    `
    INSERT INTO businesses (
      source, source_id, name, category, category_raw, category_naics,
      address, street_number, street_name, city, zip,
      lat, lng, raw
    ) VALUES (
      'la_btrc', $1, $2, $3, $4, $5,
      $6, $7, $8, $9, $10,
      $11, $12, $13
    )
    ON CONFLICT (source, source_id) DO UPDATE SET
      name = EXCLUDED.name,
      category = EXCLUDED.category,
      category_raw = EXCLUDED.category_raw,
      category_naics = EXCLUDED.category_naics,
      address = EXCLUDED.address,
      street_number = EXCLUDED.street_number,
      street_name = EXCLUDED.street_name,
      city = EXCLUDED.city,
      zip = EXCLUDED.zip,
      lat = COALESCE(EXCLUDED.lat, businesses.lat),
      lng = COALESCE(EXCLUDED.lng, businesses.lng),
      raw = EXCLUDED.raw,
      last_seen_at = now()
    RETURNING (xmax = 0) AS inserted
  `,
    [
      rec.location_account,
      name,
      category,
      rec.primary_naics_description || null,
      rec.naics || null,
      rec.street_address || null,
      number,
      street,
      rec.city || null,
      rec.zip_code ? rec.zip_code.split('-')[0] : null,
      lat,
      lng,
      JSON.stringify(rec),
    ]
  );
  return r.rows[0].inserted ? 'new' : 'updated';
}

async function main() {
  console.log('[la_btrc] starting ingest of LA City Business Tax Registrants on Ventura Blvd…');
  let offset = 0;
  let total = 0;
  let stats = { new: 0, updated: 0, skipped: 0 };
  while (true) {
    const page = await fetchPage(offset);
    if (!page.length) break;
    for (const rec of page) {
      const r = await upsert(rec);
      stats[r]++;
      total++;
    }
    console.log(
      `[la_btrc] offset ${offset}+${page.length} (total ${total}) · new=${stats.new} updated=${stats.updated} skipped=${stats.skipped}`
    );
    if (page.length < PAGE) break;
    offset += PAGE;
  }
  console.log(`[la_btrc] done · ${total} records processed · ${JSON.stringify(stats)}`);
  // BTRC rows were already pre-filtered to street_address LIKE '%VENTURA BLVD%'
  // at the SODA query — so every imported row IS on the corridor by definition.
  console.log('[la_btrc] flagging all BTRC rows on_corridor (address-prefiltered)…');
  const fence = await query(`
    UPDATE businesses
       SET on_corridor = TRUE
     WHERE source = 'la_btrc' AND on_corridor = FALSE
  `);
  console.log(`[la_btrc] marked on_corridor for ${fence.rowCount} rows.`);
  await pool.end();
}

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