Cross-dataset join keys

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:

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:

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:

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:

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.

Get started

Frameworks

Reference

The keys

Key availability by dataset

Join examples

Civic decisions in high-signal cells

Full event detail for a jurisdiction

Vessel positions around dark events

Commuter inflow to high-signal cells

Joins that will not work

Key construction rules

`h3_index`

`jurisdiction_slug`

`mmsi` vs. `imo`

`entity_urn`

​The keys

​Key availability by dataset

​Join examples

​Civic decisions in high-signal cells

​Full event detail for a jurisdiction

​Vessel positions around dark events

​Commuter inflow to high-signal cells

​Joins that will not work

​Key construction rules

​h3_index

​jurisdiction_slug

​mmsi vs. imo

​entity_urn

The keys

Key availability by dataset

Join examples

Civic decisions in high-signal cells

Full event detail for a jurisdiction

Vessel positions around dark events

Commuter inflow to high-signal cells

Joins that will not work

Key construction rules

`h3_index`

`jurisdiction_slug`

`mmsi` vs. `imo`

`entity_urn`