とりあえず、こんな感じで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（キロ）メートル内の検索を。