PolarDB:GanosBase リアルタイムヒートマップ集約クエリ：分析とベストプラクティス

前提条件

開始する前に、以下の要件を満たしていることを確認してください。

• GanosBase が有効化された PolarDB for PostgreSQL（Oracle 互換）インスタンス

• データベースにジオメトリまたは軌道データが読み込まれていること（すべてのオブジェクトは同一の空間参照系を使用する必要があります — ST_Srid を使用して確認できます）

• サーバーに Node.js がインストール済みであること

ステップ 1：データベースの構成

1. データのインポート

ジオメトリまたは軌道データをデータベースにインポートします。効率的なバルクインポートには、Foreign Data Wrapper（FDW）方式をご利用ください。詳細については、「」をご参照ください。

2. GiST 空間インデックスの作成

CREATE INDEX index_name ON table_name USING GIST(column_name);

3. 空間範囲によるヒートマップタイルのクエリ実行

バウンディングボックスを定義するには ST_MakeEnvelope を、タイル座標（z/x/y）から導出するには ST_TileEnvelope を使用します。

各グリッドセル内のオブジェクト数をカウントします。

SELECT ST_AsHMT(
    column_name,                                    -- ジオメトリ列
    ST_MakeEnvelope(0, 0, 10, 10, 4326),           -- バウンディングボックス
    512,                                            -- タイル幅
    512                                             -- タイル高さ
)
FROM table_name
WHERE column_name && ST_MakeEnvelope(0, 0, 10, 10, 4326);

各グリッドセル内で値列を合計します。

SELECT ST_AsHMT(
    column_name,
    ST_MakeEnvelope(0, 0, 10, 10, 4326),
    512,
    512,
    value                                           -- 各セルごとに合計される値列
)
FROM table_name
WHERE column_name && ST_MakeEnvelope(0, 0, 10, 10, 4326);

空間フィルターに加えて、追加のフィルター条件を設定します。

SELECT ST_AsHMT(
    column_name,
    ST_MakeEnvelope(0, 0, 10, 10, 4326),
    512,
    512,
    value
)
FROM table_name
WHERE column_name && ST_MakeEnvelope(0, 0, 10, 10, 4326)
AND name LIKE 'xxxx%' AND value > 100;

ステップ 2：Node.js バックエンドの構築

以下の例では、z/x/y リクエストを受け付けるタイルサーバーを構築します。このサーバーは ST_AsHMT をクエリし、protobuf 形式の結果をデコードし、chroma.js を用いて値を色にマッピングした後、PNG タイルを返却します。

ファイル構造：

└── hmt_server
    ├── app.js
    ├── hmt.proto
    ├── index.html
    └── package.json

hmt.proto は、「ST_AsHMT」リファレンスに記載されている protobuf スキーマです。

package.json：

{
  "name": "hmt_server",
  "version": "1.0.0",
  "main": "app.js",
  "license": "ISC",
  "dependencies": {
    "chroma-js": "^2.4.2",
    "express": "^4.18.2",
    "lru-cache": "^10.1.0",
    "pg": "^8.11.3",
    "protobufjs": "^7.2.5",
    "sharp": "^0.32.6"
  }
}

app.js：

実行前に、CONNECTION オブジェクト内のプレースホルダー値および TABLE_NAME、GEOMETRY_COLUMN、SRID 定数を置き換えてください。

const express = require('express');
const { Pool } = require('pg');
const chroma = require('chroma-js');
const sharp = require("sharp");
const protobuf = require('protobufjs');
const { LRUCache } = require('lru-cache');

// データベース接続の設定
const CONNECTION = {
  user: 'YOUR_USER',
  password: 'YOUR_PWD',
  host: 'YOUR_HOST',
  database: 'YOUR_DB',
  port: YOUR_PORT
};

// 対象テーブル名
const TABLE_NAME = 'YOUR_TABLE';

// 対象ジオメトリ列名
const GEOMETRY_COLUMN = 'YOUR_GEOM_COLUMN';

// ノーデータ値の設定
const NO_DATA_VALUE = 0;

// 対象ジオメトリ列の空間参照系
const SRID = 4326

// カラーマップの設定
const COLOR_MAP = [
  ['#536edb', 1],
  ['#5d96a5', 3],
  ['#68be70', 5],
  ['#91d54d', 7],
  ['#cddf37', 9],
  ['#fede28', 11],
  ['#fda938', 13],
  ['#fb7447', 15],
  ['#f75a40', 17],
  ['#f24734', 19],
  ['#e9352a', 21],
  ['#da2723', 23],
  ['#cb181d', 25]
];

