「位置情報」一覧

PostgreSQLで位置情報その2(インデックスを張る)

2012/11/27 覚え書き

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

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

<?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

1 2	# 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)

# 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)

# 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)

# 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（キロ）メートル内の検索を。

PostgreSQL, 位置情報

PostgreSQLで位置情報

2012/11/27 覚え書き

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

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

[使用例]

テーブル作成

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

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

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

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

1	# 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)

# 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)

# 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)

# 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);

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)

# 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)

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

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

PostgreSQL, 位置情報

「 位置情報 」一覧

PostgreSQLで位置情報 その2(インデックスを張る)

PostgreSQLで位置情報

「位置情報」一覧

PostgreSQLで位置情報その2(インデックスを張る)