How to calculate a route / find waypoints between start and destination

Hi everyone!

I've been thinking a lot but couldn't find the best way (if it's possible) to accomplish it. Maybe someone can point me in the right direction.

Ok, the thing is that I'm trying to find a route for a train but there are so many possibilities that I can't find the way to do it.

Imagine we have a list of two columns:

TRAIN LINE	WAYPOINT (STATION)
3	A
3	B
3	C
1	D
1	E
4	B
4	F
4	G
5	H
5	J
5	F
5	Z

If we want to go from A to Z, the system should know that:

• "A" starts in line 3
• "Z" destination is in line 5
• "B" is the connection between line 3 (the beginning of the route) and line 4, where "F" is the connection between line 4 (the intersect) and line 5 (destination).

So, does anybody have any ideas on how to find "B" and "F" as the waypoints needed to make the route, passing throw the diffent lines to reach the destination?

Thanks!

LOL:bug:

Hahaha yeah, it's not an easy thing ��

How much work do you want to put into this project?

Define all possible routes by mapping them; Distances, time to travel per section including any stoppages at transition points, HP required to move various trains etc
Define the rules you used to decide the routes; Shortest, most economical, gradient conditions, weather conditions, traffic requirements etc
Define the rules to run the routes nominated; What, How and Why
Define the rules to use if something goes wrong; What, How and Why
Define if more than 1 train uses the route/s;
Define if a route resides within a longer route.....

Can you see why SamT simply laughed at your request? All of the above took only a couple of minutes to think about. Any serious consideration could take months. before you know enough to start to build any software to define what goes where to get from start to finish through a waypoint.

Thanks for your reply Aussiebear.

I don't want to make a "Google Maps" nor something to serious hehe. I just want to find the intersections points. I don't care about any of the things you said. Of course, it would be important for a serious project, as you said, but this is not the case, I just want to know if it's possible to find the connections points, no matter anything else.

Do you know any approaches thinking only in the information given in the first post? I mean, as an abstract way of getting an intersection (or as much as possible in a logical way) between two points in a list.

If there were only one intersection, I would just have to look for the waypoint that matches the start and the destination points, but there might be (as in the example of my first post), several "jumps" to reach the destination (and this is the theorical problem that I don't know how to solve :think:).

Any ideas?? :ipray:

If that's all you need, it's easy...
Place Waypoint labels across the columns, place Routes down the rows. Place an X at each intersection of Route and waypoint.
Then just look down the columns for Xs between Start and End points.

Thanks for your reply SamT, however I'm afraid I don't understand it :doh:(probably is just my english, sorry for that).
Could you show me with a little example (a table or sth like the example so I can see what you mean, please? : pray2:

Create a simple matrix

Excuse me Aussiebear, but I don't understand. Could you explain it a little bit more please?
Thanks!

A Matrix is simply a table. At this stage its only you, who knows what the routes are for each line ( as in which is a start destination and which is a through point and which is a finish destination).

the algorithm is called Dijkstra (shortest path).
as said, the List you showed is Not Complete.
you need to show which Waypoint connects to another Waypoint (from pt.A -> pt.B)
you need the distance(or time) from Point X to Point Y.

there is similar discussion here:
Route Planning - shortest path | Access World Forums (access-programmers.co.uk)

the Original file is in here:
GitHub - stahamtan/shortest-path-vba: Dijkstra's shortest path Algorithm - written in VBA (OOP)

First follow the second line in my post.

Then the rules are... You can move across a Row/Route from X to X and you can move down a column from X to X.

You cannot move diagonally. A diagonal move means you must take surface transportation from Station to Station.

Trace the shortest path from Start to End. The Connections of interest are where you change directions from across to down and down to across.

Aussiebear, I know what a matrix is. What I don't know is how to solve my question with a matrix (I have already add one in my example).
Btw, it is not only me who knows the route, you too because I post an example :yes.

Arnelgp, thanks but if I need the distance between points is not what I'm looking for. I just want to know how to get the connection waypoints to reach the destination.

SamT, thanks but, "Place Waypoint labels across the columns, place Routes down the rows." Isn't that what I did with the table of my first post?

__A B C D E F G H I J K L M N O P
1 X __X __X __X
2 X X _ X__ X
3 X X ____X
4
5

A & E connect routes 1 & 3, etc

Thanks for your support SamT :)

That's close, but not exactly what I'm looking for (I think). Let me show you, perhaps you can suggest me an approach to the final solution.

Taking my first example and printing as you said, it would be:

A	B	C	D	E	F	G	H	J	Z
			1	1
3	3	3
	4				4	4
					5		5	5	5

So, 4 is the only one in B and F that matched with either the origen or the destination, so that would be the solution for one line of intersection.

However, how would you know the correct waypoints if there were another line involved? For example, something like this:

A	B	C	D	E	F	G	H	J	Z
3	3	3
			1	1
	4				4	4
			6		6			6
2		2		2
							5	5	5

The correct answer in this case should be: start A in 3 -> then go to 4 in F which turns to 6 and finally J goes to Z in line 5, so waypoints B, F, and J. Waypoint C or A would be mistakes because no further waypoints in 2 reaches destination.

Perhaps looping and taking out those waypoints that don't reach destination (broken route)??

What do you think?

Quote:

---

Originally Posted by victorjagu

Let me show you, perhaps you can suggest me an approach to the final solution.

However, how would you know the correct waypoints if there were another line involved? For example, something like this:

A	B	C	D	E	F	G	H	J	Z
3	3	3
			1	1
	4				4	4
			6		6			6
2		2		2
							5	5	5

The correct answer in this case should be: start A in 3 -> then go to 4 in F which turns to 6 and finally J goes to Z in line 5, so waypoints B, F, and J. Waypoint C or A would be mistakes because no further waypoints in 2 reaches destination.

---

How could anybody deduce that? A simple matrix has predefined values for both Rows and Columns. You provided Column Values only. Does the cell values represent Train numbers or Line Numbers?
In the attached workbook there are two examples of a table layouts (Lines/Waypoint, resulting in Train values and Trains/Waypoints resulting in Line values).

Quote:

---

Perhaps looping and taking out those waypoints that don't reach destination (broken route)??

---

How about you construct a table layout showing which Waypoints lie in which Lines, and show us that bit before this breaks down completely.

Sorry, my fault, I didn't explain myself properly.
Those numbers are actually de line itself, and the letters are the waypoints.
We are always talking about one single train, so the question or the aim is to know which waypoints are going to reach that train having in mind that it starts in point A and finishes in point Z (that's to say, build the path of the train).
The table layout you are referring to is the last table, the one you reply with quote.
Do you think that this cannot be done with more data?

Quote:

---

Originally Posted by victorjagu

Do you think that this cannot be done with more data?

---

Victorjagu, you are seriously bordering on being termed a "incompetent". Your input so far has been deficient, yet you ask others to define a solution based on poorly presented data.... hence a slow response. You talk in riddles of rules you only understand. You present tables that are basically worthless, yet you expect a response. Here's my response to you. You post any more stupidity and I will close this thread.

Aussiebear, do you feel better or powerful with your behavior? I've been polite and respectful from the very beginning, so I strongly believe that your current answer is absolutely rude, disrespectful and out of place.
I've told that English is not my main language, so perhaps I'm not explaining myself properly sometimes but that doesn't mean that I talk in "rules I only understand". You should be more comprehensive.
I don't know what extra input I have to add to make it ok for you. Since my first post, I've told that I'm looking for "a train" (not several) and explained exactly (or I tried to, at least) what I wanted to get with examples and conclusions, doing my best to explain my problem and asking for help on how to start (is not event a VBA problem, is the previous step). I was, in other words, asking someone cleverer than me (or more experienced) if there is a way to solve a problem with the data I have. Nothing more. If this forum is not for that purpose, ok, just tell me, but your several answers..., I cannot understand it.
I don't know what is your problem with me, if you are having some bad days or you simply hate me because you feel better that way.
I was looking for what SamT did, I little bit of help (other points of view) on how to start because I was stuck in a personal project and I couldn't talk to anyone about this. I thought this is the main goal of forums in general, asking questions, exchanging information and approaches...
So, summing up, I just wanted to say thank you very much to SamT for the idea; and to you, Aussiebear, if you don't know how to help me or don't care and don't want to admit it, ok, feel free to close the threat as you got the power to do it. Again, I truly can't understand your several reactions to me.
If you are having troubles in your life somehow, I'm really sorry for that and I hope everything turns ok (everyone has bad days). However, if you are usually like this, telling people you don't know that are stupid and incompetent just for asking for help, then I have nothing to say, but it's a pity people like that.

Lets get back to the real points here.

1. When you create a table, you need to give your data a series of values across the row and down the column, not just one value and expect to see an outcome. It's up to you to decide whether you use trains, Lines, Waypoints etc. As I provided in the workbook, you will see two examples where I used the the data you provided in post #15, both of which use values across the row and down the column. Your examples as you provided doesn't match your explanations.
For example in Post #1 you stated

Quote:

---

If we want to go from A to Z, the system should know that:

• "A" starts in line 3
• "Z" destination is in line 5
• "B" is the connection between line 3 (the beginning of the route) and line 4, where "F" is the connection between line 4 (the intersect) and line 5 (destination).

So, does anybody have any ideas on how to find "B" and "F" as the waypoints needed to make the route, passing throw the diffent lines to reach the destination?

---

AS both SamT and I have tried to point out to you, create a table using waypoints across the top and trains down the column. Then at your leisure put an X in the cells to represent which waypoints the train Starts at, passes through and Finishes at. This is something you can and should do.


2. Your examples in Post #15.

Quote:

---

Taking my first example and printing as you said, it would be:

A	B	C	D	E	F	G	H	J	Z
			1	1
3	3	3
	4				4	4
					5		5	5	5

So, 4 is the only one in B and F that matched with either the origen or the destination, so that would be the solution for one line of intersection.

---

How can anyone could determine your "solution" from this example is beyond us given the lack of detail provided. Why don't you simply create a table with waypoints across the top and in the line underneath place X's under A, B, F, & Z?

Quote:

---

However, how would you know the correct waypoints if there were another line involved? For example, something like this:

A	B	C	D	E	F	G	H	J	Z
3	3	3
			1	1
	4				4	4
			6		6			6
2		2		2
							5	5	5

The correct answer in this case should be: start A in 3 -> then go to 4 in F which turns to 6 and finally J goes to Z in line 5, so waypoints B, F, and J. Waypoint C or A would be mistakes because no further waypoints in 2 reaches destination.

Perhaps looping and taking out those waypoints that don't reach destination (broken route)??

---

Your explanation here is absolute rubbish. it's not logical in any sense, what so ever. I strongly suspect that your confusion comes about because you are mixing values (Alphabetical and numerical) in the cell values. Initially you say that your train starts at "A", passes through waypoints F & J, and finally arrives at Z, however you contradict that statement by later saying "so waypoints B, F, & J". Where did "B" come into this? You went on to say "waypoints C or A would be mistakes...", but clearly "A" is your start point, therefore it cannot be a mistake. You also stated "...no further waypoints in 2 reaches destination". How were we supposed to come to that conclusion from the data in the table/s or the explanation?

So logically you have 1 train and 10 waypoints. The train starts at "A" and finishes at "Z", and depending on which post you've made, either passes through waypoints B, F & J, or waypoints F & J. Everything else you talk about is fluff which is confusing everyone. I strongly suggest that if you wish to continue this thread, then you need to have a good strong look at your approach.

Since the Op appears stuck with this issue, lets see if we can decipher the scrambled data and make some sense of the task at hand.

Firstly when SamT and myself suggested that you create a Matrix this is what we had in mind

	A	B	C	D	E	F	G	H	J	Z
Line 1
Line 2
Line 3
Line 4
Line 5
Line 6

A table Matrix allows a User to show data relationships between two or more defined values. In this case we are showing the potential relationship between Line Numbers (Vertically) and Waypoints (Horizontally). You will note, they are also listed sequentially.

Next we needed to convert your scrambled data into something more meaningful. Rather than confuse Users with numbers I have used colours to show the related data in a more logical detail.

	A	B	C	D	E	F	G	H	J	Z
Line 1
Line 2
Line 3
Line 4
Line 5
Line 6

From the above matrix we can show two sets of data, namely;
1. Reading Horizontally: Waypoints that lie upon each Line, and
2. Reading Vertically: Waypoints that show common Lines.


The task at hand is to show a "Train" starting at Waypoint A on Line 3 (Cell A3) and Finishing at Waypoint Z. What Waypoints does it need to pass through?

Firstly we need to set up a set of Rules to be used in conjunction with the Matrix.
1. For a "Train" to move forward, it cannot regain any cell within the Table that it has previously occupied.
2. Any Movement between the cells can only be Horizontally or Vertically. No Diagonal moves allowed.
3. You may jump from coloured cell to coloured cell providing you follow Rule 2.

	A	B	C	D	E	F	G	H	J	Z
Line 1	A1	B1	C1	D1	E1	F1	G1	H1	J1	Z1
Line 2	A2	B2	C2	D2	E2	F2	G2	H2	J1	Z2
Line 3	A3	B3	C3	D3	E3	F3	G3	H3	J3	Z3
Line 4	A4	B4	C4	D4	E4	F4	G4	H4	J4	Z4
Line 5	A5	B5	C5	D5	E5	F5	G5	H5	J5	Z5
Line 6	A6	B6	C6	D6	E6	F6	G6	H6	J6	Z6

There are currently two possible routes under the set of rules.
1. A3, B3, C3, C2, E2, E1, D1, D6, F6, J6, J5, Z5, or
2. A3, B3, B4, F4, F6, J6, J5, Z5.