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

合集下载
  1. 1、下载文档前请自行甄别文档内容的完整性,平台不提供额外的编辑、内容补充、找答案等附加服务。
  2. 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
  3. 3、如文档侵犯您的权益,请联系客服反馈,我们会尽快为您处理(人工客服工作时间:9:00-18:30)。

公交车路线查询系统后台数据库设计--查询算法
1. 公交车路线信息在数据库中的存储方式
显然,如果在数据库中简单的使用表bus_route(路线名,路线经过的站点,费用)来保存公交车路线的线路信息,则很难使用查询语句实现乘车线路查询,因此,应该对线路的信息进行处理后再保存到数据库中,笔者使用的方法是用站点-路线关系表stop_route(站点,路线名,站点在路线中的位置)来存储公交车路线,例如,如果有以下3条路线
R1: S1->S2->S3->S4->S5
R2: S6->S7->S2->S8
R3: S8->S9->S10
则对应的站点-路线关系表stop_route为
注:Stop为站点名,Route为路线名,Position为站点在路线中的位置
2.直达乘车路线查询算法
基于表stop_route可以很方便实现直达乘车路线的查询,以下是用于查询直达乘车路线的存储过程InquiryT0:
create proc InquiryT0(@StartStop varchar(32),@EndStop varchar(32)) as
begin
select
sr1.Stop as 启始站点,
sr2.Stop as 目的站点,
sr1.Route as 乘坐线路,
sr2.Position-sr1.Position as 经过的站点数
from
stop_route sr1,
stop_route sr2
where
sr1.Route=sr2.Route
and sr1.Position<sr2.Position
and sr1.Stop=@StartStop
and sr2.Stop=@EndStop
end
3.查询换乘路线算法
(1)直达路线视图
直达路线视图可以理解为一张存储了所有直达路线的表(如果两个站点之间存在直达路线,那么在直达路线视图中就有一行与之相对应)。

例如R1,R2,R3对应的RouteT0如下:
RouteT0定义如下:
create view RouteT0
as
select
sr1.Stop as StartStop, --启始站点
sr2.Stop as EndStop, --目的站点
sr1.Route as Route, --乘坐线路
sr2.Position-sr1.Position as StopCount --经过的站点数
from
stop_route sr1,
stop_route sr2
where
sr1.Route=sr2.Route
and sr1.Position<sr2.Position
(2)换乘路线算法
显然,一条换乘路线由若干段直达路线组成(每段路线的终点与下一段路线的起点相同),因此,基于直达路线视图RouteT0可以很方便实现换乘查询,以下是实现一次换乘查询的存储过程InquiryT1:
create proc InquiryT1(@StartStop varchar(32),@EndStop varchar(32)) as
begin
select
r1.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 r2
where
r1.StartStop=@StartStop
and r1.EndStop=r2.StartStop
and r2.EndStop=@EndStop
end
同理可以得到二次换乘的查询语句
create proc InquiryT2(@StartStop varchar(32),@EndStop varchar(32)) as
begin
select
r1.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 r3
where
r1.StartStop=@StartStop
and r1.EndStop=r2.StartStop
and r2.EndStop=r3.StartStop
and r3.EndStop=@EndStop
end
4.测试
exec InquiryT1 'S1','S8'
exec InquiryT2 'S1','S9'
运行结果:
公交车路线查询系统后台数据库设计--关联地名和站点
在《公交车路线查询系统后台数据库设计——查询算法》一文中,已经实现了查询站点到站点的路线查询算法,但是,现实中用户不一定使用站点进行查询,而是使用地名。

因此,公交车查询系统数据库必需记录地名与站点的对应关系,在查询时将地名映射为站点。

根据实际情况,某一地点附近通常有几个站点,因此,地名与站点之间是多对多的关系。

显然,只需创建一个地名站点关系表stop_spot(Stop,Spot)用于储存这个关系即可。

