Train route tracking system

Question

3.00/5 (1 vote)

See more:

Hello,

I am hoping to build an train finding application when user pick source and destination will show a train to take. I am planning to use .net (WEB API) & SQL Server for the project. Currently i have designed the below Table. But I can't figure out an algorithm to find routes. Stations are per-defied in a master table and below mentioned table is the table use to store the route. Sequence is used to determine within a route in a selected node what is the next stop.

1. Every station has up and down trains [A - B & B-A]
2. Some times user may have to pick two or more trains to complete his journey.

What I have tried:

DECLARE @TrainTimeTable TABLE( 
    ID              INT,
    RoutID          INT, -- Route id (ABC to DEF)
    Sequance        INT, -- determine the next station of the same route
    BeforeStation   INT, - A
    CurrentStation  INT, - B
    [Arr.Time]      TIME, 
    [Dep.Time]      TIME
)

Posted 5-Oct-17 19:21pm

Dragon_Coder

Updated 7-Oct-17 2:41am

v2

Add a Solution

Comments

Mehdi Gholam 6-Oct-17 2:28am

Look into the traveling salesman problem, branch and bound algorithms ...

Dragon_Coder 7-Oct-17 3:04am

Will travelling salesman work? I don't have distances of the stations, also some times traveler have to take two or more trains to complete his journey

Mehdi Gholam 7-Oct-17 3:21am

It's not a simple problem.

Graeme_Grant 7-Oct-17 3:58am

You don't need distance, you have times...

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Patrice T · Answer 1 · 2017-10-07T02:41:00

Quote:
Look into the traveling salesman problem, branch and bound algorithms ...

Sorry to disagree, it is not a TSP.
Travelling salesman problem - Wikipedia[^]

It is more like the shortest path problem with a twist.
Dijkstra's algorithm - Wikipedia[^]

There is no way you can do this in SQL. You have to load the data from the server and apply algorithm.
You have to define what is the shortest path, probably something like the fastest way to go from point a to point B for a given starting time. That is the "cost function"that you need to define.