GIS应用实践: PG内置卡尔曼滤波器清理 GPS 噪声数据

本期播客

在 Postgres 中实现卡尔曼滤波器清理 GPS 噪声数据

使用 SQL 函数、递归查询和聚合直接在数据库中清理噪声数据.

现代 GPS 数据集噪声严重：卫星漂移、建筑物散射信号，以及消费类设备频繁引入误差。当处理来自车辆、智能手机或物联网设备的数百万个位置样本时，这些噪声会导致分析不可靠。路线跳跃、轨迹曲折，以及异常值会扭曲聚合结果。

题外话, 举个例子, 假设我们要获取车辆经过每个摄像头的轨迹. 首先要求车辆被路上的每个摄像头拍到, 但是, 可能因为摄像头盲点、或者车辆太多导致车牌被遮挡、或者其他原因, 某些摄像头没有拍到经过车辆. 在地图中显示该车辆的点时, 就会有缺失. 我以前在培训PPT中分享过类似案例( https://github.com/digoal/blog ), 使用pgrouting插件, 通过路径规划将缺失点补齐. 
《行程规划 , 商旅问题 , 旅游问题 , postgis , pgrouting , postgresql , Traveling Salesman Problem (or TSP)》
《[未完待续] pgrouting在机票业务中的应用 - 实时最佳转机计算》
《路径规划应用 pgRouting 实践与开放地图导入 - Openstreetmap PBF》

今天这篇文章又是另一个噪音场景, 给了另一个更高级的思路, 卡尔曼滤波器.

以下内容翻译自： https://neon.com/blog/implementing-a-kalman-filter-in-postgres-to-smooth-gps-data

卡尔曼滤波器( https://en.wikipedia.org/wiki/Kalman_filter )是平滑(消除噪音)此类数据的标准技术。传统上，它应用于数据库外部，例如 Python 或 MATLAB 等环境。但对于存储在 Postgres 中的大规模数据集，直接在数据库内部进行过滤具有明显的优势：无需额外的处理流程，结果可立即以 SQL 形式获取，并且能够进行数十亿行的可扩展分析。 (move code在这里比move data更高效)

为了方便我们在traconiq( https://neon.com/blog/why-traconiq-migrated-from-aws-rds-to-neon )的使用，我们构建了一个开源项目，用 Postgres 实现了卡尔曼滤波器。如果您有 GPS 数据，可以在这里探索代码并亲自尝试：

https://github.com/traconiq/kalman-filter-neon

背景：什么是卡尔曼滤波器？

卡尔曼滤波器是一种递归算法，用于根据噪声观测值估计动态系统的真实状态，例如移动车辆的位置。每一步都包含两部分：

• 预测：使用运动模型来预测下一个位置（以及可选的速度）。
• 更新：使用最新观察到的测量值来纠正该预测。

卡尔曼滤波器之所以有效，是因为它明确地模拟了不确定性。过程（系统如何演化）和测量值（GPS定位）都带有噪声，这些噪声以协方差矩阵表示。随着新数据的到来，滤波器会不断优化其估计值，从而产生比原始 GPS 数据更接近真实轨迹的平滑轨迹。

因此，卡尔曼滤波器广泛应用于导航、机器人、信号处理和金融领域。应用于 GPS 数据时，它们可以将抖动的位置样本转化为适合分析或可视化的真实路径。

置信度极低（HDOP 值较高）的人工 GPS 轨迹。红色轨迹：观测值。紫色轨迹：滤波后的数据。

一条真实的 GPS 轨迹，其中包含异常值（蓝色轨迹：传感器数据，红色轨迹：滤波后的轨迹），且该区域置信度极低（HDOP 值较高）。向北跳跃的异常值虽然被衰减和平滑，但并未完全消除。

项目：在 Postgres 中实现卡尔曼滤波器

使用 SQL 执行此操作的挑战

在 Postgres 中实现卡尔曼滤波器有一些困难。与过程式语言不同，SQL 不会自然地跟踪跨行的“状态”，并且滤波器依赖于将信息从一个步骤传递到下一个步骤。

特别需要满足三个要求：

• 状态：滤波器不仅必须存储上一次的位置估计，还必须存储描述其不确定性的协方差矩阵。两者都是计算下一个估计所必需的。
• 过渡：每次测量新值时，用户定义的函数必须更新状态。该函数可以整合其他可用的传感器数据，例如报告的精度（HDOP）或测量速度，以调整甚至跳过更新。
• 排序：GPS 点必须严格按照时间顺序处理。过滤器只有在每一步都建立在前一步的基础上时才有效，这意味着查询需要遵循记录顺序。

是不是有点像流计算?

对于在线过滤，状态可以按设备存储，并在每次插入时更新。对于跨历史轨迹的离线过滤，过滤器的顺序性使其更难以在 SQL 中高效实现，需要更高级的技术。

我们是如何做到的

此 repo 中的所有代码： https://github.com/traconiq/kalman-filter-neon

schema

example-schema.sql 脚本新建了一个名为 kalman 的schema，其中包含两个核心表。此设计确保下一个过滤步骤所需的状态（估计值 + 协方差）始终可用：

• kalman.positions： 存储原始 GPS 点以及在线过滤期间创建的过滤位置。
• kalman.devices： 存储设备信息，包括每个设备的最后已知位置估计和协方差矩阵。

函数

执行卡尔曼过滤步骤的主要函数是kalman.kalman_step ：它采用先前的估计和协方差以及当前测量值，并返回更新的估计值。对于在线过滤，wrapper函数（kalman.kalman_upsert_position）处理将新的 GPS 点插入到kalman.positions表并同时应用卡尔曼步骤，更新kalman.devices表内设备的状态。

在线过滤与离线过滤

每个 GPS 点插入时都会进行在线过滤。这确保了平滑后的位置始终可用，但代价是插入延迟更高。

离线过滤功能稍后会批量应用于持仓历史记录。这可以通过两种方式实现：

• 递归 CTE：逐步遍历有序的 GPS 历史记录，逐条记录地传递过滤器状态。透明，但速度较慢。
• 自定义聚合函数：在合并行时反复应用过滤器。对于大规模后处理来说更高效，并且与 SQL 分析自然契合。

基准测试

太长不看:

• 如果您需要立即获取已筛选的位置，在线筛选是可行的，但每次插入位置数据的成本更高。
• 离线筛选最好使用自定义聚合来提高效率。递归 CTE 对于调试和透明度很有用，但对于规模较大的生产环境的工作负载而言并非最佳选择。

当然，我们想测量性能。我们运行了四个 pgbench 脚本：

• benchmark_insert_nofilter.sql 插入 GPS 点而不进行过滤
• benchmark_insert_upsert.sql 使用在线过滤插入 GPS 点
• benchmark_offline_recursive.sql 通过递归查询进行离线过滤。
• benchmark_offline_aggregate.sql 通过自定义聚合进行离线过滤

所有基准测试均在同一台机器和数据集上运行，使用 pgbench -f <script>.sql -t 1000 。

结果表明，在插入过程中使用过滤器是可行的，但会带来显著的性能损失：吞吐量下降约 35% 到 40%。对于必须立即获取平滑数据的应用来说，这种权衡或许是可以接受的。

与离线过滤方法相比，差异显而易见。递归查询虽然有效，但会增加相当大的开销。相比之下，自定义聚合可以实现更高的吞吐量，因此是大型数据集或批处理的首选方法。

示例用法

https://github.com/traconiq/kalman-filter-neon/blob/main/example-schema.sql 脚本完整演示了如何在 Postgres 内部直接使用卡尔曼滤波器进行实时平滑和大规模后处理。

亲自尝试

您可以在 repo 中探索完整的实现： https://github.com/traconiq/kalman-filter-neon

1、运行example-schema.sql以创建schema、表和函数

2、加载example-usage.sql以插入样本数据并查看在线和离线过滤的实际效果。

3、使用基准测试脚本（benchmark_insert_nofilter.sql、benchmark_insert_upsert.sql、benchmark_offline_recursive.sql、benchmark_offline_aggregate.sql）进行实验来比较您自己的环境中的性能。

最后附一则济南的线下峰会消息:
PostgreSQL & IvorySQL 2026 年度峰会将于4月份在济南召开，这是目前国内规模最大的PG峰会. 
我是新特性分论坛出品人，欢迎报名参与分享，主委会可解决分享嘉宾住宿和路费。
报名地址：  https://jsj.top/f/uebqBc

议题方向：

• PostgreSQL 新功能
• PostgreSQL 内核机制与性能优化
• PostgreSQL 扩展程序
• AI + PostgreSQL 技术实践
• 云原生PostgreSQL或IvorySQL
• PostgreSQL 用户实践
• IvorySQL 兼容性与生态实践
• 基准测试与性能调优
• 高可用性技术
• …… 任何与PostgreSQL或IvorySQL相关的内容