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

转：http://www.cnblogs.com/lucc/archive/2009/02/14/1390384.html

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