# 系統架構 ## 架構總覽 - [程式碼](https://github.com/project-team-2025/highway-predictor/tree/local/work-with-codex)。目前branch：`local/work-with-codex` - **前端(純 HTML / CSS / JavaScript)**： 顯示 - [前端figma連結](https://www.figma.com/design/FgZq5YAy7cj7r8uyYHmiM6/%E9%AB%98%E9%80%9F%E5%85%AC%E8%B7%AF%E4%BA%A4%E9%80%9A%E5%A3%85%E5%A1%9E%E9%A0%90%E6%B8%AC%E6%A8%A1%E5%9E%8B-%E5%89%8D%E7%AB%AF?node-id=33-104&t=WkB4fRjKVZ6ZH8NN-0) - [文件說明](https://hackmd.io/nAaJy5VKSXimSF1uvaV6Tw#%E4%BB%8B%E9%9D%A2) - 模型：文字(長度1~10個英文字) - 路段：文字(長度2~20個中文字) - 預測結果(可能有好幾個，先預設3個) - 範例：ttc、speed1、speed2 - **資料庫**： - 共 3 張資料表，分別儲存原始數據、清洗/處理後的資料、模型預測結果。[與上述資料相關的操作](#與上述資料相關的操作) - `database.py`：負責與資料庫相關的操作。所有與資料庫的互動必須透過本程式，其他程式不得直接存取資料庫。 - **後端（Python）**：包成Docker. 1. 爬蟲(`crawler.py`)：抓取最新資料並存到資料庫。 2. 資料清洗、處理(`data_processor.py`)：從資料庫處理原始資料並存回資料庫。 3. 模型預測(`predict.py`)：預測數值並存到資料庫。 4. API(FastAPI)(`app.py`)：`GET /api/data`：從資料庫撈資料給前端。 5. 排程(`scheduler.py`)：負責在適當時機呼叫`crawler.py`、`data_processor.py`、`predict.py`程式。比如`crawler.py`成功抓取最新資料，則需要呼叫`data_processor.py`接著處理。 - ![後端總架構圖](https://hackmd.io/_uploads/HyEfNzCyZg.gif) - ![爬蟲呼叫邏輯](https://hackmd.io/_uploads/H1d27nYbWg.svg) --- ## API：GET /api/data ### 功能 回傳單一(最新)時間點的資料。 ### Query Parameters - 無 ### Response #### 200 Success ```json { "timestamp": "2025-07-20T09:00:00Z", "predictResults": { "RNN": { "ttcttf": "", "speed5": "", "speed10": "", "speed15": "", "speed30": "", "level5": "", "level10": "", "level15": "", "level30": "" }, "LSTM": { "ttcttf": "", "speed5": "", "speed10": "", "speed15": "", "speed30": "", "level5": "", "level10": "", "level15": "", "level30": "" }, "GRU": { "ttcttf": "", "speed5": "", "speed10": "", "speed15": "", "speed30": "", "level5": "", "level10": "", "level15": "", "level30": "" }, "CNN": { "ttcttf": "", "speed5": "", "speed10": "", "speed15": "", "speed30": "", "level5": "", "level10": "", "level15": "", "level30": "" }, "GBRT": { "ttcttf": "", "speed5": "", "speed10": "", "speed15": "", "speed30": "", "level5": "", "level10": "", "level15": "", "level30": "" } }, "traficState": "" } ``` - 解釋： - `timestamp`為時間，只這筆預測值是屬於哪個時間點的，換句話說，其中「預測5分鐘後車速」的資料是預測`timestamp`+5分鐘這個時刻的，前端讚時不用管他，反正就把各數值填到對應的地方就對了。 - "predictResults"，內為各模型各預測目標的數值 - "ttcttf": ttc或ttf, "speed5": 5分鐘後車速, "speed10": 10分鐘後車速, "speed15": 15分鐘後車速, "speed30": 30分鐘後車速, "level5": 5分鐘後車速等級, "level10": 10分鐘後車速等級, "level15": 15分鐘後車速等級, "level30": 30分鐘後車速等級 - "traficState": 0為目前通暢，1為目前雍塞，用於網頁顯示當前狀態。 - 各數值(例如"speed5")型別都是小數、整數，或者回傳空字串(`""`)，此時前端需進行處理，避免報錯。 - `get_predict()`回傳之`ttc`、`ttf`，必有其一為None(NULL)，以此判斷`traficState` - 看LSTM的 - 若ttc為None： `traficState`為1 - 反之ttf為None: `traficState`為0 - `speed`需確保>=0，並取整數。 - `level`需確保介於0~5之間(含)並四捨五入為整數。 - `ttcttf`則為`ttc`、`ttf`兩者非None的一方之數值，須確保>0、<=37。 - 若有數值為None(`ttcttf`則是`ttc`、`ttf`皆為None)，則該值回傳空字串(`""`) - 補充：門架距離是`1.6`km，預計行車時間就直接拿1.6/speed，然後換算成分鐘。 - 以下為舊版本 ```json { "segment_id": "A123", "timestamp": "2025-07-20T09:00:00Z", "model_id": "xgboost_v1", "type": "actual", "features": { "speed": 75.2, "travel_time": 4.8, … //找的到的feature都傳 } } ``` #### 400 Bad Request <!-- 少傳或多傳參數 ```json { "error": "Bad Request", "message": "Missing or invalid parameters", "invalid_params": ["timestamp", …] //缺少跟多餘的傳入參數 } ``` --> #### 404 Not Found <!-- 查無指定資料 ```json { "error": "Not Found", "message": "No data for {timestamp}, {segment_id}, {type} and {model_id}." } ``` --> ## 資料流定義 ### 資料表 1. 原始資料表 僅把xml或csv中特定的欄位取出轉換為二維表格，除此之外**不做**任何處理。每個資料集一個獨立表格。所有欄位的格式在本資料表中都以`string、int64, float64`儲存。 2. 特徵資料表 根據各模型的需要把`原始資料`經過所有必需的計算與清洗，包含補缺失值、取平均等。總共只有一個表格，primary key為間隔5分鐘且符合[ISO 8601 UTC](https://www.timestamp-converter.com/)的時間，該時間表示資料的蒐集時間(非檔案時間)。 > 例如有一筆資料a來自檔名為`2024/1/1 13:05`的原始檔案，但它記錄的是`2024/1/1 13:00`的資料，則應該在特徵資料中紀錄為`2024/1/1 13:00`。 3. 預測結果資料表 記錄各模型的預測結果。 ### 資料表命名規則 - 原始資料表 orgin + 資料集名稱，以下滑線分隔。 > 例如資料集`VD 動態資訊(v2.0)`的原始資料表應該命名為`orgin_vd`。 > 例如資料集`站間各車種中位數行駛車速 (M05A)`的原始資料表應該命名為`orgin_m05`。 - 特徵資料表 只有一個表，所有資料集、模型共用，名稱為`feature`。 - 預測結果資料表 只有一個表，所有資料集、模型共用，名稱為`predict`。 ### 欄位命名規則 - 原始資料表 原始欄位完整名稱。 > 例如資料集`vd`的原始xml中該欄位名稱應為`speed`，則在原始資料表中應該命名為`speed`。 - 特徵資料表 直接定義，請見範例。名稱中包含vd代表由vd計算而得，未包含則是由m05計算而得。 另新增兩個欄位vd_finished、m05_finished，為bool，分別記錄vd與m05資料在該row是否已經完成資料處理 <!-- 資料集名稱 + 特徵名稱 > 例如由vd的原始資料表中計算而得的`speed_avg`，應該命名為`vd_speed_avg`。 --> - 預測結果資料表 模型編號 + 特徵資料表中的feature名稱 + predict + 時間備註(可選)，以下滑線分隔。 > 例如由模型編號A預測speed_avg，應該命名為`A_speed_avg_predict`。 > 例如由模型編號A預測15分鐘後的speed_avg，應該命名為`A_speed_avg_predict_15min`。 ### 與上述資料相關的操作 1. 爬蟲 &rarr; 原始資料表 1. 資料清洗 原始資料表 &rarr; 特徵資料表 1. 預測 特徵資料表 &rarr; 預測結果資料表 3. api回應 預測結果資料表 &rarr; ### 資料流範例 a. 原始資料表 - orgin_vd： [完整範例檔案](https://1drv.ms/x/c/002d06ef873e3a00/EZhbL6ICF0ZFpwhnmsLHbrcBWe-zjWGc4CzHRJTwDX5h4w?e=NsO7WK) |VDID|DataCollectTime|LinkID|LaneID|LaneType|Speed|Occupancy|VehicleType|Volume|VehicleSpeed| |---|---|---|---|---|---|---|---|---|---| |VD-N1-S-25.850-M-LOOP|2024-01-01 00:00:00|0000100002560A|0|2|91|1|S|1|90| |VD-N1-S-25.850-M-LOOP|2024-01-01 00:00:00|0000100002560A|0|2|91|1|L|0|0| |VD-N1-S-25.850-M-LOOP|2024-01-01 00:00:00|0000100002560A|0|2|91|1|T|0|0| |...|...|...|...|...|...|...|...|...|...| |VD-N1-N-28.670-M-LOOP|2024-01-01 00:04:00|0000100102800F|3|2|0|0|L|0|0| |VD-N1-N-28.670-M-LOOP|2024-01-01 00:04:00|0000100102800F|3|2|0|0|T|0|0| - orgin_m05 [完整範例檔案](https://1drv.ms/x/c/002d06ef873e3a00/ESpm2sWDfvlLmE4NKbUPbT4BrH87o2APpjeNtHKt2tGK_g?e=Ycrj7Z) |DataCollectTime|GantryFrom|GantryTo|VehicleType|Speed|Volume| |---|---|---|---|---|---| |2024-01-01 00:00:00|01F0213N|01F0155N|31.0|86.0|31.0| |2024-01-01 00:00:00|01F0213N|01F0155N|32.0|82.0|4.0| |2024-01-01 00:00:00|01F0213N|01F0155N|41.0|0.0|0.0| |...|...|...|...|...|...| |2024-01-01 00:05:00|01F0293S|01F0339S|42.0|79.0|2.0| |2024-01-01 00:05:00|01F0293S|01F0339S|5.0|0.0|0.0| b. 特徵資料表 - feature [~~完整~~部分範例檔案](https://1drv.ms/x/c/002d06ef873e3a00/EeLNBIZAUh9Psk3CsysIaNoBxFGyEVvNVkW7QzuxQDkpzQ?e=2at3Bf) (完整的新增兩個欄位`vd_finished`、`m05_finished`，為`bool`，分別記錄vd與m05資料在該row是否已經完成資料處理，並可供`predict.py`預測。 欄位名稱： |timestamp|spdavg_VD-N1-N-18.010-M-LOOP|spdavg_VD-N1-N-18.770-M-RS|spdavg_VD-N1-N-21.230-M-LOOP|spdavg_VD-N1-N-22.990-N-LOOP|spdavg_VD-N1-N-23.780-M-LOOP|spdavg_VD-N1-N-24.200-M-RS|spdavg_VD-N1-N-25.200-N-LOOP|spdavg_VD-N1-N-25.800-M-LOOP|spdavg_VD-N1-N-26.780-M-RS|spdavg_VD-N1-N-27.100-N-LOOP|spdavg_VD-N1-N-27.970-M-LOOP|spdavg_VD-N1-N-28.670-M-LOOP|spdavg_VD-N1-N-30.550-M-RS|spdavg_VD-N1-N-31.830-N-LOOP|spdavg_VD-N1-N-32.200-M-LOOP|spdavg_VD-N1-S-18.710-M-LOOP|spdavg_VD-N1-S-20.000-M-RS|spdavg_VD-N1-S-21.390-M-LOOP|spdavg_VD-N1-S-22.700-M-LOOP|spdavg_VD-N1-S-23.280-N-LOOP|spdavg_VD-N1-S-23.900-M-LOOP|spdavg_VD-N1-S-24.400-M-RS|spdavg_VD-N1-S-25.440-N-LOOP|spdavg_VD-N1-S-25.850-M-LOOP|spdavg_VD-N1-S-26.350-M-RS|spdavg_VD-N1-S-26.750-N-LOOP|spdavg_VD-N1-S-28.840-M-RS|spdavg_VD-N1-S-30.540-M-LOOP|spdavg_VD-N1-S-31.400-M-LOOP|spdavg_VD-N1-S-32.080-N-LOOP|spdtrnd_VD-N1-N-18.010-M-LOOP|spdtrnd_VD-N1-N-18.770-M-RS|spdtrnd_VD-N1-N-21.230-M-LOOP|spdtrnd_VD-N1-N-22.990-N-LOOP|spdtrnd_VD-N1-N-23.780-M-LOOP|spdtrnd_VD-N1-N-24.200-M-RS|spdtrnd_VD-N1-N-25.200-N-LOOP|spdtrnd_VD-N1-N-25.800-M-LOOP|spdtrnd_VD-N1-N-26.780-M-RS|spdtrnd_VD-N1-N-27.100-N-LOOP|spdtrnd_VD-N1-N-27.970-M-LOOP|spdtrnd_VD-N1-N-28.670-M-LOOP|spdtrnd_VD-N1-N-30.550-M-RS|spdtrnd_VD-N1-N-31.830-N-LOOP|spdtrnd_VD-N1-N-32.200-M-LOOP|spdtrnd_VD-N1-S-18.710-M-LOOP|spdtrnd_VD-N1-S-20.000-M-RS|spdtrnd_VD-N1-S-21.390-M-LOOP|spdtrnd_VD-N1-S-22.700-M-LOOP|spdtrnd_VD-N1-S-23.280-N-LOOP|spdtrnd_VD-N1-S-23.900-M-LOOP|spdtrnd_VD-N1-S-24.400-M-RS|spdtrnd_VD-N1-S-25.440-N-LOOP|spdtrnd_VD-N1-S-25.850-M-LOOP|spdtrnd_VD-N1-S-26.350-M-RS|spdtrnd_VD-N1-S-26.750-N-LOOP|spdtrnd_VD-N1-S-28.840-M-RS|spdtrnd_VD-N1-S-30.540-M-LOOP|spdtrnd_VD-N1-S-31.400-M-LOOP|spdtrnd_VD-N1-S-32.080-N-LOOP|occ_VD-N1-N-18.010-M-LOOP|occ_VD-N1-N-18.770-M-RS|occ_VD-N1-N-21.230-M-LOOP|occ_VD-N1-N-22.990-N-LOOP|occ_VD-N1-N-23.780-M-LOOP|occ_VD-N1-N-24.200-M-RS|occ_VD-N1-N-25.200-N-LOOP|occ_VD-N1-N-25.800-M-LOOP|occ_VD-N1-N-26.780-M-RS|occ_VD-N1-N-27.100-N-LOOP|occ_VD-N1-N-27.970-M-LOOP|occ_VD-N1-N-28.670-M-LOOP|occ_VD-N1-N-30.550-M-RS|occ_VD-N1-N-31.830-N-LOOP|occ_VD-N1-N-32.200-M-LOOP|occ_VD-N1-S-18.710-M-LOOP|occ_VD-N1-S-20.000-M-RS|occ_VD-N1-S-21.390-M-LOOP|occ_VD-N1-S-22.700-M-LOOP|occ_VD-N1-S-23.280-N-LOOP|occ_VD-N1-S-23.900-M-LOOP|occ_VD-N1-S-24.400-M-RS|occ_VD-N1-S-25.440-N-LOOP|occ_VD-N1-S-25.850-M-LOOP|occ_VD-N1-S-26.350-M-RS|occ_VD-N1-S-26.750-N-LOOP|occ_VD-N1-S-28.840-M-RS|occ_VD-N1-S-30.540-M-LOOP|occ_VD-N1-S-31.400-M-LOOP|occ_VD-N1-S-32.080-N-LOOP|01F0248S_01F0264S_spd|traffic_state|vd_finished|m05_finished| |---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---| - 欄位(python list格式，方便複製) ```python ["timestamp", "spdavg_VD-N1-N-18.010-M-LOOP", "spdavg_VD-N1-N-18.770-M-RS", "spdavg_VD-N1-N-21.230-M-LOOP", "spdavg_VD-N1-N-22.990-N-LOOP", "spdavg_VD-N1-N-23.780-M-LOOP", "spdavg_VD-N1-N-24.200-M-RS", "spdavg_VD-N1-N-25.200-N-LOOP", "spdavg_VD-N1-N-25.800-M-LOOP", "spdavg_VD-N1-N-26.780-M-RS", "spdavg_VD-N1-N-27.100-N-LOOP", "spdavg_VD-N1-N-27.970-M-LOOP", "spdavg_VD-N1-N-28.670-M-LOOP", "spdavg_VD-N1-N-30.550-M-RS", "spdavg_VD-N1-N-31.830-N-LOOP", "spdavg_VD-N1-N-32.200-M-LOOP", "spdavg_VD-N1-S-18.710-M-LOOP", "spdavg_VD-N1-S-20.000-M-RS", "spdavg_VD-N1-S-21.390-M-LOOP", "spdavg_VD-N1-S-22.700-M-LOOP", "spdavg_VD-N1-S-23.280-N-LOOP", "spdavg_VD-N1-S-23.900-M-LOOP", "spdavg_VD-N1-S-24.400-M-RS", "spdavg_VD-N1-S-25.440-N-LOOP", "spdavg_VD-N1-S-25.850-M-LOOP", "spdavg_VD-N1-S-26.350-M-RS", "spdavg_VD-N1-S-26.750-N-LOOP", "spdavg_VD-N1-S-28.840-M-RS", "spdavg_VD-N1-S-30.540-M-LOOP", "spdavg_VD-N1-S-31.400-M-LOOP", "spdavg_VD-N1-S-32.080-N-LOOP", "spdtrnd_VD-N1-N-18.010-M-LOOP", "spdtrnd_VD-N1-N-18.770-M-RS", "spdtrnd_VD-N1-N-21.230-M-LOOP", "spdtrnd_VD-N1-N-22.990-N-LOOP", "spdtrnd_VD-N1-N-23.780-M-LOOP", "spdtrnd_VD-N1-N-24.200-M-RS", "spdtrnd_VD-N1-N-25.200-N-LOOP", "spdtrnd_VD-N1-N-25.800-M-LOOP", "spdtrnd_VD-N1-N-26.780-M-RS", "spdtrnd_VD-N1-N-27.100-N-LOOP", "spdtrnd_VD-N1-N-27.970-M-LOOP", "spdtrnd_VD-N1-N-28.670-M-LOOP", "spdtrnd_VD-N1-N-30.550-M-RS", "spdtrnd_VD-N1-N-31.830-N-LOOP", "spdtrnd_VD-N1-N-32.200-M-LOOP", "spdtrnd_VD-N1-S-18.710-M-LOOP", "spdtrnd_VD-N1-S-20.000-M-RS", "spdtrnd_VD-N1-S-21.390-M-LOOP", "spdtrnd_VD-N1-S-22.700-M-LOOP", "spdtrnd_VD-N1-S-23.280-N-LOOP", "spdtrnd_VD-N1-S-23.900-M-LOOP", "spdtrnd_VD-N1-S-24.400-M-RS", "spdtrnd_VD-N1-S-25.440-N-LOOP", "spdtrnd_VD-N1-S-25.850-M-LOOP", "spdtrnd_VD-N1-S-26.350-M-RS", "spdtrnd_VD-N1-S-26.750-N-LOOP", "spdtrnd_VD-N1-S-28.840-M-RS", "spdtrnd_VD-N1-S-30.540-M-LOOP", "spdtrnd_VD-N1-S-31.400-M-LOOP", "spdtrnd_VD-N1-S-32.080-N-LOOP", "occ_VD-N1-N-18.010-M-LOOP", "occ_VD-N1-N-18.770-M-RS", "occ_VD-N1-N-21.230-M-LOOP", "occ_VD-N1-N-22.990-N-LOOP", "occ_VD-N1-N-23.780-M-LOOP", "occ_VD-N1-N-24.200-M-RS", "occ_VD-N1-N-25.200-N-LOOP", "occ_VD-N1-N-25.800-M-LOOP", "occ_VD-N1-N-26.780-M-RS", "occ_VD-N1-N-27.100-N-LOOP", "occ_VD-N1-N-27.970-M-LOOP", "occ_VD-N1-N-28.670-M-LOOP", "occ_VD-N1-N-30.550-M-RS", "occ_VD-N1-N-31.830-N-LOOP", "occ_VD-N1-N-32.200-M-LOOP", "occ_VD-N1-S-18.710-M-LOOP", "occ_VD-N1-S-20.000-M-RS", "occ_VD-N1-S-21.390-M-LOOP", "occ_VD-N1-S-22.700-M-LOOP", "occ_VD-N1-S-23.280-N-LOOP", "occ_VD-N1-S-23.900-M-LOOP", "occ_VD-N1-S-24.400-M-RS", "occ_VD-N1-S-25.440-N-LOOP", "occ_VD-N1-S-25.850-M-LOOP", "occ_VD-N1-S-26.350-M-RS", "occ_VD-N1-S-26.750-N-LOOP", "occ_VD-N1-S-28.840-M-RS", "occ_VD-N1-S-30.540-M-LOOP", "occ_VD-N1-S-31.400-M-LOOP", "occ_VD-N1-S-32.080-N-LOOP", "01F0248S_01F0264S_spd", "traffic_state", "vd_finished", "m05_finished"] ``` <!-- 舊欄位名稱(已棄用)： |timestamp|vd_spdavg_VD-N1-S-23.900-M-LOOP|vd_spdtrnd_VD-N1-S-23.900-M-LOOP|vd_occ_VD-N1-S-23.900-M-LOOP|vd_spdavg_VD-N1-S-24.400-M-RS|vd_spdtrnd_VD-N1-S-24.400-M-RS|vd_occ_VD-N1-S-24.400-M-RS|vd_spdavg_VD-N1-S-25.440-N-LOOP|vd_spdtrnd_VD-N1-S-25.440-N-LOOP|vd_occ_VD-N1-S-25.440-N-LOOP|vd_spdavg_VD-N1-S-25.850-M-LOOP|vd_spdtrnd_VD-N1-S-25.850-M-LOOP|vd_occ_VD-N1-S-25.850-M-LOOP|vd_spdavg_VD-N1-S-26.350-M-RS|vd_spdtrnd_VD-N1-S-26.350-M-RS|vd_occ_VD-N1-S-26.350-M-RS|vd_spdavg_VD-N1-S-26.750-N-LOOP|vd_spdtrnd_VD-N1-S-26.750-N-LOOP|vd_occ_VD-N1-S-26.750-N-LOOP|vd_spdavg_VD-N1-S-28.840-M-RS|vd_spdtrnd_VD-N1-S-28.840-M-RS|vd_occ_VD-N1-S-28.840-M-RS|m05_speed|m05_state|m05_speed_level|vd_finished|m05_finished| |---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---| |2024-01-01T00:00:00+08:00|78.61111111||4.5|90.15384615||1.25|99.78947368||2.333333333|87.4||1.75|91.38461538||1.5|91.29166667||2.375|102.1||1.25|92.484375|0|4|1|1| |2024-01-01T00:05:00+08:00|81.70526316|9|3.9|88.83486239|6|2.2|99.78947368|23|2.333333333|85.03225806|16|4.15|84.56842105|28|1.7|91.29166667|7|2.375|101.1547619|33|1.4|96.5505618|0|4|1|1| --> <!-- 欄位(python list格式，方便複製) ``` [ "timestamp", "vd_spdavg_VD-N1-S-23.900-M-LOOP", "vd_spdtrnd_VD-N1-S-23.900-M-LOOP", "vd_occ_VD-N1-S-23.900-M-LOOP", "vd_spdavg_VD-N1-S-24.400-M-RS", "vd_spdtrnd_VD-N1-S-24.400-M-RS", "vd_occ_VD-N1-S-24.400-M-RS", "vd_spdavg_VD-N1-S-25.440-N-LOOP", "vd_spdtrnd_VD-N1-S-25.440-N-LOOP", "vd_occ_VD-N1-S-25.440-N-LOOP", "vd_spdavg_VD-N1-S-25.850-M-LOOP", "vd_spdtrnd_VD-N1-S-25.850-M-LOOP", "vd_occ_VD-N1-S-25.850-M-LOOP", "vd_spdavg_VD-N1-S-26.350-M-RS", "vd_spdtrnd_VD-N1-S-26.350-M-RS", "vd_occ_VD-N1-S-26.350-M-RS", "vd_spdavg_VD-N1-S-26.750-N-LOOP", "vd_spdtrnd_VD-N1-S-26.750-N-LOOP", "vd_occ_VD-N1-S-26.750-N-LOOP", "vd_spdavg_VD-N1-S-28.840-M-RS", "vd_spdtrnd_VD-N1-S-28.840-M-RS", "vd_occ_VD-N1-S-28.840-M-RS", "m05_speed", "m05_state", "m05_speed_level" ] ``` --> c. 預測結果資料表 - predict > `timestamp`: 使用`feature`資料表中哪個時間點與之前的資料進行預測，例如用`...~2024-01-01T00:05:00+08:00`的資料進行預測，則`timestamp`為`2024-01-01T00:05:00+08:00`。 [完整範例檔案](https://1drv.ms/x/c/002d06ef873e3a00/EcDED6R2FJpIvxOhsmywfH0BYB0027_8jYe1xbZu3dxCGg?e=fKSugY) |timestamp|LSTM_m05_speed_predict_5min|LSTM_m05_speed_predict_10min|LSTM_m05_speed_predict_15min|LSTM_m05_speed_predict_30min|LSTM_m05_speedlevel_predict_5min|LSTM_m05_speedlevel_predict_10min|LSTM_m05_speedlevel_predict_15min|LSTM_m05_speedlevel_predict_30min|LSTM_m05_ttf|LSTM_m05_ttc|GRU_m05_speed_predict_5min|GRU_m05_speed_predict_10min|GRU_m05_speed_predict_15min|GRU_m05_speed_predict_30min|GRU_m05_speedlevel_predict_5min|GRU_m05_speedlevel_predict_10min|GRU_m05_speedlevel_predict_15min|GRU_m05_speedlevel_predict_30min|GRU_m05_ttf|GRU_m05_ttc|RNN_m05_speed_predict_5min|RNN_m05_speed_predict_10min|RNN_m05_speed_predict_15min|RNN_m05_speed_predict_30min|RNN_m05_speedlevel_predict_5min|RNN_m05_speedlevel_predict_10min|RNN_m05_speedlevel_predict_15min|RNN_m05_speedlevel_predict_30min|RNN_m05_ttf|RNN_m05_ttc|GBRT_m05_speed_predict_5min|GBRT_m05_speed_predict_10min|GBRT_m05_speed_predict_15min|GBRT_m05_speed_predict_30min|GBRT_m05_speedlevel_predict_5min|GBRT_m05_speedlevel_predict_10min|GBRT_m05_speedlevel_predict_15min|GBRT_m05_speedlevel_predict_30min|GBRT_m05_ttf|GBRT_m05_ttc|CNN_m05_speed_predict_5min|CNN_m05_speed_predict_10min|CNN_m05_speed_predict_15min|CNN_m05_speed_predict_30min|CNN_m05_speedlevel_predict_5min|CNN_m05_speedlevel_predict_10min|CNN_m05_speedlevel_predict_15min|CNN_m05_speedlevel_predict_30min|CNN_m05_ttf|CNN_m05_ttc| |---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---| 2024-01-01T00:00:00+08:00|||||||||||||||||||||||||||||||||||||||||||||||||| 2024-01-01T00:05:00+08:00|||||||||||||||||||||||||||||||||||||||||||||||||| ### 資料庫須提供function 1. 寫入origin_vd資料表 - ```python def save_to_origin_vd(cols: List[str], data: np.ndarray) -> bool: pass ``` - 將`data`寫入`origin_vd資料表`，data中可能有一筆或數筆資料 - `cols`為`data`的欄位名稱，需以此將`data`的資料存入`origin_vd資料表`中的對應欄位。 2. 寫入origin_m05資料表 - ```python def save_to_origin_m05(cols: List[str], data: np.ndarray) -> bool: pass ``` - 將`data`寫入`origin_m05資料表`，data中可能有一筆或數筆資料 - `cols`為`data`的欄位名稱，需以此將`data`的資料存入`origin_m05資料表`中的對應欄位。 3. 回傳origin_vd資料表中，`DataCollectTime`介於指定參數之間(含)的所有資料。 - ```python def get_origin_vd(cols: List[str], time_start: str, time_end: str) -> np.ndarray: pass ``` - 回傳值不包含欄位名稱 - 回傳的data以`cols`排序 4. 回傳origin_m05資料表中，`TimeStamp`介於指定參數之間(含)的所有資料。 - ```python def get_origin_m05(cols: List[str], time_start: str, time_end: str) -> np.ndarray: pass ``` - 回傳值不包含欄位名稱 - 回傳的data以`cols`排序 5. 回傳origin_vd資料表中`DataCollectTime`欄位最新(最大)的值 - ```python def get_origin_vd_latest_time() -> str: pass ``` 6. 回傳origin_m05資料表中`TimeStamp`欄位最新(最大)的值 - ```python def get_origin_m05_latest_time() -> str: pass ``` 7. 寫入feature資料表 - ```python def save_to_feature(cols: List[str], data: np.ndarray) -> bool: pass ``` 8. 回傳feature資料表中`vd_finished`與`m05_finished`皆為true的row中，timestamp最新的四筆資料。 - ```python def get_features(cols: List[str]) -> np.ndarray: pass ``` - 回傳值不包含欄位名稱 - 回傳的data以`cols`排序 9. 回傳predict資料表中，timestamp最新的一筆row。 - ```python def get_predict(cols: List[str]) -> np.ndarray: pass ``` - 回傳值不包含欄位名稱 - 回傳的data以`cols`排序 10. 寫入predict資料表 - ```python def save_to_predict(cols: List[str], data: np.ndarray) -> bool: pass ``` ### 各資料表初始化 在資料一啟動時要做的事 1. `origin_vd資料表`初始化如下 - 單純建表，無預設資料 |VDID|DataCollectTime|LinkID|LaneID|LaneType|Speed|Occupancy|VehicleType|Volume|VehicleSpeed| |---|---|---|---|---|---|---|---|---|---| 2. `origin_m05資料表`初始化如下 - 單純建表，無預設資料 |DataCollectTime|GantryFrom|GantryTo|VehicleType|Speed|Volume| |---|---|---|---|---|---| 3. `feature資料表`初始化如下 - `vd_finished`, `m05_finished`設為1(true)，`timstamp`為2025-01-01 00:00:00~2025-01-01 00:15:00，共四個row，其他欄位都設0。 |timestamp|spdavg_VD-N1-N-18.010-M-LOOP|spdavg_VD-N1-N-18.770-M-RS|spdavg_VD-N1-N-21.230-M-LOOP|spdavg_VD-N1-N-22.990-N-LOOP|spdavg_VD-N1-N-23.780-M-LOOP|spdavg_VD-N1-N-24.200-M-RS|spdavg_VD-N1-N-25.200-N-LOOP|spdavg_VD-N1-N-25.800-M-LOOP|spdavg_VD-N1-N-26.780-M-RS|spdavg_VD-N1-N-27.100-N-LOOP|spdavg_VD-N1-N-27.970-M-LOOP|spdavg_VD-N1-N-28.670-M-LOOP|spdavg_VD-N1-N-30.550-M-RS|spdavg_VD-N1-N-31.830-N-LOOP|spdavg_VD-N1-N-32.200-M-LOOP|spdavg_VD-N1-S-18.710-M-LOOP|spdavg_VD-N1-S-20.000-M-RS|spdavg_VD-N1-S-21.390-M-LOOP|spdavg_VD-N1-S-22.700-M-LOOP|spdavg_VD-N1-S-23.280-N-LOOP|spdavg_VD-N1-S-23.900-M-LOOP|spdavg_VD-N1-S-24.400-M-RS|spdavg_VD-N1-S-25.440-N-LOOP|spdavg_VD-N1-S-25.850-M-LOOP|spdavg_VD-N1-S-26.350-M-RS|spdavg_VD-N1-S-26.750-N-LOOP|spdavg_VD-N1-S-28.840-M-RS|spdavg_VD-N1-S-30.540-M-LOOP|spdavg_VD-N1-S-31.400-M-LOOP|spdavg_VD-N1-S-32.080-N-LOOP|spdtrnd_VD-N1-N-18.010-M-LOOP|spdtrnd_VD-N1-N-18.770-M-RS|spdtrnd_VD-N1-N-21.230-M-LOOP|spdtrnd_VD-N1-N-22.990-N-LOOP|spdtrnd_VD-N1-N-23.780-M-LOOP|spdtrnd_VD-N1-N-24.200-M-RS|spdtrnd_VD-N1-N-25.200-N-LOOP|spdtrnd_VD-N1-N-25.800-M-LOOP|spdtrnd_VD-N1-N-26.780-M-RS|spdtrnd_VD-N1-N-27.100-N-LOOP|spdtrnd_VD-N1-N-27.970-M-LOOP|spdtrnd_VD-N1-N-28.670-M-LOOP|spdtrnd_VD-N1-N-30.550-M-RS|spdtrnd_VD-N1-N-31.830-N-LOOP|spdtrnd_VD-N1-N-32.200-M-LOOP|spdtrnd_VD-N1-S-18.710-M-LOOP|spdtrnd_VD-N1-S-20.000-M-RS|spdtrnd_VD-N1-S-21.390-M-LOOP|spdtrnd_VD-N1-S-22.700-M-LOOP|spdtrnd_VD-N1-S-23.280-N-LOOP|spdtrnd_VD-N1-S-23.900-M-LOOP|spdtrnd_VD-N1-S-24.400-M-RS|spdtrnd_VD-N1-S-25.440-N-LOOP|spdtrnd_VD-N1-S-25.850-M-LOOP|spdtrnd_VD-N1-S-26.350-M-RS|spdtrnd_VD-N1-S-26.750-N-LOOP|spdtrnd_VD-N1-S-28.840-M-RS|spdtrnd_VD-N1-S-30.540-M-LOOP|spdtrnd_VD-N1-S-31.400-M-LOOP|spdtrnd_VD-N1-S-32.080-N-LOOP|occ_VD-N1-N-18.010-M-LOOP|occ_VD-N1-N-18.770-M-RS|occ_VD-N1-N-21.230-M-LOOP|occ_VD-N1-N-22.990-N-LOOP|occ_VD-N1-N-23.780-M-LOOP|occ_VD-N1-N-24.200-M-RS|occ_VD-N1-N-25.200-N-LOOP|occ_VD-N1-N-25.800-M-LOOP|occ_VD-N1-N-26.780-M-RS|occ_VD-N1-N-27.100-N-LOOP|occ_VD-N1-N-27.970-M-LOOP|occ_VD-N1-N-28.670-M-LOOP|occ_VD-N1-N-30.550-M-RS|occ_VD-N1-N-31.830-N-LOOP|occ_VD-N1-N-32.200-M-LOOP|occ_VD-N1-S-18.710-M-LOOP|occ_VD-N1-S-20.000-M-RS|occ_VD-N1-S-21.390-M-LOOP|occ_VD-N1-S-22.700-M-LOOP|occ_VD-N1-S-23.280-N-LOOP|occ_VD-N1-S-23.900-M-LOOP|occ_VD-N1-S-24.400-M-RS|occ_VD-N1-S-25.440-N-LOOP|occ_VD-N1-S-25.850-M-LOOP|occ_VD-N1-S-26.350-M-RS|occ_VD-N1-S-26.750-N-LOOP|occ_VD-N1-S-28.840-M-RS|occ_VD-N1-S-30.540-M-LOOP|occ_VD-N1-S-31.400-M-LOOP|occ_VD-N1-S-32.080-N-LOOP|01F0248S_01F0264S_spd|traffic_state|vd_finished|m05_finished| |---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---| |2025-01-01 00:00:00|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|1|1| |2025-01-01 00:05:00|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|1|1| |2025-01-01 00:10:00|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|1|1| |2025-01-01 00:15:00|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|1|1| 4. `predict資料表`初始化如下 - 單純建表，無預設資料 |timestamp|LSTM_m05_speed_predict_5min|LSTM_m05_speed_predict_10min|LSTM_m05_speed_predict_15min|LSTM_m05_speed_predict_30min|LSTM_m05_speedlevel_predict_5min|LSTM_m05_speedlevel_predict_10min|LSTM_m05_speedlevel_predict_15min|LSTM_m05_speedlevel_predict_30min|LSTM_m05_ttf|LSTM_m05_ttc|GRU_m05_speed_predict_5min|GRU_m05_speed_predict_10min|GRU_m05_speed_predict_15min|GRU_m05_speed_predict_30min|GRU_m05_speedlevel_predict_5min|GRU_m05_speedlevel_predict_10min|GRU_m05_speedlevel_predict_15min|GRU_m05_speedlevel_predict_30min|GRU_m05_ttf|GRU_m05_ttc|RNN_m05_speed_predict_5min|RNN_m05_speed_predict_10min|RNN_m05_speed_predict_15min|RNN_m05_speed_predict_30min|RNN_m05_speedlevel_predict_5min|RNN_m05_speedlevel_predict_10min|RNN_m05_speedlevel_predict_15min|RNN_m05_speedlevel_predict_30min|RNN_m05_ttf|RNN_m05_ttc|GBRT_m05_speed_predict_5min|GBRT_m05_speed_predict_10min|GBRT_m05_speed_predict_15min|GBRT_m05_speed_predict_30min|GBRT_m05_speedlevel_predict_5min|GBRT_m05_speedlevel_predict_10min|GBRT_m05_speedlevel_predict_15min|GBRT_m05_speedlevel_predict_30min|GBRT_m05_ttf|GBRT_m05_ttc|CNN_m05_speed_predict_5min|CNN_m05_speed_predict_10min|CNN_m05_speed_predict_15min|CNN_m05_speed_predict_30min|CNN_m05_speedlevel_predict_5min|CNN_m05_speedlevel_predict_10min|CNN_m05_speedlevel_predict_15min|CNN_m05_speedlevel_predict_30min|CNN_m05_ttf|CNN_m05_ttc| |---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---| ## 1