How can I implement Dijkstra's Algorithm in T-SQL to find the shortest route through my data-points?

Question

• I have the following table containing n bins in a warehouse and their coordinate points on a map.
• I would like to find the distance between every point in the list.
• I have attempted to use a pivot, cross apply, cross join, etc and ultimately get nowhere close to the intended result (typically get an error) so I will refrain from posting my code here..
• Below is my sample data and my desired output.
• ...and once found, how can I insert the shortest path/route into a new temporary-table?

---

Sample Data:

BinCoord	BinNumb
(27,1)	S
(18,2)	D1
(24,2)	B1
(15,23)	E20

Desired output:

(Distances are placeholders, not actual values). I'm not too bothered about how the BinPath is represented: e.g. a To and From column would also work fine.

Distance	BinPath
3.32	S-D1
5.54	D1-B1
7.62	B1-E20
2.23	D1-E20

---

I would assume this needs to be in some sort of loop or maybe is achievable in a pivot with some dynamic SQL. My apologies for not having a better attempted path.

---

Here is what I tried:

It takes my sample data and apparently does nothing because I don't understand what I need to do. My aim was to create a matrix and then do some sort of loop or cross apply to find distances between all points in the matrix.

SELECT
    D1.[BinCoord],
    D1.[BinNum]
FROM
    ##Djik3 D1
    CROSS JOIN ##Djik3 D2
WHERE
    D1.BinCoord = D2.BinCoord
    AND
    D1.BinNum = D2.BinNum

Answer 1

The process is the same in later versions, expect that we can use built in functionality.

• For just simple 2D distance this is probably going to have a similar level of performance

We can build your table using a CTE to first parse the BinCoords into numeric values, if you have a massive amount of records then you might compute this into its own temp table first (or build the CTE logic into the query that build the #Djik3 temp table):

-- Format the data first!
WITH BinCoords as (
    SELECT 
          BinCoord
        , BinNumb
        , X = CAST(SUBSTRING(BinCoord, 2, Fx1.Comma - 2) as real)
        , Y = CAST(SUBSTRING(BinCoord, Fx1.Comma + 1, LEN(BinCoord) - Fx1.Comma - 1) as real)
    FROM #Djik3 d
    CROSS APPLY (SELECT CHARINDEX(',', BinCoord) as Comma) as Fx1
)
SELECT 
      [From] = src.BinNumb
    , [To] = tgt.BinNumb
    , [BinPath] = CONCAT(src.BinNumb,'-',tgt.BinNumb)
    , [Distance] = SQRT(((src.X - tgt.X)*(src.X - tgt.X))+((src.Y - tgt.Y)*(src.Y - tgt.Y)))
FROM BinCoords src
CROSS JOIN BinCoords tgt
WHERE src.BinNumb > tgt.BinNumb

From	To	BinPath	Distance
S	D1	S-D1	9.055385138137417
E20	D1	E20-D1	21.213203435596427
S	B1	S-B1	3.1622776601683795
D1	B1	D1-B1	6
E20	B1	E20-B1	22.847319317591726
S	E20	S-E20	25.059928172283335

Using simple Pythagoras formula to calculate the distance

The tricky thing with a CROSS JOIN is that you will get all the results of A-B and B-A, including B-B and A-A. The simple way to only get the unique path combinations is to use a less than, or greater than comparison on the node identifier.

• I have used A > B to get the larger node first to match your expectation, but A < B would have worked equally well.

See this Fiddle: http://sqlfiddle.com/#!18/74cbba/3

---

UPDATE: Dijkstras Algorithm needs the reverse paths too!

If the purpose of computing the node distances is to use Dijkstras Algorithm to find the shortest route between the nodes, then we need the reverse paths to, so the only path to exclude in the SQL is A-A, for that we change the filter:

FROM BinCoords src
CROSS JOIN BinCoords tgt
WHERE src.BinNumb <> tgt.BinNumb

Fiddle: http://sqlfiddle.com/#!18/74cbba/1

From	To	Path	Distance
D1	S	D1-S	9.05538513813742
B1	S	B1-S	3.16227766016838
E20	S	E20-S	25.0599281722833
S	D1	S-D1	9.05538513813742
B1	D1	B1-D1	6
E20	D1	E20-D1	21.2132034355964
S	B1	S-B1	3.16227766016838
D1	B1	D1-B1	6
E20	B1	E20-B1	22.8473193175917
S	E20	S-E20	25.0599281722833
D1	E20	D1-E20	21.2132034355964
B1	E20	B1-E20	22.8473193175917

---