> ## Documentation Index
> Fetch the complete documentation index at: https://docs.axiomancer.io/llms.txt
> Use this file to discover all available pages before exploring further.

# Cross-dataset join keys

> The shared keys — entity IDs, jurisdiction slugs, H3 cells, and UNLOCODEs — that let you join any two Codex datasets without writing entity resolution code.

Codex datasets are designed so any two can join without custom wrangling. This page documents the keys that make that possible, which datasets carry which keys, and how to use them.

## The keys

| Key                 | Type    | Format                                                   | Used for                                                                                               |
| ------------------- | ------- | -------------------------------------------------------- | ------------------------------------------------------------------------------------------------------ |
| `record_id`         | URN     | `urn:aprs:record:{namespace}:{source_system}:{local_id}` | Stable identity across re-ingests. Primary key within a dataset.                                       |
| `chunk_id`          | URN     | `urn:aprs:chunk:{hex16}`                                 | Vector-index key. Deterministic SHA-256 of `record_id` + section.                                      |
| `h3_index`          | string  | H3 resolution-8 cell (e.g. `88283082b9fffff`)            | Spatial join across any geospatial datasets.                                                           |
| `event_id`          | UUID    | Standard UUIDv4                                          | Joins Events Timeline to any dataset that emits events.                                                |
| `jurisdiction_slug` | string  | `{city-slug}-{state-abbr}` (e.g. `philadelphia-pa`)      | Civic-layer joins across city, county, and state boundaries.                                           |
| `mmsi`              | string  | 9-digit Maritime Mobile Service Identity                 | AIS position to vessel joins. Not stable across re-registrations — use `imo` when you need permanence. |
| `imo`               | integer | 7-digit IMO number                                       | Vessel identity across ownership, flag, and name changes. Primary vessel key.                          |
| `poi_id`            | URN     | `urn:aprs:record:poi:{source_system}:{local_id}`         | POI joins across datasets.                                                                             |
| `parcel_id`         | string  | `{jurisdiction_slug}:{parcel-number}`                    | Parcel-level real-estate joins.                                                                        |
| `entity_urn`        | URN     | `urn:aprs:entity:{type}:{canonical-id}`                  | Entity resolution across sources (person, company, agency, vessel, etc.).                              |

## Key availability by dataset

| Dataset             | `h3_index` | `event_id` | `jurisdiction_slug` | `mmsi` | `imo` | `poi_id` | `parcel_id` | `entity_urn` |
| ------------------- | :--------: | :--------: | :-----------------: | :----: | :---: | :------: | :---------: | :----------: |
| Civic Intelligence  |     yes    |     yes    |         yes         |    —   |   —   |     —    |     opt.    |      yes     |
| Events Timeline     |     yes    |     PK     |         yes         |  opt.  |  opt. |   opt.   |     opt.    |      yes     |
| Urban Signal Grid   |     PK     |      —     |         yes         |    —   |   —   |     —    |      —      |       —      |
| POI Intelligence    |     yes    |      —     |         yes         |    —   |   —   |    PK    |      —      |       —      |
| AIS Maritime        |      —     |     yes    |          —          |   yes  |  yes  |     —    |      —      |      yes     |
| LEHD Commuter Flows |     yes    |      —     |         yes         |    —   |   —   |     —    |      —      |       —      |
| Permit Signals      |     yes    |     yes    |         yes         |    —   |   —   |     —    |     yes     |      yes     |

**PK** = primary key. **opt.** = emitted when the relevant entity is known. **—** = not applicable.

## Join examples

### Civic decisions in high-signal cells

Join Civic Intelligence to Urban Signal Grid via `h3_index` to find recent zoning decisions in high-scoring cells:

