公交车路线查询系统后台数据库设计--引入步行路线

在《查询算法》和《关联地名和站点》两篇文章中,已经实现了通过地名或站点进行路线查询的算法,但是在现实中,从起点到终点不一定全程都是乘车,例如,有以下3条路线:

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

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

R3: S8->S9->S10

假如现在要从站点S1S7,如果用Inquiry查询路线,显然没有合适的乘车方案。但是S2S7相距仅仅一个站的距离,可以用步行代替,因此可以先S1乘坐R1S2再步行到S7

为了实现在乘车路线中插入步行路线,在数据库使用WalkRoute(StartStop, EndStop, Distance, Remark)(StartStop-起始站点,EndStop-目的站点,Distance-距离,Remark-备注)储存距离较近的两个站点。

加入表WalkRoute后,查询算法也要作相应的修改,其实WalkRouteRouteT0很相似,因此只需把WalkRoute看成是特殊的直达线路即可,修改后的InqueryT1如下: 

/* 
查询站点@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 

    declare @result table( 
        StartStop varchar(32), 
        Route1 varchar(256), 
        TransStop varchar(32), 
        Route2 varchar(256), 
        EndStop varchar(32), 
        StopCount int 
    ) 
    declare @count int 
    set @count=0 

    --查询"步行-乘车"路线 
    insert @result 
    select  
        sst.name as StartStop, 
        '从'+r1.StartStop+'步行到'+r1.EndStop as Route1, 
        r1.EndStop as TransStop, 
        r2.Route as Route2, 
        est.name as EndStop, 
        r2.StopCount as StopCount 
    from  
        @ss_tab sst, 
        @es_tab est, 
        (select * from WalkRoute 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 
    order by r2.StopCount 
    set @count=@@rowcount 

    --查询"乘车-步行"路线 
    insert @result 
    select  
        sst.name as StartStop, 
        r1.Route as Route1, 
        r1.EndStop as TransStop, 
        '从'+r2.StartStop+'步行到'+r2.EndStop as Route2, 
        est.name as EndStop, 
        r1.StopCount as StopCount 
    from  
        @ss_tab sst, 
        @es_tab est, 
        RouteT0 r1, 
        (select * from WalkRoute where StartStop not in (select name from @stops)) r2 
    where 
        sst.name=r1.StartStop 
        and r1.EndStop=r2.StartStop 
        and r2.EndStop=est.name 
    order by r1.StopCount 
    set @count=@count+@@rowcount 
     
    if(@count=0) 
    begin 
        --查询"乘车-乘车"路线 
        insert @result 
        select 
            sst.name as StartStop, 
            r1.Route as Route1, 
            r1.EndStop as TransStop, 
            r2.Route as Route2, 
            est.name as EndStop, 
            r1.StopCount+r2.StopCount as StopCount 
        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 
        order by r1.StopCount+r2.StopCount 
    end 

    select 
        StartStop as 起始站点, 
        Route1 as 路线1, 
        TransStop as 中转站点, 
        Route2 as 路线2, 
        EndStop as 目的站点, 
        StopCount as 总站点数 
    from 
        @result 
end
作者:卢春城
E-mail:mrlucc@126.com
出处:http://lucc.cnblogs.com/
本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接。
posted @ 2009-02-21 21:52  卢春城  阅读(6355)  评论(5编辑  收藏  举报