数据库关系图如下:
注:
Route:路线表
Stop:站点表
Spot:地名表
stop_route:路线-站点关系表
stop_spot:地名-站点关系表
1.路线和地名信息维护:
以下函数用于维护公交车路线和地名的相关信息
字符串分割函数(信息处理及路线查询的存储过程都需要使用到该函数) :
/*
函数功能:将@String以@SplitChar为分隔点分割为字符串数组,结果保留在表变量中
例如SplitString('A/B','/')返回表:
Value vindex
A 1
B 2
*/
CREATE function SplitString(
@String varchar(2048),
@SplitChar char
)
returns @res table(
Value varchar(128),
vindex int
)
as
begin
declare @index int,@unit varchar(128),@inext int,@len int,@i int set @index=1
set @i=1
set @len=len(@String)
while @index<=@len
begin
set @inext=charindex(@SplitChar,@String,@index)
if @inext=0 set @inext=@len+1
if @inext>@index
begin
set
@unit=ltrim(rtrim(substring(@String,@index,@inext-@index)))
if @unit<>''
begin
insert into @res (value,vindex) values (@unit,@i)
set @i=@i+1
end
end
set @index=@inext+1
end
return
end
插入新的公车路线:
/*
插入新的公交车路线
Route:路线名
Stops:公交车经过的所有站点,站点用'-'隔开
*/
CREATE proc InsertRoute(@Route varchar(32),@Stops_Str varchar(1024)) as
begin
declare @stops table(name varchar(32),position int)
insert @stops(name,position)
select Value,vIndex from dbo.SplitString(@Stops_Str,'-')
begin tran t1
save tran sp1
--插入路线信息
insert into Route (name) values (@Route)
if(@@error<>0)
begin
rollback tran sp1
commit tran t1
raiserror('插入路线时发生错误',16,1)
return
end
--插入不存在的站点
insert Stop(name)
select distinct name from @stops ss where name not in (select name from Stop)
if(@@error<>0)
begin
rollback tran sp1
commit tran t1
raiserror('插入路线时发生错误',16,1)
return
end
insert stop_route(Stop,Route,Position)
select ,@Route,ss.position from @stops ss
if(@@error<>0)
begin
rollback tran sp1
commit tran t1
raiserror('插入路线时发生错误',16,1)
return
end
commit tran t1
end
插入新地名函数:
/*
插入新地名
@name:地名
@Stops:地名附近的所有站点,多个站点用'/'隔开
@Remark:与地名相关的说明
*/
CREATE proc InsertSpot(
@name varchar(64),
@Stops_Str varchar(1024),
@Remark varchar(1024)
)
as
begin
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<>'')
begin
raiserror ('站点%s不存在',16,1,@n)
return
end
insert into Spot (name,remark) values (@name,@remark)
insert stop_spot(Stop,Spot)
select ,@name from @stops s
if(@@error<>0)
begin
raiserror ('插入地点时发生错误',16,1)
return
end
end
2.路线查询
在《公交车路线查询系统后台数据库设计——查询算法》一文中,使用储存过程InquiryT0,InquiryT1和InquiryT2实现了站点到站点的查询,但是地名可能对应多个站点,因此,当进行地点到地点的查询相当于站点集到站点集的查询。

