公交车路线查询系统后台数据库设计说明

1、公交车路线查询系统后台数据库设计-查询算法1. 公交车路线信息在数据库中的存储方式显然，如果在数据库中简单的使用表 bus_route（路线名,路线经过的站点，费用）来保存公交 车路线的线路信息，则很难使用查询语句实现乘车线路查询， 因此，应该对线路的信息进行 处理后再保存到数据库中，笔者使用的方法是用 站点-路线关系表stop_route（站点,路线名,站点在路线中的位置）来存储公交车路线，例如，如果有以下 3条路线R1 : S1->S2->S3->S4->S5R2 : S6->S7->S2->S8R3 : S8->S9->S10则对应的

2、站点-路线关系表stop_route 为StopRoutePositi onS1R11S2R12S3R13S4R14S5R15S6R21S7R22S2R23S8R24S8R31S9R32S10R33注：Stop为站点名，Route为路线名，Position为站点在路线中的位置2. 直达乘车路线查询算法基于表stop_route 可以很方便实现直达乘车路线的查询，以下是用于查询直达乘车路线的存储过程InquiryTO :create proc In quiryTO(StartStop varchar(32),E ndStop varchar(32)asbeg inselectsr1.Stop a

3、s 启始站点,sr2.Stop as 目 的站点，sr1.Route as 乘坐线路,fromstop_route sr1,stop_route sr2wheresr1.Route=sr2.Routeand srl.Position<sr2.Positionand sr1.Stop=StartStopand sr2.Stop=E ndStopend3. 查询换乘路线算法(1)直达路线视图直达路线视图 可以理解为一张存储了所有直达路线的表(如果两个站点之间存在直达路线,那么在直达路线视图中就有一行与之相对应)。例如R1,R2,R3 对应的RouteTO如下:起点终占 "匕八、乘坐

4、路线站点数S3S4R11S3S5R12S4S5R11S1S2R11S1S3R12S1S4R13S1S5R14S2S3R11S2S4R12S2S5R13S2S8R21S6S2R22S6S7R21S6S8R23S7S2R21S7S8R22S8S10R32S8S9R31S9S10R31RouteTO定义如下:create view RouteTOasselectsrl.Stop as StartStop,-启始站点sr2.Stop as EndStop,-目的站点srl.Route as Route,-乘坐线路sr2.Positio n-sr1.Position as StopCou nt-经过的站

5、点数fromstop_route sr1,stop_route sr2wheresr1.Route=sr2.Routeand sr1.Position<sr2.Position(2)换乘路线算法显然，一条换乘路线由若干段直达路线组成(每段路线的终点与下一段路线的起点相同)，因此，基于直达路线视图RouteTO可以很方便实现换乘查询，以下是实现一次换乘查询的存储过程 InquiryTI :create proc In quiryT1(StartStop varchar(32),E ndStop varchar(32)asbeg inselectr1.StartStop as 启始站点，r1

6、.Route as乘坐路线1,r1.EndStop as中转站点，r2.Route as乘坐路线2,r2.EndStop as目 的站点,r1.StopCou nt+r2.StopCou nt as总站点数fromRouteT0 r1,RouteT0 r2wherer1.StartStop=StartStopand r1.E ndStop=r2.StartStopand r2.E ndStop=E ndStopend同理可以得到二次换乘的查询语句create proc In quiryT2(StartStop varchar(32),E ndStop varchar(32) asbeg ins

7、electr1.StartStop as 启始站点，r1.Route as乘坐路线1,r1.E ndStop as 中转站点 1,r2.Route as乘坐路线2,r2.EndStop as 中转站点 2,r3.Route as乘坐路线3,总站点数r3.EndStop as 目 的站点，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. 测试execIn quiryTO'S1' ,'S2'execIn quiryT1'S1' ,'S8'execIn quiryT2'S1' ,'S9'运行结果：启始站点目的站点 乘坐线路 经辻的站点数LSI32R11启始站点乘坐路线1中转站点】乘尘牆线2 目的站点总站点数ISIR15222S32启始站点乘坐路线1中转站点1乘坐路线2中辂站点2乘坐略线W目的站点总站点数LSIR1S2R2S8時S931公交车路线查询系统后台数据库

9、设计-关联地名和站点在公交车路线查询系统后台数据库设计一一查询算法一文中，已经实现了查询站点到站点的路线查询算法，但是，现实中用户不一定使用站点进行 查询，而是使用地名。因此，公交车查询系统数据库必需记录地名与站点的 对应关系，在查询时将地名映射为站点。根据实际情况，某一地点附近通常 有几个站点，因此，地名与站点之间是多对多的关系。显然，只需创建一个 地名站点关系表stop_spot(Stop,Spot)用于储存这个关系即可。数据库关系 图如下:SpotPKNameRemarkKoutePKNitmeRcmurkroutePKPK.FK1PKJK2PqsiitkinSt叩PKiimeRemar

10、kSCop_spotPK.FKl PKTK2注：Route :路线表Stop :站点表Spot :地名表stop_route : 路线-站点关系表stop_spot :地名-站点关系表1.路线和地名信息维护：以下函数用于维护公交车路线和地名的相关信息字符串分割函数(信息处理及路线查询的存储过程都需要使用到该函数)：/*函数功能：将String以SplitChar为分隔点分割为字符串数组，结果保留在表变量中例如 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:公交车经过的所有站点，站点用'-'隔开*/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('插入路线时发生错误',16,1)returnend-插入不存在的站点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('插入路线时发生错误,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('插入路线时发生错误',16,1)returnendcommit tran t1end插入新地名函数：/*插入新地名name :地名Stops:地名附近的所有站点，多个站点用'/'隔开Remark:与地名相关的说明*/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 ('站点 %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 ('插入地点时发生错误',16,1)returnendend2.路线查询在公交车路线查询系统后台数据库设计查询算法 一文中，使用储存过程InquiryT0, InquiryT1

18、和InquiryT2实现了站点到站点的查询，但是地名可能对应多个站点，因此，当进行地点到地点的查询相当于站点集到站点集的查询。因此，为了支持使用地名进行查询， 将InquiryT0 ，InquiryT1 和InquiryT2 修改为站点集到站点集的查询：直达路线查询：/*查询站点StartStops到站点EndStops之间的直达乘车路线，多个站点用'/'分开，如：exec InquiryT0 '站点1/站点2','站点3/站点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 ('起点集和终点集中含有相同的站点,16,1) as启始站点， as 目 的站点，r.Route as 乘坐线路，r.StopCou nt as经过的站点数fromss_tab sst,es_tab est,RouteTO rwheresst. name=r.StartStopand r.En dStop=est. nameend一次换乘查询：/*查询站点StartStops到站点EndStops之间的一次换乘乘车路线，多个站 点用'/'分开，如：e

21、xec InquiryTI ' 站点1/站点2','站点3/站点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 ('起点集和终点集中含有相同的站点',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 起始站点,r1.Route as乘坐路线1,r1.E ndStop as中转站点 1,r2.Route as乘坐路线2, as目 的站点，r1.StopCou nt+r2.StopCou nt as总站点数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二次换乘查询：/*查询站点StartStops到站点EndStops之间的二次换乘乘车路线，多个站 点用'/'分开，如：exec InquiryT2 ' 站点1/站点2','站点3/站点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 ('起点集和终点集中含有相同的站点',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 启始站点，rl.Route as 乘坐路线1,rl.E ndStop as中转站点 1,r2.Route as 乘坐路线2,r2.EndStop as中转站点 2,r3.Route as 乘坐路线3,r3.EndStop as目 的站点，r1.StopCou nt+r2.StopCou nt+r3.StopCou nt as总站点数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综合查询：/*查询站点StartStops到站点EndStops之间的乘车路线，先查询直达路线，如不存在，则查询一次换乘路线，如果直达和一次换乘均不存在，则查询二次换 乘多个站点用'/'分开，如：exec Inquiry '站点1/站点2','站点3/站点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如要进行地名到地名的路线查询，必需先调用GetStopsOfSpot获取地名对应的所有站点，在调用In quiry 进行查询。获取地名对应的站点：/*获取地名对应的站点，如有多个站点，用'/'隔开*/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('起始地点名称'

31、;)set eps=dbo.GetStopsOfSpot('目的地点名称')exec Inquiry sps,eps公交车路线查询系统后台数据库设计-引入步行路线在查询算法和关联地名和站点两篇文章中，已经实现了通过地名或 站点进行路线查询的算法，但是在现实中，从起点到终点不一定全程都是乘车，例如，有以下3条路线：R1: S1->S2->S3->S4->S5R2: S6->S7->S2->S8R3: S8->S9->S10假如现在要从站点 S1到S7,如果用Inquiry 查询路线，显然没有合适的乘车方案。但是S2和S7相距仅

32、仅一个站的距离，可以用步行代替，因此可以 先从S1乘坐R1到S2再步行到S7。为了实现在乘车路线中插入步行路线，在数据库使用WalkRoute(StartStop,EndStop, Distanee, Remark)(StartStop- 起始站点，EndStop-目的站点,Distanee-距离Remark-备注)储存距离较近的两个站点。加入表 WalkRoute 后，查询算法也要作相应的修改，其实WalkRoute 和RouteTO 很相似，因此只需把WalkRoute 看成是特殊的直达线路即可，修改后的In queryTI如下:/*查询站点StartStops 到站点EndStops之间

33、的一次换乘乘车路线，多个站点用'/'分开,如：exec InquiryTI ' 站点1/站点2','站点3/站点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（'起点集和终点集中含有相同的站点,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起始站点，Route1 as 路线 1,TransStop as 中转站点，Route2 as 路线 2,EndStop as 目的站点，StopCountas总站点数fromresultend公交车路线查询系统后台数据库设计-换乘算法改进与优化在查询算法一文中已经实现了换乘算法，但是，使用存储过程InquiryT2查询从“东圃镇”到“车陂路口”的乘车路线时，发现居然用了5分钟才查找出结果，这样的效率显然不适合实际应用。因此，有必要对原有的换乘算 法进行优化和改进。在本文中，将给出一种改进的换乘算法，相比原有的算法，改进后的算法功能更

41、强，效率更优。1. “压缩”RouT0假设RouteTO有以下几行&tartStapRouteEndS topStopC aim：S1R1S22S1R2S23S1R3S24S?R4S34S2R5S35S3Rd54一7S3R7S4$S3RSS9S3闊S410 如下图所示，当查询 S1到S4的二次换乘路线时，将会产生3 X 2 X 4=24个结果第1段路线rl第2SSr2第3段路菱点勺型【淨|StrlS'rpEcdStM氐;叩E曲即SIR1521$1K1S?SIR1S2SIK2S251R2S251R2S2SIR3'S2 '- J沖>2SI訂S：则S1S2KIS

42、2R4欝R5SJr K5暨$3掘S4S35S!UJ$311 SJ1 R7S4$5S4$3| | S3冊S4S3ESS4S3RSS3S4S3AS4S3从图中可以看出，第1段路线中的3条线路的起点和站点都相同（第 2、3段路线也是如此），事实上，换乘查询中关心的是两个站点之间有无线路可通，而不关心是乘坐什么路线，因此，可以将RouteTO压缩为：如下图所示，压缩后，查询结果有原来的24条合并1组站财|$1R! R2 RJS3RCR' R3 RS54EfidS top31Rl R2IU甜K53"氏勺K皿屉1!曲仙RwiinBod眄51525JEtfr It ? RS R?54查询结

43、果为:S1S1S2S2乘坐K4/R5乘坐K4/R5S3S3乘坐尺倔7眼$爪9乘坐尺倔7眼$爪9那么，为什么要对视图 RouteTO进行压缩呢，原因如下:RouteTO 是原有换乘算法频繁使用的视图，因此， RouteTO的数据量直 接影响到查询的效率，压缩 RouteTO可以减少RouteTO的数据量，加速查 询效率。(2)压缩RouteTO后，将中转站点相同的路线合并为1组，加速了对结果集排序的速度。2. 视图 GRouteTO在数据库中，将使用 GRouteTO 来描述压缩的RouteTO，由于本文使用的 数据库的关系图与 查询算法中有所不同，在给出 GRouteTO的代码前, 先说明一下

44、：站点路迭关系表路线表站点表主要的改变是Stop_Route 使用了整数型的RouteKey和StopKey弓I用Route和Stop，而不是用路线名和站点名。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 的最短线路的站点数。例如：上述RouteTO 对应的GRouteTO 为：SrarEStopEndStopbn StopCountMaxStopCcuntS1S3'：24S2S343S3S47103. 二次查询算法以下是二次换乘查询的存储过程Gin quiryT2的代码，该存储过程使用了临时表来提高查询效率：Gin quiryT2/*查询站点StartStops 到站点EndStops之间的二次换乘乘车路线，多个站点用'/'分开,结果以分组方式给出，如：exec

46、InquiryT2 ' 站点1/站点2','站点3/站点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('起点集和终点集中含有相同的站点',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段乘车路线，保存到临时表 #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-在临时表#R1上创建索引create indexindex1 on #R1(StartStopKey,EndStopKey)set statistics time offprint '=print '筛选出第3段乘车路线print 'set statistics time on-筛选出第3段乘车路线，保存到临时表 #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-在临时表上创建索引create indexindex1 on #R3(StartStopKey,EndStopKey)set statistics time offprint '=print '筛选出第2段乘车路线print 'set statistics time on-筛选出第2段乘车路线，保存到临时表 #R2中select *into #R2from GRouteTOwhereStartStopKey in (

51、select EndStopKey from #R1)and EndStopKeyin (Select StartStopKey from #R3)在临时表上创建索引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 起点,dbo.JoinRoute(res.StartStopKey,res.TransStopKey1)as 路线 1,ts1.StopNameas 中转站 1,dbo.JoinRoute(res.TransStopKey1,res.TransStopKey2)as 路线 2,ts2.StopNameas 中转站 2,dbo.JoinRoute(res.TransStopKey2,res.EndStopKey)as 路线 3,es.StopName as 终点,Min StopCou ntfrom (-查询出站点数最少的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