// データベース接続プールの作成（デフォルトで 10 接続）
const pool = new Pool(CONNECTION);

// カラースケールの設定（カラーマップから構成）
const [colors, domains] = COLOR_MAP.reduce(([c, d], [colors, domains]) =>
  [[...c, colors], [...d, domains]], [[], []]);
const colorMap = chroma.scale(colors).domain(domains).mode('rgb')

// protobuf スキーマの読み込み
const hmtDecoder = protobuf.loadSync('./hmt.proto').lookupType('HMT');

// エリアにデータがない場合に返却される 1×1 の透明 PNG
const emptyPng = Buffer.from('iVBORw0KGgoAAAANSUhEUgAAAAEAAAABCAYAAAAfFcSJAAAACXBIWXMAAA7EAAAOxAGVKw4bAAAADUlEQVQImWP4//8/AwAI/AL+hc2rNAAAAABJRU5ErkJggg==', 'base64');

// 小規模タイル（z<5）は変更頻度が低いため、24 時間キャッシュ
const globalCache = new LRUCache({ max: 1000, ttl: 1000 * 3600 * 24 });

// 大規模タイル（z>=5）は変更頻度が高いため、12 時間キャッシュ
const localCache = new LRUCache({ max: 2000, ttl: 1000 * 3600 * 12 });

express()
  // HTML ページの配信
  .get("/", (_, res) => res.sendFile('index.html', { root: __dirname }))
  // ヒートマップタイルの配信
  .get('/hmt/:z/:x/:y', async ({ params: { z, x, y } }, res) => {
    const cache = z < 5 ? globalCache : localCache;
    const key = `${z},${x},${y}`
    if (!cache.has(key)) {
      // 小規模スケール（z<=5）では並列処理数を減らし、データベースへの負荷を抑制
      const parallel = z <= 5 ? 10 : 5;
      const sql = `
  set max_parallel_workers = ${parallel};
  set max_parallel_workers_per_gather = ${parallel};
  WITH _PARAMS(_BORDER) as (VALUES(ST_Transform(ST_TileEnvelope(${key}),${SRID})))
  SELECT ST_AsHMT(${GEOMETRY_COLUMN},_BORDER,256,256) tile
  FROM ${TABLE_NAME},_PARAMS
  WHERE _BORDER && ${GEOMETRY_COLUMN};`
      // クエリは 3 つの結果セットを返却します。ST_AsHMT の結果はそのうち 3 番目です。
      const { rows: [{ tile }] } = (await pool.query(sql))[2];

      if (!tile) cache.set(key, emptyPng);
      else {
        // protobuf 結果のデコード
        const { type, doubleValues, intValues } = hmtDecoder.decode(tile);
        const { values } = type == 1 ? doubleValues : intValues;

        // 各セル値を RGBA ピクセルにマッピング
        const pixels = values.reduce((_pixels, value) => {
          _pixels.push(...colorMap(value).rgb());
          _pixels.push(value <= NO_DATA_VALUE ? 0 : 255); // ノーデータセルは透明
          return _pixels;
        }, [])

        // 256×256 の PNG タイルとしてレンダリング
        const rawConfig = { raw: { width: 256, height: 256, channels: 4 } };
        const renderedPng = await sharp(Uint8Array.from(pixels), rawConfig)
          .png().toBuffer();
        cache.set(key, renderedPng);
      }
    }
    const tile = cache.get(key)
    res.set("Content-Type", "image/png").send(tile);
  })
  .listen(5500, () => console.log('HMT サーバーが起動しました。'));

主要な実装上の留意点：

• カラーマップには chroma.js を使用しており、16 進数文字列、CSS3 の色名など、さまざまな形式をサポートしています。全対応形式については、「chroma.js ドキュメント」をご参照ください。

• より滑らかなレンダリングを実現するため、512×512 の生データを取得し、sharp を用いて 256×256 にダウンサンプリングします。これにより応答時間が若干増加しますが、タイル境界におけるエイリアシングを低減できます。

• 並列処理の次数は、z<=5 の場合に負荷分散を図るために低減されています。ご利用のデータ量およびクラスター構成に応じて調整してください。

ステップ 3：マップフロントエンドの構築

フロントエンドでは、Mapbox GL JS を用いてバックエンドから配信されるタイルを表示します。HMT タイルは標準の PNG イメージであるため、ラスタータイルソースをサポートする任意のマップ SDK と互換性があります。

「account.mapbox.com」から Mapbox アクセストークンを取得し、以下のスニペット内の YOUR_MAPBOX_TOKEN、YOUR_LONGITUDE、YOUR_LATITUDE を置き換えてください。

index.html：

