Design of Bus Route Query System backend database-introducing walking routes

Bus Route query system Background Database Design Series

Download the database (the database has already entered 350 bus routes in Guangzhou as test data)

In the query algorithm and associated place names and sites, the algorithm for querying routes by Place names or sites has been implemented. However, in reality, the entire journey from the start point to the end is not always a bus trip. For example, there are three routes as follows:

R1: S1-> S2-> S3-> S4-> S5

R2: S6-> S7-> S2-> S8

R3: S8-> S9-> S10

If you want to query routes from site S1 to s7. if you use inquiry to query routes, there is obviously no suitable ride plan. However, the distance between S2 and S7 is only one station, which can be replaced by walking. Therefore, you can first take R1 to S2 from S1 and then walk to S7.

In order to insert a walking route in the routeRoute route (startstop, endstop, distance, remark)(Startstop-start site, endstop-destination site, distance-distance, remark-remarks) store two sites that are close to each other.

After the external route table is added, the query algorithm must be modified accordingly. In fact, the external route and routet0 are very similar. Therefore, you only need to regard the external route as a special direct line. The modified inqueryt1 is as follows:

/* Query the transfer route between the site @ startstops and the site @ endstops. Separate multiple sites with '/', for example, exec inquiryt1 'site 1/Site 2 ', 'site 3/Site 4' */create proc inquiryt1 (@ startstops varchar (32), @ endstops varchar (32) as begin declare @ ss_tab table (name varchar (32 )) declare @ es_tab table (name varchar (32) insert @ ss_tab select value from DBO. splitstring (@ startstops, '/') insert @ es_tab select value from DBO. splitstring (@ endstops, '/') if (exists (select * From @ ss_tab SST, @ es_tab est where SST. name = est. name) Begin raiserror ('site with the same origin set and destination set ',) return end declare @ stops table (name varchar (32 )) insert @ stops select name from @ ss_tab insert @ stops select name from @ es_tab declare @ result table (startstop varchar (32), route1 varchar (256), transstop varchar (32 ), route2 varchar (256), endstop varchar (32), stopcount INT) Declare @ count int set @ COUNT = 0 -- Query "Walking-Bus" route insert @ result select SST. name as startstop, 'walk from '+ r1.startstop +' to '+ r1.endstop as route1, r1.endstop as transstop, r2.route as route2, est. name as endstop, r2.stopcount as stopcount from @ ss_tab SST, @ es_tab est, (select * from specified route where endstop not in (Select name from @ stops) R1, routet0 R2 where SST. name = r1.startstop and r1.endstop = r2.startstop and r2.endstop = est. name order by r2.stopcount set @ COUNT = @ rowcount -- Query "Bus-walking" route insert @ result select SST. name as startstop, r1.route as route1, r1.endstop as transstop, 'walk from '+ r2.startstop +' to '+ r2.endstop as route2, est. name as endstop, r1.stopcount as stopcount from @ ss_tab SST, @ es_tab est, routet0 R1, (select * from primary route where startstop not in (Select name from @ stops) R2 where SST. name = r1.startstop and r1.endstop = r2.startstop and r2.endstop = est. name order by r1.stopcount set @ COUNT = @ count + @ rowcount if (@ COUNT = 0) begin -- Query "Bus-Ride" route insert @ result select SST. name as startstop, r1.route as route1, r1.endstop as transstop, r2.route as route2, est. name as endstop, r1.stopcount + r2.stopcount as stopcount from @ ss_tab SST, @ es_tab est, (select * From routet0 where endstop not in (Select name from @ stops) R1, routet0 R2 where SST. name = r1.startstop and r1.endstop = r2.startstop and r2.endstop = est. name and r1.route <> r2.route order by r1.stopcount + r2.stopcount end select startstop as start site, route1 as Route 1, transstop as transit site, route2 as Route 2, endstop as destination site, stopcount as station point from @ result end

Author: Lu chuncheng

E-mail: mrlucc@126.com

Source: http://lucc.cnblogs.com/

The copyright of this article is shared by the author and the blog. You are welcome to repost this article, but you must keep this statement without the author's consent and provide a connection to the original article on the article page.