Skip to content

空から飛行機が消えた日: ClickHouse の FORMAT PNG で FAA システム障害を描画する

alexey-milovidov.webp
2026年11月18日 · 20分で読む

2026年11月13日 (金)、FAA の障害によりアメリカ全土で航空便の運航が停止しました。本記事では、adsb.exposed の ADS-B データをもとに、アメリカ本土 48 州の上空におけるその 1 日全体の様子を 5 分あたり 1 フレームで可視化します。

Loading video...

このアニメーションは ClickHouse 自体がレンダリングしています。プロットライブラリも画像処理スクリプトも使っていません。SQL クエリが 1 ピクセルあたり 1 行を返し、組み込みの PNG 出力フォーマットがそれらの行をアニメーション PNG に変換します。地図、タイトル、時計、下部のタイムラインはすべて同じ結果セットの行です。文字でさえ SQL であり、map リテラルに格納した 3×5 ビットマップフォントで描かれています。

同じアニメーションはアニメーション PNG、GIF、MP4 でも公開しています。

データが示すもの

アニメーションでは、各航空機が 5 分間に進んだ距離を表す短い軌跡として描かれます。配色は adsb.exposed のデフォルトパレットに従っています:

  • 赤は高度が高くなるにつれて薄くなります。
  • 緑は地上から 5,000 フィートにかけて強くなるため、空港周辺の上昇・降下は黄色やオレンジ色に見えます。
  • 青は対地速度を表すため、高高度を巡航する機体は青く表示されます。

午前中は普段の金曜日と変わりません。航空網は東海岸から西へと埋まっていき、米国東部時間 (ET) 13:00〜14:00 頃には、フレーム内の空域で 1 時間あたり約 15,000 機が飛行しています。15:40 ET 頃から位置情報の件数が減り始め、午後の残りの時間も減り続けます。まず高高度の軌跡がまばらになり、続いて空港が次々と消えていきます。20:00 ET になる頃には、点在する低高度のフライトとフレーム端のわずかな通過機だけが残ります。

フレーム内の 1 時間ごとの数値 (ICAO アドレスで一意化した航空機数、および 18,000 ft 以上で観測された機数):

時刻 (ET)位置情報数航空機数FL180 以上
12:004,038,96314,7875,850
13:004,151,84914,8856,046
14:004,217,01414,9575,995
15:003,991,42713,9585,376
16:002,556,06610,0803,196
17:001,465,5496,3041,658
18:00949,4054,084907
19:00543,0532,592632
20:00418,4862,131559
21:00566,1552,6411,059
22:00819,0723,1321,731
23:00796,6773,1281,929

20:00 ET には、巡航高度の航空機数は午後早い時間帯の 10 分の 1 未満に落ち込んでいます。比較として、前夜で最も静かだった時間帯 (03:00 ET) でも 818 機ありました。21:00 ET 以降は高高度のトラフィックが戻り始めます。

以下は 14:00 ET (上) と 20:00 ET (下) の 2 つの 10 分間のウィンドウを並べた静止画です。公開されている adsb.exposed サーバー上の単一クエリでレンダリングしました (詳細は後述):

14:00 ET vs. 20:00 ET

同じ期間のデータはインタラクティブに探索できます: adsb.exposed, Nov 11–16。

データ

adsb.exposed は、adsb.lol、airplanes.live、ADSB Exchange からのすべての位置レポートを ClickHouse のテーブル planes_mercator に格納しています。行数は 3,800 億行あり、各行に時刻、座標、高度、速度、機体の情報が入っています。座標は Web メルカトル図法でも保持されており、事前計算された 2 つの UInt32 カラムとして格納されています:

mercator_x UInt32 MATERIALIZED 4294967295 * ((lon + 180) / 360),
mercator_y UInt32 MATERIALIZED 4294967295 * ((1 / 2) - ((log(tan(((lat + 90) / 360) * pi())) / 2) / pi())),

世界全体が 2³² × 2³² の正方形になります。テーブルは Z 階数曲線である mortonEncode(mercator_x, mercator_y) でソートされています。地図タイルや地図上の任意の矩形は mercator_x と mercator_y の範囲になるため、主キーによってこの範囲を低コストに読み取れます。座標からピクセルへの変換はビットシフト 1 回で済みます。mercator_x >> 19 で世界が 2¹³ = 8192 ピクセルに分割され、これはウェブサイト上のズームレベル 5 に相当します。この縮尺では、西経 128° から西経 64°、北緯 22° から北緯 51° の矩形は 1456 × 840 ピクセルになります。

