Oracle GPS 軌跡速度計算：觸發器實現相對速度欄位自動填充

業務場景

表 `GPS_LOG` 每幾秒插入一筆記錄 (車牌、時間、經度、緯度)。需自動計算「相對上一筆記錄的速度 (km/h)」並寫入 `SPEED_KMH` 欄位。

為何選擇 BEFORE INSERT 觸發器

• 插入時即計算，避免事後批次更新效能差、資料不一致。
• 觸發器內可查詢「同車牌、時間最接近的前一筆」。
• 若改用存儲過程，需應用層額外呼叫，易漏執行。

完整觸發器代碼

CREATE OR REPLACE TRIGGER trg_gps_speed_calc
BEFORE INSERT ON gps_log
FOR EACH ROW
DECLARE
    v_prev_lat  NUMBER;
    v_prev_lon  NUMBER;
    v_prev_time TIMESTAMP;
    v_dist_m    NUMBER;
    v_sec_diff  NUMBER;
BEGIN
    -- 1. 找同車牌、時間小於新記錄、最接近的一筆
    SELECT latitude, longitude, log_time
      INTO v_prev_lat, v_prev_lon, v_prev_time
      FROM gps_log
     WHERE plate_no = :NEW.plate_no
       AND log_time < :NEW.log_time
     ORDER BY log_time DESC
     FETCH FIRST 1 ROWS ONLY;

    -- 2. 計算球面距離
    v_dist_m := earth_distance(v_prev_lat, v_prev_lon, :NEW.latitude, :NEW.longitude);

    -- 3. 時間差 (秒)
    v_sec_diff := EXTRACT(DAY FROM (:NEW.log_time - v_prev_time)) * 86400 +
                  EXTRACT(HOUR FROM (:NEW.log_time - v_prev_time)) * 3600 +
                  EXTRACT(MINUTE FROM (:NEW.log_time - v_prev_time)) * 60 +
                  EXTRACT(SECOND FROM (:NEW.log_time - v_prev_time));

    -- 4. 速度 km/h = (公尺/秒) * 3.6
    IF v_sec_diff > 0 THEN
        :NEW.speed_kmh := ROUND(v_dist_m / v_sec_diff * 3.6, 2);
    ELSE
        :NEW.speed_kmh := 0;
    END IF;

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        :NEW.speed_kmh := 0; -- 首筆記錄無前驅
END;
/

輔助函數：球面距離

CREATE OR REPLACE FUNCTION earth_distance(
    lat1 NUMBER, lon1 NUMBER, lat2 NUMBER, lon2 NUMBER
) RETURN NUMBER DETERMINISTIC IS
    -- Haversine 公式，回傳公尺
    R CONSTANT NUMBER := 6371000; -- 地球半徑(公尺)
    dLat NUMBER := (lat2 - lat1) * PI() / 180;
    dLon NUMBER := (lon2 - lon1) * PI() / 180;
    a NUMBER;
BEGIN
    a := SIN(dLat/2) * SIN(dLat/2) +
         COS(lat1 * PI()/180) * COS(lat2 * PI()/180) *
         SIN(dLon/2) * SIN(dLon/2);
    RETURN 2 * R * ATAN2(SQRT(a), SQRT(1-a));
END;
/

效能優化建議

• 在 `(plate_no, log_time)` 建立複合索引，加速觸發器內查詢。
• 若寫入極高頻 (＞1000 TPS)，考慮改用非同步物化視圖或 Stream 處理 (Kafka + Flink) 計算速度，再回寫。
• 觸發器內避免複雜邏輯，保持輕量。

測試驗證

INSERT INTO gps_log (plate_no, log_time, latitude, longitude) VALUES ('京A12345', TIMESTAMP '2024-01-01 08:00:00', 39.9042, 116.4074);
INSERT INTO gps_log (plate_no, log_time, latitude, longitude) VALUES ('京A12345', TIMESTAMP '2024-01-01 08:00:10', 39.9045, 116.4080);
SELECT plate_no, log_time, speed_kmh FROM gps_log WHERE plate_no = '京A12345';
-- 預期第二筆 speed_kmh > 0