Accumulated road data

Accumulated road data refers to data you can build over time for your selected routes. You start receiving data once you create a route with the Roads Selection API. This data can then be used for in-depth analysis, strategic planning, and understanding of long-term behavior of routes.

Accumulated road data is provided as time series data, which includes trip duration, speed reading intervals (SRIs), and route geometry for each route. This data is stored and accessed through BigQuery.

Set up BigQuery

Before you can access your road data in BigQuery, you must set up your Google Cloud project and subscribe to the data exchange.

Prerequisites

Ensure your Google Cloud project and account have the following configurations:

  1. Enable the BigQuery API. For instructions, see Query a public dataset with the Google Cloud console.
  2. Enable the Analytics Hub API. See Analytics Hub API.
  3. Grant necessary IAM roles. Ensure your account has the following roles to perform subscriber tasks and create datasets:

Subscribe to your data exchange

Your road data is shared with you through a private data exchange in BigQuery (Analytics Hub). You will receive a specific subscribe link from a Google partner to access your data. This dataset adheres to the following naming convention:

historical_roads_data_PROJECT_NUMBER.

To subscribe to your data:

  1. Click the subscribe link provided by your Google partner. This link will take you directly to the data exchange in the Google Cloud console.
  2. In the Google Cloud console, review the data exchange details.
  3. Click the Subscribe button in the dialog.
  4. When the subscription is complete, the linked dataset appears in the BigQuery Explorer panel in the Google Cloud console. You can now use the table names shown in the Explorer panel to access the data in your SQL queries.

BigQuery tables

Accumulated road data of your selected routes is hosted under an isolated BigQuery dataset of a Google-owned cloud project. It is shared with you through a private data exchange created exclusively for you in BigQuery sharing (Analytics Hub). To access the data, you need to subscribe to the data exchange and create linked dataset under your Google Cloud project.

The shared BigQuery dataset includes a few BigQuery tables that are defined and created by Google. The following are the details of each table.

historical_travel_time table

Following is the schema for BigQuery table historical_travel_time:

Name Mode Type Description
selected_route_id NULLABLE STRING selected_route_id of the route
display_name NULLABLE STRING Display name of the route
record_time NULLABLE TIMESTAMP The timestamp when the route data is computed
duration_in_seconds NULLABLE FLOAT The traffic-aware duration of the route
static_duration_in_seconds NULLABLE FLOAT The traffic-unaware duration of the route
route_geometry NULLABLE GEOGRAPHY The traffic-aware polyline geometry of the route

Table behaviors and facts

  • The table is partitioned by day and has a 10-year expiration set for each partition.
  • The table is updated every hour with latest traffic data written in batch.
  • Once a new route is created in Roads Selection API, expect to wait up to 1 hour to see the data available in this table.
  • Once a route is deleted from Roads Selection API, no new data is written to this table for the route. However, historical data remains until expiration.

recent_roads_data table

Note: This table is available only when your contract includes Real-time road data.

Different from historical_travel_time, this table also holds SpeedReadingInterval data. Here is the schema in BigQuery:

Name Mode Type Description
selected_route_id NULLABLE STRING selected_route_id of the route
display_name NULLABLE STRING Display name of the route
record_time NULLABLE TIMESTAMP The timestamp when the route data is computed
duration_in_seconds NULLABLE FLOAT The traffic-aware duration of the route
static_duration_in_seconds NULLABLE FLOAT The traffic-unaware duration of the route
route_geometry NULLABLE GEOGRAPHY The traffic-aware polyline geometry of the route
speed_reading_intervals REPEATED RECORD Intervals representing the traffic density across the route. See the original definition in Routes API
speed_reading_intervals.interval_coordinates REPEATED GEOGRAPHY The geometry for this interval
speed_reading_intervals.speed NULLABLE STRING The classification of the speed for this interval. Possible values: NORMAL, SLOW, TRAFFIC_JAM

Table behaviors and facts

  • The table is partitioned by day and has a 30-day expiration set for each partition.
  • The table is updated every hour with latest traffic data written in batch.
  • Once a new route is created in Roads Selection API, expect to wait up to 1 hour to see the data available in this table.
  • Once a route is deleted from Roads Selection API, no new data will be written to this table for the route. However, historical data remains until expiration.

routes_status table

The table contains the selected routes metadata and status information. It aims to provide an easier way of viewing all routes and status. It can be joined with the other two tables for data filtering. Here is the schema in BigQuery:

Name Mode Type Description
selected_route_id NULLABLE STRING selected_route_id of the route
display_name NULLABLE STRING Display name of the route
status NULLABLE STRING Status of the route
validation_error NULLABLE STRING Validation error of the route
low_road_usage_start_time NULLABLE TIMESTAMP The time when the route first observed low road usage during re-validation. It is associated with VALIDATION_ERROR_LOW_ROAD_USAGE.
route_attributes NULLABLE STRING Custom attributes for the selected route

Table behaviors and facts

  • Only routes in STATE_RUNNING or STATE_INVALID status are included in this table.
  • Routes metadata and status are updated every hour, non-stop.
  • Once a new route is created in Roads Selection API, expect to wait up to 1 hour to see the route available in this table.
  • Once a route is deleted from Roads Selection API, expect to wait up to 1 hour to see the selected route being removed from this table.