How Turing Platform Achieves Sub‑Second Search on Trillion‑Scale Trajectory Data

Business background

Map‑intelligence verification needs to answer a simple question: “Has any vehicle actually driven on this road segment?” For low‑traffic, long‑tail roads (rural, newly built, remote), a single day may contain no traces, so the data window must be extended to 180 days, accumulating close to 10 trillion trajectory points.

Problem

These points reside in an offline data warehouse; a full‑table scan to retrieve trajectories for an arbitrary region takes several hours, making interactive verification impossible.

Solution overview

The service, called track‑search , builds an online retrieval layer on top of ClickHouse, S2 geographic library and a Go‑based service framework. It compresses the 180‑day history, stores it with a cold‑hot tiered policy, and streams results so that the first screen appears in 0.35 s regardless of total data volume.

Storage engine choice

ClickHouse was selected over the offline warehouse because it is a column‑oriented OLAP engine with sparse primary‑key indexes that can skip large data blocks. This enables “hour‑level to sub‑second” latency for point‑lookup queries.

S2 spatial indexing

Geographic regions are encoded with the S2 library, which maps latitude/longitude to a 64‑bit cell ID using a Hilbert curve. The cell IDs become part of the primary‑key ordering ( event_day, s2_id_18, traj_id), allowing the query to become a range scan on integer intervals.

SELECT * FROM trajectory_all_bos
WHERE s2_id_18 BETWEEN ? AND ?
  AND lng BETWEEN ? AND ?
  AND lat BETWEEN ? AND ?
  AND event_day BETWEEN ? AND ?
LIMIT ?

Integer encoding of coordinates

Latitude and longitude are stored as Int32 after multiplying by 1e6; speed is stored as UInt16 after multiplying by 10. This reduces each row from ~16 bytes to ~8 bytes, cuts storage by half, improves compression (delta + LZ4) and enables fast integer comparisons without floating‑point errors.

minLng := int32(param.MinLng * 1e6)   // 116.397428 → 116397428
minSpeedVal := uint16(*param.MinSpeed * 10)   // 65.5 km/h → 655

Cold‑hot tiered storage

A ClickHouse storage policy ( traj_tiered_policy) places recent partitions on local SSDs (hot layer) and older data on object storage (cold layer). When the SSD usage exceeds a threshold, the oldest parts are automatically moved to BOS, keeping hot data fast‑access while keeping total cost low (≈ ¥250 k per year).

SETTINGS index_granularity = 8192,
        storage_policy = 'traj_tiered_policy';

Read path

Convert the query region to one or more S2 integer intervals ( s2_id_18 BETWEEN ? AND ?).

Use ClickHouse’s sparse primary‑key index to binary‑search the primary.idx file and locate the relevant granules (8192 rows each).

Read only the granules that intersect the interval (granule‑level skipping).

Parallelize the scan across multiple SSD disks ( max_threads=32, max_download_threads=16).

Two‑stage query

First, a lightweight query retrieves distinct traj_id values that satisfy the spatial and temporal filters. Then a second query fetches the full points for those IDs, ordered by traj_id, point_id. This avoids pulling the entire point set in a single step.

SELECT DISTINCT traj_id FROM trajectory_all_bos PREWHERE s2_id_18 BETWEEN ? AND ?;
SELECT * FROM trajectory_all_bos WHERE traj_id IN (…) ORDER BY traj_id, point_id;

Streaming response

The service writes NDJSON lines to a buffered channel and flushes every 50 lines, allowing the front‑end to render the first screen as soon as the first few trajectories arrive. The total query time grows linearly with result size, but the user‑perceived latency stays at the initial 0.35 s.

w.Write(append(line, '
'))
if canFlush && flushCount >= 50 { flusher.Flush() }

Post‑processing pipeline

Before sending data to the client, the service runs a five‑step pipeline on the raw GPS points:

Spatial thinning : grid‑quota, distance‑decay, link‑quota or random sampling to reduce over‑dense regions.

Jump filter : drop points with inter‑point distance > 500 m to remove GPS drift.

Denoise : median filter, Gaussian smoothing, Kalman filter (single‑ or double‑direction), speed‑limit filter.

Smooth + simplify : exponential moving average followed by Douglas‑Peucker simplification.

Noise segment filter + map‑matching : discard short fragments and compute average distance to the road network to label new‑road candidates or false‑positives.

Key decisions & pitfalls

Decision 1 : abandon the offline warehouse and adopt ClickHouse; Decision 2 : use S2 to turn spatial queries into integer range scans; Decision 3 : stream results instead of materializing the full set.

Common pitfalls include mismatched coordinate systems (GCJ‑02 vs. WGS‑84), building summary tables that cannot serve detailed point queries, treating incomplete daily partitions as data loss, and schema evolution causing query failures. The article shows concrete fixes such as converting coordinates with Wgs84ToGcj02 and implementing a fallback query that drops missing columns.

Performance summary

On a 5‑node ClickHouse cluster, the service consistently delivers a first‑byte time (TTFB) of 0.35 s for both 30‑day and 180‑day windows, regardless of result size. Compression reduces the raw 37.69 TiB to 5.87 TiB (≈ 6.4×), and the sorting‑key columns achieve > 200× compression due to delta encoding. The overall annual cost is about ¥250 k, far cheaper than an all‑in‑memory solution.

Applicability

The same pattern—S2/GeoHash dimensionality reduction, integer‑based sorting‑key indexing, tiered storage, and streaming response—can be applied to any massive spatio‑temporal point‑lookup use case, such as POI search, geofence device filtering, logistics trace playback, or LBS heat‑map analysis.