如何用SQLite存储和管理浏览器Geolocation历史位置数据？

做位置类应用时，Geolocation API负责采集，SQLite负责记忆。一个负责实时的数据输入，一个负责持久化的数据沉淀，两者组合起来就能实现一套不依赖后端服务的本地轨迹记录系统。这篇文章把从建表、写入、去重、查询到距离计算的完整链路梳理一遍，并给出可以直接运行的代码示例。

一、位置数据表结构设计

Geolocation API每次回调会给出一组包含经度、纬度、精度、时间戳的数据，其中position.timestamp是毫秒级时间戳，position.coords里包含latitude、longitude、accuracy三个核心字段。设计表结构时，除了这几个字段，还要考虑后续查询的便利性。

下面是一个经过实践验证的表结构，时间戳同时保存了毫秒原始值和格式化后的可读时间，方便不同场景使用：

CREATE TABLE IF NOT EXISTS location_history (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    latitude    REAL    NOT NULL,
    longitude   REAL    NOT NULL,
    accuracy    REAL,
    speed       REAL,
    heading     REAL,
    ts_ms       INTEGER NOT NULL,
    ts_text     TEXT,
    created_at  TEXT DEFAULT (datetime('now', 'localtime'))
);

CREATE INDEX IF NOT EXISTS idx_history_ts ON location_history(ts_ms);

这里有几个设计要点值得展开说明。第一，ts_ms字段保留毫秒原始值，避免精度损失，同时建立索引，因为按时间范围查轨迹是最高频的操作。第二，speed和heading字段在支持的方向下会有值，不支持时为null，保留它们可以为后续判断出行方式提供依据。第三，ts_text用datetime(ts_ms/1000, 'unixepoch', 'localtime')生成，这样直接查询时肉眼可读，省去了应用层再转换一遍。

二、采集与写入：如何避免脏数据堆积

Geolocation的watchPosition会按设备节奏持续回调，如果每次都无条件写入数据库，很快就会积累大量几乎相同的坐标点。实际项目中需要一个写入策略，通常包含两个过滤条件：精度阈值和距离阈值。

先看采集端的代码，浏览器侧通过watchPosition订阅位置变化，然后调用后端接口写入：

const ACCURACY_LIMIT = 50;   // 精度差于50米的数据不要
const DIST_LIMIT     = 15;   // 与上一点距离小于15米不记录

let lastPoint = null;

function haversine(a, b) {
    const R = 6371000, rad = Math.PI / 180;
    const dLat = (b.lat - a.lat) * rad;
    const dLng = (b.lng - a.lng) * rad;
    const s = Math.sin(dLat/2)**2 +
              Math.cos(a.lat*rad) * Math.cos(b.lat*rad) * Math.sin(dLng/2)**2;
    return 2 * R * Math.asin(Math.sqrt(s));
}

navigator.geolocation.watchPosition(pos => {
    const {latitude, longitude, accuracy, speed, heading} = pos.coords;
    if (accuracy > ACCURACY_LIMIT) return;

    const cur = {lat: latitude, lng: longitude};
    if (lastPoint && haversine(lastPoint, cur) < DIST_LIMIT) return;

    lastPoint = cur;
    saveToServer({
        latitude, longitude, accuracy, speed, heading,
        ts_ms: pos.timestamp
    });
});

精度过滤的意义在于，室内或信号差的环境下accuracy可能达到几百米，这种坐标写进库里只会污染轨迹。距离过滤则通过haversine公式计算球面距离，低于阈值的点直接丢弃。这两个阈值不是固定的，步行记录类应用可以把距离阈值调小到5米，车辆轨迹类则可以放大到30米以上，需要根据实际采集密度调整。

服务端拿到数据后写入SQLite，这里用Node.js配合better-sqlite3演示，它同步的API在这种低并发写入场景下反而更简洁：

const Database = require('better-sqlite3');
const db = new Database('location.db');

const insert = db.prepare(`
    INSERT INTO location_history
        (latitude, longitude, accuracy, speed, heading, ts_ms, ts_text)
    VALUES (?, ?, ?, ?, ?, ?, datetime(?/1000, 'unixepoch', 'localtime'))
`);

function saveLocation(p) {
    insert.run(p.latitude, p.longitude, p.accuracy,
               p.speed, p.heading, p.ts_ms, p.ts_ms);
}

注意ts_text是直接在SQL里用datetime函数从毫秒值生成的，应用层不需要额外处理时间格式，减少了代码出错的可能。另外better-sqlite3的prepare语句可以复用，批量写入时性能明显优于每次都重新解析SQL。

三、轨迹查询与距离计算实战

