Analytics Toolbox for BigQuery
routing
This module contains functions that perform routing and path calculations.
DISTANCE_MAP
Description
Takes aggregated LineStrings and their corresponding speed to form a network and a reference point as input. Returns the length of the shortest path in terms of the distance between the node closest to the reference point and each of the nodes of the network.
If you are reusing the same network for multiple calls, using the GENERATE_NETWORK
function to build to a temporary table or subquery through the function and calling DISTANCE_MAP_FROM_NETWORK
will perform better.
linestring_collection
:ARRAY<STRUCT<geom GEOGRAPHY, speed FLOAT64>>
Aggregated LineStrings and their corresponding speed that form the network.point
:GEOGRAPHY
Reference point. The node of the network nearest to this point will be used as the reference point to compute the distance map.
Return type
ARRAY<STRUCT<weight FLOAT64, geography GEOGRAPHY>>
Example


DISTANCE_MAP_FROM_NETWORK
Description
Takes a network and a reference point as input. Returns the cost and geometry of the shortest path in terms of weights of links between the node closest to the reference point and each of the nodes of the network.
network
:ARRAY<STRUCT<src_idx INT64, src_geo GEOGRAPHY, dest_idx INT64, dest_geo GEOGRAPHY, weight FLOAT64>>
The network from which to compute the shortest path. You can use the result of theGENERATE_NETWORK
function.point
:GEOGRAPHY
Reference point. The node of the network nearest to this point will be used as the reference point to compute the distance map.
Return type
ARRAY<STRUCT<weight FLOAT64, geography GEOGRAPHY>>
Example


DISTANCE_MAP_FROM_NETWORK_TABLE
Description
Procedure that takes a network table and a reference point as input. Returns a table with the cost and geometry of the shortest path in terms of weights of links between the node closest to the reference point and each of the nodes of the network.
src_fullname
:STRING
The source table from where the network will be read. ASTRING
of the formprojectid.datasetid.tablename
is expected. Theprojectid
can be omitted (in which case the default one will be used). You can use the result of theGENERATE_NETWORK_TABLE
procedure.target_fullname_quoted
: The resulting table were the result will be stored. ASTRING
of the formprojectid.datasetid.tablename
is expected. Theprojectid
can be omitted (in which case the default one will be used). The dataset must exist and the caller needs to have permissions to create a new table in it. The process will fail if the target table already exists.point
:STRING
The reference geogpoint as an SQL evaluable string. The node of the network nearest to this point will be used as the reference point to compute the distance map.
Return type
ARRAY<STRUCT<weight FLOAT64, geography GEOGRAPHY>>
Example


To generate a network table check out the GENERATE_NETWORK_TABLE
procedure.
FIND_SHORTEST_PATH
Description
Takes aggregated LineStrings and their corresponding speed to form a network, a source point and a destination point as input. Returns the length and the geometry of the shortest path in terms of distance between the node closest to the source point and the node closest to the destination point. The geometry of the shortest path is based on a compacted geometry of the network.
If you are reusing the same network for multiple calls, using the GENERATE_NETWORK
function to build to a temporary table or subquery through the function and calling FIND_SHORTEST_PATH_FROM_NETWORK
will perform better.
linestring_collection
:ARRAY<STRUCT<geom GEOGRAPHY, speed FLOAT64>>
Aggregated LineStrings and their corresponding speed that form the network.point_a
:GEOGRAPHY
Source point. The node of the network nearest to this point will be used as the source point to compute the shortest path.point_b
:GEOGRAPHY
Destination point. The node of the network nearest to this point will be used as the destination point to compute the shortest path.
Return type
STRUCT<weight FLOAT64, path ARRAY<GEOGRAPHY>>
Example


FIND_SHORTEST_PATH_FROM_NETWORK
Description
Takes a network, a source point and a destination point as input. Returns the length and the geometry of the shortest path in terms of weights of links between the node closest to the source point and the node closest to the destination point.
network
:ARRAY<STRUCT<src_idx INT64, src_geo GEOGRAPHY, dest_idx INT64, dest_geo GEOGRAPHY, weight FLOAT64>>
The network from which to compute the shortest path. You can use the result of theGENERATE_NETWORK
function.point_a
:GEOGRAPHY
Source point. The node of the network nearest to this point will be used as the source point to compute the shortest path.point_b
:GEOGRAPHY
Destination point. The node of the network nearest to this point will be used as the destination point to compute the shortest path.
Return type
ARRAY<STRUCT<weight FLOAT64, geom GEOGRAPHY, path ARRAY<STRUCT<idx INT64, geom GEOGRAPHY>>>>
Example


FIND_SHORTEST_PATH_FROM_NETWORK_TABLE
Description
Procedure that takes a network, a source point and a destination point as input. Returns the length and the geometry of the shortest path in terms of weights of links between the node closest to the source point and the node closest to the destination point. It stores the result into a table.
src_fullname
:STRING
The source table from where the network will be read to compute the shortest path. ASTRING
of the formprojectid.datasetid.tablename
is expected. Theprojectid
can be omitted (in which case the default one will be used). You can use the result of theGENERATE_NETWORK_TABLE
procedure.target_fullname_quoted
: The resulting table were the result will be stored. ASTRING
of the formprojectid.datasetid.tablename
is expected. Theprojectid
can be omitted (in which case the default one will be used). The dataset must exist and the caller needs to have permissions to create a new table in it. The process will fail if the target table already exists.point_a
:STRING
The source geogpoint as an SQL evaluable string. The node of the network nearest to this point will be used as the source point to compute the shortest path.point_b
:STRING
The destination geogpoint as an SQL evaluable string. The node of the network nearest to this point will be used as the destination point to compute the shortest path.
Example


To generate a network table check out the GENERATE_NETWORK_TABLE
procedure.
GENERATE_NETWORK
Description
Generates a network graph from an aggregation of LineStrings and their corresponding speed. The network is based on a compacted geometry of the linestring collection where all nodes with only two links are dropped.
linestring_collection
:ARRAY<STRUCT<geom GEOGRAPHY, speed FLOAT64>>
Aggregated LineStrings and their corresponding speed that form the network.
Return type
ARRAY<STRUCT<src_idx INT64, src_geo GEOGRAPHY, dest_idx INT64, dest_geo GEOGRAPHY, weight FLOAT64>>
Example


GENERATE_NETWORK_TABLE
Description
Procedure that generates a network graph weighted by distance from a table with a column geo
containing LineStrings and stores the result into another table. The network is based on a compacted geometry of the LineString collection where all nodes with only two links are dropped.
To weight the graph based on a custom speed on each row, use the GENERATE_NETWORK function.
src_fullname
:STRING
The LineStrings table from where the network will be read to compute the shortest path. ASTRING
of the formprojectid.datasetid.tablename
is expected. Theprojectid
can be omitted (in which case the default one will be used).target_fullname_quoted
:STRING
The resulting table where the network will be stored. ASTRING
of the formprojectid.datasetid.tablename
is expected. Theprojectid
can be omitted (in which case the default one will be used). The dataset must exist and the caller needs to have permissions to create a new table in it. The process will fail if the target table already exists.
This procedure implements the same functionality as the GENERATE_NETWORK
function, with the only difference that it stores the resulting network into a table. It uses speed 1.0 by default.
Example


Here is a query to create a sample input table:


This project has received funding from the European Union’s Horizon 2020 research and innovation programme under grant agreement No 960401.