質問: フレームワーク側で緯度経度を配列で持っていて そこから住所情報を取得したいです まず 国土地理院のサイト に国内の市区町村レベルの代表点のデータがあったので CSVをダウンロードしてテーブルを作りました geo という geometry point 型カラムと address 情報の string カラム 2カラムのテーブルを作り geo にはインデックスを貼りました 以下のような定数座標との距離でソートして1個目を取り出すようなクエリを投げたのですが 結果が返ってくるのに数十秒かかってしまいます ``` SELECT address FROM tests ORDER BY st_Distance(ST_GeomFromText('POINT(139.7 35.69)'),geo) LIMIT 1; address --------------------------- 東京都新宿区西新宿一丁目1 (1 row) Time: 58566.859 ms (00:58.567) ``` 次に where 文で距離が 1km 以内のものに絞ってからソートを行うと一瞬で結果が返ってきます ``` SELECT address FROM tests WHERE st_Dwithin(ST_GeomFromText('POINT(139.7 35.69)'),geo,1000) ORDER BY st_Distance(ST_GeomFromText('POINT(139.7 35.69)'),geo) LIMIT 1; address --------------------------- 東京都新宿区西新宿一丁目1 (1 row) Time: 12.073 ms ``` 理由はおそらく st_Dwithin にはインデックスが効いているのに対して st_Distance を取ってしまった小数値にはインデックスが効かないために その値で全国市区町村数の膨大なデータ数でソートしてしまうせいかと思っています ただ距離で絞るにしても全国で一番大きな市区町村サイズで絞らなければならず その値が分からないので困っています st_Distanceを取った後の中間テーブルのカラムに インデックスを貼ることができればいいのですが そのようなことってできないでしょうか 他にも何かいいクエリ方法はないでしょうか --- 最小値が欲しいだけなのにソートでO(n・logn)かけてから1個だけ取得と言うのが無駄な気がして サブクエリで最小距離を計算(O(n)?) → 最小距離でフィルタ(インデックスがきくはず) と言うロジックも試してみたんですが多少早くなりましたがやはり取得に数十秒かかってしまいます ``` SELECT address FROM ( SELECT MIN(st_Distance(ST_GeomFromText('POINT(139.7 35.69)'),geo)) AS d FROM tests ) AS tmp JOIN tests ON st_Dwithin(ST_GeomFromText('POINT(139.7 35.69)'),geo,d) address --------------------------- 東京都新宿区西新宿一丁目1 (1 row) Time: 47973.601 ms (00:47.974) ``` サブクエリの ``` SELECT MIN(st_Distance(ST_GeomFromText('POINT(139.7 35.69)'),geo)) AS d FROM tests d ------------- 72.04043634 (1 row) Time: 48631.504 ms (00:48.632) ``` これだけを実行しても同じぐらいの時間がかかってしまいます 一番近いレコードを取得したいと言うのは割とよくやりそうなことだと思うんですが 決めうちで範囲制限する以外にインデックスを効かせる方法は存在しないんでしょうか --- なんとなく GIS データを扱うのに postgreSQL がいいのかなと思って選択しましたが やりたいことは緯度経度から住所が引きたいだけなので 他に適したデータベースシステムがあれば教えていただけるとありがたいです
回答: ボロノイセルのポリゴンを格納するカラムを用意して事前に計算しておけば、任意の座標に対する最近点の探索は座標が含まれるポリゴンの検索に置き換えることができます。(元のデータ量で現実的かどうかはわかりません) いきなり細かいデータでやるのではなく、都道府県→市町村、のように絞り込む方法も考えられます。 またはGoogle Geocoding APIのような外部のサービスを利用する手もあります。 インデックスというのは「使えばクエリが速くなる魔法」ではなく単なる「事前に作っておいた索引」です。本の索引で考えてみると、 > > 理由はおそらく st_Dwithin にはインデックスが効いているのに対して st_Distance > > を取ってしまった小数値にはインデックスが効かないために > > > 任意に指定される文字位置で項目を検索しようとしているようなものです。それに使える索引はありません。3番目の文字を基準にした索引を作っておけばパラメータが「3文字目」の時には役に立ちますが、ほかのパラメータには対応できません。 > > st_Distanceを取った後の中間テーブルのカラムにインデックスを貼ることができればいいのですがそのようなことってできないでしょうか > > > 文字位置が指定されたらそれに対応する索引を作って検索する、というような話です。コストがかかる場所が移動するだけで無意味です。