数据落库之后，最常见的两个需求是：按时间范围取出一段轨迹，以及统计这段轨迹的总距离和移动速度。按时间查询直接走前面建好的索引：

SELECT id, latitude, longitude, ts_ms, ts_text
FROM location_history
WHERE ts_ms >= 1700000000000 AND ts_ms < 1700086400000
ORDER BY ts_ms ASC;

总距离计算可以在SQL层面完成大部分工作。SQLite从3.23版本开始内置了窗口函数lag，可以用它取出每一点的上一个点，然后在查询结果里逐行累加haversine距离。下面这个查询把相邻两点的球面距离算出来，应用层只需要对distance列求和：

WITH pts AS (
    SELECT latitude, longitude, ts_ms,
           lag(latitude)  OVER (ORDER BY ts_ms) AS prev_lat,
           lag(longitude) OVER (ORDER BY ts_ms) AS prev_lng,
           lag(ts_ms)     OVER (ORDER BY ts_ms) AS prev_ts
    FROM location_history
    WHERE ts_ms >= ? AND ts_ms < ?
)
SELECT latitude, longitude, ts_text_placeholder.*,
       CASE WHEN prev_lat IS NULL THEN 0 ELSE
           6371000 * 2 * asin(min(1, sqrt(
               power(sin((latitude - prev_lat) * 0.5 * 0.0174533), 2) +
               cos(prev_lat * 0.0174533) * cos(latitude * 0.0174533) *
               power(sin((longitude - prev_lng) * 0.5 * 0.0174533), 2)
           )))
       END AS distance,
       CASE WHEN prev_ts IS NULL OR ts_ms = prev_ts THEN NULL
            ELSE (ts_ms - prev_ts) / 1000.0 END AS gap_seconds
FROM pts;

这段SQL的核心是lag窗口函数配合haversine公式的SQL实现。每行的distance就是当前点与上一个点之间的米数，gap_seconds是两点间隔秒数，distance除以gap_seconds就能得到瞬时速度，进而判断用户是在步行（约1.5米每秒以下）、骑行还是乘车。

有一个细节要提醒：haversine公式里asin的参数在极端数据下可能略微超过1导致报错，所以外面套了一层min(1, ...)做保护。这类边界问题在真实GPS数据中并不罕见，尤其是坐标重复或精度抖动时。

四、数据膨胀后的清理与归档

长期运行后，位置历史表会持续增长。SQLite单表百万级行的查询性能依然不错，但文件体积和写入延迟会逐渐影响体验。比较稳妥的做法是定期归档：把超过保留期的数据搬到归档表，再从主表删除。

BEGIN;
CREATE TABLE IF NOT EXISTS location_archive AS
    SELECT * FROM location_history WHERE 0;

INSERT INTO location_archive
    SELECT * FROM location_history WHERE ts_ms < ?;

DELETE FROM location_history WHERE ts_ms < ?;
COMMIT;
VACUUM;

归档和删除放在同一个事务里，保证中途失败不会丢数据。删除大量行之后记得执行VACUUM回收空间，否则SQLite文件不会自动缩小。如果业务上完全不需要历史数据，也可以只保留DELETE这一步，并把归档周期设短一些，比如只保留最近30天。

另一个可选优化是对轨迹做抽稀。道格拉斯-普克算法可以把近似直线路段上的冗余点压缩掉，通常能减少一半以上的数据量而几乎不损失轨迹形状。抽稀适合在归档时对旧数据一次性执行，新数据保持原始精度。

五、几点实践经验总结

这套方案落地时有几件事值得注意。Geolocation API必须在HTTPS环境或localhost下才能使用，本地开发时直接用127.0.0.1访问即可，部署到正式环境务必配好证书。用户授权方面，enableHighAccuracy设为true能提升精度但更耗电，做轨迹记录类应用建议开启，做低频签到类则不必。

SQLite的并发写入能力有限，同一时刻只允许一个写操作。位置采集场景下写入频率通常在秒级，完全在安全范围内；但如果同一数据库还要承载其他高频写入，建议开启WAL模式（PRAGMA journal_mode=WAL;），让读写不再互相阻塞。

最后，经纬度精度问题容易被忽视。SQLite的REAL是64位浮点数，存经纬度足够，但如果需要用整型压缩存储，可以把坐标乘以10的6次方取整，需要时再除回来，能节省一半左右的存储空间，适合数据量特别大的场景。位置数据的存储方案千差万别，SQLite胜在零部署、单文件、易备份，对中小规模的轨迹应用来说是非常务实的选择。

SQLiteGeolocation历史位置存储修改时间：2026-09-11 22:56:48