ステップ 1: サーバー上での集計

すべてのフレームとすべてのピクセルについて、サーバー側で位置情報数をカウントし、高度と速度の平均を計算します (pixels.sql):

WITH
    toUInt32(4294967295 * ((-128 + 180) / 360)) AS x0,
    toUInt32(4294967295 * ((-64 + 180) / 360)) AS x1,
    toUInt32(4294967295 * (1 / 2 - log(tan((51 + 90) / 360 * pi())) / 2 / pi())) AS y0,
    toUInt32(4294967295 * (1 / 2 - log(tan((22 + 90) / 360 * pi())) / 2 / pi())) AS y1,
    toDateTime('2026-11-13 05:00:00', 'UTC') AS t0,
    t0 + {h:UInt32} * 3600 AS hour_begin
SELECT
    toUInt16(intDiv(toUnixTimestamp(time) - toUnixTimestamp(t0), 300)) AS frame,
    toUInt16(bitShiftRight(mercator_x - x0, 19)) AS px,
    toUInt16(bitShiftRight(mercator_y - y0, 19)) AS py,
    toUInt32(count()) AS c,
    toFloat32(avg(altitude)) AS alt,
    toFloat32(avg(ground_speed)) AS speed
FROM planes_mercator
WHERE time >= hour_begin AND time < hour_begin + 3600
    AND mercator_x >= x0 AND mercator_x < x1
    AND mercator_y >= y0 AND mercator_y < y1
GROUP BY frame, px, py;

対象とする 1 日は米国東部時間基準の 1 日で、ET の午前 0 時は 05:00 UTC です。このクエリは 4,250 万件の位置情報を読み取り、288 フレームのいずれかで少なくとも 1 機が観測されたピクセルごとに 1 行、合計 900 万行を返します。全 120 万ピクセルのうち、1 フレームあたり平均で約 31,000 ピクセルが点灯する計算です。

adsb.exposed の公開エンドポイントは、結果の行数を約 100 万行に制限しています。そのため、クエリは時間をパラメータ {h:UInt32} として受け取り、24 回実行します。clickhouse-local を使えばこれを 1 つのステートメントで処理できます。url テーブル関数が URL 内の {0..23} を 24 個の URL に展開する仕組みで、ここではそのグロブをクエリパラメータ自体に指定しています:

URL="https://kvzqttvc2n.eu-west-1.aws.clickhouse-staging.com/?user=website&default_format=RowBinary&param_h={0..23}&query=$(python3 -c 'import sys, urllib.parse; print(urllib.parse.quote(sys.stdin.read(), safe=""))' < pixels.sql)"

clickhouse local --query "
    SELECT * FROM url('$URL', RowBinary, 'frame UInt16, px UInt16, py UInt16, c UInt32, alt Float32, speed Float32')
    INTO OUTFILE 'pixels.native' TRUNCATE FORMAT Native
    SETTINGS max_threads = 6"

default_format=RowBinary の指定に注意してください。url 関数はクライアントにどのフォーマットでパースするかは伝えますが、サーバーにどのフォーマットで送信するかは伝えません。website ユーザーのデフォルトは TSV であるため、TSV を RowBinary としてパースしようとすると「Cannot read all data」という分かりにくいエラーで失敗します。

結果は事前集計済みピクセルが入った 160 MB のファイルになり、以降のステップはすべてローカルで実行します。

ステップ 2: FORMAT PNG でレンダリングする

ClickHouse には、クエリの結果を画像としてレンダリングする出力フォーマットがあります。カラーモードはカラム名で決まり、RGB なら r, g, b、RGBA なら r, g, b, a、グレースケールなら v を使います。整数型の x カラムと y カラムがあれば、それらがキャンバス上の各行の位置を指定します。ない場合はスキャンライン順にピクセルを埋めていきます。さらに t カラムがあれば、結果は t の値ごとに 1 フレームを持つアニメーション PNG になります。各フレームは次の t の値まで表示され、output_format_image_time_multiplier_seconds と output_format_image_time_divisor_seconds の設定で t の単位を決めます。ここでは 2/25 秒に設定しているため、5 分単位の各フレームが 80 ms 再生され、1 日全体が 23 秒に収まります。