```sql theme={null}
SELECT
  cells.h3_index,
  cells.composite_score,
  civic.event_type,
  civic.occurred_at,
  civic.litigation_risk_score
FROM codex.urban_signal_grid cells
JOIN codex.civic_intelligence civic USING (h3_index)
WHERE civic.event_type IN ('zoning_vote', 'rezoning_hearing')
  AND civic.occurred_at >= '2026-01-01'
  AND cells.composite_score > 0.7;
```

### Full event detail for a jurisdiction

Join Events Timeline to Civic Intelligence via `event_id` to get entities, blockers, and hostility scores for civic events:

```sql theme={null}
SELECT
  e.event_id,
  e.occurred_at,
  e.category,
  c.entities_extracted,
  c.blockers,
  c.hostility_index
FROM codex.events_timeline e
JOIN codex.civic_intelligence c USING (event_id)
WHERE e.jurisdiction_slug = 'philadelphia-pa'
  AND e.occurred_at >= '2026-03-01';
```

### Vessel positions around dark events

Join AIS Maritime to Events Timeline via `event_id` to find vessels and their positions around Overwatch dark events:

```sql theme={null}
SELECT
  e.event_id,
  e.occurred_at,
  ais.mmsi,
  ais.imo,
  ais.lat,
  ais.lng,
  ais.speed
FROM codex.events_timeline e
JOIN codex.ais_maritime ais USING (event_id)
WHERE e.category = 'dark_event'
ORDER BY e.occurred_at DESC;
```

### Commuter inflow to high-signal cells

Join LEHD Commuter Flows to Urban Signal Grid via `h3_index`:

```sql theme={null}
SELECT
  g.h3_index AS destination_h3,
  g.composite_score,
  SUM(l.worker_count) AS inbound_workers
FROM codex.urban_signal_grid g
JOIN codex.lehd_commuter_flows l
  ON g.h3_index = l.destination_h3
GROUP BY g.h3_index, g.composite_score;
```

## Joins that will not work

| Attempted join                    | Why it fails                                                           | Use instead                                                                                   |
| --------------------------------- | ---------------------------------------------------------------------- | --------------------------------------------------------------------------------------------- |
| `lat`, `lng` equality             | Floating-point equality is unsafe; sources round differently           | `h3_index`                                                                                    |
| `vessel_name`                     | Names change with ownership and flag. Not unique.                      | `imo`                                                                                         |
| Source-native IDs across datasets | Different sources use different IDs for the same entity                | `entity_urn`                                                                                  |
| Date-only joins                   | Time zones and publication lag make cross-dataset alignment unreliable | Join on key + temporal filter (e.g. `ABS(a.occurred_at - b.occurred_at) < interval '7 days'`) |

## Key construction rules

### `h3_index`

Always resolution 8. Stored as the canonical H3 hexadecimal string (e.g. `88283082b9fffff`). When a record covers an area, the dataset either emits a separate row per H3 cell or an array column `h3_indexes` — the choice is documented in each dataset's schema.

### `jurisdiction_slug`

Lowercase, hyphen-separated:

* **Cities:** `{city}-{state-abbr}` (e.g. `philadelphia-pa`, `san-francisco-ca`)
* **Counties:** `{county}-county-{state-abbr}` (e.g. `harris-county-tx`)
* **States:** `{state-full-name}` (e.g. `texas`)
* **Special districts:** `{name}-{state-abbr}` (e.g. `port-authority-ny`)

### `mmsi` vs. `imo`

`mmsi` is the runtime identifier on every AIS position — use it for linking positions to vessel records during ingest. `imo` is the permanent vessel identifier (IMO A.600) that survives ownership, flag, and name changes. Use `imo` as the primary vessel key when available. Vessels without an IMO number (some fishing vessels and small craft) carry `has_imo=false`.

### `entity_urn`

Format: `urn:aprs:entity:{type}:{canonical-id}` where `type` is one of `person`, `company`, `agency`, `trust`, `vessel`, or `nonprofit`. Two source records share the same `entity_urn` when Codex entity resolution determines they refer to the same real-world entity.