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

1. 公交车路线信息在数据库中的存储方式

R1: S1->S2->S3->S4->S5

R2: S6->S7->S2->S8

R3: S8->S9->S10

 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

2.直达乘车路线查询算法

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)直达路线视图

 起点 终点 乘坐路线 站点数 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)换乘路线算法

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'

E-mail：mrlucc@126.com

posted @ 2009-02-11 23:27  卢春城  阅读(21052)  评论(25编辑  收藏  举报