I developed a one-day bus transfer algorithm. Recently I want to create a public transit app (sench touc). I want to get people's ideas here first...

Three tables (which are the most simple and do not consider fuzzy queries, single-line lines, and other things ):
1. Site table stop (stop_id, stop_name)
2. Route table line (line_id, line_name)
3. linestops (line_id, stop_id, SEQ) in the route Site table (point-line relationship table). seq indicates the sequence of a site on a line.

Current Analysis Algorithm:
1. Direct Line
First, obtain the IDs of the two sites based on the two site names. The IDS are defined as id1 and Id2.
Then Query
Select line_id from
(Select line_id from linestops where stop_id = id1),
(Select line_id from linestops where stop_id = Id2) B
Where a. line_id = B. line_id
The list of accessible lines is displayed.
2. One transfer
First, obtain the IDs of the two sites based on the two site names. The IDS are defined as id1 and Id2.
Search for the sites that can be reached by direct access to the sites. The intersection of the sites is the transfer site we need.
Select stop_id from
(
Select distinct stop_id from linestops where line_id in
(Select line_id from linestops where stop_id = id1)
),
(
Select distinct stop_id from linestops where line_id in
(Select line_id from linestops where stop_id = id1)
) B
Where a. stop_id = B. stop_id
After you get the transfer station (there may be more than one or zero), the rest is to display the lines on both sides that can reach the transfer station, which can be directly queried in the front.
3. secondary transfer
First, obtain the IDs of the two sites based on the two site names. The IDS are defined as id1 and Id2.
The central idea of the algorithm is that site 1 can set a through all sites that arrive directly, and site 2 can set B through all sites that arrive directly, and there is a direct line between A and B.
Step by step:
Site 1 is a collection of all sites that can be reached through direct access.:
Select distinct stop_id from linestops where line_id in
(Select line_id from linestops where stop_id = id1)
Site 2 is a collection of all sites that can be reached through direct access B:
Select distinct stop_id from linestops where line_id in
(Select line_id from linestops where stop_id = Id2)
The direct query is
Select line_id from
(Select line_id from linestops where stop_id = id1) c,
(Select line_id from linestops where stop_id = Id2) d
Where C. line_id = D. line_id
We replace = id1 and = Id2 with in (Select...) A and in (Select...) B
In this case, our query is
Select line_id from
(Select distinct line_id from linestops where stop_id in [a]) c,
(Select distinct line_id from linestops where stop_id in [B]) d
Where C. line_id = D. line_id
Where [a] is
(Select distinct stop_id from linestops where line_id in
(Select line_id from linestops where stop_id = id1 ))
Where [B] is
(Select distinct stop_id from linestops where line_id in
(Select line_id from linestops where stop_id = Id2 ))
In this way, we find the intermediate transfer line (there may be multiple or zero lines), and name each of the listed assumptions as X-ray, the next step is to find the direct line from site 1 to any site X, and the direct line from site 2 to any site X.
Similar to the preceding algorithm, we can search for sites that are located at the intersection of line x in all the sites that can be reached at Site 1, and then find the lines at the two sites.
Select stop_id from
(Select distinct stop_id from linestops where line_id in
(Select line_id from linestops where stop_id = id1),
(Select stop_id from linestops where line_id = x) B
Where a. stop_id = B. stop_id
After finding the site, we can find the line based on the resolved direct query.
Site 2 is similar.
The above algorithms have one advantage: SQL completes the search, so ASP code only needs a few loops.
However, the disadvantage is that it is slow. After all, it may involve hundreds of SQL queries. In addition, we only use the simplest SQL method to calculate all the schemes that can be transferred, without involving the optimal/shortest algorithms. For the shortest path, special structures and algorithms are required.

In addition:

Determine the start bus Site A and destination bus Site B to be selected based on the start and end points entered by the traveler. Search for the database and check whether there are the same vehicle routes between Site A and site B. If there are one or several direct routes, we recommend that you provide the Walker by comparing and selecting the shortest bus routes. If no, there is no public site C between Site A and site B. You can transfer from site C to site B. There are two cases: (1) If there is one transfer. Whether the same bus passes through and is saved to collection X Between Site A and Site C. Similarly, whether the same bus passes through and saves to collection y between Site B and Site C. After comparing these two sets, you can get the bus lines from site A that pass through site C to Site B. Compare these lines and select the shortest distance to recommend the practitioners. (2) If there is no public site C, there will be two transfers. Store all sites on each bus line of Site A to the Collection O. Similarly, all sites on each bus line of Site B are stored in the collection p. Compare the two sets, first take a bus from Station A to a site D, and calculate whether there is a public site e between site D and site B, if yes, site D and E are transfer sites. There may be a variety of such solutions, and the recommendation practitioners with the shortest distance can be compared. If there is no public site e, it means that after two transfers, the site a cannot arrive at site B, and the search computation is stopped.
Specific algorithm of the optimal route for public transportation:
1) Enter start site A and destination site B;
2) Search for the system database, and save the bus line at the start site A as X (I) (I =, 3 ..., M, M is a positive integer), and the bus line passing through the destination site B is Y (j) (j =, 3 ,... N,. N is a positive integer );
3) determine whether X (I) = y (j) exists, and store matching conditions into Z. If Z = 1, the bus line X (I) is the direct optimal line from site A to Site B, and the result is output and the computation is completed. Z≥1, calculate the distance between each line in Z, select a line with the shortest distance, output the result, and end the operation;
4) search the system database. The sites contained in bus route X (I) are O (I, u) (u = 1, 2, 3 ..., G, G is a positive integer. The sites contained in bus line y (j) are P (J, V) (V = 1, 2, 3 ..., H, H is a positive integer );
5) determine whether there is O (I, u) = P (J, V) and store the Qualified Data to W. If W = 1, site O (I, u) is a transfer site from site A to Site B, Bus Line X (I ), Y (j) is the optimal route for one transfer, outputs the result and ends the operation. If W is greater than or equal to 1, calculate the distance of each transfer route, select a line with the shortest distance, output the result, and end the operation;
6) Search for the system database, and save the bus lines of the station o (I, u) as R (k) (k =, 3 ..., P, P is a positive integer), and the sites contained in bus line R (k) are g (K, t) (t = 1, 2, 3 ..., Q, Q is a positive integer );
7) determine whether g (K, t) = P (J, v) exists and store s that meet the conditions. If S = 1, the site G (K, T) is the second transfer station from site A to Site B, the bus line X (I ), R (K), y (j) is the optimal route for the second transfer, output the result and end the operation. If S is greater than or equal to 1, calculate the second route distance for each transfer, select a line with the shortest distance, output the result, and end the operation;
8) If no suitable bus route is found in the above steps, the calculation is completed by outputting "no optimal bus route with no transfer times greater than twice.
This article discusses the Optimal Route Algorithm for public transportation, which is based on distance. After the transfer plan is obtained, we can further consider the time factors to find a more competitive transfer plan, which requires further discussion and research.

