`README.md`

104 lines
# LA Socrata / ArcGIS Public-Data Ingester

Programmatic ingester for Los Angeles public data → the local `realestate` Postgres DB.
**$0** — pulls from free public government APIs, writes to local Postgres. No scraping,
no captcha, no login. Feeds the WhoLivedThere / ClaimMyAddress / BubbesBlock home-history stack.

Built TK-10450 (2026-08-10). All endpoints live-probed 2026-08-10.

## Why Socrata (+ one ArcGIS adapter)

LA City and LA County publish open data on **Socrata**, which exposes the **SODA API** —
real REST/JSON endpoints with SoQL (`$where`/`$select`/`$order`/`$limit`/`$offset`). That's
the "feed-first" doctrine applied to gov data: no selector-rot, no headless browser.

The one exception: **LA County assessor parcels live on ArcGIS FeatureServer**, not Socrata,
so this ingester carries two adapters behind one common page-generator interface.

## Sources

| Source (`--list` name)          | Platform | Dataset            | Rows    | Cadence | Dedup key              | Cursor                |
|---------------------------------|----------|--------------------|---------|---------|------------------------|-----------------------|
| `building_permits`              | Socrata  | `pi9x-tg5x`        | 405k    | daily   | (dataset_id,permit_nbr)| `issue_date`          |
| `building_permits_2010_2019`    | Socrata  | `dyxf-7hc4`        | 533k    | static  | (dataset_id,permit_nbr)| —                     |
| `building_permits_pre2010`      | Socrata  | `e67z-kt2n`        | 640k    | static  | (dataset_id,permit_nbr)| —                     |
| `building_permits_electrical`   | Socrata  | `ysqd-apz7`        | 354k    | daily   | (dataset_id,permit_nbr)| `issue_date`          |
| `building_permits_mech_plumb`   | Socrata  | `67is-svtd`        | 293k    | daily   | (dataset_id,permit_nbr)| `issue_date`          |
| `assessor_parcels`              | ArcGIS   | Parcel_Data_2021   | 12.1M   | annual  | (ain,roll_year)        | `RollYear`            |
| `code_enforcement_open`         | Socrata  | `u82d-eh7z`        | 29k     | daily   | apno                   | `adddttm`             |
| `code_enforcement_closed`       | Socrata  | `rken-a55j`        | 822k    | daily   | apno                   | `resdttm`             |
| `business_registrations`        | Socrata  | `6rrh-rzua`        | 632k    | weekly  | location_account       | `location_start_date` |
| `film_permits_weho`             | Socrata  | `xd5y-dzvf`        | 1.9k    | live    | permit_id              | —                     |

**Film permits — LA City has NO live public dataset** (both prior datasets deprecated).
FilmLA holds the consolidated LA City/County permit and only releases bulk via CPRA.
WeHo is the live free feed; a business-registration NAICS `512100` filter is a weak proxy
for LA City production companies.

## Setup

```bash
cd ~/Projects/la-socrata-ingester
npm install                       # one dep: pg
cp .env.example .env              # optional: add a free SOCRATA_APP_TOKEN to raise throttle
npm run schema                    # creates la_*_raw + la_ingest_state in realestate DB
```

## Usage

```bash
node src/cli.js --list                          # show all sources
node src/cli.js building_permits --max=25        # smoke test (25 rows)
node src/cli.js code_enforcement_open            # incremental refresh (since last cursor)
node src/cli.js "building permits"               # whole group
node src/cli.js all                              # every non-static source, incremental
node src/cli.js assessor_parcels --full          # 12.1M-row backfill (ALL roll years)
```

- No args / `--full`: full crawl. Otherwise **incremental** from the stored high-water mark
  in `la_ingest_state` (Socrata `cursor > last`, so daily re-runs are cheap).
- `assessor_parcels` defaults to the **latest roll year only** (~2.4M); `--full` pulls all 12.1M.
- `--max=N` caps rows (testing); `--page=N` overrides page size.
- Static historical permit buckets are excluded from `all` — run them once explicitly.

### One-time backfill (run in order; each is resumable — cursor persists on failure)

```bash
node src/cli.js building_permits --full
node src/cli.js building_permits_2010_2019
node src/cli.js building_permits_pre2010
node src/cli.js code_enforcement_open --full
node src/cli.js code_enforcement_closed --full
node src/cli.js business_registrations --full
node src/cli.js film_permits_weho --full
node src/cli.js assessor_parcels          # latest roll year first (sane); --full later
```

### Scheduled incremental (suggested launchd/cron, daily)

```bash
cd ~/Projects/la-socrata-ingester && node src/cli.js all >> tmp/ingest.log 2>&1
```

## Schema & joins

Each `la_*_raw` table keeps full source fidelity in `raw jsonb` **plus** typed columns for
the fields we query/join, **plus** provenance (`source_url`, `fetched_at`; `dataset_id` where
multiple datasets share a table). Upserts are `ON CONFLICT (<key>) DO UPDATE`.

- **Permits ↔ parcels**: `la_building_permits_raw.apn = la_assessor_parcels_raw.ain`
  (both 10-digit APN/AIN). Clean join.
- **Code enforcement ↔ parcels**: code cases carry LADBS's internal `prclid`, **not** the AIN —
  join by geocoded address / lat-lon, not parcel key.
- **Business ↔ address**: `zip_code` + `street_address` (+ lat/lon from the source GeoJSON).

## Design notes / gotchas

- Socrata pages via `$order=:id` (always-unique system field) so rows arriving mid-crawl
  never shift the window. ArcGIS pages via `resultOffset`/`resultRecordCount` (2k cap) and
  honors `exceededTransferLimit`.
- Rows with a null primary-key component are skipped (logged as "keyless"), never crash.
- HTTP layer retries with exponential backoff on 429/5xx.
- Deprecated datasets to avoid (verified dead): permits `xnhu-aczu`; film `tg4x-b46p`, `c2az-nhru`.
- Assessor is a **tabular** FeatureServer (no geometry); parcel polygons are a separate service.