In the SQL Console paste and execute following code. It creates a procedure to calculate shortest path distances to stations with restaurants.
SET SCHEMA "SKIING";
--DROP TYPE "TT_RESTAURANTS";
CREATE TYPE "TT_RESTAURANTS" AS TABLE ("node_id" INTEGER, "distance" INTEGER, "hops" BIGINT);
--DROP PROCEDURE "NEAREST_RESTAURANT";
CREATE OR REPLACE PROCEDURE "NEAREST_RESTAURANT"(IN startV INT, OUT res "TT_RESTAURANTS")
LANGUAGE GRAPH READS SQL DATA AS
BEGIN
GRAPH g = Graph("SKIING");
VERTEX v_s = Vertex(:g, :startV);
MULTISET<Vertex> rests = v IN Vertices(:g) WHERE :v."restaurant" == N'TRUE';
ALTER g ADD TEMPORARY VERTEX ATTRIBUTE (INT "distance" = 0);
ALTER g ADD TEMPORARY VERTEX ATTRIBUTE (BIGINT "hops" = 0L);
FOREACH rest in :rests {
VERTEX v_rest = Vertex(:g, :rest."node_id");
WeightedPath<INT> p = Shortest_Path(:g, :v_s, :v_rest, (Edge conn) => INTEGER { return :conn."length"; } );
rest."hops" = Length(:p);
rest."distance" = Weight(:p);
}
res = SELECT :v."node_id", :v."distance", :v."hops" FOREACH v IN :rests;
END;
This creates an NEAREST_RESTAURANT
object in Procedures. Right click on the procedure name and pick Generate CALL Statement With UI.
Procedure’s call statement is generated. Provide input value 15
and click Run.
When the graph script is executed, then the result is displayed. It shows a shortest distance in meters to each station with the restaurant plus number of hops to get there.
What is the shortest path distance in meters from station 15
to station 3
?