楽屋

Presto で幾何データを操作してみよう

はじめに

こんにちは. DATUM STUDIO の矢野です. 休日は飛行機やその航路を眺めたり, 位置情報ゲーム片手に散歩を楽しんでおります. 本日は, そんな趣味とも関連がありそうな幾何データを SQL で扱うお話をいたします.

2017年10月, Presto に geospatial 関数の機能が バージョン 0.186 で追加されました. geospatial 関数とは, 点や折れ線, 多角形といった geometry というオブジェクトを取り扱う関数で, どこに何があるのような モノの位置関係を表現し, 位置情報の処理を書きやすくします.

geospatial 関数は MySQL, PostgreSQL, SQL Server などの RDBMS では既にサポートされていますが, Presto に機能が追加・拡充されることで, 既に Presto にある大量データの活用方法が増えたり, アプローチの仕方が増えるものと思われます.

そこで, 数回にわけて geospatial 関数は初めての方にむけて基本的な関数の紹介, 活用例について書いてみたいと思います. これを書いている時点では制約はあるようですが (空間参照系を指定して処理できないなど), 現時点でも出来ることを試しながら geospatial 関数に馴染んでみてはいかがでしょうか.

Treasure Data で geospatial 関数に対応した Presto を使用できるようになっているので, Treasure Data で geospatial 関数の基本的な内容を操作しながらその使い方に慣れていきましょう!

geometry オブジェクトの生成

いきなりですが geometry オブジェクトとは何でしょうか? 辞書を引くと geometry とは幾何という意味だそうです. 上にも モノの位置関係 と書きましたが, 大まかにいうとモノがどういう位置にあるか, どういう形をしているかを座標平面上に表現するものです.

…具体例を見ていきましょう. これを書いている時点で, Presto で扱える geometry オブジェクトには次のようなものがあります.

  • POINT: 1点で表される地点. 例: 人が立っている場所
  • LINESTRING: 点と点を結んだ折れ線. 例: ある人が歩いたコース
  • POLYGON: 中身のある多角形. 例: 建物の敷地
  • (さらに, 複数の POINT を束ねた MULTIPOINT, 複数の LINESTRING を束ねた MULTILINESTRING, 複数の POLYGON を束ねた MULTIPOLYGON, POINT, LINESTRING, POLYGON を束ねた GEOMETRYCOLLECTION があります)

さっそく, 具体的に geometry オブジェクトを生成してみましょう.

POINT

ST_Point 関数で (x, y) 座標を指定すると, 座標平面上の点である POINT geometry が作れます.

ST_Point 関数にx座標を第1引数, y座標を第2引数に指定します.

点 (1, 2) のイメージ.
POINT(1 2) のイメージ. クエリ結果には表示されない.
ST_POINT 関数の実行結果. 0 が表示されている.
クエリ実行結果

Treasure Data で実行してみると, 0 とだけ表示されてしまいます. どのような POINT オブジェクトが生成されているかどうか見るには, 次のように書きます.

ST_AsText 関数の実行結果. 'ST_POINT(1, 2)' が表示されている.
クエリ実行結果

ST_AsText 関数を適用すると, POINT で座標は (1, 2) のような文字列が得られました. 今はとりあえず深入りせず, この結果が得られたらうまく 点 (1, 2) ができた! として先に進みましょう.

LINESTRING

座標平面上の線である LINESTRING オブジェクトは, ST_LineFromText 関数を使用して生成できます.

ここで, ST_LineFromText の引数に見慣れない文字列が出てきました. これは WKT, well-known text と呼ばれる記法に従った文字列で, 文字列で LINESTRING を表現しています.

上の例の WKT 'LINESTRING(0 0, 1 1)' は, 点 (0, 0) から点 (1, 1) に向かう線分です. 入力の際は, x 座標と y 座標の間は半角スペースで区切ること, カンマは点と点を区切るのに使用されることに要注意です.

点(0, 0) と点 (1, 1) を結ぶ線分.
LINESTRING(0 0, 1 1) のイメージ. クエリ結果には表示されない.

POINT の時の例と同じように ST_AsText 関数を適用しましょう.

ST_AsText 関数の実行結果.
クエリ実行結果結果

ST_LineFromText に渡した WKT 文字列がそのままクエリ結果として得られました. ST_LineFromText は, geometry 型を WKT 文字列に変換する関数なので, 全体としては ST_LineFromText が WKT → geometry の変換した後に, ST_AsText が geometry → WKT の逆方向の変換をしたため, 元の WKT に戻ってきたわけです.

これは変換して逆変換という当たり前の処理でしたが, WKT → geometry の変換が失敗する場合もありますので見てみましょう.

WKT にエラーがある場合のイメージ.
クエリ実行結果 (エラー)

上の例では2点を真っ直ぐ結ぶ線分でしたが, 折れ線も表現できます. 折れ線を表現するには, 点をつなげて表現します.

折れ線のイメージ.
LINESTRING(0 0, 1 1, 1 0, 2 0) のイメージ. クエリ結果には表示されない.

