Deduplicating Repeated GPS Fixes from Buffered Trackers

A tracker that loses cellular coverage does not stop recording. It buffers, and when the connection returns it flushes everything it has been holding — sometimes twice, if the acknowledgement was the thing that got lost. The ingest layer is almost always at-least-once, so the same observation can arrive two or three times, minutes or hours after it was made.

The naive fix is a DISTINCT on the coordinate columns, and it is a disaster: a vehicle parked at a depot for six hours emits thousands of identical coordinates, all of them real, and deduplicating on position deletes the entire stop. This page sets out the identity a fix actually has, how to deduplicate on it, and how to keep the operation from being reintroduced incorrectly by the next person who sees a lot of repeated coordinates in the table. It supports the sort-dependent operations in trace resampling, which will not run correctly until duplicates are gone.

Compatibility and Configuration Requirements

If the feed genuinely has only one timestamp and it is the arrival time, deduplication is not possible and the upstream contract needs fixing. That is worth escalating rather than working around: a feed without observation times cannot support dwell, speed or matching correctly either.

The Deduplicator

from __future__ import annotations

import polars as pl


def deduplicate_fixes(
    fixes: pl.LazyFrame,
    key: tuple[str, ...] = ("vehicle_id", "ts_utc"),
    arrival_col: str = "received_at",
) -> pl.LazyFrame:
    """Remove retransmitted fixes, keeping the most recently received copy.

    Identity is (vehicle, observation time). Coordinates are deliberately not
    part of the key: a stationary vehicle repeats its position legitimately and
    must keep every one of those rows.
    """
    return (
        fixes.sort([*key, arrival_col])
        .unique(subset=list(key), keep="last", maintain_order=True)
    )


def duplicate_report(fixes: pl.LazyFrame, key: tuple[str, ...] = ("vehicle_id", "ts_utc")) -> pl.LazyFrame:
    """Per-device duplicate rate, for monitoring rather than for cleaning."""
    return (
        fixes.group_by(list(key))
        .agg(pl.len().alias("copies"))
        .group_by("vehicle_id")
        .agg(
            pl.len().alias("observations"),
            (pl.col("copies") - 1).sum().alias("duplicate_rows"),
        )
        .with_columns(
            (pl.col("duplicate_rows") / pl.col("observations")).alias("duplicate_rate")
        )
    )

The whole implementation is four lines because the difficulty is not in the code — it is in choosing the key. ("vehicle_id", "ts_utc") is the observation’s identity. Adding coordinates to the subset makes the operation wrong; removing the vehicle makes it catastrophic, because two vehicles can and do report at the same instant.

Execution and Tuning Guidelines

Run it at ingest, not in the analytics job. Duplicates break sorting, and sorting is a precondition for join_asof, windowed variance, and every rolling filter in outlier removal. Removing them once at the boundary is much cheaper than defending every downstream stage against them.

Keep the later arrival. Devices re-send corrected fixes: a position recomputed with more satellites, a speed field that was null in the first attempt, an accuracy estimate that arrived after the fix. Keeping the first copy systematically prefers the worse one. The sort on received_at before unique(keep="last") is what implements this, and it is easy to lose in a refactor.

Alert on the duplicate rate, per device family. A rate that jumps from 0.2 % to 9 % is a device family that has started retrying, which usually means a coverage or backend problem worth knowing about before it becomes a data problem.

Do not deduplicate across the operating-day boundary in a way that drops the earlier day’s copy. A fix observed at 23:59 and re-sent at 00:04 belongs to the earlier day. If deduplication runs per partition, the two copies never meet and both survive; run it on the observation key across a window wide enough to cover the longest buffering the devices do.

Common Pitfalls Specific to This Technique

Deduplicating on a truncated timestamp. A pipeline that casts observation time to whole seconds before deduplicating will merge genuinely distinct fixes from any device faster than 1 Hz. Keep the native resolution in the key and truncate only for display.

Using DISTINCT * and calling it deduplication. Two copies of the same observation frequently differ in one field — received_at, an ingest offset, a corrected accuracy — so DISTINCT * removes nothing while appearing to address the problem. It is worse than doing nothing, because it looks like the problem was handled.

Removing duplicates after computing derived columns. Speed, heading and displacement computed across a duplicated timestamp produce a zero time delta and therefore an infinite speed. Those rows then get flagged as outliers and removed by a later stage, which quietly deletes real observations that happened to sit next to a retransmission.

Sizing the Deduplication Window

Deduplication is only correct if both copies of a fix are visible to the same operation. A tracker that buffers for six hours will deliver its retransmission into a partition that the original left long ago, and a deduplicator scoped to one partition will keep both.

The window has to be at least as long as the longest buffering the fleet’s devices actually do, and finding that number is a measurement rather than a guess:

lag = (
    fixes.select(
        (pl.col("received_at") - pl.col("ts_utc")).dt.total_seconds().alias("lag_s"),
        "device_family",
    )
    .group_by("device_family")
    .agg(
        pl.col("lag_s").quantile(0.99).alias("p99_lag_s"),
        pl.col("lag_s").max().alias("max_lag_s"),
    )
)

A device family whose 99th-percentile lag is four minutes and whose maximum is eleven hours has one vehicle that spends its nights in an underground car park. Size the window on the maximum, not the percentile, because the percentile is exactly the number that lets rare duplicates through.

There are three practical shapes for the window, and the right one depends on how the data arrives.

Whole-batch. In a nightly job that reads a full day plus a margin, deduplicate across the entire read. Simplest, and correct as long as the margin exceeds the maximum lag.

Rolling upsert. In a warehouse that receives fixes continuously, make the observation key the primary key and let the store resolve collisions. MERGE or INSERT ... ON CONFLICT DO UPDATE gives exactly the last-writer-wins semantics wanted here, without any window at all.

Streaming with state. In a Kafka consumer, keep a bounded set of recently seen keys per vehicle and drop repeats. The set has to be bounded, which means the window is explicit and short — so this shape only works when late duplicates are also handled downstream, typically by the same upsert.

Reprocessing safely

A backfill that re-reads raw ingest will re-introduce every duplicate the first run removed. That is fine when the deduplicator runs inside the same job, and it is a data corruption when the deduplicated table is appended to rather than rewritten. Make the deduplicated dataset a function of its input partition, rewritten wholesale on each run, so replaying a day is idempotent by construction rather than by discipline.

Related

• Trace resampling and densification — the sort-dependent stage that needs this cleanup first
• Detecting stuck GPS receivers with run-length checks — the other cause of repeated coordinates, and how to tell it apart from a stop
• Snapping irregular fixes to a common time grid with Polars — the join that fails outright on duplicate keys
• Exactly-once semantics for streaming trajectory matching — the same at-least-once problem one stage later
• GPS Data Preprocessing & Cleaning Fundamentals — parent section