公交车路线查询系统后台数据库设计--查询算法
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 |
S1 |
R1 |
1 |
S2 |
R1 |
2 |
S3 |
R1 |
3 |
S4 |
R1 |
4 |
S5 |
R1 |
5 |
S6 |
R2 |
1 |
S7 |
R2 |
2 |
S2 |
R2 |
3 |
S8 |
R2 |
4 |
S8 |
R3 |
1 |
S9 |
R3 |
2 |
S10 |
R3 |
3 |
注: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如下:
起点 |
终点 |
乘坐路线 |
站点数 |
S3 |
S4 |
R1 |
1 |
S3 |
S5 |
R1 |
2 |
S4 |
S5 |
R1 |
1 |
S1 |
S2 |
R1 |
1 |
S1 |
S3 |
R1 |
2 |
S1 |
S4 |
R1 |
3 |
S1 |
S5 |
R1 |
4 |
S2 |
S3 |
R1 |
1 |
S2 |
S4 |
R1 |
2 |
S2 |
S5 |
R1 |
3 |
S2 |
S8 |
R2 |
1 |
S6 |
S2 |
R2 |
2 |
S6 |
S7 |
R2 |
1 |
S6 |
S8 |
R2 |
3 |
S7 |
S2 |
R2 |
1 |
S7 |
S8 |
R2 |
2 |
S8 |
S10 |
R3 |
2 |
S8 |
S9 |
R3 |
1 |
S9 |
S10 |
R3 |
1 |
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 InquiryT0 'S1','S2'
exec InquiryT1 'S1','S8'
exec InquiryT2 'S1','S9'
运行结果: