公交路线查询系统数据库设计方案

1. 公 交 车 路 线 信 息 在 数 据 库 中 的 存 储 方 式显然，如果在数据库中简单的使用表bus_route(路线名,路线经过的站点,费用>来保存公交车路线的线路信息，则很难使用查询语句实现乘车线路查询，因此，应该对线路的信息进行处理后再保存到数据库中，考试大使用的方法是用站点 -路线关系表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可以很方便实现直达乘车路线的查询，以下是用于查询直达乘车路线的存储过程InquiryT0：createprocInquiryT0(@StartStopvarchar(32>,@EndStopvarchar(32>>asbeginselectsr1.Stopas启始站点,sr2.Stopas目的站点,sr1.Routeas乘坐线路,sr2.Position-sr1.Positionas经过的站点数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--经过的站点数fromstop_routesr1,stop_routesr2wheresr1.Route=sr2.Routeandsr1.Position<sr2.Position(2>换乘路线算法显然，一条换乘路线由若干段直达路线组成，因此，基于直达路线视图RouteT0可以很方便实现换乘查询，以下是实现一次换乘查询的存储过程InquiryT1：createprocInquiryT1(@StartStopvarchar(32>,@EndStopvarchar(32>>asbeginselectr1.StartStopas启始站点,r1.Routeas乘坐路线1,r1.EndStopas中转站点,r2.Routeas乘坐路线2,r2.EndStopas目的站点,r1.StopCount+r2.StopCountas总站点数fromRouteT0r1,RouteT0r2wherer1.StartStop=@StartStopandr1.EndStop=r2.StartStopandr2.EndStop=@EndStopend同理可以得到二次换乘的查询语句createprocInquiryT2(@StartStopvarchar(32>,@EndStopvarchar(32>>as2/8beginselectr1.StartStopas启始站点,r1.Routeas乘坐路线1,r1.EndStopas中转站点1,r2.Routeas乘坐路线2,r2.EndStopas中转站点2,r3.Routeas乘坐路线3,r3.EndStopas目的站点,r1.StopCount+r2.StopCount+r3.StopCountas总站点数fromRouteT0r1,RouteT0r2,RouteT0r3wherer1.StartStop=@StartStopandr1.EndStop=r2.StartStopandr2.EndStop=r3.StartStopandr3.EndStop=@EndStopend(3>.测试execInquiryT0’S1’,’S2’execInquiryT1’S1’,’S8’execInquiryT2’S1’,’S9’运行结果:那么有没有方法可以提高筛选第 2段路线的效率呢？答案是肯定的。只需把 GRouteT0改成实表，并创建索引就行了。修改成实表后，就不需要把第 2段路线缓存到临时表 #R2中，修改后的GInquiryT2(重命名为 GInquiryT2_1> 如下：GInquiryT2_1/*查询站点@StartStops 到站点@EndStops 之间的二次换乘乘车路线，多个站点用 '/'分开，结果以分组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 上 创 建 索 引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-- 在 临 时 表 上 创 建 索 引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 中 转 站 1,dbo.JoinRoute(res.TransStopKey1,res.TransStopKey2> as 路线2,ts2.StopName as 中 转 站 2,dbo.JoinRoute(res.TransStopKey2,res.EndStopKey> as 路线3,es.StopName as 终 点 ,MinStopCountfrom(-- 查 询 出 站 点 数 最 少 的 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