Design of background database for bus route query system--introduction of walking route

In the "Query algorithm" and "associated place names and sites," two articles, has been implemented through the name or site route query algorithm, but in reality, from the beginning to the end of the journey is not necessarily the whole ride, for example, there are the following 3 routes:

R1:s1->s2->s3->s4->s5

R2:s6->s7->s2->s8

R3:s8->s9->s10

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

In order to achieve the insertion of walking routes in the bus route, use Walkroute (Startstop, Endstop, Distance, remark) in the database (startstop-start site, endstop-destination site, distance-distance, remark-notes) Stores two sites that are close to each other.

Add table Walkroute, query algorithm also to make corresponding changes, in fact, Walkroute and RouteT0 very similar, so just take the walkroute as a special direct line can be modified InqueryT1 as follows:

InquiryT1

* Query site @startstops to the site @endstops between a transfer bus route, multiple sites with '/' separate, such as: Exec InquiryT1 ' site 1/Site 2 ', ' Site 3/Site 4 '/CREATE proc Inquir YT1 (@StartStops varchar, @EndStops varchar) as BEGIN DECLARE @ss_tab table (name varchar) declar e @es_tab table (name varchar) Inserts @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 (' Start and end set contains same site ', 16,1) return declare @stops table (name varchar) i 

　　　　　　　 Nsert @stops select name from @ss_tab insert @stops the select name from @es_tab declare @result table ( 

　　　　　　　 Startstop varchar, Route1 varchar (256), Transstop varchar (a), Route2 varchar (256), Endstop varchar, Stopcount int) declare @count int set @count = 0--Query "walk-Ride "route Insert @result Select Sst.name as Startstop, ' from ' +r1. startstop+ ' walk 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 Walkroute 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 "ride-walk" route insert @result Select Sst.name as starts Top, R1. Route as Route1, R1. Endstop as Transstop, ' from ' +r2. startstop+ ' walk to ' +r2. Endstop as Route2, est.name as Endstop, R1. 　Stopcount as Stopcount from @ss_tab SST, @es_tab est, RouteT0 R1, (SELECT * From Walkroute 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 "ride-Ride" Route Inser T @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.N Ame=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 starting site, Route1 as Route 1, transstop as Transit point, Route2 as Route 2, Endstop as Site, Stopcount as terminus points from @result end