Architecture Design for Bus Routing with Time

Question

This is to confirm if my design is good enough or get the better ideas to solve the bus routing problem with time. Here is my solution with the primary steps given below:

1. Have one edges table which represents all the edges (the source and target represent vertices (bus stops):

   postgres=# select id, source, target, cost from busedges;
    id | source | target | cost
   ----+--------+--------+------
     1 |      1 |      2 |    1
     2 |      2 |      3 |    1
     3 |      3 |      4 |    1
     4 |      4 |      5 |    1
     5 |      1 |      7 |    1
     6 |      7 |      8 |    1
     7 |      1 |      6 |    1
     8 |      6 |      8 |    1
     9 |      9 |     10 |    1
    10 |     10 |     11 |    1
    11 |     11 |     12 |    1
    12 |     12 |     13 |    1
    13 |      9 |     15 |    1
    14 |     15 |     16 |    1
    15 |      9 |     14 |    1
    16 |     14 |     16 |    1
   

2. Have a table which represents bus details like from time, to time, edge etc.

   NOTE: I have used integer format for "from" and "to" column for faster results as I can do an integer query, but I can replace it with any better format if available.

   postgres=# select id, "busedgeId", "busId", "from", "to" from busedgetimes;
    id | busedgeId | busId | from  |  to
   ----+-----------+-------+-------+-------
    18 |         1 |     1 | 33000 | 33300
    19 |         2 |     1 | 33300 | 33600
    20 |         3 |     2 | 33900 | 34200
    21 |         4 |     2 | 34200 | 34800
    22 |         1 |     3 | 36000 | 36300
    23 |         2 |     3 | 36600 | 37200
    24 |         3 |     4 | 38400 | 38700
    25 |         4 |     4 | 38700 | 39540
   

3. Use dijkstra algorithm to find the nearest path.

4. Get the upcoming buses from the busedgetimes table in the earliest first order for the nearest path detected by dijkstra algorithm. => This leads to a bit complex query though.

Can I do any kind of improvements to this, or are there any better designs?

Links to docs, articles related to this would be really helpful.

Answer 1

This is totally normal and the regular way to do it. See also,

• PgRouting Example