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

在《公交车路线查询系统后台数据库设计——查询算法》一文中，已经实现了查询站点到站点的路线查询算法，但是，现实中用户不一定使用站点进行查询，而是使用地名。因此，公交车查询系统数据库必需记录地名与站点的对应关系，在查询时将地名映射为站点。根据实际情况，某一地点附近通常有几个站点，因此，地名与站点之间是多对多的关系。显然，只需创建一个地名站点关系表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 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

如要进行地名到地名的路线查询，必需先调用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 

使用地名查询乘车路线示例：

declare @sps varchar(1024),@eps varchar(1024)
set @sps=dbo.GetStopsOfSpot('起始地点名称')
set @eps=dbo.GetStopsOfSpot('目的地点名称')
exec Inquiry @sps,@eps