製作公車查詢系統的資料庫

 

作者：楊先民  
精誠資訊/恆逸教育訓練中心資深講師

※網路引用請註明完整出處

本期是想到我在快20年前寫的一個行動裝置程式：公車查詢系統。

現在回想起來，當年不太懂要怎麼樣在行動裝置上才能更快，現在則沒有這種問題，因為現在的行動裝置已經不能和之前比了，速度上來說都非常快了，所以可以儘可能的用資料庫設計來解決使用者的需求。

公車查詢系統的使用者需求

公車查詢系統，主要還是查公車資訊，但是如果我們把需求再多增加，想要查詢兩站之間的轉車資訊，問題似乎就比較複雜了，總之，我們先把需求先講一下：

設計公車查詢系統（像是台北等公車、公車動態動態查詢）時，資料庫的核心技術挑戰在於「如何最佳化高頻率的班次與路線查詢」以及「如何儲存複雜的站牌順序（去程/回程）」。

為了滿足使用者「查詢某路線公車還有幾分鐘到某站」、「查詢某站有哪些公車會到」等核心需求，我們通常會參考國際通用的 GTFS (General Transit Feed Specification) 標準來簡化設計。

以下規劃這套公車查詢系統的完整關聯式資料表設計如下：

為了好懂，我們把系統拆分為三個核心區塊：

● 🔵 藍色區域（路線與班次）：管理公車路線（如：307、藍26）與發車班次。

● 🟢 綠色區域（站牌與地理資訊）：管理實體站牌（如：台北車站、板橋公車站）的位置。

● 🟡 黃色區域（動態與預估時間）：管理班次、路線、站牌之間的「順序關係」與「即時到站時間」。

資料表詳細規格規劃

表一：Routes (公車路線表)

記錄公車的基本資訊

欄位名稱 (Column Name)	資料型態	允許 NULL	說明
RouteID	INT IDENTITY(1,1)	否	主鍵 (PK)
RouteName	NVARCHAR(50)	否	路線名稱 (如：'307', '承德幹線')
DepartureStop	NVARCHAR(50)	否	起點站描述 (如：'撫遠街')
DestinationStop	NVARCHAR(50)	否	終點站描述 (如：'板橋')
Operator	NVARCHAR(50)	是	營運客運公司 (如：'首都客運')
IsActive	BIT	否	是否營運中 (1: 營運, 0: 停駛)

 

Stops (實體站牌表)

記錄每個實體站牌的名稱與經緯度

欄位名稱 (Column Name)	資料型態	允許 NULL	說明
StopID	INT IDENTITY(1,1)	否	主鍵 (PK)
StopName	NVARCHAR(100)	否	站牌名稱 (如：'南京敦化路口')
Latitude	DECIMAL(9,6)	否	緯度 (用於地圖定位)
Longitude	DECIMAL(9,6)	否	經度 (用於地圖定位)

RouteStops (路線站牌順序表)

同一條路線，去程和回程的站牌順序往往不同（甚至可能少停靠某幾站）。這張表用來定義「某一條路線的去/回程，第幾站是什麼站」。

欄位名稱 (Column Name)	資料型態	允許 NULL	說明
RouteStopID	INT IDENTITY(1,1)	否	主鍵 (PK)
RouteID	INT	否	外鍵 (FK)，關聯 Routes
StopID	INT	否	外鍵 (FK)，關聯 Stops
Direction	TINYINT	否	方向：0 = 去程, 1 = 回程
Sequence	INT	否	站序：從 1 開始遞增 (第1站、第2站...)

這張表必須加上 UNIQUE (RouteID, Direction, Sequence)，確保一條路線的某個方向，絕對不會有兩個「第一站」。

Schedules (班次表 / 定時發車時間)

記錄這條路線每天的固定發車班次（例如首班車 05:30、每15分鐘一班）。

欄位名稱 (Column Name)	資料型態	允許 NULL	說明
ScheduleID	INT IDENTITY(1,1)	否	主鍵 (PK)
RouteID	INT	否	外鍵 (FK)
Direction	TINYINT	否	0 = 去程, 1 = 回程
DepartureTime	TIME(0)	否	總站發車時間 (如 '06:15:00')
ServiceType	TINYINT	否	1: 平日, 2: 假日, 3: 天天行駛

-- 1. 路線表

CREATE TABLE Routes (

    RouteID INT IDENTITY(1,1) PRIMARY KEY,

    RouteName NVARCHAR(50) NOT NULL,

    DepartureStop NVARCHAR(50) NOT NULL,

    DestinationStop NVARCHAR(50) NOT NULL,

    Operator NVARCHAR(50) NULL,

    IsActive BIT NOT NULL DEFAULT 1

);

 