I. The algorithm for Public Transit transfer is divided into two steps:

1. Construct and solve the transfer matrix to obtain the Transit transfer plan (that is, the minimum transfer count from the start point to the end point, and the transfer site ).
There are three implementation modes:
① Obtain the T matrix of all nodes. Set the two nodes directly to 1, and the two nodes cannot be connected or need to be transferred to 0. For details, see Public Transport System Optimal Route Algorithm.
② Obtain the T matrix of all lines. The intersection of the two lines is set to 1, and the intersection of the two lines is set to 0; see the study of bus transfer algorithm based on the adjacent matrix (note that this algorithm is an idealized algorithm)
③ Narrow the scope through the above 2nd steps, and then use the first step to obtain accurate results

2. Based on the minimum number of transfer times, narrow the solution scope and find the shortest path between the start site and the target site to get the optimal path.
The shortest path can be obtained in the following forms:
①. The simplest way to achieve this is: if the least transfer times and transfer sites in the previous step can be solved, you only need to query the transfer times at least, the line with the least number of sites in all possible paths is required (premise, assuming that the distance between all sites is roughly equal );
② If the above 1 finds the minimum number of transfer times, then we need to use the algorithm to find the shortest path. The common method is to improve the Dijkstra algorithm, that is, a minimum transfer algorithm is added to the diskstra algorithm;
③ In game design programs, the shortest path is often involved. The most common algorithm is the * algorithm, see Design and Implementation of Shortest Path search for game maps and a Algorithm
④ K (<= 3) gradient short path search algorithm, which is widely used abroad. Due to its troubles, there are not many researches in China, see K (≤ 3) A Study of gradient short path search algorithms, a new kth shortest path search algorithm, and a study on the ticket clearance model of Urban Rail Transit (Master's thesis)
⑤ Ant algorithm, see a Dijkstra-like Ant Algorithm
Among them, steps ② and ④ all depend on the minimum number of transfers to reduce complexity.

2. If the above algorithms can be well implemented, a good data structure needs to be established.

1. Data Storage Format
I have asked two people who have actually done this. One person uses Arrays for storage, and the other uses a combination of linked lists and arrays (that is, long-chain tables use arrays, the other uses the linked list method). In fact, the two storage formats are mainly because the two algorithms have different focuses, and the previous one focuses on least transfer times, the latter focuses on the implementation of the shortest path.
2. Data Structure storage, for more information, see Computer Algorithm Research on urban public transit network travel path selection, GIS-based standard public transit Basic Information System, and GIS-based public transit passenger travel path selection model. considering public transit transfer, similar sites are usually put in place (sites with the same name but different geographic locations, different site names, but very close geographic locations) as a node in the public transit network topology.
3. Selection of arc weight values between two nodes (required to calculate the Shortest Path): Generally, the distance between two nodes is used instead, however, some people use the speed or driving time between the two nodes as the weight value. It is a loop vehicle parameter provided by Shenyang public transport network. Based on this parameter and the distance between the two sites, we can find the driving time (normal) between the sites ). Of course, the more comprehensive the factors are, the more humane the process is, But it increases a lot of difficulty, for example: consider "waiting for time, site distance, line heat (to the Commercial District) ", time (peak hours, etc.)" and other factors, these factors are difficult to quantify. Therefore, generally, the simplest method is to use distance as the weight of the arc block between two nodes.