BigQuery Geo Router

Converts a series of long/lat coordinates in BigQuery into routes generated using OpenStreetMap data and the Open Source Routing Machine.

The resulting data can be loaded back into BigQuery for further analysis - each route is typed appropriately to be used with BigQuery's geospatial querying functionality.

Usage

Setup

The first step is to download and prepare the OpenStreetMap data for the region you're interested in calculating routes for.

Geofabrik has a number of regional OpenStreetMap extracts that can be used as our starting point. The included Cloud Build config, cloudbuild-setup.yaml, takes care of many the of steps you need to get everything set up.

The two main parameters needed for the Cloud Build job are _OSM_NAME and _DATA_BUCKET.

• _OSM_NAME: this is the name, including any prefixes, for the OpenStreetMap data file available from Geofabrik (the Cloud Build job is configured to download extracts from this location). The example below is referencing the data for Illinois available here (note the URL path and its correlation to _OSM_NAME).
• _DATA_BUCKET: the name of the Google Cloud Storage bucket where the resulting files will be saved and referenced when calculating routes.

gcloud builds submit \
    --config cloudbuild-setup.yaml \
    --substitutions=_OSM_NAME=north-america/us/illinois \
    --substitutions=_DATA_BUCKET=gs://my-bucket-for-storing-routing-data

Generating Routes

Once the above step is complete, routes can be calculated by running (another) Cloud Build job. The job builds a Docker container containing a Node.js application that queries BigQuery and runs the routing calculation process. The resulting routes are output to a local JSON file that can subsequently be loaded back into BigQuery.

The input to the route calculator can either be a reference to a table in BigQuery (in the format dataset.table) or the path to a SQL file.

gcloud builds submit \
    --config cloudbuild.yaml \
    --substitutions=_OSM_NAME=north-america/us/illinois \
    --substitutions=_DATA_BUCKET=gs://my-bucket-for-storing-routing-data
    --substitutions=_DESTINATION_DATASET=trip_routes \
    --substitutions=_INPUT_TABLE=trip_routes.trips

Visualising Routes

TBC

Configuring OSRM

TBC