因此,为了支持使用地名进行查询,将InquiryT0,InquiryT1和InquiryT2修改为站点集到站点集的查询:
直达路线查询:
/*
查询站点@StartStops到站点@EndStops之间的直达乘车路线,多个站点用'/'分开,如:
exec InquiryT0 '站点1/站点2','站点3/站点4'
*/
CREATE proc InquiryT0(@StartStops varchar(32),@EndStops varchar(32)) as
begin
declare @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
=))
begin
raiserror ('起点集和终点集中含有相同的站点',16,1)
return
end
select
as 启始站点,
as 目的站点,
r.Route as 乘坐线路,
r.StopCount as 经过的站点数
from
@ss_tab sst,
@es_tab est,
RouteT0 r
where
=r.StartStop
and r.EndStop=
end
一次换乘查询:
/*
查询站点@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(32))
insert @ss_tab select Value from dbo.SplitString(@StartStops,'/')
if(exists(select * from @ss_tab sst,@es_tab est where
=))
begin
raiserror ('起点集和终点集中含有相同的站点',16,1)
return
end
declare @stops table(name varchar(32))
insert @stops select name from @ss_tab
insert @stops select name from @es_tab
select
as 起始站点,
r1.Route as 乘坐路线1,
r1.EndStop as 中转站点1,
r2.Route as 乘坐路线2,
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 r2
where
=r1.StartStop
and r1.EndStop=r2.StartStop
and r2.EndStop=
and r1.Route<>r2.Route
end
二次换乘查询:
/*
查询站点@StartStops到站点@EndStops之间的二次换乘乘车路线,多个站点用'/'分开,如:
exec InquiryT2 '站点1/站点2','站点3/站点4'
*/
CREATE proc InquiryT2(@StartStops varchar(32),@EndStops
varchar(32))
as
begin
declare @ss_tab table(name varchar(32))
declare @es_tab table(name varchar(32))
insert @ss_tab select Value from dbo.SplitString(@StartStops,'/')
if(exists(select * from @ss_tab sst,@es_tab est where
=))
begin
raiserror ('起点集和终点集中含有相同的站点',16,1)
return
end
declare @stops table(name varchar(32))
insert @stops select name from @ss_tab
insert @stops select name from @es_tab
select
r1.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
@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 from @stops)) r2,
RouteT0 r3
where
=r1.StartStop
and r1.EndStop=r2.StartStop
and r2.EndStop=r3.StartStop
and r3.EndStop=
and r1.Route<>r2.Route
and r2.Route<>r3.Route
and r3.Route<>r1.Route
end
综合查询:
/*
查询站点@StartStops到站点@EndStops之间的乘车路线,先查询直达路线,
如不存在,则查询一次换乘路线,如果直达和一次换乘均不存在,则查询二次换乘
多个站点用'/'分开,如:
exec Inquiry '站点1/站点2','站点3/站点4'
*/
CREATE proc Inquiry(@StartStops varchar(32),@EndStops varchar(32)) as
begin
exec InquiryT0 @StartStops,@EndStops
if(@@rowcount=0)
begin
exec InquiryT1 @StartStops,@EndStops
if(@@rowcount=0)
begin
exec InquiryT2 @StartStops,@EndStops
end
end
end
如要进行地名到地名的路线查询,必需先调用GetStopsOfSpot获取地名对应的所有站点,在调用Inquiry进行查询。

获取地名对应的站点:
/*
获取地名对应的站点,如有多个站点,用'/'隔开
*/
CREATE function GetStopsOfSpot(@Spot varchar(32))
returns varchar(1024)
as
begin
declare @stops varchar(1024)
set @stops=''
select @stops=@stops+'/'+stop from stop_spot where Spot=@Spot
return substring(@stops,2,len(@stops)-1)
end
使用地名查询乘车路线示例:
公交车路线查询系统后台数据库设计--引入步行路线
在《查询算法》和《关联地名和站点》两篇文章中,已经实现了通过地名或站点进行路线查询的算法,但是在现实中,从起点到终点不一定全程都是乘车,例如,有以下3条路线:
R1: S1->S2->S3->S4->S5
R2: S6->S7->S2->S8
R3: S8->S9->S10
假如现在要从站点S1到S7,如果用Inquiry查询路线,显然没有合适的乘车方案。

但是S2和S7相距仅仅一个站的距离,可以用步行代替,因此可以先从S1乘坐R1到S2再步行到S7。

为了实现在乘车路线中插入步行路线,在数据库使用
WalkRoute(StartStop, EndStop, Distance,
Remark)(StartStop-起始站点,EndStop-目的站点,Distance-距
离,Remark-备注)储存距离较近的两个站点。

加入表WalkRoute后,查询算法也要作相应的修改,其实WalkRoute和RouteT0很相似,因此只需把WalkRoute看成是特殊的直达线路即可,修改后的InqueryT1如下:
StartStop as 起始站点,
Route1 as 路线1,
TransStop as 中转站点,
Route2 as 路线2,
EndStop as 目的站点,
StopCount as 总站点数
from
@result
end
公交车路线查询系统后台数据库设计--换乘算法改进与优化
在《查询算法》一文中已经实现了换乘算法,但是,使用存储过程InquiryT2查询从“东圃镇”到“车陂路口”的乘车路线时,发现居然用了5分钟才查找出结果,这样的效率显然不适合实际应用。

因此,有必要对原有的换乘算法进行优化和改进。

在本文中,将给出一种改进的换乘算法,相比原有的算法,改进后的算法功能更强,效率更优。

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

(2)压缩RouteT0后,将中转站点相同的路线合并为1组,加速了对结果集排序的速度。

2.视图GRouteT0
在数据库中,将使用GRouteT0来描述压缩的RouteT0,由于本文使用的数据库的关系图与《查询算法》中有所不同,在给出GRouteT0的代码前,先说明一下:
主要的改变是Stop_Route使用了整数型的RouteKey和StopKey引用Route和Stop,而不是用路线名和站点名。

