Usually databases are treated primarily as fairly dumb data storage systems, but they can be capable of much more. Case in point the PostgreSQL database and its – Ada-based – PL/pgSQL programming language, which allows you to perform significantly more complex operations than would be realistically possible with raw SQL. Case in point the implementation of a Kalman Filter by the folk over at Traconiq, which thus removes the necessity for an external filtering pipeline.

Using a Kalman Filter is highly desirable when you’re doing something like vehicle tracking using both dead-reckoning and GPS coordinates, as it filters out noise that can be the result of e.g. GPS reception issues. As noted in the article, transferring state from one row to the next requires a bit of lateral thinking, but is doable with some creative SQL usage. As PL/pgSQL is very similar to Oracle’s PL/SQL, this same code should work there too without too much porting required.

The code for the different implementations and associated benchmarks can be found on GitHub, though the benchmark results make it abundantly clear that the most efficient approach is to run an offline aggregate processing routine. This coincides with the other batch processing tasks that are typically performed by a database server to e.g. optimize storage, so this isn’t entirely unsurprising.