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

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

2、onS1 R1 1S2 R1 2S3 R1 3S4 R1 4S5 R1 5S6 R2 1S7 R2 2S2 R2 3S8 R2 4S8 R3 1S9 R3 2S10 R3 3注：Stop 为站点名， Route 为路线名，Position 为站点在路线中的位置2.直达乘车路线查询算法基于表 stop_route 可以很方便实现直达乘车路线的查询，以下是用于查询直达乘车路线的存储过程 InquiryT0： create proc InquiryT0(StartStop varchar(32),EndStop varchar(32)asbeginselectsr1.Stop as 启始站点,sr2

3、.Stop as 目的站点,sr1.Route as 乘坐线路,sr2.Position-sr1.Position as 经过的站点数fromstop_route sr1,stop_route sr2wheresr1.Route=sr2.Routeand sr1.Positionindexbeginset unit=ltrim(rtrim(substring(String,index,inext-index)if unit0)beginrollback tran sp1commit tran t1raiserror(插入路线时发生错误,16,1)returnend-插入不存在的站点insert

4、 Stop(name)select distinct name from stops ss where name not in (select name from Stop)if(error0)beginrollback tran sp1commit tran t1raiserror(插入路线时发生错误,16,1)returnendcommit tran t1end插入新地名函数： /*插入新地名name：地名Stops:地名附近的所有站点，多个站点用/隔开Remark:与地名相关的说明*/CREATE proc InsertSpot(name varchar(64),Stops_Str va

5、rchar(1024),Remark varchar(1024)asbegindeclare stops table(name varchar(32)insert stops select distinct Value from dbo.SplitString(Stops_Str,/)declare n varchar(32)set n=select top 1 n=name from stops s where name not in (select name from stop)if(n0)beginraiserror (插入地点时发生错误,16,1)returnendend 2.路线查询

6、在公交车路线查询系统后台数据库设计查询算法一文中，使用储存过程 InquiryT0，InquiryT1 和 InquiryT2 实现了站点到站点的查询，但是地名可能对应多个站点，因此，当进行地点到地点的查询相当于站点集到站点集的查询。因此，为了支持使用地名进行查询，将InquiryT0，InquiryT1 和 InquiryT2 修改为站点集到站点集的查询：直达路线查询： /*查询站点StartStops 到站点EndStops 之间的直达乘车路线，多个站点用/分开，如：exec InquiryT0 站点 1/站点 2,站点 3/站点 4*/CREATE proc InquiryT0(Star

7、tStops varchar(32),EndStops varchar(32)asbegindeclare ss_tab table(name varchar(32)declare es_tab table(name varchar(32)insert ss_tab select Value from dbo.SplitString(StartStops,/)insert es_tab select Value from dbo.SplitString(EndStops,/)if(exists(select * from ss_tab sst,es_tab est where sst.name

8、=est.name)beginraiserror (起点集和终点集中含有相同的站点,16,1)returnendselectsst.name as 启始站点,est.name as 目的站点,r.Route as 乘坐线路,r.StopCount as 经过的站点数fromss_tab sst,es_tab est,RouteT0 rwheresst.name=r.StartStopand r.EndStop=est.nameend一次换乘查询： /*查询站点StartStops 到站点EndStops 之间的一次换乘乘车路线，多个站点用/分开，如：exec InquiryT1 站点 1/站点

9、 2,站点 3/站点 4*/CREATE proc InquiryT1(StartStops varchar(32),EndStops varchar(32)asbegindeclare ss_tab table(name varchar(32)declare es_tab table(name varchar(32)insert ss_tab select Value from dbo.SplitString(StartStops,/)insert es_tab select Value from dbo.SplitString(EndStops,/)if(exists(select * f

10、rom ss_tab sst,es_tab est where sst.name=est.name)beginraiserror (起点集和终点集中含有相同的站点,16,1)returnenddeclare stops table(name varchar(32)insert stops select name from ss_tabinsert stops select name from es_tabselectsst.name as 起始站点,r1.Route as 乘坐路线 1,r1.EndStop as 中转站点 1,r2.Route as 乘坐路线 2,est.name as 目的

11、站点,r1.StopCount+r2.StopCount as 总站点数fromss_tab sst,es_tab est,(select * from RouteT0 where EndStop not in (select name from stops) r1,RouteT0 r2wheresst.name=r1.StartStopand r1.EndStop=r2.StartStopand r2.EndStop=est.nameand r1.Router2.Routeand r2.Router1.Routeend综合查询： /*查询站点StartStops 到站点EndStops 之间

12、的乘车路线,先查询直达路线，如不存在，则查询一次换乘路线，如果直达和一次换乘均不存在，则查询二次换乘多个站点用/分开，如：exec Inquiry 站点 1/站点 2,站点 3/站点 4*/CREATE proc Inquiry(StartStops varchar(32),EndStops varchar(32)asbeginexec InquiryT0 StartStops,EndStopsif(rowcount=0)beginexec InquiryT1 StartStops,EndStopsif(rowcount=0)beginexec InquiryT2 StartStops,End

13、Stopsendendend如要进行地名到地名的路线查询，必需先调用 GetStopsOfSpot 获取地名对应的所有站点，在调用 Inquiry 进行查询。 获取地名对应的站点: /*获取地名对应的站点，如有多个站点，用/隔开*/CREATE function GetStopsOfSpot(Spot varchar(32)returns varchar(1024)asbegindeclare stops varchar(1024)set stops=select stops=stops+/+stop from stop_spot where Spot=Spotreturn substring

14、(stops,2,len(stops)-1)end 使用地名查询乘车路线示例：declare sps varchar(1024),eps varchar(1024)set sps=dbo.GetStopsOfSpot(起始地点名称 )set eps=dbo.GetStopsOfSpot(目的地点名称 )exec Inquiry sps,eps公交车路线查询系统后台数据库设计-引入步行路线在查询算法和关联地名和站点两篇文章中，已经实现了通过地名或站点进行路线查询的算法，但是在现实中，从起点到终点不一定全程都是乘车，例如，有以下 3 条路线：R1: S1-S2-S3-S4-S5R2: S6-S7-

15、S2-S8R3: S8-S9-S10假如现在要从站点 S1 到 S7，如果用 Inquiry 查询路线，显然没有合适的乘车方案。但是 S2 和 S7 相距仅仅一个站的距离，可以用步行代替，因此可以先从 S1 乘坐 R1 到 S2 再步行到 S7。为了实现在乘车路线中插入步行路线，在数据库使用WalkRoute(StartStop, EndStop, Distance, Remark)(StartStop-起始站点,EndStop-目的站点,Distance-距离,Remark-备注)储存距离较近的两个站点。加入表 WalkRoute 后，查询算法也要作相应的修改，其实 WalkRoute 和

16、RouteT0 很相似，因此只需把 WalkRoute 看成是特殊的直达线路即可，修改后的 InqueryT1 如下： /* 查询站点StartStops 到站点EndStops 之间的一次换乘乘车路线，多个站点用 /分开，如： exec InquiryT1 站点 1/站点 2,站点 3/站点 4 */ CREATE proc InquiryT1(StartStops varchar(32),EndStops varchar(32) as begin declare ss_tab table(name varchar(32) declare es_tab table(name varchar(

17、32) insert ss_tab select Value from dbo.SplitString(StartStops,/) insert es_tab select Value from dbo.SplitString(EndStops,/) if(exists(select * from ss_tab sst,es_tab est where sst.name=est.name) begin raiserror (起点集和终点集中含有相同的站点,16,1) return end declare stops table(name varchar(32) insert stops sel

18、ect name from ss_tab insert stops select name from es_tab declare result table( StartStop varchar(32), Route1 varchar(256), TransStop varchar(32), Route2 varchar(256), EndStop varchar(32), StopCount int ) declare count int set count=0 -查询“步行-乘车“ 路线 insert result select sst.name as StartStop, 从+r1.St

19、artStop+ 步行到+r1.EndStop as Route1, r1.EndStop as TransStop, r2.Route as Route2, est.name as EndStop, r2.StopCount as StopCount from ss_tab sst, es_tab est, (select * from WalkRoute where EndStop not in (select name from stops) r1, RouteT0 r2 where sst.name=r1.StartStop and r1.EndStop=r2.StartStop an

20、d r2.EndStop=est.name order by r2.StopCount set count=rowcount -查询“乘车-步行“ 路线 insert result select sst.name as StartStop, r1.Route as Route1, r1.EndStop as TransStop, 从+r2.StartStop+ 步行到+r2.EndStop as Route2, est.name as EndStop, r1.StopCount as StopCount from ss_tab sst, es_tab est, RouteT0 r1, (sel

21、ect * from WalkRoute where StartStop not in (select name from stops) r2 where sst.name=r1.StartStop and r1.EndStop=r2.StartStop and r2.EndStop=est.name order by r1.StopCount set count=count+rowcount if(count=0) begin -查询 “乘车-乘车 “路线 insert result select sst.name as StartStop, r1.Route as Route1, r1.E

22、ndStop as TransStop, r2.Route as Route2, est.name as EndStop, r1.StopCount+r2.StopCount as StopCount from ss_tab sst, es_tab est, (select * from RouteT0 where EndStop not in (select name from stops) r1, RouteT0 r2 where sst.name=r1.StartStop and r1.EndStop=r2.StartStop and r2.EndStop=est.name and r1

23、.Router2.Route order by r1.StopCount+r2.StopCount end select StartStop as 起始站点, Route1 as 路线 1, TransStop as 中转站点, Route2 as 路线 2, EndStop as 目的站点 , StopCount as 总站点数 from result end公交车路线查询系统后台数据库设计-换乘算法改进与优化在查询算法一文中已经实现了换乘算法，但是，使用存储过程InquiryT2 查询从“东圃镇”到“车陂路口”的乘车路线时，发现居然用了 5 分钟才查找出结果，这样的效率显然不适合实际应用。

24、因此，有必要对原有的换乘算法进行优化和改进。在本文中，将给出一种改进的换乘算法，相比原有的算法，改进后的算法功能更强，效率更优。1. “压缩 ”RouteT0假设 RouteT0 有以下几行如下图所示，当查询 S1 到 S4 的二次换乘路线时，将会产生 324=24个结果从图中可以看出，第 1 段路线中的 3 条线路的起点和站点都相同（第 2、3段路线也是如此），事实上，换乘查询中关心的是两个站点之间有无线路可通，而不关心是乘坐什么路线，因此，可以将 RouteT0 压缩为：如下图所示，压缩后，查询结果有原来的 24 条合并 1 组查询结果为：那么，为什么要对视图 RouteT0 进行压缩呢，

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

26、路线名和站点名。GRouteT0 定义如下： create view GRouteT0asselect StartStopKey,EndStopKey,min(StopCount) as MinStopCount,max(StopCount) as MaxStopCountfrom RouteT0group by StartStopKey,EndStopKey 注意，视图 GRouteT0 不仅有 StartStopKey 和 EndStopKey 列，还有MinStopCount 列，MinStopCount 是指从 StartStop 到 EndStop 的最短线路的站点数。例如：上述 R

27、outeT0 对应的 GRouteT0 为：3.二次查询算法以下是二次换乘查询的存储过程 GInquiryT2 的代码，该存储过程使用了临时表来提高查询效率： GInquiryT2/*查询站点StartStops 到站点EndStops 之间的二次换乘乘车路线，多个站点用 /分开，结果以分组方式给出，如：exec InquiryT2 站点 1/站点 2,站点 3/站点 4*/CREATE proc GInquiryT2(StartStops varchar(2048),EndStops varchar(2048)asbegindeclare ss_tab table(StopKey int)d

28、eclare es_tab table(StopKey int)insert ss_tab select distinct Stop.StopKey from dbo.SplitString(StartStops,/) sn,Stopwhere sn.Value=Stop.StopNameinsert es_tab select distinct Stop.StopKey from dbo.SplitString(EndStops,/) sn,Stopwhere sn.Value=Stop.StopNameif(exists(select top 1 * from ss_tab sst,es_

29、tab est where sst.StopKey=est.StopKey)beginraiserror (起点集和终点集中含有相同的站点,16,1)returnenddeclare stops table(StopKey int)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 GRout

30、eT0where StartStopKey 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 on-筛选出第 3 段乘车路线，保存到临时表#

31、R3 中select *into #R3from GRouteT0where EndStopKey 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 筛选出第 2 段乘车路线 print -set statistics tim

32、e on-筛选出第 2 段乘车路线，保存到临时表#R2 中select *into #R2from GRouteT0where StartStopKey in (select EndStopKey from #R1)and EndStopKey in (Select StartStopKey from #R3)-在临时表上创建索引create clustered index index1 on #R2(StartStopKey,EndStopKey)create index index2 on #R2(EndStopKey,StartStopKey)-set statistics time o

33、ffprint =print 二次换乘查询print -set statistics time on-二次换乘查询select ss.StopName as 起点,dbo.JoinRoute(res.StartStopKey,res.TransStopKey1) as 路线 1,ts1.StopName as 中转站 1,dbo.JoinRoute(res.TransStopKey1,res.TransStopKey2) as 路线2,ts2.StopName as 中转站 2,dbo.JoinRoute(res.TransStopKey2,res.EndStopKey) as 路线 3,es

34、.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,#R2 r2,#R3 r3where r1.EndStopKey=r2.

35、StartStopKey and r2.EndStopKey=r3.StartStopKeyorder by (r1.MinStopCount+r2.MinStopCount+r3.MinStopCount) asc)res,Stop ss,Stop es,Stop ts1,Stop ts2whereres.StartStopKey=ss.StopKey andres.EndStopKey=es.StopKey andres.TransStopKey1=ts1.StopKey andres.TransStopKey2=ts2.StopKey-set statistics time offpri

36、nt =end 4.测试(1) 测试环境测试数据：广州市 350 条公交车路线操作系统：Window XP SP2数据库：SQL Server 2000 SP4 个人版CPU：AMD Athlon(tm) 64 X2 Dual 2.4GHz内存： 2G(2）选择用于测试的的站点二次换乘查询的 select 语句使用的三张表#R1,#R2,#R3，因此，这三张表的数据量直接影响到二次换乘查询使用的时间：显然，R1 的数据量由起点决定，查询起始站点对应的#R1 的数据量的SQL 语句如下： select Stop.StopName as 站点 ,count(StartStopKey) #R1 的数

37、据量from RouteT0 full join Stop on RouteT0.StartStopKey=Stop.StopKeygroup by Stop.StopNameorder by count(StartStopKey) desc运行结果如下：显然，但起点为“东圃镇”时，#R1 的数据量最大，同理可得终点和 #R3 数据量关系如下：因此，在仅考虑数据量的情况下，查询“东圃镇”到“车陂路口”所用的时间是最长的。在下文中，将使用“东圃镇”作为起点，“车陂路口”为终点对二次查询算法进行效率测试(3)效率测试测试语句如下：exec GInquiryT2 东圃镇, 车陂路口 测试结果：查询结

38、果如下：输出如下： =筛选出第 1 段乘车路线-SQL Server 执行时间: CPU 时间 = 0 毫秒，耗费时间 = 0 毫秒。（所影响的行数为 458 行）SQL Server 执行时间: CPU 时间 = 10 毫秒，耗费时间 = 10 毫秒。SQL Server 分析和编译时间 : CPU 时间 = 0 毫秒，耗费时间 = 0 毫秒。SQL Server 执行时间: CPU 时间 = 0 毫秒，耗费时间 = 0 毫秒。SQL Server 执行时间: CPU 时间 = 0 毫秒，耗费时间 = 0 毫秒。=筛选出第 3 段乘车路线-SQL Server 执行时间: CPU 时间 = 0 毫秒，耗费时间 = 0 毫秒。（所影响的行数为 449 行）SQL Server 执行时间: CPU 时间 = 0 毫秒，耗费时间 = 9 毫秒。SQL Server 分析和编译时间 : CPU 时间 = 0 毫秒，耗费时间 = 0 毫秒。SQL Server 执行时间: CPU 时间 = 1 毫秒，耗费时间 = 1 毫秒。SQL Server 执行时间: CPU 时间 = 15 毫秒，耗费时间 = 1 毫秒。=