-- 2. 實體站牌表

CREATE TABLE Stops (

    StopID INT IDENTITY(1,1) PRIMARY KEY,

    StopName NVARCHAR(100) NOT NULL,

    Latitude DECIMAL(9,6) NOT NULL,

    Longitude DECIMAL(9,6) NOT NULL

);

 

-- 3. 路線站牌順序表

CREATE TABLE RouteStops (

    RouteStopID INT IDENTITY(1,1) PRIMARY KEY,

    RouteID INT NOT NULL FOREIGN KEY REFERENCES Routes(RouteID),

    StopID INT NOT NULL FOREIGN KEY REFERENCES Stops(StopID),

    Direction TINYINT NOT NULL, -- 0: 去程, 1: 回程

    Sequence INT NOT NULL,      -- 站牌順序

   

    -- 確保邏輯正確性：同路線同方向的站序不可重複

    CONSTRAINT UQ_Route_Direction_Seq UNIQUE (RouteID, Direction, Sequence)

);

 

-- 4. 班次表

CREATE TABLE Schedules (

    ScheduleID INT IDENTITY(1,1) PRIMARY KEY,

    RouteID INT NOT NULL FOREIGN KEY REFERENCES Routes(RouteID),

    Direction TINYINT NOT NULL, -- 0: 去程, 1: 回程

    DepartureTime TIME(0) NOT NULL,

    ServiceType TINYINT NOT NULL DEFAULT 3 -- 1:平日, 2:假日, 3:天天

);

 

-- 5. 效能優化索引（使用者點擊某一條公車路線時，查詢所有站牌速度要極快）

CREA COUNT(CASE WHEN PAResult IN ('1B', '2B', '3B', 'HR') THEN 1 END) * 1.0 /

    NULLIF(COUNT(CASE WHEN PAResult IN ('1B', '2B', '3B', 'HR', 'GO', 'FO', 'SO', 'E') THEN 1 END), 0) AS RISP_BA

FROM Plate_Appearances

TE NONCLUSTERED INDEX IX_RouteStops_Query ON RouteStops (RouteID, Direction) INCLUDE (StopID, Sequence);

 

關鍵功能 SQL 查詢範例

設計完表後，我們可以看看如何應對使用者在 App 上的即時查詢需求：

情境 A：使用者點選 307 公車「去程」，App 要依序顯示所有站名

SELECT rs.Sequence AS [站序], s.StopName AS [站牌名稱]

FROM RouteStops rs

JOIN Stops s ON rs.StopID = s.StopID

JOIN Routes r ON rs.RouteID = r.RouteID

WHERE r.RouteName = N'307' AND rs.Direction = 0

ORDER BY rs.Sequence ASC; -- 依站序從小到大排列

情境 B：使用者在「台北車站」等車，想知道有哪些公車路線會經過這一站？

SELECT DISTINCT r.RouteName AS [公車路線], r.DepartureStop + ' - ' + r.DestinationStop AS [行駛區間]

FROM RouteStops rs

JOIN Routes r ON rs.RouteID = r.RouteID

JOIN Stops s ON rs.StopID = s.StopID

WHERE s.StopName = N'台北車站' AND r.IsActive = 1;

那「還有幾分鐘到站」的即時資料呢？

在實務上，公車的「即時位置」和「預估到站時間（如：將到站、3分鐘）」是變動頻率極高的即時數據（通常每 5~15 秒更新一次）。

● 不建議：把每台公車這一秒的即時動態直接寫入傳統的關聯式資料庫（如 SQL Server 的實體表），這會導致硬碟 I/O 爆炸。

● 業界做法：通常會另外搭配 Redis（記憶體資料庫），將接收到的公車 GPS 資訊與動態預估時間，以快取（Cache）形式存在記憶體中。當前端 App 請求「307公車的即時動態」時，先從關聯式資料庫抓出 RouteStops 的靜態站牌順序，再將 Redis 裡的「即時到站秒數」組合在一起呈現給使用者，這樣系統的併發效能才會達到極致！

如果我要輸入站名資訊，可以透過SQL指令找出如何轉車嗎?

在公車或捷運的查詢系統中，透過輸入「起點站」與「終點站」來找出「如何轉車」（路徑規劃），在電腦科學中屬於經典的圖形演算法（Graph Algorithm）問題。

如果想純粹透過 SQL 指令來達成這個功能，最核心的武器就是使用 SQL 的 CTE 遞迴查詢（Recursive CTE）。遞迴 CTE 能夠在資料庫內部不斷地自我調用、像探針一樣沿著公車路線一站一站往下搜尋，直到找到目的地為止。

