您可能听说过 Flight Radar，即实时航班跟踪地图，并且在空中追踪飞机时获得了许多乐趣，但在本博客文章中，我们将向您介绍更酷的东西！！
ADS-B（广播式自动相关监视）是一种用于广播各种飞行数据的无线电协议。我们的联合创始人兼首席技术官 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 函数向 ClickHouse 的 HTTP API 发出请求
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);
数据使用“Display 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)
此架构包含 lat 和 lon 值，这些值使用 MATERIALIZED 列自动转换为 Web-Mercator 投影中的坐标。这被 Leaflet 软件和互联网上的大多数地图使用。Mercator 坐标存储在 UInt32 中，使其易于在 SQL 查询中使用图块坐标和缩放级别进行算术运算。
Web Mercator 的 Morton 曲线使用 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、红色、绿色和蓝色。它们通过 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，它将显示 Airbus 380-800 的可视化效果。
作为奖励，如果您将光标移动到飞机类型上，它将转到 Wikipedia API 并尝试查找该飞机的图片。不过，它经常在 Wikipedia 上找到其他东西。
已保存的查询
您可以编辑查询，然后共享链接。查询将转换为 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 选择的一些初始图像。这只是冰山一角，该网站提供了大量的免费墙面艺术！
如果我们放大机场，我们可以看到飞机停在哪里，甚至可以按制造商或航空公司对其进行着色
在迪拜机场，绿色的毛线团是阿联酋工程公司的机库，空客飞机在那里进行维护。
