SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Simple Route Calculator in SQL
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

reidacus
Starting Member

2 Posts

Posted - 01/28/2013 :  17:42:08  Show Profile  Reply with Quote
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 - 01/29/2013 :  00:58:03  Show Profile  Reply with Quote
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 - 01/29/2013 :  03:01:18  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000