The Shortest_Path_One_To_All
function is like the Shortest_Path
that you have used in the previous tutorials. As the name suggests, it calculates the minimum distances/costs from a start vertex to all other vertices in the network. The result is not a single WeigthedPath
, but a Graph which contains edges of shortest paths only. The vertices of the output Graph have an attribute CALCULATED_COST
which indicates the minimum distance/cost required to reach the vertex.
The SPOA statement is constructed like this:
GRAPH g_spoa = SHORTEST_PATHS_ONE_TO_ALL(:g, :v_start, "CALCULATED_COST",
(EDGE e, DOUBLE current_path_cost) => DOUBLE {
IF(:current_path_cost < :i_max) { RETURN :e."length"/DOUBLE(:e."SPEED_MPH"); }
ELSE { END TRAVERSE; }
});

Here is the complete statement that you can run in your example. As previously, first you create a table type and then the GRAPH procedure:
CREATE TYPE "TT_SPOA_VERTICES" AS TABLE ("osmid" BIGINT, "CALCULATED_COST" DOUBLE);
CREATE OR REPLACE PROCEDURE "GS_SPOA"(
IN i_startVertex BIGINT,  the key of the start vertex
IN i_max DOUBLE,  the maximum distance/cost
OUT o_vertices "TT_SPOA_VERTICES"
)
LANGUAGE GRAPH READS SQL DATA AS
BEGIN
GRAPH g = Graph("DAT260", "LONDON_GRAPH");
VERTEX v_start = Vertex(:g, :i_startVertex);
 Running shortest paths one to all, which returns a subgraph. The WEIGHT based path length to a vertex is stored in the attribute CALCULATED_COST
GRAPH g_spoa = SHORTEST_PATHS_ONE_TO_ALL(:g, :v_start, "CALCULATED_COST",
(EDGE e, DOUBLE current_path_cost) => DOUBLE{
IF(:current_path_cost < :i_max) { RETURN :e."length"/(DOUBLE(:e."SPEED_MPH")*0.44704); }
ELSE { END TRAVERSE; }
});
o_vertices = SELECT :v."osmid", :v."CALCULATED_COST" FOREACH v IN Vertices(:g_spoa);
END;

As an example, what to do with the SPOA method, you can calculate, where you can go in 300 seconds from your starting point Canary Wharf with this statement:
CALL "GS_SPOA" (1433737988, 300, ?);

To visualize the data, let’s wrap it in a function and do some postprocessing to our graph results. With resultType
= POINTS
the function will return the raw data, i.e. the individual street intersections that are reachable given a certain maximum of travel time. With resultType
= CONVEXHULL
the function will calculate a convex hull of the points and return a single shape. Finally, with resultType
= HEXAGON
a spatial clustering is applied, and travel times are averaged for each cluster cell.
CREATE OR REPLACE FUNCTION "F_SPOA_VERTICES"(
IN i_startVertex BIGINT,  the key of the start vertex
IN i_max DOUBLE,  the maximum distance/cost
IN i_resultType VARCHAR(20)  indicates if the result should be POINTS, CONVEXHULL, or HEXAGON
)
RETURNS TABLE("ID" BIGINT, "SHAPE" ST_GEOMETRY(32630), "CALCULATED_COST" DOUBLE)
LANGUAGE SQLSCRIPT READS SQL DATA AS
BEGIN
CALL "GS_SPOA"(:i_startVertex, :i_max, o_path_vertices);
IF (:i_resultType = 'POINTS') THEN
RETURN SELECT pv."osmid" AS "ID", lv."SHAPE", pv."CALCULATED_COST"
FROM :o_path_vertices AS pv
LEFT JOIN "LONDON_VERTICES" lv ON pv."osmid" = lv."osmid";
ELSEIF (:i_resultType = 'CONVEXHULL') THEN
RETURN SELECT i_startVertex AS "ID", ST_CONVEXHULLAGGR("SHAPE") AS "SHAPE", :i_max AS "CALCULATED_COST" FROM (
SELECT pv."osmid", lv."SHAPE", pv."CALCULATED_COST"
FROM :o_path_vertices AS pv
LEFT JOIN "LONDON_VERTICES" lv ON pv."osmid" = lv."osmid");
ELSEIF (:i_resultType = 'HEXAGON') THEN
RETURN SELECT ST_CLUSTERID() AS "ID", ST_CLUSTERCELL() AS "SHAPE", CAST(AVG("CALCULATED_COST") AS DOUBLE) AS "CALCULATED_COST" FROM (
SELECT pv."osmid", lv."SHAPE", pv."CALCULATED_COST"
FROM :o_path_vertices AS pv
LEFT JOIN "LONDON_VERTICES" lv ON pv."osmid" = lv."osmid")
GROUP CLUSTER BY "SHAPE" USING HEXAGON X CELLS 50;
END IF;
END;

You can use these three statements to explore from your starting point Canary Wharf:
SELECT * FROM "F_SPOA_VERTICES"(1433737988, 60, 'POINTS') ORDER BY "CALCULATED_COST" DESC;
SELECT * FROM "F_SPOA_VERTICES"(1433737988, 60, 'CONVEXHULL') ORDER BY "CALCULATED_COST" DESC;
SELECT * FROM "F_SPOA_VERTICES"(1433737988, 240, 'HEXAGON') ORDER BY "CALCULATED_COST" DESC;

Using a GIS application like Esri ArcGIS Pro
(*) or QGIS
(*), the HEXAGON result can be colorcoded by the average CALCULATED_COST
, resulting in a map visualization like below. Areas with the same color can be reached with the same drive time  so called isochrones
.

Let’s say you next want to do business with cyclists in London. Your goal is to open a new bike repair shop. To find the right location for your shop, you could look at the existing repair stations and their reach. Maybe you can find white spots on the map where there is not a lot of competition.
So let’s calculate 3 min drive time areas around all the bike repair shops in London. You’ll use the MAP_MERGE
operation to digest multiple POI’s:
CREATE OR REPLACE FUNCTION "F_SPOA_VERTICES_MULTI" (IN i_filter VARCHAR(5000), IN i_max DOUBLE, IN i_resultType VARCHAR(20))
RETURNS TABLE("ID" BIGINT, "SHAPE" ST_GEOMETRY(32630), "CALCULATED_COST" DOUBLE)
LANGUAGE SQLSCRIPT READS SQL DATA AS
BEGIN
startPOIs = APPLY_FILTER("LONDON_POI", :i_filter);
res = MAP_MERGE(:startPOIs, "F_SPOA_VERTICES"(:startPOIs."VERTEX_OSMID", :i_max, :i_resultType));
RETURN SELECT * FROM :res;
END;
SELECT * FROM "F_SPOA_VERTICES_MULTI"(' "amenity" = ''bicycle_repair_station'' ', 180, 'CONVEXHULL');
The result is a set of CONVEXHULL
polygons which indicate “good repair shop coverage”.