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

《公交车路线查询系统后台数据库的设计.doc》由会员分享，可在线阅读，更多相关《公交车路线查询系统后台数据库的设计.doc（41页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、公交车路线查询系统后台数据库设计转自：一、查询算法 实现站点到站点的换乘路线查询2公交车路线信息在数据库中的存储方式2直达乘车路线查询算法2查询换乘路线算法3直达路线视图3换乘路线算法3测试4二、关联地名和站点 实现通过地名或站点的路线查询5路线和地名信息维护6字符串分割函数6插入新的公车路线：7插入新地名函数：8路线查询91.1.6直达路线查询：9一次换乘查询：10二次换乘查询：11综合查询：12获取地名对应的站点:12三、引入步行路线 在乘车路线中插入步行路线13四、换乘算法的改进与优化 改进原查询算法，提高其查询效率17“压缩”RouteT017视图GRouteT019二次查询算法20测

2、试24效率优化29展开路线组35总结41一、 查询算法 实现站点到站点的换乘路线查询1.1 公交车路线信息在数据库中的存储方式显然，如果在数据库中简单的使用表bus_route(路线名,路线经过的站点,费用)来保存公交车路线的线路信息，则很难使用查询语句实现乘车线路查询，因此，应该对线路的信息进行处理后再保存到数据库中，笔者使用的方法是用站点-路线关系表stop_route(站点,路线名,站点在路线中的位置)来存储公交车路线,例如，如果有以下3条路线R1: S1-S2-S3-S4-S5R2: S6-S7-S2-S8R3: S8-S9-S10则对应的站点-路线关系表stop_route为Stop