2 つの行が同じピクセルに重なった場合は、後から来た行が勝ちます (ペインターズアルゴリズム)。そのため、画像は ORDER BY layer でソートしたレイヤー構造で組み立てます:

  1. ゴースト (残像)。 1 日全体のトラフィックをピクセルごとに対数スケールで合算し、全フレームに暗いグレーで描画します。空から機体が消えたときも米国の輪郭が見えるようにするためです。405,000 ピクセルあり、ARRAY JOIN range(frames) で 288 フレームに複製します。
  2. 航空機。 現在のフレームの航空機を、高度と速度で色分けして描画します。
  3. タイムライン。 各フレームの位置情報数を地図の下に棒グラフとして描画します。現在のフレームをハイライトし、過去は未来よりも明るいグレーで表示します。
  4. テキスト。 各文字列を文字に分割し、各文字をグリフの 15 ビットに分割し、点灯している各ビットを scale × scale ピクセルの正方形に展開します。4 つの ARRAY JOIN を順に重ねて直積を作っています。時計は America/New_York と UTC タイムゾーンでの通常の formatDateTime です。

クエリの全体は以下のとおりです (render.sql):

-- Renders the animation from the per-frame, per-pixel aggregates in `pixels.native`.
-- Every row of the result is one pixel of one frame: (t, x, y, r, g, b).
-- Rows are painted in the order they arrive, so the layers are sorted: background first, text last.

WITH
    1456 AS width,          -- map width in pixels
    840 AS map_height,      -- the timeline goes below the map
    288 AS frames,          -- one frame per 5 minutes

    pixels AS
    (
        SELECT * FROM file('pixels.native')
        WHERE px < width AND py < map_height AND frame < frames
    ),

    -- The faint trace of the whole day, repeated in every frame, so that the outline of the US
    -- is still there when the sky is empty.
    ghost AS
    (
        SELECT px, py, toUInt8(pow(log1p(total) / log1p(max(total) OVER ()), 1.5) * 55) AS v
        FROM (SELECT px, py, sum(c) AS total FROM pixels GROUP BY px, py)
        QUALIFY v >= 4
    ),

    -- The number of positions in every frame, for the timeline.
    activity AS
    (
        SELECT frame, sum(c) / max(sum(c)) OVER () AS share
        FROM pixels
        GROUP BY frame
    ),

    -- A 3x5 bitmap font: five rows of three bits each.
    map(
        '0', '111101101101111', '1', '010110010010111', '2', '111001111100111', '3', '111001111001111',
        '4', '101101111001001', '5', '111100111001111', '6', '111100111101111', '7', '111001001001001',
        '8', '111101111101111', '9', '111101111001111', ':', '000010000010000', '.', '000000000000010',
        '-', '000000111000000', ' ', '000000000000000',
        'A', '010101111101101', 'B', '110101110101110', 'C', '111100100100111', 'D', '110101101101110',
        'E', '111100110100111', 'F', '111100110100100', 'G', '111100101101111', 'I', '111010010010111',
        'N', '110101101101101', 'O', '010101101101010', 'P', '111101111100100', 'R', '110101110101101',
        'S', '111100111001111', 'T', '111010010010010', 'U', '101101101101111', 'V', '101101101101010',
        'X', '101101010101101', 'Y', '101101010010010') AS font,

    -- All the text, one row per string: the frame, the position of the top-left corner, the scale, the color.
    texts AS
    (
        SELECT number AS frame, 20 AS x0, 16 AS y0, 4 AS scale,
            'FAA OUTAGE - FRIDAY NOV 13 2026' AS text, 255 AS r, 255 AS g, 255 AS b
        FROM numbers(frames)
        UNION ALL
        SELECT number, 20, 44, 4,
            formatDateTime(toDateTime('2026-11-13 05:00:00', 'UTC') + number * 300, '%H:%i ET', 'America/New_York')
            || '   ' || formatDateTime(toDateTime('2026-11-13 05:00:00', 'UTC') + number * 300, '%H:%i UTC', 'UTC'),
            255, 210, 110
        FROM numbers(frames)
        UNION ALL
        SELECT number, width - 20 - 12 * 8, map_height - 22, 2, 'ADSB.EXPOSED', 150, 150, 150
        FROM numbers(frames)
        UNION ALL
        -- The hour labels under the timeline: 00:00, 03:00, ..., 24:00.
        SELECT frame, toInt32(30 + (width - 60) * hour / 24 - 2.5 * 8), map_height + 72, 2,
            leftPad(toString(hour), 2, '0') || ':00', 150, 150, 150
        FROM (SELECT number * 3 AS hour FROM numbers(9)) ARRAY JOIN range(frames) AS frame
    ),

    text_pixels AS
    (
        SELECT frame,
            x0 + ((char_num - 1) * 4 + bit % 3) * scale + dx AS x,  -- glyphs are 3 pixels wide, plus 1 of spacing
            y0 + intDiv(bit, 3) * scale + dy AS y,
            r, g, b
        FROM texts
        ARRAY JOIN arrayEnumerate(splitByString('', text)) AS char_num
        ARRAY JOIN range(15) AS bit
        ARRAY JOIN range(scale) AS dx
        ARRAY JOIN range(scale) AS dy
        WHERE substring(font[substring(text, char_num, 1)], bit + 1, 1) = '1'
    ),

    -- The timeline: a bar for every frame, highlighted for the current one.
    timeline AS
    (
        SELECT
            current AS frame,
            toInt32(floor(30 + (width - 60) * bar / frames)) AS xa,
            toInt32(floor(30 + (width - 60) * (bar + 1) / frames)) - 1 AS xb,
            toInt32(48 * share) AS h,
            multiIf(bar = current, (255, 200, 80), bar < current, (110, 110, 110), (55, 55, 55)) AS color
        FROM (SELECT frame AS bar, share FROM activity) ARRAY JOIN range(frames) AS current
    )

