在《公交车路线查询系统后台数据库设计——查询算法》一文中，已经实现了查询站点到站点的路线查询算法，但是，现实中用户不一定使用站点进行查询，而是使用地名。因此，公交车查询系统数据库必需记录地名与站点的对应关系，在查询时将地名映射为站点。根据实际情况，某一地点附近通常有几个站点，因此，地名与站点之间是多对多的关系。显然，只需创建一个地名站点关系表stop_spot(Stop,Spot)用于储存这个关系即可。数据库关系图如下：
注：
Route：路线表
Stop：站点表
Spot：地名表
stop_route： 路线-站点关系表
stop_spot：地名-站点关系表
1.路线和地名信息维护：
以下函数用于维护公交车路线和地名的相关信息
字符串分割函数（信息处理及路线查询的存储过程都需要使用到该函数) :
CREATE function SplitString( @String varchar(2048), @SplitChar char)returns @res table( Value varchar(128), vindex int)asbegin 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 returnend
插入新的公车路线：
CREATE proc InsertRoute(@Route varchar(32),@Stops_Str varchar(1024))asbegin 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 t1end插入新地名函数：
CREATE proc InsertSpot(@name varchar(64),@Stops_Str varchar(1024),@Remark varchar(1024))asbegin 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 endend 2.路线查询
在《公交车路线查询系统后台数据库设计——查询算法》一文中，使用储存过程InquiryT0，InquiryT1和InquiryT2实现了站点到站点的查询，但是地名可能对应多个站点，因此，当进行地点到地点的查询相当于站点集到站点集的查询。因此，为了支持使用地名进行查询，将InquiryT0，InquiryT1和InquiryT2修改为站点集到站点集的查询：
直达路线查询：
CREATE proc InquiryT0(@StartStops varchar(32),@EndStops varchar(32))asbegin 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.nameend一次换乘查询：
CREATE proc InquiryT1(@StartStops varchar(32),@EndStops varchar(32))asbegin 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.Routeend二次换乘查询：
CREATE proc InquiryT2(@StartStops varchar(32),@EndStops varchar(32))asbegin 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.Routeend综合查询：
CREATE proc Inquiry(@StartStops varchar(32),@EndStops varchar(32))asbegin exec InquiryT0 @StartStops,@EndStops if(@@rowcount=0) begin exec InquiryT1 @StartStops,@EndStops if(@@rowcount=0) begin exec InquiryT2 @StartStops,@EndStops end endend
如要进行地名到地名的路线查询，必需先调用GetStopsOfSpot获取地名对应的所有站点，在调用Inquiry进行查询。
获取地名对应的站点:
CREATE function GetStopsOfSpot(@Spot varchar(32))returns varchar(1024)asbegin 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
