公交路線查詢系統(tǒng)數(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)的站點(diǎn) -路線關(guān)系表stop_route為StopRoutePositionS1R11S2R12S3R13S4R14S5R15S6R21S7R22S2R23S8R24S8R31S9R32S10R33注：Stop為站點(diǎn)名，Route為路線名，Position為站點(diǎn)在路線中的位置2.直達(dá)乘車路線查詢算法基于表stop_route可以很方便實(shí)現(xiàn)直達(dá)乘車路線的查詢，以下是用于查詢直達(dá)乘車路線的存儲(chǔ)過(guò)程InquiryT0：createprocInquiryT0(@StartStopvarchar(32>,@EndStopvarchar(32>>asbeginselectsr1.Stopas啟始站點(diǎn),sr2.Stopas目的站點(diǎn),sr1.Routeas乘坐線路,sr2.Position-sr1.Positionas經(jīng)過(guò)的站點(diǎn)數(shù)fromstop_routesr1,stop_routesr2wheresr1.Route=sr2.Routeandsr1.Position<sr2.Positionandsr1.Stop=@StartStop1/8andsr2.Stop=@EndStopend3.查詢換乘路線算法(1>直達(dá)路線視圖直達(dá)路線視圖可以理解為一張存儲(chǔ)了所有直達(dá)路線的表(如果兩個(gè)站點(diǎn)之間存在直達(dá)路線，那么在直達(dá)路線視圖中就有一行與之相對(duì)應(yīng)>createviewRouteT0asselectsr1.StopasStartStop,--啟始站點(diǎn)sr2.StopasEndStop,--目的站點(diǎn)sr1.RouteasRoute,--乘坐線路sr2.Position-sr1.PositionasStopCount--經(jīng)過(guò)的站點(diǎn)數(shù)fromstop_routesr1,stop_routesr2wheresr1.Route=sr2.Routeandsr1.Position<sr2.Position(2>換乘路線算法顯然，一條換乘路線由若干段直達(dá)路線組成，因此，基于直達(dá)路線視圖RouteT0可以很方便實(shí)現(xiàn)換乘查詢，以下是實(shí)現(xiàn)一次換乘查詢的存儲(chǔ)過(guò)程InquiryT1：createprocInquiryT1(@StartStopvarchar(32>,@EndStopvarchar(32>>asbeginselectr1.StartStopas啟始站點(diǎn),r1.Routeas乘坐路線1,r1.EndStopas中轉(zhuǎn)站點(diǎn),r2.Routeas乘坐路線2,r2.EndStopas目的站點(diǎn),r1.StopCount+r2.StopCountas總站點(diǎn)數(shù)fromRouteT0r1,RouteT0r2wherer1.StartStop=@StartStopandr1.EndStop=r2.StartStopandr2.EndStop=@EndStopend同理可以得到二次換乘的查詢語(yǔ)句createprocInquiryT2(@StartStopvarchar(32>,@EndStopvarchar(32>>as2/8beginselectr1.StartStopas啟始站點(diǎn),r1.Routeas乘坐路線1,r1.EndStopas中轉(zhuǎn)站點(diǎn)1,r2.Routeas乘坐路線2,r2.EndStopas中轉(zhuǎn)站點(diǎn)2,r3.Routeas乘坐路線3,r3.EndStopas目的站點(diǎn),r1.StopCount+r2.StopCount+r3.StopCountas總站點(diǎn)數(shù)fromRouteT0r1,RouteT0r2,RouteT0r3wherer1.StartStop=@StartStopandr1.EndStop=r2.StartStopandr2.EndStop=r3.StartStopandr3.EndStop=@EndStopend(3>.測(cè)試execInquiryT0’S1’,’S2’execInquiryT1’S1’,’S8’execInquiryT2’S1’,’S9’運(yùn)行結(jié)果:那么有沒(méi)有方法可以提高篩選第 2段路線的效率呢？答案是肯定的。只需把 GRouteT0改成實(shí)表，并創(chuàng)建索引就行了。修改成實(shí)表后，就不需要把第 2段路線緩存到臨時(shí)表 #R2中，修改后的GInquiryT2(重命名為 GInquiryT2_1> 如下：GInquiryT2_1/*查詢站點(diǎn)@StartStops 到站點(diǎn)@EndStops 之間的二次換乘乘車路線，多個(gè)站點(diǎn)用 '/'分開(kāi)，結(jié)果以分組3/8方 式 給 出 ， 如 ：exec GInquiryT2_1 ' 站 點(diǎn) 1/ 站 點(diǎn) 2',' 站 點(diǎn) 3/ 站 點(diǎn) 4'*/CREATE proc GInquiryT2_1(@StartStops varchar(2048>,@EndStops varchar(2048>>asbegindeclare @ss_tab table(StopKey int>declare @es_tab table(StopKey int>insert @ss_tabselect distinct Stop.StopKeyfrom dbo.SplitString(@StartStops,'/'> sn,Stopwhere sn.Value=Stop.StopNameinsert @es_tabselect distinct Stop.StopKeyfrom dbo.SplitString(@EndStops,'/'> sn,Stopwhere sn.Value=Stop.StopNameif(exists(select top 1 * from @ss_tab sst,@es_tab est wheresst.StopKey=est.StopKey>>beginraiserror (' 起點(diǎn)集和終點(diǎn)集中含有 相同 的站 點(diǎn)',16,1>returnenddeclare @stops table(StopKey int>insert @stops select StopKey from @ss_tabinsert @stops select StopKey from @es_tab4/8print'===================================================='print ' 篩 選 出 第 1 段 乘 車 路 線 'print '----------------------------------------------------'set statistics time on------------------------------------------------------------篩選出第1段乘車路線，保存到臨時(shí)表#R1中select *into #R1from GRouteT0whereStartStopKey in (select StopKey from @ss_tab>and EndStopKey not in (Select StopKey from @stops>order by StartStopKey,EndStopKey-- 在 臨 時(shí) 表 #R1 上 創(chuàng) 建 索 引create index index1 on #R1(StartStopKey,EndStopKey>------------------------------------------------------------set statistics time offprint'===================================================='print ' 篩 選 出 第 3 段 乘 車 路 線 'print '----------------------------------------------------'set statistics time on5/8------------------------------------------------------------篩選出第3段乘車路線，保存到臨時(shí)表#R3中select *into #R3from GRouteT0whereEndStopKey in (select StopKey from @es_tab>and StartStopKey not in (Select StopKey from @stops>order by StartStopKey,EndStopKey-- 在 臨 時(shí) 表 上 創(chuàng) 建 索 引create index index1 on #R3(StartStopKey,EndStopKey>------------------------------------------------------------set statistics time offprint'===================================================='print ' 二 次 換 乘 查 詢 'print '----------------------------------------------------'set statistics time on-------------------------------------------------------------- 二 次 換 乘 查 詢selectss.StopName as 起 點(diǎn) ,dbo.JoinRoute(res.StartStopKey,res.TransStopKey1> as 路線1,6/8ts1.StopName as 中 轉(zhuǎn) 站 1,dbo.JoinRoute(res.TransStopKey1,res.TransStopKey2> as 路線2,ts2.StopName as 中 轉(zhuǎn) 站 2,dbo.JoinRoute(res.TransStopKey2,res.EndStopKey> as 路線3,es.StopName as 終 點(diǎn) ,MinStopCountfrom(-- 查 詢 出 站 點(diǎn) 數(shù) 最 少 的 10 組 路 線select top 10r1.StartStopKey as StartStopKey,r2.StartStopKey as TransStopKey1,r2.EndStopKey as TransStopKey2,r3.EndStopKey as EndStopKey,(r1.MinStopCount+r2.MinStopCount+r3.MinStopCount>as MinStopCountfrom #R1 r1,GRouteT0 r2,#R3 r3where r1.EndStopKey=r2.StartStopKey andr2.EndStopKey=r3.StartStopKeyorder by(r1.MinStopCount+r2.MinStopCount+r3.MinStopCount> asc>res,Stop ss,Stop es,Stop ts1,Stop ts2whereres.StartStopKey