SELECT t, toInt32(x) AS x, toInt32(y) AS y, toUInt8(round(r)) AS r, toUInt8(round(g)) AS g, toUInt8(round(b)) AS b
FROM
(
    -- Layer 0: the ghost of the whole day.
    SELECT 0 AS layer, frame AS t, px AS x, py AS y, v AS r, v AS g, v AS b
    FROM ghost ARRAY JOIN range(frames) AS frame

    UNION ALL

    -- Layer 1: the aircraft, colored with the same formula as on adsb.exposed: red fades with the altitude,
    -- green grows over the first 5000 feet (so the climbs and descents near airports are yellow), blue is the speed.
    SELECT 1, frame, px, py,
        (1 + transparency) / 2 * (1 - least(alt, 50000) / 50000) * 255,
        transparency * greatest(0, least(alt, 5000)) / 5000 * 255,
        greatest(0, least(speed, 700)) / 700 * 255
    FROM (SELECT *, pow(least(c / 30, 1), 1 / 5) AS transparency FROM pixels)

    UNION ALL

    -- Layer 2: the timeline.
    SELECT 2, frame, xa + dx, map_height + 66 - dy, color.1, color.2, color.3
    FROM timeline
    ARRAY JOIN range(xb - xa + 1) AS dx
    ARRAY JOIN range(h + 1) AS dy

    UNION ALL

    -- Layer 3: the text.
    SELECT 3, frame, x, y, r, g, b
    FROM text_pixels
)
ORDER BY layer

INTO OUTFILE 'faa-outage-2026-11-13.png' TRUNCATE
FORMAT PNG
SETTINGS
    output_format_image_width = 1456,
    output_format_image_height = 930,
    -- One unit of `t` is 2/25 of a second: 80 ms per frame, the whole day in 23 seconds.
    output_format_image_time_multiplier_seconds = 2,
    output_format_image_time_divisor_seconds = 25;

このクエリは約 1 億 3,000 万行のピクセル行を生成します。内訳はゴーストが 1 億 1,700 万行、航空機が 900 万行、残りがタイムラインとテキストです。単一マシン上で全 288 フレームを 31 秒で書き出します。

いくつか補足しておきます:

  • ピクセル値はクランプされ、浮動小数点数はスケーリングされます。 整数チャンネルは [0, 255] にクランプされ、Float チャンネルは [0, 1] にクランプされたあと 255 倍されます。ここでの色計算は [0, 255] の範囲の浮動小数点数を返すため、外側の SELECT で toUInt8(round(...)) を使って変換しています。そうしないと、点灯しているすべてのピクセルが真っ白になってしまいます。
  • フレームは互いに独立しています。 各フレームは黒いキャンバスから始まるため、ゴーストは一度だけ描画するのではなく全フレームで繰り返す必要があります。その代わり、t はどの順序で到着しても構いません。1 つのフレーム内の行同士がレイヤー順に並んでいさえすれば十分です。
  • メモリ保持とストリーミングの違い。 デフォルトでは、このフォーマットはクエリ終了まで全フレームをメモリ上に保持します。今回は 288 × 1456 × 930 × 3 バイトで約 1.2 GB です。output_format_image_streaming_animation = 1 を設定すると、t が進んだ時点で各フレームを即座に書き出し、メモリ上には 1 フレームだけを保持します。これには ORDER BY t, layer が必要です。また、その場合ヘッダー内のフレーム数は上限値にすぎなくなるため、ブラウザでは再生できるものの一部のデコーダーはエラーを出します。

