Mobility Metrics Core Data Product¶
The Mobility CDP provides calibrated traffic metrics: vehicle miles traveled (VMT), speeds, volumes, and annual average daily traffic (AADT). Data comes from INRIX probe data and FHWA counts, conflated to the OSM road network.
Key Data Sources¶
FHWA AADT¶
Annual Average Daily Traffic counts from the Federal Highway Administration. Stored as shapefiles uploaded to BigQuery.
- 2018 data for all states except California (which uses 2017 due to data irregularities)
- Table: model-159019.mobility.aadt_2018_except_for_CA_which_is_2017
INRIX Speed Profiles¶
Quarter-hourly speed data on INRIX XD segments, conflated to OSM via the OSM conflation table.
- Project: model-inrix.speed_profiles
- Schema: XDSegmentID, DayOfWeek, MinutesFromMidnight, SpeedMPH
- Reference speeds: USA_RefSpeedXD_* tables (schema: XDSegmentID, SpeedMPH)
INRIX Volume Profiles¶
Traffic volume data on OSM segments.
- Project: model-inrix.volume_profiles
OSM Conflation¶
Maps INRIX XD segments to OSM way IDs:
- Table: model-inrix.speed_profiles.osm_conflation_2020
- Key columns: XDSegID, OSMWayIDs (ARRAY), OSMWayDirections (ARRAY)
VMT¶
State-level total VMT from the FHWA Trends dashboard (September of each year).
BigQuery Schema¶
This section documents the actual BigQuery table structure for each mobility metric, so you can write queries directly in Hex without guessing at column names.
Table Naming¶
All mobility metric tables follow one pattern:
| Component | Value |
|---|---|
| project | core-data-service-stage (CDS data) or model-159019 (geostore, GPS probe mapping) |
| region (dataset) | usa |
| metric | aadt, hourly_volumes, qtr_hourly_speeds, annual_speeds, tmc |
| YYYY | Four-digit year (e.g. 2024) |
| Q{N} | Quarter number (e.g. Q4) |
Example fully-qualified table names:
core-data-service-stage.usa.aadt_2024_Q4core-data-service-stage.usa.hourly_volumes_2024_Q4core-data-service-stage.usa.qtr_hourly_speeds_2024_Q4core-data-service-stage.usa.annual_speeds_2024_Q4core-data-service-stage.usa.tmc_2024_Q4
Supporting tables:
- Geostore: model-159019.geos.geostore (geometry lookup by geo ID)
- GPS probe stable edges: model-159019.usa.gps_probe_stable_edges_{YYYY}_Q{N}_{hash} (segment-to-link mapping for newer speed queries)
- Compass direction UDF: model-159019.geos.compass_direction(heading) converts heading integers to compass strings (N, NE, E, etc.)
Column Schemas¶
AADT (aadt_{YYYY}_Q{N})¶
| Column | Type | Notes |
|---|---|---|
stable_edge_id |
STRING | Primary stable edge identifier |
comp_stable_edge_id |
STRING | Complementary (opposite direction) stable edge ID |
seg_id |
STRING | Internal segment ID |
osm_id |
STRING | OpenStreetMap ID |
aadt |
INTEGER | Annual average daily traffic (bidirectional total) |
aadt_single_unit |
INTEGER | Single unit truck AADT |
aadt_combination |
INTEGER | Combination truck AADT |
aadt_directional |
INTEGER | Directional AADT (2024_Q4+ only) |
aadt_single_unit_directional |
INTEGER | Directional single unit truck AADT (2024_Q4+) |
aadt_combination_directional |
INTEGER | Directional combination truck AADT (2024_Q4+) |
comp_aadt_directional |
INTEGER | Complementary direction AADT (2024_Q4+) |
comp_aadt_single_unit_directional |
INTEGER | Comp. direction single unit AADT (2024_Q4+) |
comp_aadt_combination_directional |
INTEGER | Comp. direction combination AADT (2024_Q4+) |
street_name |
STRING | Street name |
highway |
STRING | Road classification (motorway, trunk, primary, secondary, tertiary, etc.) |
bidirectional |
BOOLEAN | Whether the segment is bidirectional |
length |
FLOAT | Segment length |
heading |
INTEGER | Heading in degrees |
geometry |
GEOGRAPHY | Segment line geometry |
Schema note: Older tables (pre-2024_Q4) only have aadt, aadt_single_unit, aadt_combination. The directional and comp-directional columns were added starting in 2024_Q4.
Hourly Volumes (hourly_volumes_{YYYY}_Q{N})¶
| Column | Type | Notes |
|---|---|---|
stable_edge_id |
STRING | Stable edge identifier |
seg_id |
STRING | Segment ID (join key to AADT table) |
day |
STRING | Day of week: MON, TUE, WED, THU, FRI, SAT, SUN |
hour |
INTEGER | Hour of day (0-23) |
volume |
INTEGER | Total traffic volume |
volume_single_unit |
INTEGER | Single unit truck volume |
volume_combination |
INTEGER | Combination truck volume |
volume_directional |
INTEGER | Directional volume (newer tables) |
volume_single_unit_directional |
INTEGER | Directional single unit volume (newer) |
volume_combination_directional |
INTEGER | Directional combination volume (newer) |
comp_volume_directional |
INTEGER | Complementary direction volume (newer) |
comp_volume_single_unit_directional |
INTEGER | Comp. direction single unit (newer) |
comp_volume_combination_directional |
INTEGER | Comp. direction combination (newer) |
reference_point |
GEOGRAPHY | Spatial point for the volume record |
Important: Hourly volumes tables do not carry road metadata. You must JOIN to the AADT table on seg_id (or stable_edge_id for newer versions) to get street_name, highway, geometry, and other segment attributes.
Quarterly Hourly Speeds (qtr_hourly_speeds_{YYYY}_Q{N})¶
| Column | Type | Notes |
|---|---|---|
stable_edge_id |
STRING | Stable edge identifier (join key for newer tables) |
id |
INTEGER | Row ID (join key for older tables only) |
osm_id |
STRING | OpenStreetMap ID |
forward |
BOOLEAN | Direction indicator |
day |
STRING | Day of week abbreviation |
qtr_hour |
INTEGER | Quarter-hour index: 0-95 (96 fifteen-minute intervals per day) |
speed_mph |
INTEGER | Average speed in mph |
speed_p50_mph |
INTEGER | 50th percentile speed |
speed_p85_mph |
INTEGER | 85th percentile speed |
speed_p95_mph |
INTEGER | 95th percentile speed |
To convert qtr_hour to a time string (e.g. 0730, 1445):
LPAD(CAST(DIV(qtr_hour, 4) AS STRING), 2, '0')
|| LPAD(CAST(MOD(qtr_hour, 4) * 15 AS STRING), 2, '0')
Road metadata comes from joining to annual_speeds (same time period) on stable_edge_id (newer) or id (older).
Annual Speeds (annual_speeds_{YYYY}_Q{N})¶
| Column | Type | Notes |
|---|---|---|
stable_edge_id |
STRING | Stable edge identifier |
seg_id |
STRING | Segment ID |
osm_id |
STRING | OpenStreetMap ID |
id |
INTEGER | Row ID (older tables) |
forward |
BOOLEAN | Direction indicator |
free_flow_speed_mph |
INTEGER | Free flow speed |
speed_limit_mph |
INTEGER | Posted speed limit (2024_Q4+ only) |
average_speed_mph |
INTEGER | Average speed (2024_Q4+ only) |
speed_p50_mph |
INTEGER | 50th percentile speed (2024_Q4+ only) |
speed_p85_mph |
INTEGER | 85th percentile speed (2024_Q4+ only) |
speed_p95_mph |
INTEGER | 95th percentile speed (2024_Q4+ only) |
street_name |
STRING | Street name |
highway |
STRING | Road classification |
length |
FLOAT | Segment length |
heading |
INTEGER | Heading in degrees |
lat |
FLOAT | Latitude |
lng |
FLOAT | Longitude |
geometry |
GEOGRAPHY | Segment line geometry |
Schema note: Older tables (pre-2024_Q4) only have free_flow_speed_mph. The speed limit, average speed, and percentile columns were added in 2024_Q4+.
TMC - Turning Movement Counts (tmc_{YYYY}_Q{N})¶
| Column | Type | Notes |
|---|---|---|
intersection_id |
STRING | Unique intersection identifier |
intersection_id_geom |
GEOGRAPHY | Intersection point geometry (used for spatial filtering) |
inbound_stable_edge_id |
STRING | Stable edge ID for the inbound road |
inbound_street_name |
STRING | Name of the inbound street |
inbound_direction |
STRING | Inbound approach direction |
inbound_heading |
INTEGER | Heading of inbound approach |
outbound_osm_id |
STRING | OSM ID for the outbound road |
outbound_street_name |
STRING | Name of the outbound street |
turn_maneuver |
STRING | Description of the turn |
movement_direction |
STRING | Direction of movement |
turning_movement |
STRING | Turning movement type |
state |
STRING | State abbreviation (uppercased in output) |
day |
STRING | Day of week abbreviation |
hour |
INTEGER | Hour of day (0-23) |
count |
INTEGER | Traffic count for this movement/hour/day |
TMC uses intersection_id_geom (point) for spatial filtering, not line geometry like the other metrics.
Geo Filtering Pattern¶
Mobility queries use a two-stage spatial filter. When writing your own queries in Hex, follow this approach to avoid scanning entire tables.
Stage 1 -- Coarse polygon cutout. Create a CTE that filters the metric table with ST_Intersects against a WKT polygon covering your area of interest:
WITH aadt_cutout AS (
SELECT *
FROM `core-data-service-stage.usa.aadt_2024_Q4`
WHERE ST_Intersects(
ST_GEOGFROMTEXT(
'POLYGON((-73.99 40.75, -73.97 40.75, -73.97 40.77, -73.99 40.77, -73.99 40.75))',
make_valid => TRUE
),
geometry
)
)
This uses BigQuery's spatial index to quickly discard rows outside your bounding area.
Stage 2 -- Precise geostore join. Join the cutout against the exact geometry from the geostore to clip precisely to your target geography:
geometry_query AS (
SELECT geometry
FROM `model-159019.geos.geostore`
WHERE id IN (55257) -- your geo IDs
)
SELECT ac.*
FROM aadt_cutout ac
JOIN geometry_query gq
ON ST_Intersects(gq.geometry, ac.geometry)
Alternative: filter by stable edge IDs. If you already know your stable_edge_id values, skip the geostore join and filter directly:
For hourly volumes with network link IDs, check both directions:
Additional WHERE clauses you can layer on:
- Map bounds: AND ST_IntersectsBox(geometry, lng_min, lat_min, lng_max, lat_max)
- Street name: AND LOWER(street_name) LIKE LOWER('%Main St%')
- Highway class: AND highway IN ('motorway', 'primary', 'secondary')
Daily & Monthly Data¶
In addition to the seasonal AADT product, daily network link volumes and MADT (Monthly Average Daily Traffic) are available. These provide day-to-day nationwide vehicle counts at the network link level, enabling analysis at finer temporal resolution than the seasonal model. Use daily volumes when you need to understand specific-day traffic patterns or compute monthly averages rather than annual ones.
AADT Coverage¶
AADT coverage spans every county in the United States, updated annually. Per-county download is the standard access pattern. Year-over-year AADT comparisons are also available for longitudinal analysis.
Speed Profile Inclusion Threshold¶
A network link is included in the quarter-hourly speed profile data if it has observed data in at least two-thirds of the quarter-hour buckets (i.e., sufficient temporal coverage throughout the day). Links with sparser observations are excluded to maintain data quality.
Common Query Patterns¶
Join speed profiles to OSM links¶
SELECT speeds.*, conflation.OSMWayIDs
FROM `model-inrix.speed_profiles.USA_NAS672XD_2102` speeds
JOIN `model-inrix.speed_profiles.osm_conflation_2020` conflation
ON speeds.XDSegmentID = conflation.XDSegID
Regional cutout by XDSegmentID¶
WITH regional_osm AS (
SELECT osm.sseg_id AS way_id
FROM `model-inrix.osm.segments_2021_12_01` osm
JOIN `model-159019.geos.region` region
ON region.id = '{region_id}'
AND ST_COVERS(region.geom, osm.geom)
),
regional_conflation AS (
SELECT DISTINCT XDSegID
FROM `model-inrix.speed_profiles.osm_conflation_2020` conflation
CROSS JOIN UNNEST(conflation.OSMWayIDs) way_id
JOIN regional_osm USING(way_id)
)
SELECT speeds.*
FROM `model-inrix.speed_profiles.USA_NAS672XD_2102` speeds
JOIN regional_conflation ON speeds.XDSegmentID = regional_conflation.XDSegID