公交車路線查詢系統(tǒng)后臺(tái)數(shù)據(jù)庫(kù)設(shè)計(jì)說(shuō)明

1、公交車路線查詢系統(tǒng)后臺(tái)數(shù)據(jù)庫(kù)設(shè)計(jì)-查詢算法1. 公交車路線信息在數(shù)據(jù)庫(kù)中的存儲(chǔ)方式顯然，如果在數(shù)據(jù)庫(kù)中簡(jiǎn)單的使用表 bus_route（路線名,路線經(jīng)過(guò)的站點(diǎn)，費(fèi)用）來(lái)保存公交 車路線的線路信息，則很難使用查詢語(yǔ)句實(shí)現(xiàn)乘車線路查詢， 因此，應(yīng)該對(duì)線路的信息進(jìn)行 處理后再保存到數(shù)據(jù)庫(kù)中，筆者使用的方法是用 站點(diǎn)-路線關(guān)系表stop_route（站點(diǎn),路線名,站點(diǎn)在路線中的位置）來(lái)存儲(chǔ)公交車路線，例如，如果有以下 3條路線R1 : S1->S2->S3->S4->S5R2 : S6->S7->S2->S8R3 : S8->S9->S10則對(duì)應(yīng)的

2、站點(diǎn)-路線關(guān)系表stop_route 為StopRoutePositi onS1R11S2R12S3R13S4R14S5R15S6R21S7R22S2R23S8R24S8R31S9R32S10R33注：Stop為站點(diǎn)名，Route為路線名，Position為站點(diǎn)在路線中的位置2. 直達(dá)乘車路線查詢算法基于表stop_route 可以很方便實(shí)現(xiàn)直達(dá)乘車路線的查詢，以下是用于查詢直達(dá)乘車路線的存儲(chǔ)過(guò)程InquiryTO :create proc In quiryTO(StartStop varchar(32),E ndStop varchar(32)asbeg inselectsr1.Stop a

3、s 啟始站點(diǎn),sr2.Stop as 目 的站點(diǎn)，sr1.Route as 乘坐線路,fromstop_route sr1,stop_route sr2wheresr1.Route=sr2.Routeand srl.Position<sr2.Positionand sr1.Stop=StartStopand sr2.Stop=E ndStopend3. 查詢換乘路線算法(1)直達(dá)路線視圖直達(dá)路線視圖 可以理解為一張存儲(chǔ)了所有直達(dá)路線的表(如果兩個(gè)站點(diǎn)之間存在直達(dá)路線,那么在直達(dá)路線視圖中就有一行與之相對(duì)應(yīng))。例如R1,R2,R3 對(duì)應(yīng)的RouteTO如下:起點(diǎn)終占 "匕八、乘坐

4、路線站點(diǎn)數(shù)S3S4R11S3S5R12S4S5R11S1S2R11S1S3R12S1S4R13S1S5R14S2S3R11S2S4R12S2S5R13S2S8R21S6S2R22S6S7R21S6S8R23S7S2R21S7S8R22S8S10R32S8S9R31S9S10R31RouteTO定義如下:create view RouteTOasselectsrl.Stop as StartStop,-啟始站點(diǎn)sr2.Stop as EndStop,-目的站點(diǎn)srl.Route as Route,-乘坐線路sr2.Positio n-sr1.Position as StopCou nt-經(jīng)過(guò)的站

5、點(diǎn)數(shù)fromstop_route sr1,stop_route sr2wheresr1.Route=sr2.Routeand sr1.Position<sr2.Position(2)換乘路線算法顯然，一條換乘路線由若干段直達(dá)路線組成(每段路線的終點(diǎn)與下一段路線的起點(diǎn)相同)，因此，基于直達(dá)路線視圖RouteTO可以很方便實(shí)現(xiàn)換乘查詢，以下是實(shí)現(xiàn)一次換乘查詢的存儲(chǔ)過(guò)程 InquiryTI :create proc In quiryT1(StartStop varchar(32),E ndStop varchar(32)asbeg inselectr1.StartStop as 啟始站點(diǎn)，r1

6、.Route as乘坐路線1,r1.EndStop as中轉(zhuǎn)站點(diǎn)，r2.Route as乘坐路線2,r2.EndStop as目 的站點(diǎn),r1.StopCou nt+r2.StopCou nt as總站點(diǎn)數(shù)fromRouteT0 r1,RouteT0 r2wherer1.StartStop=StartStopand r1.E ndStop=r2.StartStopand r2.E ndStop=E ndStopend同理可以得到二次換乘的查詢語(yǔ)句create proc In quiryT2(StartStop varchar(32),E ndStop varchar(32) asbeg ins

7、electr1.StartStop as 啟始站點(diǎn)，r1.Route as乘坐路線1,r1.E ndStop as 中轉(zhuǎn)站點(diǎn) 1,r2.Route as乘坐路線2,r2.EndStop as 中轉(zhuǎn)站點(diǎn) 2,r3.Route as乘坐路線3,總站點(diǎn)數(shù)r3.EndStop as 目 的站點(diǎn)，r1.StopCou nt+r2.StopCou nt+r3.StopCou nt as fromRouteT0 r1,RouteT0 r2,RouteT0 r3wherer1.StartStop=StartStopand r1.E ndStop=r2.StartStopand r2.E ndStop=r3.S

8、tartStopand r3.E ndStop=E ndStopend4. 測(cè)試execIn quiryTO'S1' ,'S2'execIn quiryT1'S1' ,'S8'execIn quiryT2'S1' ,'S9'運(yùn)行結(jié)果：?jiǎn)⑹颊军c(diǎn)目的站點(diǎn) 乘坐線路 經(jīng)辻的站點(diǎn)數(shù)LSI32R11啟始站點(diǎn)乘坐路線1中轉(zhuǎn)站點(diǎn)】乘塵牆線2 目的站點(diǎn)總站點(diǎn)數(shù)ISIR15222S32啟始站點(diǎn)乘坐路線1中轉(zhuǎn)站點(diǎn)1乘坐路線2中輅站點(diǎn)2乘坐略線W目的站點(diǎn)總站點(diǎn)數(shù)LSIR1S2R2S8時(shí)S931公交車路線查詢系統(tǒng)后臺(tái)數(shù)據(jù)庫(kù)

9、設(shè)計(jì)-關(guān)聯(lián)地名和站點(diǎn)在公交車路線查詢系統(tǒng)后臺(tái)數(shù)據(jù)庫(kù)設(shè)計(jì)一一查詢算法一文中，已經(jīng)實(shí)現(xiàn)了查詢站點(diǎn)到站點(diǎn)的路線查詢算法，但是，現(xiàn)實(shí)中用戶不一定使用站點(diǎn)進(jìn)行 查詢，而是使用地名。因此，公交車查詢系統(tǒng)數(shù)據(jù)庫(kù)必需記錄地名與站點(diǎn)的 對(duì)應(yīng)關(guān)系，在查詢時(shí)將地名映射為站點(diǎn)。根據(jù)實(shí)際情況，某一地點(diǎn)附近通常 有幾個(gè)站點(diǎn)，因此，地名與站點(diǎn)之間是多對(duì)多的關(guān)系。顯然，只需創(chuàng)建一個(gè) 地名站點(diǎn)關(guān)系表stop_spot(Stop,Spot)用于儲(chǔ)存這個(gè)關(guān)系即可。數(shù)據(jù)庫(kù)關(guān)系 圖如下:SpotPKNameRemarkKoutePKNitmeRcmurkroutePKPK.FK1PKJK2PqsiitkinSt叩PKiimeRemar

10、kSCop_spotPK.FKl PKTK2注：Route :路線表Stop :站點(diǎn)表Spot :地名表stop_route : 路線-站點(diǎn)關(guān)系表stop_spot :地名-站點(diǎn)關(guān)系表1.路線和地名信息維護(hù)：以下函數(shù)用于維護(hù)公交車路線和地名的相關(guān)信息字符串分割函數(shù)(信息處理及路線查詢的存儲(chǔ)過(guò)程都需要使用到該函數(shù))：/*函數(shù)功能：將String以SplitChar為分隔點(diǎn)分割為字符串?dāng)?shù)組，結(jié)果保留在表變量中例如 SplitString('A/B','/') 返回表：Value vin dexA1B2*/CREATEfun ctio n SplitStri ng(S

11、tri ng varchar(2048),SplitChar char)returns res table(Value varchar(128),vin dex int)asbeg indeclare in dex in t, unit varchar(128),i next in t,le n in t,i intset in dex=1set i=1set le n=len( Stri ng)while in dex<=le nbegi nset in ext=chari ndex(SplitChar,Stri ng,i ndex) if i next=0 set i next=le

12、 n+1 if in ext> in dex beg insetun it=ltrim(rtrim(substri ng(Stri ng,i ndex,i next-i ndex)if un it<>''beg inin sert into res (value, vin dex) values (un it,i)set i=i+1endendset in dex=in ext+1endreturnend插入新的公車路線：/*插入新的公交車路線Route:路線名Stops:公交車經(jīng)過(guò)的所有站點(diǎn)，站點(diǎn)用'-'隔開*/CREATE proc In

13、sertRoute(Route varchar(32),Stops_Strvarchar(1024)as beg indeclare stops table( name varchar(32),positi on int)insert stops( name,positi on)select Value,vl ndex from dbo.SplitStri ng(Stops_Str,'-')begi n tran t1save tran sp1-插入路線信息in sert into Route (n ame) values (Route)if(error<>0)be

14、gi nrollback tran sp1commit tran t1raiserror('插入路線時(shí)發(fā)生錯(cuò)誤',16,1)returnend-插入不存在的站點(diǎn)in sert Stop( name)select dist inct n ame from stops ss where n ame not in (select n ame from Stop)if(error<>0)begi nrollback tran sp1 commit tran t1raiserror('插入路線時(shí)發(fā)生錯(cuò)誤,16,1)returnendin sert stop_route

15、(Stop,Route,Positi on)select ss.n ame,Route,ss.positi on from stops ss if(error<>0)beg inrollback tran sp1commit tran t1raiserror('插入路線時(shí)發(fā)生錯(cuò)誤',16,1)returnendcommit tran t1end插入新地名函數(shù)：/*插入新地名name :地名Stops:地名附近的所有站點(diǎn)，多個(gè)站點(diǎn)用'/'隔開Remark:與地名相關(guān)的說(shuō)明*/CREATE proc In sertSpot(n ame varchar(6

16、4),Stops_Str varchar(1024),Remark varchar(1024)asbeg indeclare stops table( name varchar(32)in sert stops select disti net Value fromdbo.SplitStri ng(Stops_Str,'/')declare n varchar(32)set n=”select top 1 n=n ame from stops s where n ame not in (select n ame from stop)if(n< >''

17、)begi nraiserror ('站點(diǎn) %s 不存在',16,1,n)returnendin sert into Spot (n ame,remark) values (n ame,remark)in sert stop_spot(Stop,Spot)select s.n ame, n ame from stops sif(error<>0)begi nraiserror ('插入地點(diǎn)時(shí)發(fā)生錯(cuò)誤',16,1)returnendend2.路線查詢?cè)诠卉嚶肪€查詢系統(tǒng)后臺(tái)數(shù)據(jù)庫(kù)設(shè)計(jì)查詢算法 一文中，使用儲(chǔ)存過(guò)程InquiryT0, InquiryT1

18、和InquiryT2實(shí)現(xiàn)了站點(diǎn)到站點(diǎn)的查詢，但是地名可能對(duì)應(yīng)多個(gè)站點(diǎn)，因此，當(dāng)進(jìn)行地點(diǎn)到地點(diǎn)的查詢相當(dāng)于站點(diǎn)集到站點(diǎn)集的查詢。因此，為了支持使用地名進(jìn)行查詢， 將InquiryT0 ，InquiryT1 和InquiryT2 修改為站點(diǎn)集到站點(diǎn)集的查詢：直達(dá)路線查詢：/*查詢站點(diǎn)StartStops到站點(diǎn)EndStops之間的直達(dá)乘車路線，多個(gè)站點(diǎn)用'/'分開，如：exec InquiryT0 '站點(diǎn)1/站點(diǎn)2','站點(diǎn)3/站點(diǎn)4'*/CREATEproc In quiryT0(StartStops varchar(32),E ndStops v

19、archar(32)as beg indeclare ss_tab table( name varchar(32)declare es_tab table( name varchar(32)in sert ss_tab select Value from dbo.SplitStri ng(StartStops,'/') in sert es_tab select Value from dbo.SplitStri ng(E ndStops,'/') if(exists(select * from ss_tab sst,es_tab est wheresst. na

20、me=est .n ame)begi nraiserror ('起點(diǎn)集和終點(diǎn)集中含有相同的站點(diǎn),16,1) as啟始站點(diǎn)， as 目 的站點(diǎn)，r.Route as 乘坐線路，r.StopCou nt as經(jīng)過(guò)的站點(diǎn)數(shù)fromss_tab sst,es_tab est,RouteTO rwheresst. name=r.StartStopand r.En dStop=est. nameend一次換乘查詢：/*查詢站點(diǎn)StartStops到站點(diǎn)EndStops之間的一次換乘乘車路線，多個(gè)站 點(diǎn)用'/'分開，如：e

21、xec InquiryTI ' 站點(diǎn)1/站點(diǎn)2','站點(diǎn)3/站點(diǎn)4'*/CREATE proc In quiryT1(StartStops varchar(32),E ndStopsvarchar(32)asbeg indeclare ss_tab table( name varchar(32)declare es_tab table( name varchar(32)in sert ss_tab select Value from dbo.SplitStri ng(StartStops,'/')in sert es_tab select Val

22、ue from dbo.SplitStri ng(E ndStops,'/')if(exists(select * from ss_tab sst,es_tab est wheresst. name=est .n ame)begi nraiserror ('起點(diǎn)集和終點(diǎn)集中含有相同的站點(diǎn)',16,1)returnenddeclare stops table( name varchar(32)in sert stops select n ame from ss_tabin sert stops select n ame from es_tab selectsst.

23、 name as 起始站點(diǎn),r1.Route as乘坐路線1,r1.E ndStop as中轉(zhuǎn)站點(diǎn) 1,r2.Route as乘坐路線2, as目 的站點(diǎn)，r1.StopCou nt+r2.StopCou nt as總站點(diǎn)數(shù)fromss_tab sst,es_tab est,(select * from RouteTO where En dStop not in (select n ame fromstops) r1,RouteTO r2wheresst. name=r1.StartStopand rl.E ndStop=r2.StartStopand r2.E ndStop=

24、est. name and rl.R oute<>r2.Routeend二次換乘查詢：/*查詢站點(diǎn)StartStops到站點(diǎn)EndStops之間的二次換乘乘車路線，多個(gè)站 點(diǎn)用'/'分開，如：exec InquiryT2 ' 站點(diǎn)1/站點(diǎn)2','站點(diǎn)3/站點(diǎn)4'*/CREATEproc In quiryT2(StartStops varchar(32),E ndStopsvarchar(32)asbeg indeclare ss_tab table( name varchar(32)declare es_tab table( name

25、 varchar(32)in sert ss_tab select Value from dbo.SplitStri ng(StartStops,'/')in sert es_tab select Value from dbo.SplitStri ng(E ndStops,'/') if(exists(select * from ss_tab sst,es_tab est wheresst. name=est .n ame)begi nraiserror ('起點(diǎn)集和終點(diǎn)集中含有相同的站點(diǎn)',16,1)returnenddeclare stops

26、 table( name varchar(32)in sert stops select n ame from ss_tabin sert stops select n ame from es_tabselectrl.StartStop as 啟始站點(diǎn)，rl.Route as 乘坐路線1,rl.E ndStop as中轉(zhuǎn)站點(diǎn) 1,r2.Route as 乘坐路線2,r2.EndStop as中轉(zhuǎn)站點(diǎn) 2,r3.Route as 乘坐路線3,r3.EndStop as目 的站點(diǎn)，r1.StopCou nt+r2.StopCou nt+r3.StopCou nt as總站點(diǎn)數(shù)fromss_tab

27、sst,es_tab est,(select * from RouteTO where En dStop not in (select n ame fromstops) r1,(select * from RouteTO where En dStop not in (select n ame fromstops) r2,RouteTO r3wheresst. name=r1.StartStopand rl.E ndStop=r2.StartStopand r2.E ndStop=r3.StartStopand r3.E ndStop=est. nameand rl.R oute<>

28、r2.Routeand r2.R oute<>r3.Routeand r3.R oute<>r1.Routeend綜合查詢：/*查詢站點(diǎn)StartStops到站點(diǎn)EndStops之間的乘車路線，先查詢直達(dá)路線，如不存在，則查詢一次換乘路線，如果直達(dá)和一次換乘均不存在，則查詢二次換 乘多個(gè)站點(diǎn)用'/'分開，如：exec Inquiry '站點(diǎn)1/站點(diǎn)2','站點(diǎn)3/站點(diǎn)4'*/CREATE proc In quiry(StartStops varchar(32),E ndStopsvarchar(32)asbeg inexe

29、c In quiryTO StartStops,E ndStopsif(rowcou nt=0)begi nexec In quiryTI StartStops,E ndStopsif(rowcou nt=0)beg inexec In quiryT2 StartStops,E ndStopsendendend如要進(jìn)行地名到地名的路線查詢，必需先調(diào)用GetStopsOfSpot獲取地名對(duì)應(yīng)的所有站點(diǎn)，在調(diào)用In quiry 進(jìn)行查詢。獲取地名對(duì)應(yīng)的站點(diǎn)：/*獲取地名對(duì)應(yīng)的站點(diǎn)，如有多個(gè)站點(diǎn)，用'/'隔開*/CREATEfun ctio n GetStopsOfSpot(Spot

30、 varchar(32)returns varchar(1024)asbeg indeclare stops varchar(1024)set stops=''select stops=stops+'/'+stop from stop_spot where Spot=Spotretur n substri ng(stops,2,le n( stops)-1)end使用地名查詢乘車路線示例:declare sps varchar (1024),eps varchar (1024) set sps=dbo.GetStopsOfSpot('起始地點(diǎn)名稱'

31、;)set eps=dbo.GetStopsOfSpot('目的地點(diǎn)名稱')exec Inquiry sps,eps公交車路線查詢系統(tǒng)后臺(tái)數(shù)據(jù)庫(kù)設(shè)計(jì)-引入步行路線在查詢算法和關(guān)聯(lián)地名和站點(diǎn)兩篇文章中，已經(jīng)實(shí)現(xiàn)了通過(guò)地名或 站點(diǎn)進(jìn)行路線查詢的算法，但是在現(xiàn)實(shí)中，從起點(diǎn)到終點(diǎn)不一定全程都是乘車，例如，有以下3條路線：R1: S1->S2->S3->S4->S5R2: S6->S7->S2->S8R3: S8->S9->S10假如現(xiàn)在要從站點(diǎn) S1到S7,如果用Inquiry 查詢路線，顯然沒(méi)有合適的乘車方案。但是S2和S7相距僅

32、僅一個(gè)站的距離，可以用步行代替，因此可以 先從S1乘坐R1到S2再步行到S7。為了實(shí)現(xiàn)在乘車路線中插入步行路線，在數(shù)據(jù)庫(kù)使用WalkRoute(StartStop,EndStop, Distanee, Remark)(StartStop- 起始站點(diǎn)，EndStop-目的站點(diǎn),Distanee-距離Remark-備注)儲(chǔ)存距離較近的兩個(gè)站點(diǎn)。加入表 WalkRoute 后，查詢算法也要作相應(yīng)的修改，其實(shí)WalkRoute 和RouteTO 很相似，因此只需把WalkRoute 看成是特殊的直達(dá)線路即可，修改后的In queryTI如下:/*查詢站點(diǎn)StartStops 到站點(diǎn)EndStops之間

33、的一次換乘乘車路線，多個(gè)站點(diǎn)用'/'分開,如：exec InquiryTI ' 站點(diǎn)1/站點(diǎn)2','站點(diǎn)3/站點(diǎn)4'*/ as beginCREATE procIn quiryT1(StartStopsvarchar (32),EndStopsvarchar (32)declare ss_tab table (name varchar(32)declare es_tab table (name varchar(32)insert ss_tab select Value from dbo.SplitString(StartStops, '/

34、')insert es_tab select Value from dbo.SplitString(EndStops, '/')if (exists (select * from ss_tab sst,es_tab est where =est. name)beginraiserror（'起點(diǎn)集和終點(diǎn)集中含有相同的站點(diǎn),16,1） returnenddeclare stops table (name varchar (32) insert stops select name fromss_tabinsert stops select name f

35、romes_tabdeclare result table (StartStop varchar (32),Routel varchar (256),TransStop varchar (32),Route2 varchar (256),EndStop varchar (32),StopCou ntint)declare count intset count=0-查詢"步行-乘車"路線insert resultselectsst. name as StartStop,'從'+r1.StartStop+'步行到'+r1.EndStop as R

36、outel,rl.E ndStopas Tran sStop,r2.Route as Route2, as EndStop,r2.StopCo untas StopCo untfromss_tab sst,es_tab est,(select n ame from(select * from WalkRoute where EndStop not instops) r1,RouteT0 r2wheresst. name=r1.StartStopand r1.EndStop=r2.StartStopand r2.EndStop=order by r2.StopCo

37、 untset count=rowcount-查詢"乘車-步行"路線insert resultselectsst. name as StartStop,r1.Route as Route1,r1.E ndStopas Tran sStop,'從'+r2.StartStop+'步行到'+r2.EndStopas Route2, as EndStop,rl.StopCou ntas StopCou ntfromss_tab sst,es_tab est,RouteTO r1,(select * from WalkRoute whe

38、re StartStop not instops) r2wheresst. name=r1.StartStopand r1.EndStop=r2.StartStopand r2.EndStop=order by r1.StopCou ntset count=count+rowcountif(cou nt=0)begin-查詢”乘車-乘車”路線insert resultselectsst. name as StartStop,r1.Route as Route1,r1.EndStopas TransStop,r2.Route as Route2,est .n ame as En

39、dStop,r1.StopCount+r2.StopCountas StopCountfromss_tab sst,es_tab est,(select * from RouteT0 where EndStop not instops) r1,(select n ame from(select n ame fromRouteT0 r2wheresst. name=r1.StartStopand rl.EndStop=r2.StartStopand r2.EndStop=and r1.Route<>r2.Routeorder by rl.StopCou nt+r2.S

40、topCou ntendselectStartStop as起始站點(diǎn)，Route1 as 路線 1,TransStop as 中轉(zhuǎn)站點(diǎn)，Route2 as 路線 2,EndStop as 目的站點(diǎn)，StopCountas總站點(diǎn)數(shù)fromresultend公交車路線查詢系統(tǒng)后臺(tái)數(shù)據(jù)庫(kù)設(shè)計(jì)-換乘算法改進(jìn)與優(yōu)化在查詢算法一文中已經(jīng)實(shí)現(xiàn)了換乘算法，但是，使用存儲(chǔ)過(guò)程InquiryT2查詢從“東圃鎮(zhèn)”到“車陂路口”的乘車路線時(shí)，發(fā)現(xiàn)居然用了5分鐘才查找出結(jié)果，這樣的效率顯然不適合實(shí)際應(yīng)用。因此，有必要對(duì)原有的換乘算 法進(jìn)行優(yōu)化和改進(jìn)。在本文中，將給出一種改進(jìn)的換乘算法，相比原有的算法，改進(jìn)后的算法功能更

41、強(qiáng)，效率更優(yōu)。1. “壓縮”RouT0假設(shè)RouteTO有以下幾行&tartStapRouteEndS topStopC aim：S1R1S22S1R2S23S1R3S24S?R4S34S2R5S35S3Rd54一7S3R7S4$S3RSS9S3闊S410 如下圖所示，當(dāng)查詢 S1到S4的二次換乘路線時(shí)，將會(huì)產(chǎn)生3 X 2 X 4=24個(gè)結(jié)果第1段路線rl第2SSr2第3段路菱點(diǎn)勺型【淨(jìng)|StrlS'rpEcdStM氐;叩E曲即SIR1521$1K1S?SIR1S2SIK2S251R2S251R2S2SIR3'S2 '- J沖>2SI訂S：則S1S2KIS

42、2R4欝R5SJr K5暨$3掘S4S35S!UJ$311 SJ1 R7S4$5S4$3| | S3冊(cè)S4S3ESS4S3RSS3S4S3AS4S3從圖中可以看出，第1段路線中的3條線路的起點(diǎn)和站點(diǎn)都相同（第 2、3段路線也是如此），事實(shí)上，換乘查詢中關(guān)心的是兩個(gè)站點(diǎn)之間有無(wú)線路可通，而不關(guān)心是乘坐什么路線，因此，可以將RouteTO壓縮為：如下圖所示，壓縮后，查詢結(jié)果有原來(lái)的24條合并1組站財(cái)|$1R! R2 RJS3RCR' R3 RS54EfidS top31Rl R2IU甜K53"氏勺K皿屜1!曲仙RwiinBod眄51525JEtfr It ? RS R?54查詢結(jié)

43、果為:S1S1S2S2乘坐K4/R5乘坐K4/R5S3S3乘坐尺倔7眼$爪9乘坐尺倔7眼$爪9那么，為什么要對(duì)視圖 RouteTO進(jìn)行壓縮呢，原因如下:RouteTO 是原有換乘算法頻繁使用的視圖，因此， RouteTO的數(shù)據(jù)量直 接影響到查詢的效率，壓縮 RouteTO可以減少RouteTO的數(shù)據(jù)量，加速查 詢效率。(2)壓縮RouteTO后，將中轉(zhuǎn)站點(diǎn)相同的路線合并為1組，加速了對(duì)結(jié)果集排序的速度。2. 視圖 GRouteTO在數(shù)據(jù)庫(kù)中，將使用 GRouteTO 來(lái)描述壓縮的RouteTO，由于本文使用的 數(shù)據(jù)庫(kù)的關(guān)系圖與 查詢算法中有所不同，在給出 GRouteTO的代碼前, 先說(shuō)明一下

44、：站點(diǎn)路迭關(guān)系表路線表站點(diǎn)表主要的改變是Stop_Route 使用了整數(shù)型的RouteKey和StopKey弓I用Route和Stop，而不是用路線名和站點(diǎn)名。GRouteTO 定義如下：create view GRouteTOasselectStartStopKey,En dStopKey,min (StopCount) as MinStopCount,max (StopCount) as MaxStopCountfrom RouteT0group by StartStopKey,E ndStopKey注意，視圖 GRouteTO 不僅有StartStopKey 和EndStopKey 列，

45、還有MinStopCount列，MinStopCount 是指從 StartStop 至U EndStop 的最短線路的站點(diǎn)數(shù)。例如：上述RouteTO 對(duì)應(yīng)的GRouteTO 為：SrarEStopEndStopbn StopCountMaxStopCcuntS1S3'：24S2S343S3S47103. 二次查詢算法以下是二次換乘查詢的存儲(chǔ)過(guò)程Gin quiryT2的代碼，該存儲(chǔ)過(guò)程使用了臨時(shí)表來(lái)提高查詢效率：Gin quiryT2/*查詢站點(diǎn)StartStops 到站點(diǎn)EndStops之間的二次換乘乘車路線，多個(gè)站點(diǎn)用'/'分開,結(jié)果以分組方式給出，如：exec

46、InquiryT2 ' 站點(diǎn)1/站點(diǎn)2','站點(diǎn)3/站點(diǎn)4'*/CREATE proc GinquiryT2(StartStops varchar (2048),EndStops varchar (2048)asbegindeclare ss_tab table (StopKey int )declare es_tab table (StopKey int )insert ss_tabselect dist inct Stop.StopKeyfrom dbo.SplitString(StartStops, T ) sn,Stopwhere sn.Value=St

47、op.StopNameinsert es_tabselect dist inctStop.StopKeyfrom dbo.SplitString(EndStops,'/' ) sn,Stopwhere sn.Value=Stop.StopNameif (exists (select top 1 * from ss_tab sst,es_tab est where sst.StopKey=est.StopKey)beginraiserror('起點(diǎn)集和終點(diǎn)集中含有相同的站點(diǎn)',16,1)returnenddeclare stops table (StopKey i

48、nt )insert stops select StopKey from ss_tabinsert stops select StopKey from es_tabprint '=print '篩選出第1段乘車路線'print ''set statistics time on-篩選出第1段乘車路線，保存到臨時(shí)表 #R1中select *into #R1from GRouteTOwhereStartStopKey in (select StopKey from ss_tab)and EndStopKey not in (Select StopKey fro

49、m stops) order by StartStopKey,E ndStopKey-在臨時(shí)表#R1上創(chuàng)建索引create indexindex1 on #R1(StartStopKey,EndStopKey)set statistics time offprint '=print '篩選出第3段乘車路線print 'set statistics time on-篩選出第3段乘車路線，保存到臨時(shí)表 #R3中select *into #R3from GRouteTOwhereEndStopKeyin (select StopKey from es_tab)and Star

50、tStopKey not in (Select StopKey from stops) order by StartStopKey,E ndStopKey-在臨時(shí)表上創(chuàng)建索引create indexindex1 on #R3(StartStopKey,EndStopKey)set statistics time offprint '=print '篩選出第2段乘車路線print 'set statistics time on-篩選出第2段乘車路線，保存到臨時(shí)表 #R2中select *into #R2from GRouteTOwhereStartStopKey in (

51、select EndStopKey from #R1)and EndStopKeyin (Select StartStopKey from #R3)在臨時(shí)表上創(chuàng)建索引create clustered in dexin dex1 on #R2(StartStopKey,E ndStopKey)create in dexin dex2on #R2(EndStopKey,StartStopKey)set statistics time offprint '=：print '二次換乘查詢print 'set statistics time on-二次換乘查詢selectss.S

52、topName as 起點(diǎn),dbo.JoinRoute(res.StartStopKey,res.TransStopKey1)as 路線 1,ts1.StopNameas 中轉(zhuǎn)站 1,dbo.JoinRoute(res.TransStopKey1,res.TransStopKey2)as 路線 2,ts2.StopNameas 中轉(zhuǎn)站 2,dbo.JoinRoute(res.TransStopKey2,res.EndStopKey)as 路線 3,es.StopName as 終點(diǎn),Min StopCou ntfrom (-查詢出站點(diǎn)數(shù)最少的10組路線select top 10r1.StartStopKey as StartStopKey,r2.StartStopKey as Tran sStopKeyl,r2.E ndStopKeyas Tran sStopKey2,r3.EndStopKeyas EndStopKey,(rl.Mi nStopCou nt+r2.Mi nStopCou nt+r3.Mi nStopCou nt)asMi nStopCou ntfrom #R1 r1,#R2 r2,#R3 r3where r1.EndStopKe