公交车路线查询系统后台数据库设计—关联地名和站点

来源:互联网 发布:淘宝直通车宝贝在哪里 编辑:程序博客网 时间:2024/03/29 19:24

在《公交车路线查询系统后台数据库设计——查询算法》一文中,已经实现了查询站点到站点的路线查询算法,但是,现实中用户不一定使用站点进行查询,而是使用地名。因此,公交车查询系统数据库必需记录地名与站点的对应关系,在查询时将地名映射为站点。根据实际情况,某一地点附近通常有几个站点,因此,地名与站点之间是多对多的关系。显然,只需创建一个地名站点关系表stop_spot(Stop,Spot)用于储存这个关系即可。数据库关系图如下:
单击显示全图,Ctrl+滚轮缩放图片

注:

Route:路线表
Stop:站点表
Spot:地名表
stop_route: 路线-站点关系表
stop_spot:地名-站点关系表


1.路线和地名信息管理:

以下函数用于管理公交车路线和地名的相关信息

字符串分割函数(信息处理的存储过程及查询相关的存储过程均需要使用到该函数) :

复制  保存
/*
函数功能:将@String以@SplitChar为分隔点分割为字符串数组,结果保留在表变量中
*/


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 ss.name,@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 s.name,@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 sst.name=est.name))
begin
raiserror ('起点集和终点集中含有相同的站点',16,1)
return
end
select
sst.name as 启始站点,
est.name as 目的站点,
r.Route as 乘坐线路,
r.StopCount as 经过的站点数
from
@ss_tab sst,
@es_tab est,
RouteT0 r
where
sst.name=r.StartStop
and r.EndStop=est.name
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,'/')
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 select name from @ss_tab
insert @stops select name from @es_tab

select
sst.name as 起始站点,
r1.Route as 乘坐路线1,
r1.EndStop as 中转站点1,
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 r2
where
sst.name=r1.StartStop
and r1.EndStop=r2.StartStop
and r2.EndStop=est.name
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,'/')
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 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
sst.name=r1.StartStop
and r1.EndStop=r2.StartStop
and r2.EndStop=r3.StartStop
and r3.EndStop=est.name
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


获取地名对应的站点:

复制  保存
/*
获取地名对应的站点,如有多个站点,用'/'隔开
*/


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


如要进行地名到地名的路线查询,必需先调用GetStopsOfSpot获取地名对应的所有站点,在调用Inquiry进行查询,例如:

复制  保存
declare @sps varchar(1024),@eps varchar(1024)
set @sps=dbo.GetStopsOfSpot('起始地点名称')
set @eps=dbo.GetStopsOfSpot('目的地点名称')
exec Inquiry @sps,@eps
原创粉丝点击