GRouteT0定义如下:
create view GRouteT0
as
select
StartStopKey,
EndStopKey,
min(StopCount) as MinStopCount,
max(StopCount) as MaxStopCount
from RouteT0
group by StartStopKey,EndStopKey
注意,视图GRouteT0不仅有StartStopKey和EndStopKey列,还有MinStopCount列,MinStopCount是指从StartStop到EndStop的最短线路的站点数。

例如:上述RouteT0对应的GRouteT0为:
3.二次查询算法
以下是二次换乘查询的存储过程GInquiryT2的代码,该存储过程使用了临时表来提高查询效率:
GInquiryT2
/*
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的数据量' from RouteT0 full join Stop on RouteT0.StartStopKey=Stop.StopKey group by Stop.StopName
order by count(StartStopKey) desc
运行结果如下:
显然,但起点为“东圃镇”时,#R1的数据量最大,同理可得终点和#R3数据量关系如下:
因此,在仅考虑数据量的情况下,查询“东圃镇”到“车陂路口”所用的时间是最长的。

在下文中,将使用“东圃镇”作为起点,“车陂路口”为终点对二次查询算法进行效率测试
(3)效率测试
测试语句如下:
exec GInquiryT2 '东圃镇','车陂路口'
测试结果:
查询结果如下:
输出如下:
====================================================
筛选出第1段乘车路线
----------------------------------------------------
SQL Server 执行时间:
CPU 时间 = 0 毫秒,耗费时间 = 0 毫秒。

(所影响的行数为 458 行)
从消息窗口的信息可以看出,查询大概用了1秒
5.效率优化
用GInquiryT2查询“东圃镇”到“车陂路口”仅用了1秒钟,那么,还能不能再优化呢?仔细分析输出的结果,可发现查询时最耗时的并不是换乘查询语句(140ms),而是筛选出第2段查询路线的语句(825ms),如图所示:
那么有没有方法可以提高筛选第2段路线的效率呢?答案是肯定的。

只需把GRouteT0改成实表,并创建索引就行了。

修改成实表后,就不需要把第2段路线缓存到临时表#R2中,修改后的GInquiryT2(重命名为GInquiryT2_1)如下:
/*
查询站点@StartStops到站点@EndStops之间的二次换乘乘车路线,多个站点用'/'分开,结果以分组方式给出,如:
exec GInquiryT2_1 '站点1/站点2','站点3/站点4'
*/
CREATE proc GInquiryT2_1(
@StartStops varchar(2048),
下面,仍然用查询“东圃镇”到“车陂路口”为例测试改成实表后GInquiryT2的效率,测试语句如下:
消息窗口输出如下:
从输出可以看出,大概用了250ms
6.展开路线组
GInquiryT2查询给出的结果是10组最短路线,那么,怎样才能得到最短的10条路线,显然,只需将这10组路线展开即可(展开后的路线数>=10),而最短的10条路线必然在展开的结果中。

查询10条最短路线的存储过程GInquiryT2_Expand如下:
下面,仍然以查询“东圃镇”到“车陂路口”为例测试GInquiryT2_Expand,代码如下:
查询结果如下:
消息窗口输出如下:
==================================================== 筛选出第1段乘车路线
----------------------------------------------------
SQL Server 执行时间:
CPU 时间 = 0 毫秒,耗费时间 = 0 毫秒。

(所影响的行数为 458 行)
SQL Server 执行时间:
CPU 时间 = 0 毫秒,耗费时间 = 3 毫秒。

SQL Server 分析和编译时间:
CPU 时间 = 0 毫秒,耗费时间 = 0 毫秒。

SQL Server 执行时间:
CPU 时间 = 0 毫秒,耗费时间 = 0 毫秒。

SQL Server 执行时间:
CPU 时间 = 0 毫秒,耗费时间 = 1 毫秒。

====================================================
由输出结果可看出,大约用了300ms
7.总结
下面,对本文使用的优化策略做一下总结:
(1)使用临时表;
(2)将频繁使用的视图改为表;
(3)从实际出发,合并RouteT0中类似的行,从而“压缩”RouteT0的数据量,减少查询生成的结果,提高查询和排序效率。

相关文档
最新文档