公交路線查詢系統(tǒng)數(shù)據(jù)庫設計方案

1. 公 交 車 路 線 信 息 在 數(shù) 據(jù) 庫 中 的 存 儲 方 式顯然，如果在數(shù)據(jù)庫中簡單的使用表bus_route(路線名,路線經(jīng)過的站點,費用>來保存公交車路線的線路信息，則很難使用查詢語句實現(xiàn)乘車線路查詢，因此，應該對線路的信息進行處理后再保存到數(shù)據(jù)庫中，考試大使用的方法是用站點 -路線關系表stop_route(站點,路線名,站點在路線中的位置>來存儲公交車路線,例如，如果有以下3條路線R1:S1->S2->S3->S4->S5R2:S6->S7->S2->S8R3:S8->S9->S10則對應的站點 -路線關系表stop_route為StopRoutePositionS1R11S2R12S3R13S4R14S5R15S6R21S7R22S2R23S8R24S8R31S9R32S10R33注：Stop為站點名，Route為路線名，Position為站點在路線中的位置2.直達乘車路線查詢算法基于表stop_route可以很方便實現(xiàn)直達乘車路線的查詢，以下是用于查詢直達乘車路線的存儲過程InquiryT0：createprocInquiryT0(@StartStopvarchar(32>,@EndStopvarchar(32>>asbeginselectsr1.Stopas啟始站點,sr2.Stopas目的站點,sr1.Routeas乘坐線路,sr2.Position-sr1.Positionas經(jīng)過的站點數(shù)fromstop_routesr1,stop_routesr2wheresr1.Route=sr2.Routeandsr1.Position<sr2.Positionandsr1.Stop=@StartStop1/8andsr2.Stop=@EndStopend3.查詢換乘路線算法(1>直達路線視圖直達路線視圖可以理解為一張存儲了所有直達路線的表(如果兩個站點之間存在直達路線，那么在直達路線視圖中就有一行與之相對應>createviewRouteT0asselectsr1.StopasStartStop,--啟始站點sr2.StopasEndStop,--目的站點sr1.RouteasRoute,--乘坐線路sr2.Position-sr1.PositionasStopCount--經(jīng)過的站點數(shù)fromstop_routesr1,stop_routesr2wheresr1.Route=sr2.Routeandsr1.Position<sr2.Position(2>換乘路線算法顯然，一條換乘路線由若干段直達路線組成，因此，基于直達路線視圖RouteT0可以很方便實現(xiàn)換乘查詢，以下是實現(xiàn)一次換乘查詢的存儲過程InquiryT1：createprocInquiryT1(@StartStopvarchar(32>,@EndStopvarchar(32>>asbeginselectr1.StartStopas啟始站點,r1.Routeas乘坐路線1,r1.EndStopas中轉(zhuǎn)站點,r2.Routeas乘坐路線2,r2.EndStopas目的站點,r1.StopCount+r2.StopCountas總站點數(shù)fromRouteT0r1,RouteT0r2wherer1.StartStop=@StartStopandr1.EndStop=r2.StartStopandr2.EndStop=@EndStopend同理可以得到二次換乘的查詢語句createprocInquiryT2(@StartStopvarchar(32>,@EndStopvarchar(32>>as2/8beginselectr1.StartStopas啟始站點,r1.Routeas乘坐路線1,r1.EndStopas中轉(zhuǎn)站點1,r2.Routeas乘坐路線2,r2.EndStopas中轉(zhuǎn)站點2,r3.Routeas乘坐路線3,r3.EndStopas目的站點,r1.StopCount+r2.StopCount+r3.StopCountas總站點數(shù)fromRouteT0r1,RouteT0r2,RouteT0r3wherer1.StartStop=@StartStopandr1.EndStop=r2.StartStopandr2.EndStop=r3.StartStopandr3.EndStop=@EndStopend(3>.測試execInquiryT0’S1’,’S2’execInquiryT1’S1’,’S8’execInquiryT2’S1’,’S9’運行結(jié)果:那么有沒有方法可以提高篩選第 2段路線的效率呢？答案是肯定的。只需把 GRouteT0改成實表，并創(chuàng)建索引就行了。修改成實表后，就不需要把第 2段路線緩存到臨時表 #R2中，修改后的GInquiryT2(重命名為 GInquiryT2_1> 如下：GInquiryT2_1/*查詢站點@StartStops 到站點@EndStops 之間的二次換乘乘車路線，多個站點用 '/'分開，結(jié)果以分組3/8方 式 給 出 ， 如 ：exec GInquiryT2_1 ' 站 點 1/ 站 點 2',' 站 點 3/ 站 點 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 (' 起點集和終點集中含有 相同 的站 點',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段乘車路線，保存到臨時表#R1中select *into #R1from GRouteT0whereStartStopKey in (select StopKey from @ss_tab>and EndStopKey not in (Select StopKey from @stops>order by StartStopKey,EndStopKey-- 在 臨 時 表 #R1 上 創(chuàng) 建 索 引create index index1 on #R1(StartStopKey,EndStopKey>------------------------------------------------------------set statistics time offprint'===================================================='print ' 篩 選 出 第 3 段 乘 車 路 線 'print '----------------------------------------------------'set statistics time on5/8------------------------------------------------------------篩選出第3段乘車路線，保存到臨時表#R3中select *into #R3from GRouteT0whereEndStopKey in (select StopKey from @es_tab>and StartStopKey not in (Select StopKey from @stops>order by StartStopKey,EndStopKey-- 在 臨 時 表 上 創(chuàng) 建 索 引create index index1 on #R3(StartStopKey,EndStopKey>------------------------------------------------------------set statistics time offprint'===================================================='print ' 二 次 換 乘 查 詢 'print '----------------------------------------------------'set statistics time on-------------------------------------------------------------- 二 次 換 乘 查 詢selectss.StopName as 起 點 ,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 終 點 ,MinStopCountfrom(-- 查 詢 出 站 點 數(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