https://note.com/moli9ma/n/n3b530e3ad1d5

概要

mysql.5.7.12(AWS aurora)で、近傍検索を試したのでその手順をまとめます。大枠以下のような流れです。

1. データを用意する 国土交通省のページから、住所と緯度経度情報のデータをダウンロードする
2. テーブルを作成し、データを流し込む ダウンロードしたデータをテーブルにimportできるような形に整形する。
3. 近傍検索を試す。 特定の位置から、近い住所を検索する。

環境

AWS aurora

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.7.12    |
+-----------+

1) データを用意する

以下のページから、位置参照情報を取得していきます。

このページから市区町村単位を押して次へ進みます。

県と、市区町村を選択します。ここでは、とりあえず試せれば良いので八王子にしました。特に意味はありません。データ量が必要なら市区町村全域をチェックすれば良いです。 全国&&市区町村を一回で取る方法は提供されてなかった。 47回やらずに済む方法が知りたい・・・

次に進むとこのような画面になります。ここで、レベル(大字・町丁目)になっている項目を選択してダウンロードします。同意してダウンロードすると、以下のようなデータが取得できます。

"都道府県コード","都道府県名","市区町村コード","市区町村名","大字町丁目コード","大字町丁目名","緯度","経度","原典資料コード","大字・字・丁目区分コード"
"13","東京都","13201","八王子市","132010001000","宇津貫町","35.617579","139.335315","0","1"
"13","東京都","13201","八王子市","132010002002","兵衛二丁目","35.623214","139.334553","0","3"
"13","東京都","13201","八王子市","132010003000","上柚木","35.623189","139.368880","0","1"
"13","東京都","13201","八王子市","132010004002","上柚木二丁目","35.619540","139.370093","0","3"
"13","東京都","13201","八王子市","132010005005","南大沢五丁目","35.611379","139.371016","0","3"
"13","東京都","13201","八王子市","132010004003","上柚木三丁目","35.616890","139.371494","0","3"
"13","東京都","13201","八王子市","132010006002","南陽台二丁目","35.633140","139.377550","0","3"
​

ファイルがShift-jisになっていてmysqlへimportした時に文字化けしてしまいす。nkfコマンドでUTF-8に変換します(mysqlをsjisで運用してない限り)

nkf -w --overwrite 13201_2018.csv

2) スキーマ作成 + 整形データの読み込み

データベース(test)と、位置情報を格納するテーブル(geo_test)を以下のようにして作成します。

CREATE DATABASE test CHARACTER SET utf8mb4;