有关公交路由算法

[willdavis]

跟公交换乘有点类似的路由算法
A点 B点
广州 深圳
深圳 珠海
江门 珠海
东莞 深圳
湛江 江门

现在我想从广州到东莞，那就有 广州——》深圳——》东莞
广州到江门：广州——》深圳——》珠海——》江门

比如 广州到湛江 ，他的路由应该是：
(广州 深圳)——》（深圳 珠海）——》(江门 珠海)——》（湛江 江门)

有没有哪位兄弟接触过这种算法，指导一下

[feilniu]

--城市路由表：典型的无向图邻接表
CREATE TABLE CityRoute(
	A nvarchar(10) NOT NULL,
	B nvarchar(10) NOT NULL,
	CONSTRAINT PK_CityRoute PRIMARY KEY CLUSTERED(A,B),   --每个路由只记录一次
	CONSTRAINT CK_CityRoute_Undirected_graph CHECK(A < B) --无向图只需要记录(X,Y)，不需要再记录(Y,X)
)
GO
--可以建一个INSTEAD OF触发器，处理A,B的方向问题
CREATE TRIGGER TR_CityRoute_INSERT
ON CityRoute
INSTEAD OF INSERT
AS
INSERT INTO CityRoute
SELECT A,B
FROM inserted
WHERE A < B
UNION ALL
SELECT B,A
FROM inserted
WHERE A > B
GO
--城市路由视图，生成双向路由。也可以在表中直接存储双向路由，以空间换时间。
CREATE VIEW vwCityRoute
AS
SELECT A,B
FROM CityRoute
UNION ALL
SELECT B,A
FROM CityRoute
GO
--示例数据
INSERT INTO CityRoute VALUES(N'广州',N'深圳')
INSERT INTO CityRoute VALUES(N'深圳',N'珠海')
INSERT INTO CityRoute VALUES(N'江门',N'珠海')
INSERT INTO CityRoute VALUES(N'东莞',N'深圳')
INSERT INTO CityRoute VALUES(N'湛江',N'江门')
GO
--查询
DECLARE @CityFrom nvarchar(10)
DECLARE @CityTo   nvarchar(10)
SET @CityFrom = N'广州'
SET @CityTo   = N'湛江'
--采用递归CTE实现广度优先查找
;WITH CityPath AS(
	SELECT
		CityFrom = A,
		CityPath = CAST(A + '->' + B AS nvarchar(1000)),
		CityTo   = B
	FROM vwCityRoute
	WHERE A = @CityFrom
	UNION ALL
	SELECT
		CityFrom = cp.CityFrom,
		CityPath = CAST(cp.CityPath + '->' + c_next.B AS nvarchar(1000)),
		CityTo   = c_next.B
	FROM CityPath cp
	INNER JOIN vwCityRoute c_next
	ON cp.CityTo = c_next.A
		AND cp.CityPath NOT LIKE '%'+c_next.B+'%'  --防止出现环路
)
SELECT *
FROM CityPath
WHERE CityTo = @CityTo
OPTION (MAXRECURSION 0)  --0表示不限制递归次数；服务器默认为100（在本例100就够用了）。
GO

[thinclient]

强烈关注

[thinclient]

参考以下网文:

SQL与最短路径算法(1)
http://database.51cto.com 2006-07-26 15:37 IT专家网 我要评论(0)
摘要：题目:空间有若干个点，每个点之间的联系都是随机的，现求任意一个点(设为A)到另一任意点(设为Z)之间间隔最少其他点的最佳算法(可用SQL数据库)
标签：SQL 算法
Oracle帮您准确洞察各个物流环节
题目:空间有若干个点，每个点之间的联系都是随机的，现求任意一个点(设为A)到另一任意点(设为Z)之间间隔最少其他点的最佳算法(可用SQL数据库)

约束:在一个点中只可以直接找出和它有直接联系的点

用途:通过朋友列表以最快的速度认识一个认识的人(MM/GG)

比如5的好友列表中有1，30，3

7的好友列表中有9，5，8

10的好友列表中有7，21，30

11的好友列表中有7，5，30

21的好友列表中有7，30，66

30的好友列表中有21，88，99

如果5要和7交朋友，则可通过5-11-7。而5-30-21-7是较长的路径。

各位大虾有什么绝招能在SQL里实现这算法?

--如果全部建立双向关联，可以试试看下面的语句

declare @t table
(
id int,
f_id varchar(20)
)
insert into @t
select 5,'1,7,30,3' union all
select 7,'11,21,9,5,8' union all
select 11,'7,21,30' union all
select 21,'7,11,30,66' union all
select 30,'5,11,21,88,99'
--select * from @t
declare @start int
declare @end int
declare @node int
declare @count int
declare @result varchar(100)
set @count=0
set @start=5
set @end=11
set @result=''
declare @tmp table
(
id int,
f_id varchar(20),
step int
)
insert into @tmp select @start,'',@count
while @end not in (select id from @tmp)
begin
set @count=@count+1
insert into @tmp
select distinct a.id,a.f_id,@count from @t a,@tmp b where charindex(rtrim(b.id),a.f_id)>0 and a.id not in (select id from @tmp)
end
select @result=rtrim(@count)+':'+rtrim(@end)
while @count>1
begin
set @count=@count-1
select top 1 @end=id from @tmp where step=@count and charindex(rtrim(@end),f_id)>0
select @result=rtrim(@count)+':'+rtrim(@end)+'/'+@result
end
select @result='0:'+rtrim(@start)+'/'+@result
select @result


