rmi-sampledatasets

name	rmi-sampledatasets
description	Specialized guidance for discovering, subscribing to, and working with Roads Management Insights (RMI) public sample datasets across global metropolitan areas (Boston, Paris, Tokyo, Detroit, Manhattan, Rome, Singapore, Sydney, Buenos Aires, São Paulo State). Use when Gemini CLI needs to list available datasets from Analytics Hub via api-analyticshub, validate queries against sample data, estimate production costs based on sample baselines, or ensure correct temporal filtering for static snapshots.
dependencies	["api-analyticshub","bigquery-practices"]

RMI Sample Datasets (rmi-sampledatasets)

This skill provides comprehensive context, architectural scaling methods, automated listing discovery tools, and strict static temporal constraints for working with the Roads Management Insights (RMI) public sample datasets published on BigQuery Analytics Hub.

---

1. Multi-Region Global Sample Catalog

RMI maintains 10 localized, pre-subscribed sample datasets on Google Cloud Analytics Hub. Anyone with a standard Google account can access and subscribe to these listings directly into their own BigQuery project for zero-copy, zero-egress analysis:

• Public Analytics Hub Exchange: Analytics Hub Data Exchange: rmi_sampledata_v2_ga_prod
• Publisher Project ID: 1024202510105
• Location: us
• Exchange ID: rmi_sampledata_v2_ga_prod

Active Regional Listings Snapshot

[!TIP] Subscribed Linked Dataset Model: Because publisher source datasets reside in provider projects without direct query access, users subscribe to listings via Analytics Hub to create a linked dataset inside their own destination project (e.g. [YOUR_PROJECT].[YOUR_LINKED_DATASET]). All queries must target the subscriber's linked dataset.

#	Listing ID	Metro Area / Region	Default Linked Dataset Name	Primary Route Monitoring Strategy	Baseline Window
1	boston_ga	Boston, MA (USA)	ah_rmi_boston	Priority road network (CONTROLLED_ACCESS, LIMITED_ACCESS, PRIMARY_HIGHWAY, SECONDARY_ROAD, MAJOR_ARTERIAL, MINOR_ARTERIAL) (~1,847 routes)	Spring/Summer 2026
2	src_buenosaires_ga	Buenos Aires (Argentina)	ah_rmi_buenosaires	Top road priorities across Ciudad Autónoma de Buenos Aires	Spring/Summer 2026
3	detroit_ga	Detroit, MI (USA)	ah_rmi_detroit	Priority corridors (CONTROLLED_ACCESS, PRIMARY_HIGHWAY, SECONDARY_ROAD)	Spring/Summer 2026
4	manhattan_ga	Manhattan / NYC (USA)	ah_rmi_manhattan	Origin-Destination pairs from Manhattan to major airports (LGA, JFK, EWR) with 0 intermediate waypoints for dynamic pathing	Spring/Summer 2026
5	paris_ga	Paris (France)	ah_rmi_paris	Blvd Périphérique ring-road and connected radial feeder segments

---

2. Automated Discovery Tooling (scripts/list_sample_datasets.sh)

This skill includes an executable CLI discovery tool scripts/list_sample_datasets.sh that leverages the api-analyticshub API client to query the live Analytics Hub exchange and output real-time metadata.

Usage Commands

# 1. Format and print human-readable catalog table and route criteria
./scripts/list_sample_datasets.sh

# 2. Output raw JSON payload from the Analytics Hub API for automated parsing
./scripts/list_sample_datasets.sh --json

Subscribing to a Listing via API

To link any listing directly into your destination BigQuery project:

source api-analyticshub/scripts/analyticshub_v1.sh

# 1. Create subscription payload
sub_payload=$(create_subscribe_listing_request_json "projects/MY_PROJECT/datasets/ah_rmi_boston")

# 2. Subscribe to listing
analyticshub_projects_locations_dataExchanges_listings_subscribe \
  "1024202510105" "us" "rmi_sampledata_v2_ga_prod" "boston_ga" "${sub_payload}"

---

3. Key Principles for Sample Dataset Usage

