您可能听说过航班雷达（实时航班追踪地图），并且在追蹤天空中的飞机时玩得很开心，但在这篇博文中，我们将向您介绍更酷的东西！
ADS-B（自动应答器依赖监视广播）是一种无线电协议，用于广播各种飞行数据。我们的联合创始人兼 CTO Alexey Milovidov 在此数据上构建了一个交互式可视化和分析工具。如果还有什么，他在这过程中发明了一种全新的艺术形式！
该网站聚合并可视化了大量的空中交通数据。这些数据托管在 ClickHouse 数据库中，并进行实时查询。您可以使用自定义 SQL 查询调整可视化效果，并从 500 亿条记录钻取到单个数据记录。结果是一些非常壮观的图像！
虽然这篇博文主要关注演示的构建方式，但请随时 跳到最后，观看一些令人惊叹的视觉效果。
或者，访问 https://adsb.exposed，找到您所在城市的视觉效果，并将其分享到社交媒体上！我们为最美丽的图像提供一件 ClickHouse T 恤，获胜者将在 我们下次社区电话会议 上宣布，时间为 4 月 30 日。
此演示的完整源代码可以在这里找到 这里。
如何获取 ADS-B 数据？
ADS-B 由安装在飞机（不仅是飞机）上的“应答器”广播。该协议未加密，并且在收集、使用或重新分发此数据方面没有任何限制。大多数客机有义务广播此数据，甚至滑翔机、无人机和某些国家的机场地面车辆。军用飞机和私人轻型飞机可能会或可能不会广播。
可以使用您自己的无线电接收器（例如，SDR 形式）从空中收集此数据，但您的接收器只能看到您所在位置特定范围内的数据。存在用于共享和交换此数据的平台。一些平台邀请参与者共享数据，但通过提供商业访问来限制其重新分发。虽然飞机广播的源数据本质上是公共领域的，但这些公司可能会从这些数据中制作和许可衍生作品。
我们使用来自两个来源的数据：ADSB.lol（提供无限制的完整历史数据：每天 3000 万到 5000 万条记录，自 2023 年起根据 开放数据库许可 提供）和 ADSB-Exchange（只提供每个月第一天数据的样本：每天约 12 亿条记录，覆盖率更高）。
另一个有希望的数据源，airplanes.live，引起了我们的注意。作者表示愿意为非商业用途提供历史和实时数据。它具有很好的覆盖范围和数据质量，我们将在接下来的几天内将其纳入。
实施细节
该网站实现为单个 HTML 页面。它不使用 JavaScript 框架，源代码也没有缩小，因此易于阅读。
渲染地图
使用 Leaflet 库以两层显示地图。背景层使用来自 OpenStreetMap 的图块来创建典型的地理地图。主层将可视化效果叠加到背景地图之上。
可视化层使用具有自定义回调函数 createTile 的 GridLayer，该函数会动态生成 Canvas 元素
L.GridLayer.ClickHouse = L.GridLayer.extend({
createTile: function(coords, done) {
let tile = L.DomUtil.create('canvas', 'leaflet-tile');
tile.width = 1024;
tile.height = 1024;
render(this.options.table, this.options.priority, coords, tile).then(err => done(err, tile));
return tile;
}
});
const layer_options = {
tileSize: 1024,
minZoom: 2,
maxZoom: 19,
minNativeZoom: 2,
maxNativeZoom: 16,
attribution: '(c) Alexey Milovidov, ClickHouse, Inc. (data: adsb.lol, adsbexchange.com)'
};
每个图块具有 1024x1024 的高分辨率，以减少对数据库的请求次数。
渲染函数使用 JavaScript 的 fetch 函数通过其 HTTP API 向 ClickHouse 发出请求
const query_id = `${uuid}-${query_sequence_num}-${table}-${coords.z - 2}-${coords.x}-${coords.y}`;
const hosts = getHosts(key);
const url = host => `${host}/?user=website&default_format=RowBinary` +
`&query_id=${query_id}&replace_running_query=1` +
`¶m_table=${table}¶m_sampling=${[0, 100, 10, 1][priority]}` +
`¶m_z=${coords.z - 2}¶m_x=${coords.x}¶m_y=${coords.y}`;
progress_update_period = 1;
const response = await Promise.race(hosts.map(host => fetch(url(host), { method: 'POST', body: sql })));
用户可以使用页面上的表单动态编辑 SQL 查询以调整可视化效果。此参数化查询接受图块坐标 (x, y) 和缩放级别作为参数。
查询以 RowBinary 格式返回图像每个像素的 RGBA 值（1024x1024 像素，1048576 行，每行 4 字节，每个图块共 4 MiB）。只要浏览器支持，它就会在 HTTP 响应中使用 ZSTD 压缩。值得注意的是，ZSTD 压缩比 PNG 在原始像素位图上效果更好！（虽然这并不令人惊讶）。
虽然图像数据经常被压缩多次，但仍有数百兆字节需要通过网络传输。这就是为什么在网络连接不良的情况下，该服务可能会感觉很慢的原因。
let ctx = tile.getContext('2d');
let image = ctx.createImageData(1024, 1024, {colorSpace: 'display-p3'});
let arr = new Uint8ClampedArray(buf);
for (let i = 0; i < 1024 * 1024 * 4; ++i) { image.data[i] = arr[i]; }
ctx.putImageData(image, 0, 0, 0, 0, 1024, 1024);
使用“显示 P3”颜色空间将数据放在画布上，以便在支持的浏览器中获得更广的色域。
我们使用三个具有不同详细程度的表：planes_mercator 包含 100% 的数据，planes_mercator_sample10 包含 10%，planes_mercator_sample100 包含 1%。加载从 1% 的样本开始，即使在渲染整个世界时也能提供即时响应。加载第一个详细程度后，它将继续加载下一个 10% 的详细程度，然后再加载 100% 的数据。这在渐进加载中提供了很好的效果。
图像数据还使用简单的 JavaScript 对象缓存在客户端
if (!cached_tiles[key]) cached_tiles[key] = [];
/// If there is a higer-detail tile, skip rendering of this level of detal.
if (cached_tiles[key][priority + 1]) return;
buf = cached_tiles[key][priority];
唯一的缺点是，浏览一段时间后，页面会占用太多内存——这是未来版本需要解决的问题。
数据库和查询
按照 ClickHouse 标准，该数据库很小。截至 2024 年 3 月 29 日，planes_mercator 表中有 444.7 亿行，并且正在不断更新新的记录。它占用 1.6 TB 的磁盘空间。
表架构如下（您可以在 setup.sql 源代码中阅读）。
CREATE TABLE planes_mercator
(
`mercator_x` UInt32 MATERIALIZED 4294967295 * ((lon + 180) / 360),
`mercator_y` UInt32 MATERIALIZED 4294967295 * ((1 / 2) - ((log(tan(((lat + 90) / 360) * pi())) / 2) / pi())),
`time` DateTime64(3),
`date` Date,
`icao` String,
`r` String,
`t` LowCardinality(String),
`dbFlags` Int32,
`noRegData` Bool,
`ownOp` LowCardinality(String),
`year` UInt16,
`desc` LowCardinality(String),
`lat` Float64,
`lon` Float64,
`altitude` Int32,
`ground_speed` Float32,
`track_degrees` Float32,
`flags` UInt32,
`vertical_rate` Int32,
`aircraft_alert` Int64,
`aircraft_alt_geom` Int64,
`aircraft_gva` Int64,
`aircraft_nac_p` Int64,
`aircraft_nac_v` Int64,
`aircraft_nic` Int64,
`aircraft_nic_baro` Int64,
`aircraft_rc` Int64,
`aircraft_sda` Int64,
`aircraft_sil` Int64,
`aircraft_sil_type` LowCardinality(String),
`aircraft_spi` Int64,
`aircraft_track` Float64,
`aircraft_type` LowCardinality(String),
`aircraft_version` Int64,
`aircraft_category` Enum8('A0', 'A1', 'A2', 'A3', 'A4', 'A5', 'A6', 'A7', 'B0', 'B1', 'B2', 'B3', 'B4', 'B5', 'B6', 'B7', 'C0', 'C1', 'C2', 'C3', 'C4', 'C5', 'C6', 'C7', 'D0', 'D1', 'D2', 'D3', 'D4', 'D5', 'D6', 'D7', ''),
`aircraft_emergency` Enum8('', 'none', 'general', 'downed', 'lifeguard', 'minfuel', 'nordo', 'unlawful', 'reserved'),
`aircraft_flight` LowCardinality(String),
`aircraft_squawk` String,
`aircraft_baro_rate` Int64,
`aircraft_nav_altitude_fms` Int64,
`aircraft_nav_altitude_mcp` Int64,
`aircraft_nav_modes` Array(Enum8('althold', 'approach', 'autopilot', 'lnav', 'tcas', 'vnav')),
`aircraft_nav_qnh` Float64,
`aircraft_geom_rate` Int64,
`aircraft_ias` Int64,
`aircraft_mach` Float64,
`aircraft_mag_heading` Float64,
`aircraft_oat` Int64,
`aircraft_roll` Float64,
`aircraft_tas` Int64,
`aircraft_tat` Int64,
`aircraft_true_heading` Float64,
`aircraft_wd` Int64,
`aircraft_ws` Int64,
`aircraft_track_rate` Float64,
`aircraft_nav_heading` Float64,
`source` LowCardinality(String),
`geometric_altitude` Int32,
`geometric_vertical_rate` Int32,
`indicated_airspeed` Int32,
`roll_angle` Float32,
INDEX idx_x mercator_x TYPE minmax GRANULARITY 1,
INDEX idx_y mercator_y TYPE minmax GRANULARITY 1
)
ENGINE = MergeTree
ORDER BY (mortonEncode(mercator_x, mercator_y), time)
此架构包含经纬度值，这些值会通过 MATERIALIZED 列 自动转换为 Web-Mercator 投影中的坐标。Leaflet 软件和互联网上大多数地图都使用此投影。Mercator 坐标存储在 UInt32 中，这使得在 SQL 查询中使用瓦片坐标和缩放级别进行算术运算变得容易。
Web Mercator 的莫顿曲线使用 minmax 索引 对表坐标进行排序，允许查询某些瓦片时只读取所需数据。
物化视图 用于生成不同详细级别的表。
CREATE TABLE planes_mercator_sample10 AS planes_mercator;
CREATE TABLE planes_mercator_sample100 AS planes_mercator;
CREATE MATERIALIZED VIEW view_sample10 TO planes_mercator_sample10
AS SELECT *
FROM planes_mercator
WHERE (rand() % 10) = 0;
CREATE MATERIALIZED VIEW view_sample100 TO planes_mercator_sample100
AS SELECT *
FROM planes_mercator
WHERE (rand() % 100) = 0;
使用 ClickHouse Cloud
我们在 ClickHouse Cloud 的预发布环境中使用一项服务。预发布环境用于测试新 ClickHouse 版本以及我们实现的新型基础设施。
例如，我们可以尝试不同类型的机器和不同规模的服务，或者我们可以测试开发中的新功能，例如分布式缓存。
预发布环境还使用故障注入：我们以一定概率中断网络连接，以确保服务正常运行。此外，它利用混沌工程：我们随机终止 clickhouse-server 和 clickhouse-keeper 的各种机器，并随机将服务缩放回不同的机器数量。这就是这个项目如何促进我们服务的开发和测试。
最后，请求也被负载均衡到备份服务。哪个服务先返回，就会使用哪个服务。这就是我们在使用预发布环境的同时避免停机的方法。
示例查询：波音 vs. 空客
考虑以下相当热门的可视化："波音 vs. 空客"。
让我们看一下此可视化的 SQL 查询。
WITH
bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor,
bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size,
tile_size * {x:UInt16} AS tile_x_begin,
tile_size * ({x:UInt16} + 1) AS tile_x_end,
tile_size * {y:UInt16} AS tile_y_begin,
tile_size * ({y:UInt16} + 1) AS tile_y_end,
mercator_x >= tile_x_begin AND mercator_x < tile_x_end
AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile,
bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x,
bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y,
y * 1024 + x AS pos,
count() AS total,
sum(desc LIKE 'BOEING%') AS boeing,
sum(desc LIKE 'AIRBUS%') AS airbus,
sum(NOT (desc LIKE 'BOEING%' OR desc LIKE 'AIRBUS%')) AS other,
greatest(1000000 DIV {sampling:UInt32} DIV zoom_factor, total) AS max_total,
greatest(1000000 DIV {sampling:UInt32} DIV zoom_factor, boeing) AS max_boeing,
greatest(1000000 DIV {sampling:UInt32} DIV zoom_factor, airbus) AS max_airbus,
greatest(1000000 DIV {sampling:UInt32} DIV zoom_factor, other) AS max_other,
pow(total / max_total, 1/5) AS transparency,
255 * (1 + transparency) / 2 AS alpha,
pow(boeing, 1/5) * 256 DIV (1 + pow(max_boeing, 1/5)) AS red,
pow(airbus, 1/5) * 256 DIV (1 + pow(max_airbus, 1/5)) AS green,
pow(other, 1/5) * 256 DIV (1 + pow(max_other, 1/5)) AS blue
SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8
FROM {table:Identifier}
WHERE in_tile
GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024
查询的第一部分计算条件 in_tile，该条件用于 WHERE 部分以筛选请求的瓦片中的数据。然后计算颜色：alpha、red、green 和 blue。它们由 pow 函数调整，以实现更好的均匀性，并限制在 0..255 范围内，并转换为 UInt8。采样参数用于调整，因此具有较低详细级别的查询将返回颜色相对一致的图片。我们按像素坐标 pos 分组，并在 ORDER BY 中使用 WITH FILL 修饰符 以填充没有数据的像素位置的零。因此，我们将获得一个具有精确 1024x1024 大小的 RGBA 位图。
报告
如果您使用鼠标右键选择一个区域或使用选择工具，它将从数据库中生成选择区域的报告。这完全很简单。例如，以下是顶级飞机类型的查询
const sql_types = `
WITH mercator_x >= {left:UInt32} AND mercator_x < {right:UInt32}
AND mercator_y >= {top:UInt32} AND mercator_y < {bottom:UInt32} AS in_tile
SELECT t, anyIf(desc, desc != '') AS desc, count() AS c
FROM {table:Identifier}
WHERE t != '' AND ${condition}
GROUP BY t
ORDER BY c DESC
LIMIT 100`;
报告是针对航班号、飞机类型、注册号（尾号）和所有者计算的。您可以点击任何项目，它将对主 SQL 查询应用筛选器。例如，点击 A388 将显示空客 380-800 的可视化。
作为奖励，如果您将光标悬停在飞机类型上，它将转到维基百科 API 并尝试查找该飞机的图片。不过，它通常会在维基百科上找到其他内容。
保存的查询
您可以编辑查询，然后分享链接。查询将被转换为 128 位哈希值并保存在同一个 ClickHouse 数据库中。
async function saveQuery(text) {
const sql = `INSERT INTO saved_queries (text) FORMAT RawBLOB`;
const hosts = getHosts(null);
const url = host => `${host}/?user=website_saved_queries&query=${encodeURIComponent(sql)}`;
const response = await Promise.all(hosts.map(host => fetch(url(host), { method: 'POST', body: text })));
}
async function loadQuery(hash) {
const sql = `SELECT text FROM saved_queries WHERE hash = unhex({hash:String}) LIMIT 1`;
const hosts = getHosts(null);
const url = host => `${host}/?user=website_saved_queries&default_format=JSON¶m_hash=${hash}`;
const response = await Promise.race(hosts.map(host => fetch(url(host), { method: 'POST', body: sql })));
const data = await response.json();
return data.data[0].text;
}
我们使用不同的用户 website_saved_queries，该用户具有不同的访问控制和配额来处理这些查询。
进度条
显示一个进度条，其中包含按行和字节处理的数据量，这很好。
const sql = `SELECT
sum(read_rows) AS r,
sum(total_rows_approx) AS t,
sum(read_bytes) AS b,
r / max(elapsed) AS rps,
b / max(elapsed) AS bps,
formatReadableQuantity(r) AS formatted_rows,
formatReadableSize(b) AS formatted_bytes,
formatReadableQuantity(rps) AS formatted_rps,
formatReadableSize(bps) AS formatted_bps
FROM clusterAllReplicas(default, system.processes)
WHERE user = 'website' AND startsWith(query_id, {uuid:String})`;
const hosts = getHosts(uuid);
const url = host => `${host}/?user=website_progress&default_format=JSON¶m_uuid=${uuid}`;
let responses = await Promise.all(hosts.map(host => fetch(url(host), { method: 'POST', body: sql })));
let datas = await Promise.all(responses.map(response => response.json()));
我们从集群中所有服务器的 system.processes 表中进行选择。它不会显示精确的进度，因为并行请求了多个瓦片，并且有多个查询，其中一些已完成，而另一些仍在进行中。查询将只看到正在进行的查询，因此处理的总记录将低于实际值。
当我们加载第一个详细级别、第二个详细级别等时，我们也会将进度条的颜色设置为不同。
缓存局部性
ClickHouse Cloud 中的服务可以使用多个副本，默认情况下，请求将路由到任意一个健康的副本。处理大量数据的查询将在多个副本中并行化，而简单的查询将只使用单个副本。数据存储在 AWS S3 中，每个副本 pod 还具有一个本地附加的 SSD，用于缓存，因此内存中的页面缓存也会影响最终的查询时间。
很棒的可视化
下面我们展示了一些 Alexey 选择的初始图像。这只是触及了表面，该网站提供了一个免费墙纸的宝库！
如果我们 放大到机场，我们可以看到飞机停在哪里，甚至可以根据制造商或航空公司对它们进行颜色编码。
在迪拜机场，绿色的毛线球是阿联酋工程的机库，在那里维护空客飞机。