<!DOCTYPE html>
<html>
<head>
  <meta charset="utf-8">
  <title>HMT ビューアー</title>
  <meta name="viewport" content="initial-scale=1,maximum-scale=1,user-scalable=no">
  <link href="https://api.mapbox.com/mapbox-gl-js/v2.14.1/mapbox-gl.css" rel="stylesheet">
  <script src="https://api.mapbox.com/mapbox-gl-js/v2.14.1/mapbox-gl.js"></script>
</head>
<body>
  <div id="map" style="position: absolute;left:0; top: 0; bottom: 0; width: 100%;"></div>
  <script>
    let CENTER = [YOUR_LONGITUDE, YOUR_LATITUDE]
    mapboxgl.accessToken = YOUR_MAPBOX_TOKEN;
    const map = new mapboxgl.Map({
      container: 'map',
      style: "mapbox://styles/mapbox/navigation-night-v1",
      center: CENTER,
      zoom: 5
    })
    map.on("load", () => {
      map.addSource('hmt_source', {
        type: 'raster',
        minzoom: 3,
        tiles: [`${window.location.href}hmt/{z}/{x}/{y}`],
        tileSize: 256,
      });
      map.addLayer({
        id: 'hmt',
        type: 'raster',
        source: 'hmt_source',
      });
    });
  </script>
</body>
</html>

ステップ 4：サービスの実行

cd ./hmt_server
npm i
node .

ブラウザで http://localhost:5500/ を開くと、ヒートマップを確認できます。

プレビュー

3,100 万件の軌道ポイントおよび 45 万件の軌道ラインのリアルタイム集約：

3 億 800 万件の建物フットプリントのリアルタイム集約：

大規模データセット向けの並列処理の有効化

大規模データセットの場合、ST_AsHMT の実行前に並列処理パラメーターを設定してください。並列処理の次数はビューポートのズームレベルに応じて調整します。ズームレベルが高いほど空間範囲は狭くなり、スキャン対象データ量も減少します。

SET max_worker_processes = 300;
SET max_parallel_workers = 260;
SET max_parallel_workers_per_gather = 16;
ALTER TABLE table_name SET (parallel_workers=16);
SET force_parallel_mode = on;

パラメーターの全リファレンスについては、「PostgreSQL ドキュメント（リソース消費）」をご参照ください。

トレードオフ：max_parallel_workers_per_gather の値を増加させると個々のクエリは高速化されますが、CPU 使用量も増加します。複数ユーザーが同時にタイルリクエストを送信する場合、クエリ単位の高い並列処理は利用可能なワーカーを枯渇させる可能性があります。そのため、max_worker_processes および max_parallel_workers は、並列処理の次数と想定される同時実行数の積以上に設定することを推奨します。

適切なタイルサイズの選択

空間フィルターには && を ST_Intersects の代わりに使用

ST_AsHMT の計算は ST_Intersects よりも大幅に高速です。&& バウンディングボックス演算子を WHERE 句で使用することで、GiST インデックスの恩恵を受けることができます。

SELECT ST_AsHMT(
    column_name,
    ST_MakeEnvelope(0, 0, 10, 10, 4326),
    512,
    512,
    value
)
FROM table_name
WHERE column_name && ST_MakeEnvelope(0, 0, 10, 10, 4326);

クエリ範囲の空間参照系の変換

クエリのバウンディングボックスとジオメトリ列で異なる空間参照系が使用されている場合、クエリ実行前にバウンディングボックスを変換してください。自動変換に依存するとパフォーマンスが低下します。

SELECT ST_AsHMT(
    column_name,                                          -- WGS 84（SRID 4326）のジオメトリ列
    ST_Transform(ST_TileEnvelope(6, 48, 32), 4326),      -- タイルエンベロープを SRID 4326 に変換
    512,
    512,
    value
)
FROM table_name
WHERE column_name && ST_Transform(ST_TileEnvelope(6, 48, 32), 4326);

タイルを取得した後、表示用に目的の空間参照系へ変換します。

VACUUM FULL および CLUSTER の実行（空間テーブル向け）

これらの操作は、空間クエリにおける I/O 効率を向上させます。

• VACUUM FULL は空き領域を回収し、テーブルファイルを圧縮することで、クエリ時のディスク読み取りを削減します。

  VACUUM FULL table_name;

• CLUSTER は、空間インデックスに従ってテーブル行を物理的に再並べ替え、空間的に隣接するデータを隣接するデータページ上に格納します。これにより、ランダムディスクアクセスが大幅に削減されます。

  CLUSTER table_name USING index_name;

  構文の全リファレンスについては、「PostgreSQL CLUSTER ドキュメント」をご参照ください。