PolarDB:GanosBase：ラスターピクセルの空間範囲の取得

データのインポート

温度データのインポート

-- GANOS_RASTER 拡張の作成
CREATE EXTENSION GANOS_RASTER CASCADE;

-- 温度テーブルの作成
CREATE TABLE temperature(id integer, rast raster);

-- NetCDF 温度データのインポート。空間参照系を 4326 に設定
INSERT INTO temperature
VALUES (1, ST_SetSrid(ST_ImportFrom('chunk_table', 'OSS://<id>:<key>@<endpoint>/bucket/path/file.nc'), 4326));

インポート完了後、メタデータを確認します。

-- メタデータ情報の照会
SELECT st_metadata(rast) FROM temperature WHERE id =1;

デバイスデータのインポート

-- ganos_fdw 拡張の作成
CREATE EXTENSION ganos_fdw;

-- シェープファイル用の FDW テーブルの作成
SELECT ST_RegForeignTables('OSS://<id>:<key>@<endpoint>/bucket/path/file.shp') );

-- データをデータベースにインポートし、空間インデックスを作成
CREATE TABLE devices AS SELECT * from <foreign_table_name> ;
CREATE INDEX idx_devices_geom ON devices USING Gist(geom);

この例では、外部データラッパー（FDW）を用いて OSS からデバイスデータをインポートしています。その他のインポート方法については、「」をご参照ください。

温度範囲の抽出

以下のクエリは、気温が 27°C ～ 28°C のすべてのピクセルをポリゴンに変換し、ST_Union を用いて単一のジオメトリにマージします。

-- 気温が 27°C より大きく 28°C 未満のピクセルをマージ
WITH tmp AS (
    SELECT (ST_PixelAsPolygons(rast)).*
    FROM temperature
  WHERE id = 1)
SELECT ST_Union(geom)
FROM tmp
WHERE value >= 270 AND value < 279;

パフォーマンス最適化

高解像度ラスターデータの場合、ST_PixelAsPolygons はすべてのピクセルを処理するため、ピクセル数が増えるほどクエリ実行速度が低下します。ST_PixelAsPolygons の呼び出し前に、ST_Reclassify を使用して範囲外のピクセルを nodata として再分類することで、処理対象データ量を削減し、クエリ実行速度を大幅に向上させることができます。

-- 再分類の実行
With tmp As(
 SELECT ST_Reclassify(rast,
                      '[{"band":0,"remap":{"(-100,270,280, 1000]":"0,1,0"}, "nodata":false, "nodataValue":0}]',
                      '{"chunktable":"reclass_chunk_table"}') AS rast
from temperature
where id =1 ),

-- 空間範囲の返却
tmp2 AS (
  SELECT (ST_PixelAsPolygons(rast)).*
FROM tmp)
SELECT ST_Union(geom)
FROM tmp2;

空間オーバーレイ分析の実行

ST_Contains を使用して、マージされた温度ポリゴン内に位置するデバイスを特定します。

WITH tmp AS (
    SELECT (ST_PixelAsPolygons(rast)).*
    FROM temperature WHERE id = 1),
tmp2 AS (
  SELECT ST_Union(geom) as geom
    FROM tmp
    WHERE value >= 270 AND value < 279)
SELECT count(*) -- 任意の列を選択可能です。ここでは例として count 列を使用
FROM devices, tmp2
WHERE ST_Contains(tmp2.geom, devices.geom);

実行結果の例：

 count
-------
  1879