1. The Static Temporal Anchor Mandate

Because public sample datasets are historical snapshots, any queries utilizing CURRENT_TIMESTAMP(), CURRENT_DATE(), or relative offsets (e.g. TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)) will return zero rows.

• Mandatory Pattern: You must replace all relative time filters with static datetime literals matching the snapshot's active timeframe (e.g. June 2026).

-- INCORRECT (Returns 0 rows on static sample datasets)
WHERE record_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)

-- CORRECT (Captures the active Spring/Summer 2026 snapshot window)
WHERE record_time BETWEEN '2026-06-01' AND '2026-07-01'

2. The road_segment_ids Schema Threshold (June 19, 2026)

The road_segment_ids column was introduced on June 19, 2026. Telemetry snapshots captured prior to this date contain empty arrays ([]). Always guard segment unnesting queries:

WHERE record_time >= '2026-06-19'
  AND ARRAY_LENGTH(road_segment_ids) > 0

3. Route Attributes Metadata & Mandatory Type Casting

Routes in routes_status include a JSON string column route_attributes containing automatically populated contextual metadata.

[!IMPORTANT] Strict String Key-Value Representation: RMI route attributes are defined as a strict map<string, string>. Consequently, all values are stored as string literals, even inherently numeric properties (e.g., route_length_meters is stored as "128.088" rather than a float, and num_of_roads is stored as "3" rather than an integer). You must explicitly typecast these values using SAFE_CAST() in BigQuery SQL when performing arithmetic, aggregations, or numeric range filtering (e.g., SAFE_CAST(JSON_VALUE(route_attributes, '$.route_length_meters') AS FLOAT64) >= 500.0) to avoid lexicographical string comparison errors.

Attribute Key	Storage Type	Logical Type	Description	Example String Value
sampledataset	STRING	String	Metropolitan region or partition identifier.	"boston", "paris", "sydney"
priority	STRING	Enum String	Road hierarchy class (ROAD_PRIORITY_CONTROLLED_ACCESS, ROAD_PRIORITY_PRIMARY_HIGHWAY, ROAD_PRIORITY_SECONDARY_ROAD, ROAD_PRIORITY_LIMITED_ACCESS, ROAD_PRIORITY_MAJOR_ARTERIAL, ROAD_PRIORITY_MINOR_ARTERIAL).	"ROAD_PRIORITY_CONTROLLED_ACCESS"
route_length_meters	STRING	Float	Modeled physical length of the route corridor in meters (requires SAFE_CAST(... AS FLOAT64)).	"128.088"
num_of_roads	STRING	Integer	Number of underlying road segments composing the corridor (requires SAFE_CAST(... AS INT64)).	"1", "3"
road_first_placeid	STRING	Place ID	Google Maps Place ID for the first/entry segment of the corridor.	"ChIJBaTdJ76uEmsRouRmspMwbFQ"
road_last_placeid

BigQuery Extraction & Casting Pattern

SELECT 
  selected_route_id,
  display_name,
  JSON_VALUE(route_attributes, '$.priority') AS road_priority,
  -- Explicit SAFE_CAST for numeric attributes stored as strings
  SAFE_CAST(JSON_VALUE(route_attributes, '$.route_length_meters') AS FLOAT64) AS length_meters,
  SAFE_CAST(JSON_VALUE(route_attributes, '$.num_of_roads') AS INT64) AS segment_count
FROM 
  `LINKED_DATASET_NAME.routes_status`
WHERE 
  -- Numeric range filter using SAFE_CAST
  SAFE_CAST(JSON_VALUE(route_attributes, '$.route_length_meters') AS FLOAT64) >= 500.0
  AND JSON_VALUE(route_attributes, '$.priority') = 'ROAD_PRIORITY_CONTROLLED_ACCESS';

4. Multi-Tiered Cost and Fleet Scaling Projections

The sample datasets are lightweight and highly partitioned, making interactive validation queries inexpensive (< $0.01 per scan). Use the scaling multipliers below to project production costs:

