psqlで接続して
すべて見るなら
|
1 |
show all; |
特定のパラメータのみ(例えばshared_buffers)を見るなら
|
1 |
show shared_buffers; |
「 PostgreSQL 」一覧
PostgreSQLで位置情報 その2(インデックスを張る)
とりあえず、こんな感じで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（キロ）メートル内の検索を。
PostgreSQLで位置情報
PostGISがメジャー（なのか？）だけど、使わないやり方。
PostgreSQLのデータ型に、平面における座標点を扱う『point型』というのがある。
[使用例]
テーブル作成
|
1 |
# create table location_data (id integer, location point); |
データ挿入
x座標が100、y座標が200の場合
|
1 |
# insert into location_data (id, location) values(1, point(100, 200)); |
抽出すると
|
1 2 3 4 5 6 7 |
# select * from location_data; id | location ----+----------- 1 | (100,200) 2 | (150,100) 3 | (200,200) (3 rows) |
x座標、y座標を別々に取り出すには
|
1 2 3 4 5 6 7 |
# 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を対角とした長方形に含まれる範囲
|
1 2 3 4 5 6 |
# 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の円に含まれる範囲
|
1 2 3 4 5 6 |
# select * from location_data where location @ circle(point(200, 200),100); id | location ----+----------- 1 | (100,200) 3 | (200,200) (2 rows) |
ソート
|
1 2 3 4 5 6 |
# 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) |
とりあえず、ざっとこんな感じ。
インデックスの張り方は次の機会に。
スポンサーリンク