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

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

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

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

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

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

6、ndStop as中转站点,r2.Route as乘坐路线2,r2.EndStop as目的站点，rl.StopCou nt+r2.StopCou nt as总站点数fromRouteTO r1,RouteTO 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 inselectr1.StartStop as

7、启始站点，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.StartStopand r3.E ndStop=E

8、ndStopend4. 测试exec Inqu iryTOS1 , S2execInqu iryTIS1 ，S8S1 , S9execInqu iryT2运行结果：启始站点目的姑点 乘坐线路 经过的站点数LS152R11启姒站点乘尘蹈线1中转站点1乘坐畴2目的站点憶站点数1SIR12R2SS2启始站点乘坐圖线I中转站点1乘坐路线2中转站点2乘坐蹈线3自的站点总姑点数LSIR1S2R258R3S93公交车路线查询系统后台数据库设计-关联地名和站点在公交车路线查询系统后台数据库设计一一查询算法一文中，已经实现了查询站点到站点的路线查询算法，但是，现实中用户不一定使用站点进行 查询，而是使用地名。因

9、此，公交车查询系统数据库必需记录地名与站点的 对应关系，在查询时将地名映射为站点。根据实际情况，某一地点附近通常 有几个站点，因此，地名与站点之间是多对多的关系。显然，只需创建一个地名站点关系表stop_spot(Stop,Spot)用于储存这个关系即可。数据库关系图如下：SpotPKNameRemarkKoutePKNitmeRcmurkroutePKPK.FK1PKJK2PqsiitkinSt叩PKiimeRemarkSCop_spotPK.FKl PKTK2注:Route :路线表Stop :站点表Spot :地名表stop_route : 路线-站点关系表stop_spot :地名-站

10、点关系表1.路线和地名信息维护：以下函数用于维护公交车路线和地名的相关信息字符串分割函数(信息处理及路线查询的存储过程都需要使用到该函数)：/*函数功能：将String以SplitChar为分隔点分割为字符串数组，结果保留在表 变量中例如 SplitString(A/B,/)返回表：Value vin dexA 1B 2*/CREATE fun ctio n SplitStri ng(Stri ng varchar(2048),SplitChar char)returns res table(Value varchar(128),vin dex int)asbeg indeclare in d