/*
0:5/1:7/2:11
*/

[htl258_Tony]

ASP.Net + SQL2005 公交查询系统系统源码 http://download.csdn.net/source/2379817

[zhengduan964532]

乘车线路查询 收藏 
  


公交站点-线路表 Stop_Route(Stop,Route,Seq)
Stop=站点 Route=线路 Seq=此站在该线路中的顺序
Stop Route Seq
 a 2 1
 b 2 2
 c 2 3
 d 2 4
 f 4 1
 g 4 2
 c 4 3
 r 4 4

想用这一个表来进行站站查询，求大侠帮忙！
就是通过起始站名和目的站名，来查询乘车线路和经过站数的SQL语句。
 create table #tt(Stop varchar(10),Route int,Seq int)
 insert #tt select  'a', 2 ,1
insert #tt select   'b', 2 ,2
 insert #tt select  'c', 2 ,3
 insert #tt select  'd', 2 ,4
 insert #tt select  'f', 4 ,1
 insert #tt select  'g', 4 ,2
 insert #tt select  'c', 4 ,3
 insert #tt select  'r', 4 ,4
 
 
select Route,stuff((select N'-'+Stop from #tt where a.Route=Route order by Seq for xml path('')),1,1,'') from #tt a
group by Route

Route       
----------- ------------
2           a-b-c-d
4           f-g-c-r

(2 行受影响)

背景
有如下表示乘车线路和站点的数据，要求查询出指定站点之间的所有乘车线路：
USE tempdb
GO
 
-- 模拟数据
SET NOCOUNT ON
IF OBJECT_ID(N'tempdb..#tb') IS NOT NULL
    DROP TABLE #tb
CREATE TABLE #tb(
    id int IDENTITY 
        PRIMARY KEY,
    lineID int, 
    state nvarchar(10), 
    orderid int
)
INSERT #tb(
     lineID, state, orderid)
SELECT 1, N'广州东', 1 UNION ALL
SELECT 1, N'体育中心', 2 UNION ALL
SELECT 1, N'体育西', 3 UNION ALL
SELECT 1, N'烈士陵园', 4 UNION ALL
SELECT 1, N'公园前', 5 UNION ALL
SELECT 1, N'西门口', 6 UNION ALL
SELECT 2, N'火车站', 1 UNION ALL
SELECT 2, N'纪念堂', 2 UNION ALL
SELECT 2, N'公园前', 3 UNION ALL
SELECT 2, N'中大', 4 UNION ALL
SELECT 2, N'客村', 5 UNION ALL
SELECT 2, N'琶洲', 6 UNION ALL
SELECT 2, N'万胜围', 7 UNION ALL
SELECT 3, N'广州东', 1 UNION ALL
SELECT 3, N'体育西', 2 UNION ALL
SELECT 3, N'珠江新城', 3 UNION ALL
SELECT 3, N'客村', 4 UNION ALL
SELECT 3, N'市桥', 5 UNION ALL
SELECT 4, N'万胜围', 1 UNION ALL
SELECT 4, N'金洲', 2
 
CREATE INDEX IX_lineID 
    ON #tb(
        lineID)
 
CREATE INDEX IX_state 
    ON #tb(
        state)
 
CREATE INDEX IX_orderid 
    ON #tb(
        orderid)
GO
 
处理方法：
之前也有发表过一些如何处理这个问题的方法，但效率不是太好。下面的这种方法加上了乘车方向的考虑：同一条线路上，只有两个乘车方向，而且一旦方向了，就不会再反向乘车（因为是从这个方向来，再坐回去是不合理的）；如果某个站点可以换到另一条线路，则换乘后的另一条线路也是两个方向乘车。通过乘车方向的控制，减少了算法要搜索的路径。
-- 乘车路线查询
DECLARE
    @state_start nvarchar(10),
    @state_stop nvarchar(10)
SELECT
    @state_start = N'广州东',
    @state_stop = N'中大'
 
-- 查询
IF OBJECT_ID(N'tempdb..#re') IS NOT NULL
    DROP TABLE #re
CREATE TABLE #re(
    ID int IDENTITY
       PRIMARY KEY,
    path nvarchar(max),
    state_count int,
    line_count int,
    start_lineID int,
    start_state nvarchar(10),
    current_lineID int,
    current_state nvarchar(10),
    next_orderid int,
    flag int,
    lineIDs varchar(max),
    level int
)
 
CREATE INDEX IX_current_lineID 
    ON #re(
       current_lineID )
 
CREATE INDEX IX_current_state 
    ON #re(
       current_state )
 
