Transfer algorithm "turn"

Transferred from: http://blog.csdn.net/windflow/article/details/2820929

Three tables (most simplistic, without regard to fuzzy queries, one-lane, etc.):
1, Site table Stop (stop_id,stop_name)
2, route form line (Line_id,line_name)
3, Route site table (Point line Relationship table) Linestops (line_id, stop_id, seq) here the SEQ refers to the order of a site in a line.

Now analysis algorithm:
1, Direct Line
First get two site IDs based on two site names, defined here as Id1,id2
and then query
Select line_id from
(select line_id from linestops where stop_id = id1) A
(select line_id from linestops where stop_id = Id2) B
where a.line_id = b.line_id
You get a list of direct lines

2, one-time transfer
first obtain two site's respective ID according to two site name, here is defined as Id1,id2
and then search two sites through the direct way each can reach the site collection, finally their intersection is we need to transfer site.
Select stop_id from
(
select distinct stop_id from Linestops where line_id in
(select line_id from Linestops where stop_id = id1)
) A,
(
Select distinct stop_id from Linestops where line_id on
(select line_id from LINEST OPS where stop_id = id1)
) B
where a.stop_id= b.stop_id
Gets the transfer station (there may be more or 0), the rest is to show the lines that can reach the transfer station, which can be done through the front direct query.

3, two times transfer
First get two site IDs based on two site names, defined here as Id1,id2
The main idea of the algorithm is that site 1 can be reached by direct access to all site collection A, site 2 can be reached by direct access to all the site collection between B,a and B has a direct line.
Step-by-step:
Site 1 can be reached by direct access to all site collection A:
Select distinct stop_id from Linestops where line_id in
(select line_id from linestops where stop_id = id1)
Site 2 can be reached by direct access to all site collection B:
Select distinct stop_id from Linestops where line_id in
(select line_id from linestops where stop_id = Id2)
And 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 changed =ID1 and =id2 into in (select ...). A and in (select ...) B

This concludes our query is
Select line_id from
(select distinct line_id from Linestops where stop_id in "A") C,
(select Distin CT line_id from Linestops where stop_id in "B") D
where c.line_id = d.line_id
where "a" is
(select distinct stop_id fro M 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)
So we found the route as an intermediate transfer (there may be multiple or 0), Each of the listed assumptions is named X-ray, and the next step is to find a direct line from site 1 to any site in X, and a direct line that can reach x any site from site 2.
so similar to the previous algorithm, we in the site 1 all reachable sites to find and line x intersection of the site, and then to find the line between the two points
Select stop_id from
(select distinct stop_id from Linestops where line_id in
(select line_id from linestops where stop_id = id1)) A,
(select stop_id from Linestops whe Re line_id = X) B
where a.stop_id = b.stop_id
found the site, the following is based on the resolved direct query to find the line.
Site 2 is similar.

The above algorithm has one advantage, all is SQL to complete the search, so ASP code only a few lines of loop.
But the downside is: slow, after all, it might involve hundreds of SQL queries. And just use the simplest SQL method to figure out all the options that can be transferred, not the optimal/shortest algorithm. If it is the shortest path, it has to use special structures and algorithms.

Other than that:

Based on the starting and ending points entered by the traveler, determine the starting bus site A and the destination bus station B to be selected for the trip. Search the database, query between site A and site B whether there is the same car passing, if there is one or several direct lines, by comparing the selection of the shortest distance bus routes recommended to the traveler. If not, calculate whether there is a public site C between site A and site B, from site C You can transfer to site B. There are two situations: (1) If any, it belongs to a transfer. Calculates whether the same bus passes between site A and public site C and is stored in collection x; Similarly, there is no same bus passing and depositing in the set Y between site B and public Site C. Compare these two sets to get from site A through public Site C arrives at site B bus lines, in these lines to compare, choose the shortest distance recommended to travelers. (2) If there is no public site C, there will be two times to transfer the situation. All sites that pass each bus line of site A are saved to the collection O, and all sites that pass each line of site B are stored in the collection p. Compare these two sets, the first bus through site A to reach a site D, calculate site D and site B there is no public site E, if there is a site D, E for the transfer site. This kind of scheme may have many, compare chooses the shortest distance recommendation to the traveler. If there is no public site E, it means that after two transfers cannot reach site B from site A, stop the search calculation.

The specific algorithm of bus travel optimal route:

1) Enter the starting site A and the destination site B;

2) Search the system database, after the bus line of the starting site A is stored as x (i) (I=1,2,3...,m,m is a positive integer), the bus line after the destination station B is stored as Y (j) (j=1,2,3,... N,.N is a positive integer);

3) Determine if there is x (i) =y (j), which will satisfy the condition of the deposit Z. If z=1, the bus line x (i), Y (j), is the direct route from site A to site B, outputting the result and ending the operation. Z≥1, calculates the distance of each line in Z, selects a line with shortest distance, outputs the result and ends the operation;

4) Search system database, bus line x (i) contains the site is stored as O (i,u) (U=1,2,3...,g,g is a positive integer) bus line y (j) contains a site to save as P (j,v) (V=1,2,3...,h,h is a positive integer);

5) Determine if there is O (i,u) = P (j,v), which will satisfy the condition of the deposit W. If w=1, then the site O (i,u) is P (j,v) from site A to site B a transfer site, bus line x (i), Y (j) for the first time to transfer the best route, output and end the operation. If w≥1, calculate the distance of each transfer route separately, select a line with shortest distance, output the result and end the operation;

6) Search system database, after the site O (i,u) bus line stored as r (k) (K=1,2,3...,p,p is a positive integer), bus line R (k) contains the site is stored as G (k,t) (T=1,2,3...,q,q is a positive integer);

7) Determine if there is g (k,t) =p (J,V), which will satisfy the condition of depositing s. If S=1, then the site G (k,t) is P (j,v) from site A to Site B two transfer site, bus line x (i), R (k), Y (j) for the transfer of two times the best route, output and end the operation. If s≥1, calculate the route distance of each transfer two times respectively, select a line with shortest distance, output the result and end the operation;

8) The above steps did not find a suitable bus line, the output "did not find the number of transfers not more than two times the optimal bus line", the end of the operation.

Transfer algorithm "turn"