How to Implement Shortest Path search in SQL Server

Start

This is a problem last year. I found this problem only today when I am sorting out emails. It is very interesting and special.

In the RelationGraph table, there are three fields (ID, Node, RelatedNode). The Node and RelatedNode fields describe the connection relationship between the two nodes, find the shortest path from node "p" to node "j" (that is, the minimum number of passing nodes ).

Figure 1.

Resolution:

To better describe the relationship between the Node field and the RelatedNode field in the RelationGraph table. Here I use a specific graph to describe it,
2.

Figure 2.

In Figure 2, we can clearly see how each node is directly connected, or several possible paths from node "p" to node "j.

We can see from the above 2nd possible paths, with the least number of nodes passing through.

To solve the problem, I have referred to two methods,

The 1st method is,

Refer to the single-source shortest path algorithm:Dijkstra (Dijela)The algorithm is mainly characterized by layer-by-layer expansion from the starting point until the end point.

Figure 3.

The 2nd method is,

For the improvement of the 1st method, the multi-source point method is adopted. Here, the node "p" and node "j" are centered to expand to the outer layer until the outer tangent points of the two circles, 4 .:

Figure 4.

Implementation:

Next, I will describe how to implement it in SQL Server. Of course, I use the 2nd methods mentioned above, which are expanded layer by layer outside the center of the starting point image with "P" and "J.

The create & Insert data script for the table RelactionGraph is provided here:
Copy codeThe Code is as follows:
Use TestDB

Go

If object_id ('relactiongraph') Is not null drop table RelactionGraph

Create table RelactionGraph (ID int identity, Item nvarchar (50), RelactionItem nvarchar (20), constraint PK_RelactionGraph primary key (ID ))

Go

Create nonclustered index IX_RelactionGraph_Item on RelactionGraph (Item) include (RelactionItem)

Create nonclustered index IX_RelactionGraph_RelactionItem on RelactionGraph (RelactionItem) include (Item)

Go

Insert into RelactionGraph (Item, RelactionItem) values

('A', 'B'), ('A', 'C'), ('A', 'D'), ('A', 'E '),

('B', 'F'), ('B', 'G'), ('B', 'H '),

('C', 'I'), ('C', 'J '),

('F', 'k'), ('F', 'L '),

('K', 'O'), ('k', 'P '),

('O', 'I'), ('O', 'L ')

Go

Write a stored procedure up_GetPath
Copy codeThe Code is as follows:
Use TestDB
Go
-- Procedure:
If object_id ('up _ getpath') Is not null
Drop proc up_GetPath
Go
Create proc up_GetPath
(
@ Node nvarchar (50 ),
@ RelatedNode nvarchar (50)
)
As
Set nocount on

Declare
@ Level smallint = 1, -- current search depth
@ MaxLevel smallint = 100, -- maximum searchable depth
@ Node_WhileFlag bit = 1, -- Mark whether or not to use @ Node as the center for search
@ RelatedNode_WhileFlag bit = 1 -- Mark whether or not to search cyclically when @ RelatedNode is used as the center

-- If a direct relationship exists between the two nodes, the system returns the result directly.
If Exists (select 1 from RelationGraph where (Node = @ Node And RelatedNode = @ RelatedNode) or (Node = @ RelatedNode And RelatedNode = @ Node) or @ Node = @ RelatedNode
Begin
Select convert (nvarchar (2000), @ Node + '-->' + @ RelatedNode) As RelationGraphPath, convert (smallint, 0) As StopCount
Return
End

--

If object_id ('tempdb .. # 1') Is not null Drop Table #1 -- temporary Table #1, stores data of each Node that Is extended to the center by @ Node.
If object_id ('tempdb .. # 2') Is not null Drop Table #2 -- temporary Table #2, stores data of each node that Is extended to the center using @ RelatedNode

Create table #1 (
Node nvarchar (50), -- relative source point
RelatedNode nvarchar (50), -- Relative Target
Level smallint -- depth
)

Create table #2 (Node nvarchar (50), RelatedNode nvarchar (50), Level smallint)

Insert into #1 (Node, RelatedNode, Level)
Select Node, RelatedNode, @ level from RelationGraph a where a. Node = @ Node union -- Forward: Query with @ Node as the source
Select RelatedNode, Node, @ level from RelationGraph a where a. RelatedNode = @ Node -- reverse: Query with @ Node as the target
Set @ Node_WhileFlag = sign (@ rowcount)

Insert into #2 (Node, RelatedNode, Level)
Select Node, RelatedNode, @ level from RelationGraph a where a. Node = @ RelatedNode union -- Forward: Use @ RelatedNode as the Source Query
Select RelatedNode, Node, @ level from RelationGraph a where a. RelatedNode = @ RelatedNode -- reverse: Query with @ RelatedNode as the target
Set @ RelatedNode_WhileFlag = sign (@ rowcount)