11、ex in t, un it 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 in dexbeg insetun it=ltrim(rtrim(substri ng(Stri ng,i ndex,i next-i ndex)if un itbeg inin sert into res (value, vin dex) values (un it,i)set i=i+1endendset in dex=in ext+1endreturnend插入新的公车路线

12、：/*插入新的公交车路线Route:路线名Stops:公交车经过的所有站点，站点用-隔开*/CREATE proc In sertRoute(Route varchar(32),Stops_Str varchar(1024) asbeg indeclare stops table( name varchar(32),positi on int)insert stops( name,positi on)select Value,vl ndex from dbo.SplitStri ng(Stops_Str,-)beg in tran t1save tran sp1-插入路线信息in sert i

13、nto Route (n ame) values (Route)if(error0)beg inrollback 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(error0)beg inrollback tran sp1commit tran t1raiserror(插入路线时发生错误,16,1)returne

14、ndin sert stop_route(Stop,Route,Positi on)select ss.n ame,Route,ss.positi on from stops ssif(error0)beg inrollback tran sp1commit tran t1raiserror(插入路线时发生错误,16,1)returnendcommit tran t1end插入新地名函数：/*插入新地名name地名Stops:地名附近的所有站点，多个站点用/隔开Remark与地名相关的说明*/CREATE proc In sertSpot(n ame varchar(64),Stops_Str

15、 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=namfpom stops s where namenot in (select namefromstop)if(n)beg inraiserror (站点 s 不存在,16,1, n)returnendin se

16、rt into Spot (n ame,remark) values (n ame,remark)in sert stop_spot(Stop,Spot)select s.n ame, n ame from stops sif(error0)beg inraiserror (插入地点时发生错误,16,1)returnendend2.路线查询在公交车路线查询系统后台数据库设计查询算法 一文中，使用储存过程InquiryTO ，InquiryT1 和InquiryT2实现了站点到站点的查询，但是地名可能对应多个站点，因此，当进行地点到地点的查询相当于站点集到站点集的查询。因此，为了支持使用地名进行

17、查询，将InquiryTO ，InquiryT1和InquiryT2修改为站点集到站点集的查询：直达路线查询：/*查询站点StartStops到站点EndStopS之间的直达乘车路线，多个站点用/ 分开，如：exec InquiryTO 站点1/站点2,站点3/站点4*/CREATE proc InquiryT0(StartStopsvarchar(32),EndStops varchar(32)as beg indeclare ss_tab table( name varchar(32)declare es_tab table( name varchar(32)in sert ss_tab

18、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. name) beg inraiserror ( returnendselectsst. name as est. name as r.Route as r.StopCo unt as起点集和终点集中含有相同的站点启始站点，目的站点，乘坐线路，经

19、过的站点数,16,1)fromss_tab sst, es_tab est, RouteTO rwheresst. name=r.StartStopand r.En dStop=est. name end次换乘查询:/*查询站点StartStops到站点EndStops之间的一次换乘乘车路线，多个站点用/分开，如：exec InquiryTI 站点1/站点2,站点3/站点4*/CREATE proc InquiryT1(StartStops varchar(32),EndStops varchar(32) asbeg indeclare ss_tab table( name varchar(3

20、2)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 where sst. name=est .n ame)beg inraiserror (起点集和终点集中含有相同的站点,16,1)returnenddeclare stops

21、table( name varchar(32)in sert stops select n ame from ss_tabin sert stops select n ame from es_tab as起始站点，rl.Route as乘坐路线1,rl.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 (

22、select n ame fromstops) r1,RouteTO r2wheresst. name=r1.StartStopand r1.E ndStop=r2.StartStopand r2.E ndStop=est. nameand r1.Router2 .R outeend二次换乘查询：/*查询站点StartStops到站点EndStops之间的二次换乘乘车路线，多个站点用 /分开，如：exec InquiryT2 站点1/站点2,站点3/站点4*/CREATE proc In quiryT2(StartStops varchar(32),E ndStops varchar(32)

23、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. name=est .n ame)beg inraiserror (

24、起点集和终点集中含有相同的站点,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 selectrl.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.StopC

25、ou nt+r3.StopCou nt as总站点数fromss_tab 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 r1.E ndStop=r2.StartStopand r2.E ndStop=r3.StartStopand r3.E

26、ndStop=est. nameand r1.Router2 .R outeand r2.Router3 .R outeand r3.Router1 .R outeend综合查询：/*查询站点StartStops到站点EndStops之间的乘车路线,先查询直达路线， 如不存在，则查询一次换乘路线，如果直达和一次换乘均不存在，则查询二次换 乘多个站点用/分开，如：exec Inquiry 站点1/站点2,站点3/站点4*/CREATE proc In quiry(StartStops varchar(32),E ndStops varchar(32) asbeg inexec In quiryT

27、O StartStops,E ndStopsif(rowcou nt=0)beg inexec In quiryT1 StartStops,E ndStopsif(rowcou nt=0)beg inexec In quiryT2 StartStops,E ndStopsendendend如要进行地名到地名的路线查询，必需先调用GetStopsOfSpot获取地名对应的所有站点，在调用In quiry进行查询。获取地名对应的站点：/*获取地名对应的站点，如有多个站点，用/隔开*/CREATE fun ctio n GetStopsOfSpot(Spot varchar(32)returns v

28、archar(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(起始地点名称)set eps=dbo.GetStopsOfSpot(目的地点名称)exec Inquiry sps

29、,eps公交车路线查询系统后台数据库设计-引入步行路线在查询算法和关联地名和站点两篇文章中，已经实现了通过地名或 站点进行路线查询的算法，但是在现实中，从起点到终点不一定全程都是乘 车，例如，有以下3条路线：R1: S1-S2-S3-S4-S5R2: S6-S7-S2-S8R3: S8-S9-S10假如现在要从站点 S1到S7，如果用Inquiry 查询路线，显然没有合适的乘 车方案。但是S2和S7相距仅仅一个站的距离， 可以用步行代替，因此可以 先从S1乘坐R1到S2再步行到S7。为了实现在乘车路线中插入步行路线，在数据库使用WalkRoute(StartStop, En dStop, Di

30、sta nee,Remark) (StartStop-起始站点，EndStop- 目的站点，Distanee- 距离Remark-备注)储存距离较近的两个站点。加入表 WalkRoute后，查询算法也要作相应的修改，其实WalkRoute和RouteTO很相似，因此只需把 WalkRoute看成是特殊的直达线路即可，修改后的InqueryTI如下：/*查询站点StartStops到站点EndStops之间的一次换乘乘车路线，多个站点用/分开，如：exec InquiryTI 站点1/站点2, 站点3/站点4*/CREATE proc In quiryT1(StartStopsvarchar (

31、32),E ndStops varchar (32)asbegindeclaress_tabtable ( name varchar(32)declarees_tab table(name varchar(32)in sertss_tab selectValue from dbo.SplitStri ng(StartStops,in sertes_tab selectValue from dbo.SplitStri ng(E ndStops,/)/)not in ( select nameif (exists ( select * from ss_tab sst,es_tab estwhere

32、sst. name=est .n ame)begin raiserror（起点集和终点集中含有相同的站点 ，16,1）returnenddeclare stops table ( name varchar(32)in sertstops select n ame fromss_tabinsertstops select n ame fromes_tabdeclareresult table (StartStopvarchar (32),Route1varchar (256),Tran sStopvarchar (32),Route2varchar (256),En dStopvarchar (

33、32),StopCou ntint)declare co unt int setco un t=0-查询”步行-乘车”路线insert resultRoute1,in ( selectname fromRoute2,as Startstop, 从+r1.StartStop+ 步行到+r1.EndStop asr1.E ndStopas TransStop,r2.Routeas Route2,est. nameas EndStop,r2.StopCou ntas StopCountfromss_tab sst,es_tab est,( select * from WalkRout

34、e where EndStop not stops) r1,RouteTO r2wheresst. name=r1.StartStopand r1.E ndStop=r2.StartStopand r2.E ndStop=est. nameorder byr2.StopCo untset co un t=rowco unt-查询”乘车-步行”路线insert as StartStop,r1.Routeas Route1,r1.EndStopas TransStop,从+r2.StartStop+ 步行到+r2.EndSas En

35、dStop,r1.StopCountas StopCountfromss_tab sst,es_tab est,RouteT0 r1,( select * from WalkRoute where StartStop from stops) r2wheresst. name=r1.StartStopand rl.E ndStop=r2.StartStopand r2.E ndStop=est. nameorder by rl.StopCou ntset co un t=co un t+rowco untif (count=O)begin-查询”乘车-乘车”路线in sert resultsel

36、ectsst. name asStartStop,r1.RouteasRoute1,r1.EndStopas TransStop,r2.RouteasRoute2,est .n ameasEn dStop,r1.StopCount+r2.StopCountas StopCountfromss_tab sst,es_tab est,(select * from RouteT0 whereEndStopnot in ( select namefrom stops) r1,RouteT0 r2wheresst. name=r1.StartStopand r1.E ndStop=r2.StartSto

37、pand r2.En dStop=est. nameand r1.Router2.Routeorder by r1.StopCou nt+r2.StopCou ntendStartStopas 起始站点，Routelas路线1,Tran sStopas中转站点，Route2as路线2,En dStopas目的站点，StopCou ntas总站点数fromresultend公交车路线查询系统后台数据库设计-换乘算法改进与优化在查询算法一文中已经实现了换乘算法，但是，使用存储过程InquiryT2查询从 东圃镇”到 车陂路口 ”的乘车路线时，发现居然用了5分钟才查找出结果，这样的效率显然不适合实际

38、应用。因此，有必要对原有的换乘算法进 行优化和改进。在本文中，将给出一种改进的换乘算法，相比原有的算法， 改进后的算法功能更强，效率更优。1. 压缩 ” RouteTO假设RouteTO有以下几行SianStcpRouteEndSrupS1R1S2S1R2S23SIR3S24S2R4S34S2R5S35S3Rd547S3R7S4SS3RS549S3S4LO5R5ssR6S3S4S4SIS3$4$!SI5354RiR查询结果为:如下图所示，当查询 S1到S4的二次换乘路线时，将会产生3X 2X 4=24个结果段路线也是如此），事实上，换乘查询中关心的是两个站点之间有无线路可通，而不关心是乘坐什么

39、路线，因此，可以将RouteTO 压缩为:如下图所示，压缩后，查询结果有原来的24条合并1组第:!段路线用从图中可以看出，第1段路线中的3条线路的起点和站点都相同（第 2、3Sluice pS ranSt-op RouteshiidSro-pR1 R2 R3R4 R5S!JtS!5pEll JCR3第1段協线Fl第2段斶2R6 对 R RPEM蚀YtartStaEnd隔Rl JU RJ、R4RlSt討那E股跡线日RcotsuidSEcpSI匹$?SIR2S2SIS2S2R4S3S3S3酎S3RSS3RP那么，为什么要对视图 RouteTO 进行压缩呢，原因如下：RouteTO 是原有换乘算法频

40、繁使用的视图，因此， RouteTO的数据量直 接影响到查询的效率，压缩 RouteTO 可以减少RouteTO的数据量，加速查 询效率。(2)压缩RouteTO后，将中转站点相同的路线合并为1组，加速了对结果集排序的速度。2. 视图 GRouteTO在数据库中，将使用 GRouteTO 来描述压缩的RouteTO，由于本文使用的 数据库的关系图与 查询算法中有所不同，在给出 GRouteTO的代码前， 先说明一下：站点路进黃系表主要的改变是Stop_Route 使用了整数型的 RouteKey 和StopKey弓I用Route和Stop，而不是用路线名和站点名GRouteTO 定义如下：cr

41、eate view GRouteTOasselectStartStopKey,En dStopKey,min (StopCount)as MinStopCount,max(StopCount)as MaxStopCountfrom RouteT0 group by StartStopKey,E ndStopKey注意，视图 GRouteT0 不仅有StartStopKey 和EndStopKey 列，还有MinStopCount列，MinStopCount是指从 StartStop 至U EndStop 的最短线路的站点数。例如：上述RouteT0 对应的GRouteT0 为：S tanSto

42、pEndSiopbn StopCountXlaxStopCouDtSIS224S2S3.45S3S47103. 二次查询算法以下是二次换乘查询的存储过程Gin quiryT2的代码，该存储过程使用了临时表来提高查询效率：Gin quiryT2 /*查询站点StartStops到站点EndStops之间的二次换乘乘车路线，多个站点用/分开，结果以分组方式给出，如：exec Inqu iryT2 站点1/站点2, 站点3/站点4*/CREATE procGin quiryT2(StartStopsvarchar (2048),En dStopsvarchar(2048)asbegindeclare

43、ss_tabtable (StopKeyintdeclarees_tabtable (StopKeyintin sertss_tabselect dist inctStop.StopKeyfromdbo.SplitStri ng(StartStops,/)sn, Stopwhere sn. Value=Stop.StopNameinsert es_tabselect dist inctStop.StopKeyfrom dbo.SplitStri ng(E ndStops,/)sn ,Stopwhere sn. Value=Stop.StopNameif ( exists ( select to

44、psst.StopKey=est.StopKey)1 * fromss_tab sst,es_tab estwherebeginraiserror（起点集和终点集中含有相同的站点,16,1）returnenddeclarestopstable(StopKeyint )in sertstopsselectStopKeyfromss_tabin sertstopsselectStopKeyfromes_tabprint=：print 筛选出第1段乘车路线printset statistics time on-筛选出第1段乘车路线，保存到临时表 #R1中select*into#R1fromGRout

45、eTOwhereStartStopKey in (select StopKey from ss_tab)and EndStopKeynot in (Select StopKey from stops)order by StartStopKey,E ndStopKey-在临时表#R1上创建索引create indexindex1on #R1(StartStopKey,EndStopKey)set statistics time offprint=：print筛选出第3段乘车路线printset statistics time on-筛选出第3段乘车路线，保存到临时表 #R3中select*int

46、o#R3fromGRouteT0whereEn dStopKey in (select StopKey from es_tab)and StartStopKey not in (Select StopKey from stops)order byStartStopKey,E ndStopKey-在临时表上创建索引create indexindexlon #R3(StartStopKey,EndStopKey)set statistics time offprint=：print筛选出第2段乘车路线printset statistics time on-筛选出第2段乘车路线，保存到临时表 #R2

47、中select*into#R2fromGRouteTOwhereStartStopKey in (select EndStopKey from #R1)and EndStopKeyin (Select StartStopKey from #R3)-在临时表上创建索引create clustered indexindex1on #R2(StartStopKey,EndStopKey)create indexindex2on #R2(EndStopKey,StartStopKey)set statistics time offprint=print二次换乘查询printset statistics

48、 time on.次换乘查询selectss.StopName as 起点,dbo.Joi nRoute(res.StartStopKey,res.Tra nsStopKeyl) tsl.StopNameas 中转站 1,dbo. Join Route(res.Tra nsStopKey1,res.Tra nsStopKey2) ts2.StopNameas 中转站 2,dbo. Join Route(res.Tra nsStopKey2,res.E ndStopKey) es.StopNameas 终点,Mi nStopCou ntas路线1,as路线2,as路线3,from (-查询出站点数最少的select top 10r1.StartStopKeyr2.StartStopKeyr2.E ndStopKeyr3.E ndStopKey10组路线as StartStopKey,as TransStopKey1,as TransStopKey2,as EndStop