以下示範如何設計這個防轉車的 SQL 查詢：

建立搜尋的基礎：轉線檢視表（Data View）

為了讓遞迴 SQL 寫起來更乾淨，我們需要先建立一個檢視表，把「哪一條公車路線，可以從 A 站直接坐到 B 站（同一方向、且 B 站序大於 A 站序）」的直達關係整理出來。

先執行以下 SQL 建立這個檢索依據：

CREA CREATE VIEW v_DirectConnections AS

SELECT

    r1.RouteID,

    r.RouteName,

    r1.Direction,

    r1.StopID AS FromStopID,

    s1.StopName AS FromStopName,

    r2.StopID AS ToStopID,

    s2.StopName AS ToStopName,

    (r2.Sequence - r1.Sequence) AS Distance -- 經過幾站

FROM RouteStops r1

-- 自我連接（Self-Join）：找出同一條路線、同方向、後續的站

JOIN RouteStops r2 ON r1.RouteID = r2.RouteID

                  AND r1.Direction = r2.Direction

                  AND r2.Sequence > r1.Sequence

JOIN Routes r ON r1.RouteID = r.RouteID

JOIN Stops s1 ON r1.StopID = s1.StopID

JOIN Stops s2 ON r2.StopID = s2.StopID

WHERE r.IsActive = 1;

核心技術：遞迴 CTE 轉車搜尋 SQL

接下來，就是最關鍵的搜尋指令。我們設定起點為 '板橋公車站'，終點為 '台北車站'。這個 SQL 會自動幫我們舉出：

1.       直達的公車有哪些

2.       轉車一次（換一次車）的搭乘組合有哪些

DECLAR DECLARE @StartStation NVARCHAR(100) = N'板橋公車站';

DECLARE @EndStation NVARCHAR(100) = N'台北車站';

 

WITH BusPaths AS (

    -- 【A. 錨點成員 Anchor】：找出所有從「起點站」出發的直達公車

    SELECT

        RouteName AS CurrentRoute,

        ToStopID,

        ToStopName,

        CAST(RouteName AS NVARCHAR(MAX)) AS TravelPath, -- 紀錄路線軌跡

        CAST(FromStopName + ' -> ' + ToStopName AS NVARCHAR(MAX)) AS StationPath, -- 紀錄站點軌跡

        0 AS TransferCount, -- 轉車次數

        Distance AS TotalStops

    FROM v_DirectConnections

    WHERE FromStopName = @StartStation

 

    UNION ALL

 

    -- 【B. 遞迴成員 Recursive】：上一班公車的「到站」，如果是另一班公車的「起點」，代表可以轉車

    SELECT

        nextBus.RouteName AS CurrentRoute,

        nextBus.ToStopID,

        nextBus.ToStopName,

        p.TravelPath + ' -> ' + nextBus.RouteName,

        p.StationPath + ' -> ' + nextBus.ToStopName,

        p.TransferCount + 1 AS TransferCount, -- 轉車次數 + 1

        p.TotalStops + nextBus.Distance

    FROM BusPaths p

    JOIN v_DirectConnections nextBus ON p.ToStopID = nextBus.FromStopID -- 轉車點交會

    WHERE p.TransferCount < 1 -- 🌟 限制最多只轉車 1 次（防止無限遞迴或繞遠路）

      AND p.TravelPath NOT LIKE '%' + nextBus.RouteName + '%' -- 防呆：不坐回重覆的路線

)

-- 【C. 最終篩選】：撈出所有成功到達「終點站」的路徑，並依轉車次數與站數排序

SELECT

    TransferCount AS [轉車次數],

    TravelPath AS [公車搭乘順序],

    StationPath AS [乘車路徑軌跡],

    TotalStops AS [總乘車站數]

FROM BusPaths

WHERE ToStopName = @EndStation

ORDER BY TransferCount ASC, TotalStops ASC;

查詢結果會長怎樣？

執行上述 SQL 後，資料庫會產生非常直觀的報表：

轉車次數	公車搭乘順序	乘車路徑軌跡	總乘車站數
0 (直達)	307	板橋公車站 -> 台北車站	12
0 (直達)	藍38	板橋公車站 -> 台北車站	15
1 (轉車)	藍26 -> 承德幹線	板橋公車站 -> 南京敦化路口(轉車點) -> 台北車站	18

在20幾年前撰寫這個行動裝置的程式時，並沒有 CTE這樣的寫法，CTE是 SQL Server 2005之後才有的語法，所以如果這個系統是交給前端來做的話，確實是比較快。

以上就是本期的主題：設計公車查詢系統的資料庫。