ポスグレでの幾何学データ配列の使い方について質問です。

質問

ポスグレでの幾何学データ配列の使い方について質問です。

create table test_table1 (id serial, test_lseg lseg[]);
insert into test_table1(test_lseg) values (ARRAY[lseg'((0,44),(0,88))',lseg'((1,44),(1,88))',lseg'((2,44),(2,88))',lseg'((3,44),(3,88))',lseg'((4,44),(4,88))',lseg'((5,44),(5,88))'] );

という感じのテーブルを作成し、test_lsegカラムのArray内にある線分に特定の座標が含まれているかを調べるような事をしたいのですが、ARRAY内の要素を検索する際の演算子を指定する方法がよく分かりません。とりあえず以下のようなSQL文では検索できないようです。

SELECT * from test_table1
WHERE test_lseg && ARRAY[lseg'((0,80),(0,80))'];

SELECT * from test_table1
WHERE lseg'((0,80),(0,80))' && any (test_lseg);

test_lsegカラムのArray内にある線分に特定の座標が含まれているかが分かり、出来ればIndexが張れるようなやり方がありましたらご教授ください。

回答No.1

mitoneko · ベストアンサー

大概のＲＤＢＭＳで、サポートされ、一応使える配列型ですが、実は、ＳＱＬとは人一倍相性が悪いです。ついでに、インデックスとはもっと相性が悪いです。

　そもそも、リレーショナルデータベースを学ぶ時に、テーブル設計の一番最初の方で学ぶことの一つに
　「繰り返し項目を無くしましょう。」
　というのがあります。
　繰り返し項目の直接的な表現が配列であり、その変形が、「線分１」「線分２」「線分３」とフィールドを並べていくやり方です。両方とも、テーブルを設計する最初の段階で排除しなければならないデータ形式です。
　なぜかという理由の一つが、あなたが今やろうとしている事が出来なくなるからです。

　今回の事例で、線の集合が何を表すのかが今ひとつ、質問からは読み取れませんが、例えば、線の集合が一つの形を表すのだとすれば、
　create table shape_tbl (id serial, note varchar(20) , primary key (id)) ;
　create table line_tbl (shape_id integer, line_id serial, line_coordinates lseg
　　　　　　　　　　　　　primary key(shape_id, line_id));
　と構成するべきかと思います。
　これなら、質問の検索は、単純に
　select * from line_tbl where line_coordinates && lseg((0,80),(0,80));
　で検索できますし、形に関する情報も添付する必要があるのであれば、shape_tblをshape_idをキーとして連結すればＯＫです。
　形毎の座標を列挙するのも、shape_idで結合すれば、別に複雑な話ではありません。

　インデックスも、line_tblのline_coordinatesに対してなら、問題なく張れます。（ただし、線分に含まれる座標のインデックスは・・・・無理です。線分に含まれる点の数が∞ですから。両端の座標に対してなら、幾何関数を使ってインデックスを張ればいけるかな。）

　使ってはいけないのなら、なぜ、配列型をサポートするのか。ごくごくまれに、配列で表現することが素直なデータも存在するからです。でも、これを使う時には、十分な配慮と考慮が必要です。
　間違っても、配列の要素をスキャンして検索するＳＱＬなんて事は考えてはいけません。配列で表現することが素直であると宣言した限りは、そのデータは原則として一群のものとして取り扱われるはずですし、一部を扱う際には、そのための関数が用意されているはずです。演算が必要なら、その演算のための関数も用意されているべきでしょう。少なくとも、ＳＱＬ文で、配列の要素にアクセスすることは無いはずです。（例えば、integer型において、各桁のどこかに1が含まれているかどうか・・・なんてＳＱＬは書きませんよね？文字列において、部分文字列を検索する時には、専用の演算子と関数が用意されていますね？そういう事です。）

　テーブル構造を変えることが、何らかの事情で出来ないのであれば、配列内の座標を扱うための一連のユーザー関数を定義するしかないと思われます。例えば、is_exist_point(座標群, 検索座標)とか言った形の関数ですね。これがあれば、配列の中に特定の座標があるかどうかが、SQLで検索できます。がやっぱり、泥縄ですね。

お礼

詳しくご回答いただきながら、レス遅くなり大変申し訳ありません。

このデータ構造の発端は、
　・1レコードに時間の範囲を複数含める。
　・検索キーを1時刻としてその範囲内に含まれているかを高速に抽出したい。
　・マスタデータは膨大なので別テーブルに範囲データを切り出したくない。
　・時間型では遅すぎるので数値系比較で高速化したい。
というのが目的でした。

手っ取り早く考えたのは、時間をintでブロック分割してarrayで持たせIndexを貼ることでしたが、時間範囲が広ければ広いほどデータ量が非効率ですから、線分利用でデータ量を抑えられないかの検討をしていて、当該検索がうまくいかなかったために質問させていただきました。

単なるC言語であればbit配列のマトリックスが一番早いのですが、SQLでは結局数値比較されるのでそう簡単に速度効率が出ず…。

RDBMSの有用性は分かりながら、速度特化させるには比較自体も単純化したいわけでその恩恵をうけられない場合はやはり泥臭くなってしまうのですね…。なんとか、DBエンジン自体の保持機能でやりたかったのですが…。

ユーザー関数を作る方向では検討していますが、それはそれで汎用さを欠くので目下変更保留中です。

ご回答ありがとうございました。

回答No.2

yamada59

参考 URL の幾何データ型に対する演算子を使えばできます。

SELECT * FROM test_table1 WHERE point '(0,80)' <@ ANY (test_lseg);

インデックスを使うのは難しい、というかできないと思います。

参考URL：

http://www.postgresql.jp/document/8.4/html/functions-geometry.html