とりあえず、こんな感じでSQLを作って、テストデータを準備。
1 2 3 4 5 |
<?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型の場合、「点は半径ゼロの円」という考え方をして、このようにインデックスを張る。
1 2 |
# create index idxloc on location_data using gist(circle(location, 0)); CREATE INDEX |
実際に検索する場合はこのようにする。
1 2 3 4 5 6 7 8 9 10 |
# 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で検索スピードを確認。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
# 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。
インデックスを落としてもう一度。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
# 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(キロ)メートル内の検索を。