Fleet Tier	Monitored Routes	Multiplier vs. Sample Base	Monthly Bytes (Approx.)	Scan Cost Category
Sample Baseline	~1,850 Routes	1x (Base)	~1.6 GB / month	Negligible (< $0.01)
City-Wide Fleet	~10,000 Routes	~5.4x	~8.6 GB / month	Minimal (< $0.05)
State / Regional Fleet	~50,000 Routes	~27x	~43 GB / month	Moderate (~$0.22)
Mega Fleet (Nationwide)	~500,000 Routes	~270x	~430 GB / month	Strategic (~$2.15)

5. Complexity Classes

• O(T) - Time-Dependent (Linear Growth): Multi-month longitudinal trend queries. Storage scan sizes grow linearly with time.
• O(1) - Time-Invariant (Flat Cost): Operational queries with bounded partition pruning (record_time BETWEEN ...).
• O(R) - Route-Dependent (Metadata Growth): Administrative queries filtering on routes_status.

---

4. Troubleshooting & Common Pitfalls

Symptom / Error	Root Cause	Corrective Recovery Action
Zero rows returned	Using relative datetime functions like CURRENT_TIMESTAMP().	Replace with static anchors (e.g. BETWEEN '2026-06-01' AND '2026-07-01').
road_segment_ids empty or missing	Querying date partitions prior to June 19, 2026 threshold.	Add filter WHERE record_time >= '2026-06-19' AND ARRAY_LENGTH(road_segment_ids) > 0.
Inaccurate Dry Run Cost	Missing date partition filters causing full-table scan estimate.	Apply partition filters on record_time first before evaluating dry-run byte counts.
Mismatched coordinate comparisons	Attempting joins on floating-point latitude/longitude coordinates.	Never join on spatial float coordinates. Use selected_route_id as the robust primary join key.

---

5. References & Linked Artifacts

• Analytics Hub Data Exchange: rmi_sampledata_v2_ga_prod
• RMI Multipliers & Ingestion Costs
• Discovery Script: list_sample_datasets.sh

---

6. Examples

Example 1: Morning Commute Peak Performance Audit (Local Timezone Aware)

Analyze average speed drops during morning rush hour (7:00 AM – 9:00 AM local Boston time / EDT) across the Boston network for June 2026:

SELECT 
  h.selected_route_id,
  s.display_name,
  ROUND(AVG(h.duration_in_seconds), 1) AS avg_peak_duration,
  ROUND(AVG(h.static_duration_in_seconds), 1) AS free_flow_duration,
  -- Compute Peak Travel Time Index (TTI)
  ROUND(SAFE_DIVIDE(
    AVG(h.duration_in_seconds), 
    AVG(h.static_duration_in_seconds)
  ), 2) AS peak_tti
FROM 
  `my_project.ah_rmi_boston.historical_travel_time` AS h
JOIN
  `my_project.ah_rmi_boston.routes_status` AS s
  USING (selected_route_id)
WHERE 
  -- Static Temporal Anchor matching active Spring/Summer 2026 snapshot
  h.record_time BETWEEN '2026-06-01' AND '2026-07-01'
  -- Morning commute hour bounds converted to local Boston time (America/New_York: EDT UTC-4)
  AND EXTRACT(HOUR FROM DATETIME(h.record_time, 'America/New_York')) BETWEEN 7 AND 8
  AND s.status = 'STATUS_RUNNING'
  AND ST_GEOMETRYTYPE(h.route_geometry) = 'ST_LineString'
GROUP BY 
  h.selected_route_id,
  s.display_name
HAVING
  peak_tti > 1.1
ORDER BY 
  peak_tti 
LIMIT ;

Example 2: Operational Route Status & Attribute Mapping

Count active routes and list validation states grouped by custom road priority tiers defined in route metadata:

SELECT 
  JSON_VALUE(route_attributes, '$.priority') AS priority_tier,
  status,
  validation_error,
  COUNT(1) AS route_count
FROM 
  `my_project.ah_rmi_boston.routes_status`
GROUP BY 
  priority_tier,
  status,
  validation_error
ORDER BY 
  route_count DESC;