3、RoutePositionS1R11S2R12S3R13S4R14S5R15S6R21S7R22S2R23S8R24S8R31S9R32S10R33注：Stop为站点名，Route为路线名，Position为站点在路线中的位置1.2 直达乘车路线查询算法基于表stop_route可以很方便实现直达乘车路线的查询，以下是用于查询直达乘车路线的存储过程InquiryT0：create proc InquiryT0(StartStop varchar(32),EndStop varchar(32)asbeginselect sr1.Stop as 启始站点,sr2.Stop as 目的站点,sr1.

4、Route as 乘坐线路,sr2.Position-sr1.Position as 经过的站点数from stop_route sr1,stop_route sr2where sr1.Route=sr2.Route and sr1.Positionsr2.Position and sr1.Stop=StartStop and sr2.Stop=EndStopend1.3 查询换乘路线算法1.1.1 直达路线视图直达路线视图可以理解为一张存储了所有直达路线的表(如果两个站点之间存在直达路线，那么在直达路线视图中就有一行与之相对应)create view RouteT0asselect sr1.

5、Stop as StartStop,-启始站点sr2.Stop as EndStop,-目的站点sr1.Route as Route,-乘坐线路ition as StopCount-经过的站点数fromstop_route sr1,stop_route sr2where sr1.Route=sr2.Route and 1.1.2 换乘路线算法显然，一条换乘路线由若干段直达路线组成，因此，基于直达路线视图RouteT0可以很方便实现换乘查询，以下是实现一次换乘查询的存储过程InquiryT1：create proc InquiryT1(StartStop varchar(32),EndStop

6、varchar(32)asbeginselectr1.StartStop as 启始站点,r1.Route as 乘坐路线1,r1.EndStop as 中转站点,r2.Route as 乘坐路线2,r2.EndStop as 目的站点,r1.StopCount+r2.StopCount as 总站点数from RouteT0 r1,RouteT0 r2wherer1.StartStop=StartStopand and r2.EndStop=EndStopend同理可以得到二次换乘的查询语句create proc InquiryT2(StartStop varchar(32),EndStop

7、 varchar(32)asbeginselectr1.StartStop as 启始站点,r1.Route as 乘坐路线1,r1.EndStop as 中转站点1,r2.Route as 乘坐路线2,r2.EndStop as 中转站点2,r3.Route as 乘坐路线3,r3.EndStop as 目的站点,r1.StopCount+r2.StopCount+r3.StopCount as 总站点数from RouteT0 r1,RouteT0 r2,RouteT0 r3wherer1.StartStop=StartStopand and and r3.EndStop=EndStope

8、nd1.4 测试exec InquiryT0 S1,S2exec InquiryT1 S1,S8exec InquiryT2 S1,S9运行结果:二、 关联地名和站点 实现通过地名或站点的路线查询在公交车路线查询系统后台数据库设计查询算法一文中，已经实现了查询站点到站点的路线查询算法，但是，现实中用户不一定使用站点进行查询，而是使用地名。因此，公交车查询系统数据库必需记录地名与站点的对应关系，在查询时将地名映射为站点。根据实际情况，某一地点附近通常有几个站点，因此，地名与站点之间是多对多的关系。显然，只需创建一个地名站点关系表stop_spot(Stop,Spot)用于储存这个关系即可。数据库

9、关系图如下：注：Route：路线表 Stop：站点表 Spot：地名表 stop_route： 路线-站点关系表 stop_spot：地名-站点关系表1.5 路线和地名信息维护以下函数用于维护公交车路线和地名的相关信息1.1.3 字符串分割函数（信息处理及路线查询的存储过程都需要使用到该函数) :/*函数功能：将String以SplitChar为分隔点分割为字符串数组，结果保留在表变量中*/create function SplitString(String varchar(2048),SplitChar char)returns res table(Value varchar(128),vi

10、ndex int)asbegindeclare index int,unit varchar(128),inext int,len int,i intset index=1set i=1set len=len(String)while indexindexbeginset unit=ltrim(rtrim(substring(String,index,inext-index)if unit begininsert into res (value,vindex) values (unit,i)set i=i+1endendset index=inext+1endreturnend1.1.4 插入

11、新的公车路线：/*插入新的公交车路线Route:路线名Stops:公交车经过的所有站点，站点用-隔开*/create proc InsertRoute(Route varchar(32),Stops_Str varchar(1024)asbegindeclare stops table(name varchar(32),position int)insert stops(name,position)select Value,vIndex from litString(Stops_Str,-)begin tran t1save tran sp1insert into Route (name) v

12、alues (Route)if(error0)beginrollback tran sp1commit tran t1raiserror(插入路线时发生错误,16,1)returnendinsert 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)returnendinsert stop_route(Stop,Route,Posit

13、ion)select ss.name,Route,ss.position from stops ssif(error0)beginrollback tran sp1commit tran t1raiserror(插入路线时发生错误,16,1)returnendcommit tran t1end1.1.5 插入新地名函数：/*插入新地名name：地名Stops:地名附近的所有站点，多个站点用/隔开Remark:与地名相关的说明*/create proc InsertSpot(name varchar(64),Stops_Str varchar(1024),Remark varchar(1024)

14、asbegin declare 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(n)beginraiserror (站点%s不存在,16,1,n)returnendinsert into Spot (name,remark) values (nam

15、e,remark)insert stop_spot(Stop,Spot)select s.name,name from stops sif(error0)beginraiserror (插入地点时发生错误,16,1)returnendend1.6 路线查询在公交车路线查询系统后台数据库设计查询算法一文中，使用储存过程InquiryT0，InquiryT1和InquiryT2实现了站点到站点的查询，但是地名可能对应多个站点，因此，当进行地点到地点的查询相当于站点集到站点集的查询。因此，为了实现地点到地点的查询，将InquiryT0，InquiryT1和InquiryT2修改为站点集到站点集的查询

16、。以下是与查询相关的函数和存储过程：1.1.6 直达路线查询：/*查询站点StartStops到站点EndStops之间的直达乘车路线，多个站点用/分开，如：exec InquiryT0 站点1/站点2,站点3/站点4*/create proc InquiryT0(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.SplitSt

17、ring(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)beginraiserror (起点集和终点集中含有相同的站点,16,1)returnendselectsst.name as 启始站点,est.name as 目的站点,r.Route as 乘坐线路,r.StopCount as 经过的站点数fromss_tab sst,es_tab est,Rout

18、eT0 rwhere sst.name=r.StartStop and end1.1.7 一次换乘查询：/*查询站点StartStops到站点EndStops之间的一次换乘乘车路线，多个站点用/分开，如：exec InquiryT1 站点1/站点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

19、 Value from litString(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)beginraiserror (起点集和终点集中含有相同的站点,16,1)returnenddeclare stops table(name varchar(32)insert stops select name from ss_tabinsert stops selec

20、t name from es_tabselectsst.name as 起始站点,r1.Route as 乘坐路线1,r1.EndStop as 中转站点,r2.Route as 乘坐路线2,est.name as 目的站点,r1.StopCount+r2.StopCount as 总站点数from ss_tab sst,es_tab est,(select * from RouteT0 where EndStop not in (select name from stops) r1,RouteT0 r2whereand and and end1.1.8 二次换乘查询：/*查询站点StartS

21、tops到站点EndStops之间的二次换乘乘车路线，多个站点用/分开，如：exec InquiryT2 站点1/站点2,站点3/站点4*/create proc InquiryT2(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 Va

22、lue from dbo.SplitString(EndStops,/)if(exists(select * from 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_tabselectr1.StartStop as 启始站点,r1.Route as 乘

23、坐路线1,r1.EndStop as 中转站点1,r2.Route as 乘坐路线2,r2.EndStop as 中转站点2,r3.Route as 乘坐路线3,dStop as 目的站点,r1.StopCount+r2.StopCount+r3.StopCount as 总站点数from ss_tab sst,es_tab est,(select * from RouteT0 where EndStop not in (select name from stops) r1,(select * from RouteT0 where EndStop not in (select name fro

24、m stops) r2,RouteT0 r3whereand and and and and and end1.1.9 综合查询：/*查询站点StartStops到站点EndStops之间的乘车路线,先查询直达路线，如不存在，则查询一次换乘路线，如果直达和一次换乘均不存在，则查询二次换乘多个站点用/分开，如：exec Inquiry 站点1/站点2,站点3/站点4*/create proc Inquiry(StartStops varchar(32),EndStops varchar(32)asbeginexec InquiryT0 StartStops,EndStopsif(rowcount

25、=0)beginexec InquiryT1 StartStops,EndStopsif(rowcount=0)beginexec InquiryT2 StartStops,EndStopsendendend1.1.10 获取地名对应的站点:/*获取地名对应的站点，如有多个站点，用/隔开*/create function GetStopsOfSpot(Spot varchar(32)returns varchar(1024)asbegindeclare stops varchar(1024)set stops=select stops=stops+/+stop from stop_spot w

26、here Spot=Spotreturn substring(stops,2,len(stops)-1)end如要进行地名到地名的路线查询，必需先调用GetStopsOfSpot获取地名对应的所有站点，在调用Inquiry进行查询使用地名查询乘车路线示例declare sps varchar(1024),eps varchar(1024)set sps=dbo.GetStopsOfSpot(起始地点名称)set eps=dbo.GetStopsOfSpot(目的地点名称)exec Inquiry sps,eps三、 引入步行路线 在乘车路线中插入步行路线在查询算法和关联地名和站点两篇文章中，已

27、经实现了通过地名或站点进行路线查询的算法，但是在现实中，从起点到终点不一定全程都是乘车，例如，有以下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, EndStop, Distance, Remark)(StartStop-起始站点,EndStop-目的站点,Distance-距离

28、,Remark-备注)储存距离较近的两个站点。加入表WalkRoute后，查询算法也要作相应的修改，其实WalkRoute和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(na

29、me 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=est.name) begin raiserror (起点集和终点集中含有相同的站点,16,1) return end decl

30、are stops table(name varchar(32) insert stops select 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 -查询步行-乘车路线 inser

31、t result select as StartStop, 从+r1.StartStop+步行到 as Route1, as TransStop, as Route2, as EndStop, 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 and r2.EndStop=est.

32、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, (select * from WalkRoute

33、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 as StartStop, as Route1, r1.EndStop as TransStop, r2.Route as Route2, es

34、t.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.Router2.Route order by r1.StopCount+r2.Sto

35、pCount end select StartStop as 起始站点, Route1 as 路线1, TransStop as 中转站点, Route2 as 路线2, EndStop as 目的站点, StopCount as 总站点数 from result end四、 换乘算法的改进与优化 改进原查询算法，提高其查询效率在查询算法一文中已经实现了换乘算法，但是，使用存储过程InquiryT2查询从“东圃镇”到“车陂路口”的乘车路线时，发现居然用了5分钟才查找出结果，这样的效率显然不适合实际应用。因此，有必要对原有的换乘算法进行优化和改进。在本文中，将给出一种改进的换乘算法，相比原有的算

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

37、减少RouteT0的数据量，加速查询效率。(2)压缩RouteT0后，将中转站点相同的路线合并为1组，加速了对结果集排序的速度。1.8 视图GRouteT0在数据库中，将使用GRouteT0来描述压缩的RouteT0，由于本文使用的数据库的关系图与查询算法中有所不同，在给出GRouteT0的代码前，先说明一下：主要的改变是Stop_Route使用了整数型的RouteKey和StopKey引用Route和Stop，而不是用路线名和站点名。GRouteT0定义如下： create view GRouteT0asselect StartStopKey, EndStopKey, min(StopCou

38、nt) as MinStopCount, max(StopCount) as MaxStopCountfrom RouteT0group by StartStopKey,EndStopKey 注意，视图GRouteT0不仅有StartStopKey和EndStopKey列，还有MinStopCount列，MinStopCount是指从StartStop到EndStop的最短线路的站点数。例如：上述RouteT0对应的GRouteT0为：1.9 二次查询算法以下是二次换乘查询的存储过程GInquiryT2的代码，该存储过程使用了临时表来提高查询效率： GInquiryT2/*查询站点StartS

39、tops到站点EndStops之间的二次换乘乘车路线，多个站点用/分开，结果以分组方式给出，如：exec InquiryT2 站点1/站点2,站点3/站点4*/CREATE proc GInquiryT2( StartStops varchar(2048), EndStops varchar(2048)asbegin declare ss_tab table(StopKey int) declare es_tab table(StopKey int) insert ss_tab select distinct Stop.StopKey from dbo.SplitString(StartStops,/) sn,Stop where insert es_tab select distinct Stop.StopKey from dbo.SplitString(EndStops,/) sn,Stop where if(exists(select top 1 * from ss_tab sst,es_tab est where sst.StopKey=est.StopKey) begin raiserror (起点集和终点集中含有相同的站点,16,1) return end declare st