Spatial Extension for BigQuery
Routing
Creating a network
Importing linestrings from OpenStreetMap
To create a route, we need a network. Let’s extract linestrings from New York’s Liberty Island from OpenStreetMap planet ways table in BigQuery to build ourselves a playground.
Be careful, this query scans a lot of data (223GB). You may skip this query by creating directly the mydataset.liberty_island_linestrings
table from this Newlinedelimited JSON using this schema.


Creating the network from linestrings
Now let’s create a compacted network. During this process all nodes with only two links are removed, but total distances between nodes are kept.
We are going to use the GENERATE_NETWORK_TABLE
procedure:


If you prefer, you can also use the GENERATE_NETWORK
function instead, although it requires a slightly more advanced query:


Visualizing the network
Let’s see the difference between the collection of linestrings (in red) and the compacted network (in blue). Compaction reduces drastically the number of nodes and thus links, but it preserves the length of paths between the remaining nodes.


Calculating the shortest path
Let’s calculate the shortest path between two points.
You can use the FIND_SHORTEST_PATH_FROM_NETWORK_TABLE
procedure:


Our you can use the FIND_SHORTEST_PATH_FROM_NETWORK
function, which is a bit trickier than using the procedure:


Here is the result:
Calculating a distance map
Given a starting point, we are going to calculate the distances from that point to all the nodes of the network. Then, we are going to visualize the result to identify the destination points that are within a similar distance.
You can use the DISTANCE_MAP_FROM_NETWORK_TABLE
procedure:


Or you can use the DISTANCE_MAP_FROM_NETWORK
function, which is a bit trickier than using the procedure:


Here is the result. The starting point is highlighted in green. Destination points are colored according to the total length of the shortest path from the origin (darker means further).
In this GIF we can see all the destination points of the network that are reachable from the origin point, starting with the closest. The compacted network is depicted in gray.