Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Simple Route Calculator in SQL

Author  Topic 

reidacus
Starting Member

2 Posts

Posted - 2013-01-28 : 17:42:08
Hi All

I have a problem where I have a train network and using SQL I need to determine a route through the network, specifying a start and end station. The query that I write should be able to automatically be able to determine which stations the route needs to traverse to reach the destination.

I have produced a couple of scaled down tables to start with but I am unsure how to go about creating such a query to return the information that I require. I am familiar with the mathematical works of Dijkstra's algorithm but from what I've researched so far this can be a bit of a nightmare to implement. Is there a simpler way of devising a different solution for this, such as a series of select statements perhaps? Ideally I am looking for an output which lists the hops within the network and the cumulative distance between them to the destination. Apologies if I have lost anyone, I am happy to clarify anything above.

The tables I have are outlined below.

Place Table

PlaceID, ID : (A, 1; B, 2; C, 3; D, 4; E, 5)

Route Links Table

Start, End, Distance : (A, B, 5; A, C, 27; A, D, 20; B, C, 10; B, E, 21; C, E, 5; D, A, 5; D, E, 6)

Any help at all will be much appreciated!

sqlbay
Starting Member

12 Posts

Posted - 2013-01-29 : 00:58:03
Would you please give an example for output?

SQL Server Professional http://sqlbay.blogspot.in
Go to Top of Page

reidacus
Starting Member

2 Posts

Posted - 2013-01-29 : 03:01:18
quote:
Originally posted by sqlbay

Would you please give an example for output?


The output would ideally be a table that gave me the from node, the next hop node and the cumulative distance from start to the present node.

So if i wanted to calculate a route from A to E using the values above, the output would be similar to below.

Eg

[From, To, Cumulative_Distance]
[A, B, 5]
[B, C, 15]
[C, E, 20]
Go to Top of Page
   

- Advertisement -