-- If the data of @ Node or @ RelatedNode cannot be found in the RelationGraph table, skip the subsequent While process.
If not exists (select 1 from #1) or not exists (select 1 from #2)
Begin
Goto While_Out
End

 
While not exists (select 1 from #1 a inner join #2 B on B. RelatedNode = a. RelatedNode) -- determine whether a cut point exists.
And (@ Node_WhileFlag | @ RelatedNode_WhileFlag)> 0 -- determines whether a search can be performed.
And @ level <@ MaxLevel -- Control depth
Begin
If @ Node_WhileFlag> 0
Begin
Insert into #1 (Node, RelatedNode, Level)
-- Forward
Select a. Node, a. RelatedNode, @ level + 1
From RelationGraph
Where exists (select 1 from #1 where RelatedNode = a. Node And Level = @ level) And
Not exists (select 1 from #1 where Node = a. Node)
Union
-- Reverse
Select a. RelatedNode, a. Node, @ level + 1
From RelationGraph
Where exists (select 1 from #1 where RelatedNode = a. RelatedNode And Level = @ level) And
Not exists (select 1 from #1 where Node = a. RelatedNode)

Set @ Node_WhileFlag = sign (@ rowcount)

End


If @ RelatedNode_WhileFlag> 0
Begin
Insert into #2 (Node, RelatedNode, Level)
-- Forward
Select a. Node, a. RelatedNode, @ level + 1
From RelationGraph
Where exists (select 1 from #2 where RelatedNode = a. Node And Level = @ level) And
Not exists (select 1 from #2 where Node = a. Node)
Union
-- Reverse
Select a. RelatedNode, a. Node, @ level + 1
From RelationGraph
Where exists (select 1 from #2 where RelatedNode = a. RelatedNode And Level = @ level) And
Not exists (select 1 from #2 where Node = a. RelatedNode)
Set @ RelatedNode_WhileFlag = sign (@ rowcount)
End

Select @ level + = 1
End

While_Out:

-- The following is the returned result path.
If object_id ('tempdb .. # Path1 ') Is not null Drop Table # Path1
If object_id ('tempdb .. # Path2 ') Is not null Drop Table # Path2

; With cte_path1
(
Select. node,. relatedNode, Level, convert (nvarchar (2000),. node + '->' +. relatedNode) As RelationGraphPath, Convert (smallint, 1) As PathLevel From #1 a where exists (select 1 from #2 where RelatedNode =. relatedNode)
Union all
Select B. node,. relatedNode, B. level, convert (nvarchar (2000), B. node + '->' +. relationGraphPath) As RelationGraphPath, Convert (smallint,. pathLevel + 1) As PathLevel
From cte_path1
Inner join #1 B on B. RelatedNode = a. Node
And B. Level = a. Level-1
)
Select * Into # Path1 from cte_path1

; With cte_path2
(
Select. node,. relatedNode, Level, convert (nvarchar (2000),. node) As RelationGraphPath, Convert (smallint, 1) As PathLevel From #2 a where exists (select 1 from #1 where RelatedNode =. relatedNode)
Union all
Select B. node,. relatedNode, B. level, convert (nvarchar (2000),. relationGraphPath + '->' + B. node) As RelationGraphPath, Convert (smallint,. pathLevel + 1)
From cte_path2
Inner join #2 B on B. RelatedNode = a. Node
And B. Level = a. Level-1
)
Select * Into # Path2 from cte_path2

; With cte_result
(
Select. relationGraphPath + '->' + B. relationGraphPath As RelationGraphPath,. pathLevel + B. pathLevel-1 As StopCount, rank () over (order by. pathLevel + B. pathLevel) As Result_row
From # Path1
Inner join # Path2 B on B. RelatedNode = a. RelatedNode
And B. Level = 1
Where a. Level = 1
)
Select distinct RelationGraphPath, StopCount From cte_result where Result_row = 1
Go

The preceding stored procedure consists of two parts: how to search and how to construct the returned results. The code in Part 2 searches for the outer layer through the @ Node and @ RelatedNode nodes based on the previous method 2. The nodes returned by each search are saved to the temporary table #1 and #2, then, judge whether a cut point exists in the temporary tables #1 and #2. If yes, the shortest path has been found (with the least number of nodes). Otherwise, the search continues cyclically, until the maximum search depth is reached (@ MaxLevel smallint = 100) or the cut point is found. If no vertex is found at Layer 3, the search will be abandoned. Here, the maximum searchable depth @ MaxLevel is used to control the performance poor due to a large amount of data, because the data volume here is inversely proportional to the search performance. In the code, we also talk about a forward and reverse search, which is mainly used for external search compared with Node and RelatedNode.

The execution of the stored procedure is as follows:
Copy codeThe Code is as follows:
Use TestDB

Go

Exec dbo. up_GetPath

@ Node = 'P ',

@ RelatedNode = 'J'

Go

You can assign different values to @ Node and @ RelatedNode as needed.

Expansion:

In the previous example, the bus routes can be extended to the city, and two sites are provided to search for the bus routes that pass through the two sites at least. The sites can be extended to the interpersonal relationship searches in the community, if one wants to know another person, how many talents do they need. In addition to directly associating people with friends and relatives, you can also find associations between people and things through associations between people and things. For example, several writers publish a book, it means that the authors of a book can find their associations with the books published together in the list of authors, which provides a reference for searching the paths recognized by two people. This problem may be very complicated, but it can be extended like this.

Summary:

Here, we only find the path with the least number of nodes in all the paths of two nodes. In actual applications, it may be more complicated than here. In other environments or scenarios, there may be information such as length, time, multiple nodes, and Multiple scopes. In any case, we generally need to refer to some principles and Algorithms for implementation.