CREATE INDEX IX_next_orderid 
    ON #re(
       next_orderid )
 
CREATE INDEX IX_current_level
    ON #re(
       level )
 
DECLARE
    @level int,
    @rows int
SET
    @level = 0
 
-- 开始
INSERT #re(
    path,
    state_count, line_count,
    start_lineID, start_state,
    current_lineID, current_state,
    next_orderid, flag, lineIDs, level)    
SELECT
    path = CONVERT(nvarchar(max), 
           RTRIM(A.lineID) + N'{' 
              + RTRIM(A.orderid) + N'.' + A.state 
       ),
    state_count = 0,
    line_count = 0,
    start_lineID = A.lineID,
    start_state = A.state,
    current_lineID = A.lineID,
    current_state = A.state,
    next_orderid = A.orderid,
    flag = CASE
           WHEN A.state = @state_stop THEN 0
           ELSE NULL END,
    lineIDs = ',' + RTRIM(A.lineID) + ',',
    level = -(@level + 1)
FROM #tb A
WHERE state = @state_start
SET @rows = @@ROWCOUNT
WHILE @rows > 0
BEGIN
    SELECT 
       @level = @level + 1
    INSERT #re(
       path,
       state_count, line_count,
       start_lineID, start_state,
       current_lineID, current_state,
       next_orderid, flag, lineIDs, level)    
    -- 同一LineID
    SELECT 
       path = CONVERT(nvarchar(max),
              A.path 
                  + N'->'
                  + RTRIM(B.orderid) + N'.' + B.state 
           ),
       state_count = A.state_count + 1,
       A.line_count,
       A.start_lineID, A.start_state,
       current_lineID = B.lineID,
       current_state = B.state,
       next_orderid = B.orderid + A.flag,
       flag = CASE
              WHEN B.state = @state_stop THEN 0
              ELSE A.flag END,
       A.lineIDs,
       level = @level
    FROM #re A, #tb B
    WHERE A.flag <> 0
       AND A.level = @level - 1
       AND A.current_lineID = B.lineID
       AND A.next_orderid = B.orderid
    
    UNION ALL
    -- 不同LineID
    SELECT 
       path = CONVERT(nvarchar(max),
              A.path + N')->'
                  + RTRIM(B.lineID) + N'{' 
                  + RTRIM(B.orderid) + N'.' + B.state 
           ),
       state_count = A.state_count + 1,
       line_count = A.line_count + 1,
       A.start_lineID, A.start_state,
       current_lineID = B.lineID,
       current_state = B.state,
       next_orderid = B.orderid,
       flag = CASE
              WHEN B.state = @state_stop THEN 0
              ELSE NULL END,
       A.lineIDs + RTRIM(B.lineID) + ',',
       level = - @level
    FROM #re A, #tb B
    WHERE A.flag <> 0
       AND state_count = @level - 1
       AND A.current_lineID <> B.lineID
       AND A.current_state = B.state
       AND NOT EXISTS(
              SELECT * FROM #re
              WHERE CHARINDEX(',' + RTRIM(B.lineID) + ',', A.lineIDs) > 0)
    SET @rows = @@ROWCOUNT
 
    INSERT #re(
       path,
       state_count, line_count,
       start_lineID, start_state,
       current_lineID, current_state,
       next_orderid, flag, lineIDs, level)    
    -- 不同LineID 的第站正向
    SELECT 
       path = CONVERT(nvarchar(max), 
              A.path 
                  + N'->'
                  + RTRIM(B.orderid) + N'.' + B.state 
           ),
       state_count = A.state_count + 1,
       A.line_count,
       A.start_lineID, A.start_state,
       current_lineID = B.lineID,
       current_state = B.state,
       next_orderid = B.orderid + 1,
       flag = CASE
              WHEN B.state = @state_stop THEN 0
              ELSE 1 END,
       A.lineIDs,
       level = @level
    FROM #re A, #tb B
    WHERE A.flag IS NULL
       AND A.level = - @level
       AND A.current_lineID = B.lineID
       AND A.next_orderid + 1 = B.orderid
    UNION ALL
    -- 不同LineID 的第站反向
    SELECT 
       path = CONVERT(nvarchar(max), 
              A.path 
                  + N'->'
                  + RTRIM(B.orderid) + N'.' + B.state 
           ),
       state_count = A.state_count + 1,
       A.line_count,
       A.start_lineID, A.start_state,
       current_lineID = B.lineID,
       current_state = B.state,
       next_orderid = B.orderid - 1,
       flag = CASE
              WHEN B.state = @state_stop THEN 0
              ELSE - 1 END,
       A.lineIDs,
       level = @level
    FROM #re A, #tb B
    WHERE A.flag IS NULL
       AND A.level = - @level
       AND A.current_lineID = B.lineID
       AND A.next_orderid - 1 = B.orderid
 
    SET @rows = @rows + @@ROWCOUNT
END
 
SELECT
-- *,
    path = path + N'}',
    line_count,
    state_count
FROM #re
WHERE flag = 0

[htl258_Tony]

公交线路查询应用实例