宣告 adsb.exposed - 基于 ClickHouse 的 ADS-B 航班数据交互式可视化和分析

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)'
};

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` +
  `&param_table=${table}&param_sampling=${[0, 100, 10, 1][priority]}` +
  `&param_z=${coords.z - 2}&param_x=${coords.x}&param_y=${coords.y}`;


progress_update_period = 1;
const response = await Promise.race(hosts.map(host => fetch(url(host), { method: 'POST', body: sql })));

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);

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];

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)

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;

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

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`;

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&param_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;
}

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&param_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()));