FORMAT PNG はトゥルーカラーのフレームを書き出すため、288 フレームで 150 MB になります。画像は大部分が黒なので、いくつかのサンプルフレームから 256 色パレットを 1 つ作ってもほとんど劣化しません。そのパレットに変換すると、APNG は 25 MB まで小さくなります。GIF と MP4 は同じファイルから ffmpeg で生成します:

PALETTE="split[a][b];[a]palettegen=max_colors=256:stats_mode=full[p];[b][p]paletteuse=dither=none"
ffmpeg -i faa-outage-2026-11-13.png -vf "$PALETTE" -loop 0 faa-outage-2026-11-13.gif
ffmpeg -i faa-outage-2026-11-13.png -c:v libx264 -preset slow -crf 18 -pix_fmt yuv420p \
    -profile:v high -level 4.1 -movflags +faststart faa-outage-2026-11-13.mp4

MP4 は yuv420p の H.264 High プロファイルを使用しているため、QuickTime、Windows の各種プレーヤー、VLC、ブラウザ、メッセンジャーアプリで再生できます。1 ピクセル幅の細い軌跡が何千本もある映像は動画圧縮にとって苦手なため、3 つのフォーマットの中で最もサイズが大きくなります。

サーバーから直接画像を返す

adsb.exposed の公開ユーザー website は読み取り専用のため、画像サイズを変更できません。ただ、デフォルトのキャンバスは 1024 × 1024 であり、静止画にはそれで十分です。次のクエリは地図のアスペクト比を保ったまま、上下に 2 つのパネルを収めます。サーバー上で実行され、HTTP レスポンスでそのまま PNG を返します (before-after.sql):

WITH
    toUInt32(4294967295 * ((-128 + 180) / 360)) AS x0,
    toUInt32(4294967295 * ((-64 + 180) / 360)) AS x1,
    toUInt32(4294967295 * (1 / 2 - log(tan((51 + 90) / 360 * pi())) / 2 / pi())) AS y0,
    toUInt32(4294967295 * (1 / 2 - log(tan((22 + 90) / 360 * pi())) / 2 / pi())) AS y1,
    -- Two panels of 1024 x 512, one above the other; keep the aspect ratio of the map.
    512 / (y1 - y0) AS scale,
    (1024 - (x1 - x0) * scale) / 2 AS left_margin,
    time >= '2026-11-13 21:00:00' AS after,  -- 14:00 ET goes to the top panel, 20:00 ET to the bottom one
    toInt32(left_margin + (mercator_x - x0) * scale) AS x,
    toInt32((mercator_y - y0) * scale) + after * 512 AS y,
    pow(least(count() / 20, 1), 1 / 5) AS transparency
SELECT x, y,
    toUInt8((1 + transparency) / 2 * (1 - least(avg(altitude), 50000) / 50000) * 255) AS r,
    toUInt8(transparency * greatest(0, least(avg(altitude), 5000)) / 5000 * 255) AS g,
    toUInt8(greatest(0, least(avg(ground_speed), 700)) / 700 * 255) AS b
FROM planes_mercator
WHERE ((time >= '2026-11-13 19:00:00' AND time < '2026-11-13 19:10:00')
    OR (time >= '2026-11-14 01:00:00' AND time < '2026-11-14 01:10:00'))
    AND mercator_x >= x0 AND mercator_x < x1
    AND mercator_y >= y0 AND mercator_y < y1
GROUP BY x, y
FORMAT PNG;
curl 'https://kvzqttvc2n.eu-west-1.aws.clickhouse-staging.com/?user=website' --data-binary @before-after.sql > before-after.png

実行時間は 10 秒で、ClickHouse の外側では一切コードを実行していません。

手元で再現する

ファイルはすべて同じディレクトリに揃っており、make.sh が 3 つのステップを実行します。必要なものは clickhouse (FORMAT PNG を備えた最近のバージョンであればどれでも動作します。本記事は 26.10 で作成しました)、ffmpeg、そして最後のパレット変換に使う Python と Pillow です。

データの著作権表示: © adsb.lol (ODbL v1.0), © airplanes.live, © adsbexchange.com.

今すぐ始める

ご自身のデータで ClickHouse を試してみませんか。ClickHouse Cloud なら数分で利用を開始でき、$300 分の無料クレジットが付与されます。

サインアップ

この記事をシェア

  • Y Combinator icon
  • X icon
  • Bluesky icon
  • Facebook icon
  • LinkedIn icon

Subscribe to our newsletter

Stay informed on feature releases, product roadmap, support, and cloud offerings!

Follow us

XBlueskySlackGithubTelegramMeetupRSS