長くなりましたので LINESTRING の生成についてまとめましょう. ST_LineFromText に LINESTRING の WKT を指定すると LINESTRING geometry が得られます.

POLYGON

多角形 POLYGON は, ST_Polygon に POLYGON の WKT を指定すると POLYGON geometry が得られます.

多角形のイメージ.
POLYGON((0 0, 0 10, 10 10, 10 0, 0 0)) のイメージ. クエリ結果には表示されない.

POLYGON の WKT は, 多角形の頂点を順に結んで囲われる領域です. LINESTRING との比較で, LINESTRING と POLYGON でそれぞれ正方形を書いてみました. LINESTRING は外周のみ, POLYGON は中身も詰まっている図形が得られます.

折れ線と多角形の比較イメージ.
LINESTRING と POLYGON の比較イメージ

ここで, POLYGON の WKT にある括弧の組の数に注意です. LINESTRING のときは1組の括弧の開閉でしたが, 上の例では2組開閉しています. その理由は, POLYGON は穴の開いた多角形も表現できるからです. WKT の内側の各個の組に2つ目以降は穴の領域を指定できます.

穴の開いた多角形のイメージ.
穴の開いたポリゴンのイメージ

上は, 2つの正方形の穴が開いた例です.

geometry オブジェクトを操作しよう

geometry オブジェクトを生成できたものの, どのようなメリットがあるのか geometry オブジェクトらしい処理をいくつか書いてみましょう.

ここで紹介するもの以外にも多数 geospatial 関数があるので, リファレンスに目を通して, いろいろ試してみることをおすすめします.

点が動いた距離

ある敷地内で防犯用カメラで来場する人を撮影・識別して, 1秒おきにその人の位置を取得できたとしましょう. 得られたデータは次のような motion テーブルだったとします.

motion テーブル
person_idsequencexy
1100
1210
1301
1421
1522

person_id は個々の来場者を識別する番号, sequence をデータの連番, x, y を x, y 座標とします. このとき, 個々の来場者ごとに合計でどれぐらいの距離を移動したかを計算してみましょう.

移動距離に関するクエリの実行結果. person1 の移動距離は 5.414213562373095 と表示されている.
クエリの実行結果

このクエリの処理方針は, sequence が隣り合う点に線分を引き, その線の長さを合計して求めるクエリです. 隣り合う x と y の値から LINESTRING を生成するところで WKT 文字列を構築するための連結処理が煩雑に見えますね.

結果は \(5.414213562373095…\) です. 他方, 隣り合う点を LINESTRING として扱い, その距離を ST_Length で計算した結果は:

  1. \((0, 0)\) と \((1, 0)\) の距離: \(\sqrt{(0-1)^2 + (0-0)^2} = \sqrt{1} = 1\)
  2. \((1, 0)\) と \((0, 1)\) の距離: \(\sqrt{(1-0)^2 + (0-1)^2} = \sqrt{2} = \sqrt{2}\)
  3. \((0, 1)\) と \((2, 1)\) の距離: \(\sqrt{(0-2)^2 + (1-1)^2} = \sqrt{4} = 2\)
  4. \((2, 1)\) と \((2, 2)\) の距離: \(\sqrt{(2-2)^2 + (1-2)^2} = \sqrt{1} = 1\)

合計して, \(1 + \sqrt{2} + 2 + 1 = 4 + \sqrt{2} = 5.414213562373095\dots\)

LINESTRING に変換して ST_Length を適用するほか, 1行ごとに POINT geometry に変換し, 隣り合う sequence で POINT 同士を ST_Distance で計算する方法もあります. こちらの場合, 文字列連結の必要がなく, クエリも短くて済みます.

お部屋の広さ

エレベータを中央に配した住居の間取りが少し前に話題になりました. あの間取り図のようなお部屋を題材に, 部屋面積を測ってみましょう.

今回考える間取りは, 中心に一辺 4m の正方形状のエレベータホール, その周りに 4m 幅の洋室, その周りに 2m 幅のバストイレ, さらにその周りに 4m 幅の LDK があるとします.

間取りのイメージ.
間取りイメージ. 考えたいのは青で塗られたスペースの面積

MULTIPOLYGON で外側の LDK 部分と 内側の洋室部分を分けて表現します. そしてその面積を求めるには, ST_Area を用います.

クエリの計算結果. 面積は 448.0 と計算されている.
クエリ実行結果

学校の算数の試験では, どういう形状かひとつひとつ確認しながら足し算引き算を工夫して計算すると思いますが, geometry オブジェクトで表現できれば ST_Area で統一的に面積を求められ, クエリもわかりやすくてよいですね.

まとめ

以上, Presto に追加された geospatial 関数の紹介として geometry オブジェクトを生成し, 簡単な計算を試してみました. 次回からは, geospatial 関数を用いてより実際にありそうな設定の問題に取り組んでみたいと思います.

繰り返しになりますが, ぜひリファレンスを読んで, いろいろ試してみてください.