• 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:

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.

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

CodePudding user response：

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

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