最近本を乱買（こんな言葉ある？）してるなあ。




ゴールは偶然の産物ではない~FCバルセロナ流世界最強マネジメント~

もし高校野球の女子マネージャーがドラッカーの『マネジメント』を読んだら

ダービー!!―フットボール28都市の熱狂

サッカー「見るプロ」になれる!50問50答

日本科学技術大学教授上田次郎のどんと来い、超常現象2010



はやく読まなきゃ。

ちょっと前にこれを食べました。


桃屋 辛そうで辛くない少し辛いラー油




めちゃくちゃ美味い。


ただ、予想以上に売れているらしく、品薄（品切れ？）状態が続いているらしい。


でも食べたい。




ということで、明日はこのレシピで作ってみようと思います。


サクサク美味しい食べるラー油【モモラー】

ちゃんとできてるだろか？

とりあえず、こんな感じでSQLを作って、テストデータを準備。

<？php
for($i=1; $i<=100000; $i++){
        $sql = "INSERT INTO location_data VALUES(" . $i . ", point(" . rand(10000000, 999999999)/1000000 . ", " . rand(10000000, 999999999)/1000000 . "))";
}
？>

インデックスを張れるのは『box型』『circle型』『polygon型』の3種類で、『point型』には張れないらしい。
ただ、PostgreSQLには『式に対するインデックス』というものがあって、例えばcircle型の場合、「点は半径ゼロの円」という考え方をして、このようにインデックスを張る。

# create index idxloc on location_data using gist(circle(location, 0));
CREATE INDEX

実際に検索する場合はこのようにする。

# select * from location_data
 where circle(location, 0) @ circle(point(35.173161, 136.906845),32.435939)
 order by location <-> point(35.173161, 136.906845);
  id   |        location        
-------+------------------------
 69043 | (35.091225,140.067053)
 95577 | (31.701982,137.488926)
...
 85878 | (12.450013,160.012371)
(343 rows)

EXPLAINで検索スピードを確認。

# explain analyze select * from location_data
 where circle(location, 0) @ circle(point(35.173161, 136.906845),3.243594)
 order by location <-> point(35.173161, 136.906845);
                                                        QUERY PLAN                                                        
--------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=271.28..271.53 rows=100 width=20) (actual time=8.954..8.956 rows=1 loops=1)
   Sort Key: ((location <-> '(35.173161,136.906845)'::point))
   Sort Method:  quicksort  Memory: 17kB
   ->  Bitmap Heap Scan on location_data  (cost=5.07..267.95 rows=100 width=20) (actual time=8.917..8.920 rows=1 loops=1)
         Filter: (circle(location, 0::double precision) @ '<(35.173161,136.906845),3.243594>'::circle)
         ->  Bitmap Index Scan on idxloc  (cost=0.00..5.04 rows=100 width=0) (actual time=8.861..8.861 rows=1 loops=1)
               Index Cond: (circle(location, 0::double precision) @ '<(35.173161,136.906845),3.243594>'::circle)
 Total runtime: 9.086 ms
(8 rows)

9.008ms。

インデックスを落としてもう一度。

# drop index idxloc;
DROP INDEX

# explain analyze select * from location_data
 where circle(location, 0) @ circle(point(35.173161, 136.906845),3.243594)
 order by location <-> point(35.173161, 136.906845);
                                                     QUERY PLAN                                                      
---------------------------------------------------------------------------------------------------------------------
 Sort  (cost=2092.57..2092.82 rows=100 width=20) (actual time=57.908..57.910 rows=1 loops=1)
   Sort Key: ((location <-> '(35.173161,136.906845)'::point))
   Sort Method:  quicksort  Memory: 17kB
   ->  Seq Scan on location_data  (cost=0.00..2089.25 rows=100 width=20) (actual time=39.362..57.864 rows=1 loops=1)
         Filter: (circle(location, 0::double precision) @ '<(35.173161,136.906845),3.243594>'::circle)
 Total runtime: 58.133 ms
(6 rows)

今度は58.133ms。よさそうですね。


次回は、ある地点からn（キロ）メートル内の検索を。

PostGISがメジャー（なのか？）だけど、使わないやり方。


PostgreSQLのデータ型に、平面における座標点を扱う『point型』というのがある。




[使用例]

テーブル作成

=====
# create table location_data (id integer, location point);
=====

データ挿入
x座標が100、y座標が200の場合

=====
# insert into location_data (id, location) values(1, point(100, 200));
=====

抽出すると

=====
# select * from location_data;
 id | location  
----+-----------
  1 | (100,200)
  2 | (150,100)
  3 | (200,200)
(3 rows)
=====

x座標、y座標を別々に取り出すには

=====
# select location[0] as x, location[1] as y from location_data;
  x  |  y  
-----+-----
 100 | 200
 150 | 100
 200 | 200
(3 rows)
=====

絞り込み その1
2つのpointを対角とした長方形に含まれる範囲

=====
# select * from location_data \
where location @ box(point(150, 100),point(200,300));
 id | location  
----+-----------
  2 | (150,100)
  3 | (200,200)
(2 rows)
=====

絞り込み その2
あるpointを中心とする半径rの円に含まれる範囲

=====
# select * from location_data where location @ circle(point(200, 200),100);
 id | location  
----+-----------
  1 | (100,200)
  3 | (200,200)
(2 rows)
=====

ソート

=====
# select * from location_data 
where location @ circle(point(200, 200),100) order by location <-> point(200,200);
 id | location  
----+-----------
  3 | (200,200)
  1 | (100,200)
(2 rows)
=====

とりあえず、ざっとこんな感じ。


インデックスの張り方は次の機会に。