Complex Algorithms Using GraphScript

In the SQL Console paste and execute following code.

SET SCHEMA "SKIING";

--DROP TYPE "TT_NODES" CASCADE;
CREATE TYPE "TT_NODES" AS TABLE ("node_id" INTEGER, "name" NVARCHAR(16));

--DROP PROCEDURE "NHOOD";
CREATE OR REPLACE PROCEDURE "NHOOD"(
	IN startV INTEGER,
	IN minDepth INTEGER,
	IN maxDepth INTEGER,
	OUT res "TT_NODES")
LANGUAGE GRAPH READS SQL DATA AS
BEGIN
  GRAPH g = Graph("SKIING");
  VERTEX v_s = Vertex(:g, :startV);
  MULTISET<VERTEX> ms_n = Neighbors(:g, :v_s, :minDepth, :maxDepth);
  res = SELECT :v."node_id", :v."name" FOREACH v IN :ms_n;
END;

This creates an NHOOD object in Procedures.

Run CALL "NHOOD" with different parameters to check results.

CALL "SKIING"."NHOOD"(10, 1, 1, ?);

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?