[pgsql-jp: 37086] Re: point 型のカラムに対する INDEX の作成方法について

[haward99 ＠ yahoo.co.jp]

渡辺です。

--- ITAGAKI Takahiro <itagaki.takahiro ＠ oss.ntt.co.jp> からのメッセージ：
> > point型のカラムにINDEXをはるのに苦戦しております。
> > => create index testindex on test_tbl using gist (keidoido circle_ops);
> > ERROR:  operator class "circle_ops" does not accept data type point
> 
> 「point を circle で検索したい」という機能は、どうやら無いようです。
> 「boxをboxで検索」「同polygon」「同circle」以外は見当たりませんでした。

そういうことでしたか。。。

google maps APIだとかそういう地図情報サービスが盛んな今、
「ある円（or 四角）の内側にある座標を持つレコードをDBから拾い出して
地図上にピン立てて表示したい」
みたいな要件ってよくあることだと思うので、
これは残念というかほしい機能ですね。

> 正統派な方法は、GiSTの演算子クラスを自分で作ることです。
> point @ circle などは既にありますので、GiSTまわりのみ必要です。

Ｃに精通した勇者におまかせします。（苦笑）

> また、検索のときのWHEREの書き方ですが、
> ↑のように、絞込み条件が右辺にまとまるように書いてください。
> ↓の書き方ではインデックスが使われません。

おっと。言われてみればそのとおり。ご指摘感謝です。

おっしゃるようにpointを半径ゼロのcircleとみなして関数インデックスを使うという
作戦をためしたところ、うまいことindexが使われるようになって
SeqScanと比べて実行速度が100倍超になりましたので、それでいこうと思います。
ありがとうございました。

=>create table test_tbl (
    keidoido point
);
＃経度緯度のデータを10万件ほどつっこむ
=>select * from test_tbl;
         keidoido
----------------------
 (141.341,43.067)
 (139.3103,35.352)
 (139.019,35.1209)
...

=> explain analyze select * from test_tbl where circle(keidoido,0) @
circle(point(139.0123,35.0123),0.1);
                                                  QUERY PLAN                       
            
--------------------------------------------------------------------------------------------------------------
 Seq Scan on test_tbl  (cost=0.00..2459.04 rows=118 width=16) (actual
time=128.802..214.017 rows=160 loops=1)
   Filter: (circle(keidoido, 0::double precision) @
'<(139.0123,35.0123),0.1>'::circle)
 Total runtime: 214.138 ms
(3 rows)

=> create index testindex on test_tbl USING gist (circle(keidoido,0)); 

=> explain analyze select * from test_tbl where circle(keidoido,0) @
circle(point(139.0123,35.0123),0.1);
                                                      QUERY PLAN                   
            
----------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on test_tbl  (cost=3.41..310.93 rows=118 width=16) (actual
time=0.336..0.560 rows=160 loops=1)
   Filter: (circle(keidoido, 0::double precision) @
'<(139.0123,35.0123),0.1>'::circle)
   ->  Bitmap Index Scan on testindex  (cost=0.00..3.41 rows=118 width=0) (actual
time=0.309..0.309 rows=223 loops=1)
         Index Cond: (circle(keidoido, 0::double precision) @
'<(139.0123,35.0123),0.1>'::circle)
 Total runtime: 0.694 ms
(5 rows)