![variable.jp [データベース,パフォーマンス,運用]](https://variable.jp/wp-content/uploads/2013/09/cropped-variable.jp_.jpg)
MySQLでGeoHashを試してみた。以前、Webサイトを構築したときは、Google Map APIを利用して、住所から緯度経度を逆引きしてvarcharで作った列に情報を格納していましたが、あの時にgeohashを知っていたら色々とサービスで使えたかもしれない。。と思い、少し確認してみた。
MEMO:
Geohashは、任意の精度の緯度と経度座標をテキスト文字列にエンコード
Geohash値は、”0123456789bcdefghjkmnpqrstuvwxyz”から選択された文字のみを含む文字列
Geohash
桁数の誤差による差異
変換テーブル
Wiki参照： https://en.wikipedia.org/wiki/Geohash
変換例: xn76g = 11101 10100 00111 00110 01111
左から数え始め、経度は偶数ビット（1110001101011）、緯度は奇数ビット（101100101011）として表現される。二進数は1ビットずつ、左のビットから順に評価されるて、緯度については、-90から+90が2つの区間に分割され、経度は-180から+180の区間に分割される。
確認で利用したMySQLのバージョン
root@localhost [GIS]> select @@version; +--------------+ | @@version | +--------------+ | 8.0.4-rc-log | +--------------+ 1 row in set (0.00 sec)
MySQL8.0のgeohash関連関数
ST_から始まるものが、最新のGEO関数です。
ST_GeoHash(経度,緯度,最大長)、ST_GeoHash(point,max_length)
接続文字セットと照合順序でgeohash文字列を返します。
ST_LatFromGeoHash(geohash_str)
geohash文字列値から[-90、90]の範囲の倍精度数として緯度を返します。
ST_LongFromGeoHash(geohash_str)
geohash文字列値から[-180,180]の範囲の倍精度数値として経度を返します。
ST_PointFromGeoHash(geohash_str、srid)
geohash文字列値を指定して、解読されたgeohash値を含むPOINT値を返します。
root@localhost [GIS]> SELECT ST_GeoHash(180,0,10), ST_GeoHash(-180,-90,15); +----------------------+-------------------------+ | ST_GeoHash(180,0,10) | ST_GeoHash(-180,-90,15) | +----------------------+-------------------------+ | xbpbpbpbpb | 000000000000000 | +----------------------+-------------------------+ 1 row in set (0.01 sec) root@localhost [GIS]> SELECT ST_LatFromGeoHash(ST_GeoHash(45,-20,10)); +------------------------------------------+ | ST_LatFromGeoHash(ST_GeoHash(45,-20,10)) | +------------------------------------------+ | -20 | +------------------------------------------+ 1 row in set (0.00 sec) root@localhost [GIS]> SELECT ST_LongFromGeoHash(ST_GeoHash(45,-20,10)); +-------------------------------------------+ | ST_LongFromGeoHash(ST_GeoHash(45,-20,10)) | +-------------------------------------------+ | 45 | +-------------------------------------------+ 1 row in set (0.00 sec) root@localhost [GIS]> SET @gh = ST_GeoHash(45,-20,10); Query OK, 0 rows affected (0.00 sec) root@localhost [GIS]> SELECT ST_AsText(ST_PointFromGeoHash(@gh,0)); +---------------------------------------+ | ST_AsText(ST_PointFromGeoHash(@gh,0)) | +---------------------------------------+ | POINT(45 -20) | +---------------------------------------+ 1 row in set (0.00 sec) root@localhost [GIS]>
実際にGoogle Mapを利用して確認
オラクル青山オフィスのgeohash(5桁:xn76g)を利用してレストランを検索。
データをImport後に、緯度-経度を読み込んで確認してみる。
検証用のデータはこちらからダウンロード可能です。
Download OpenStreetMap data for this region:Japan
http://download.geofabrik.de/asia/japan.html
Download OpenStreetMap data for this region:Asia
http://download.geofabrik.de/asia.html
インポート方法はこちらが参考になります。
MySQL 5.7 and GIS, an Example
https://mysqlserverteam.com/mysql-5-7-and-gis-an-example/
検証データと結果の確認
root@localhost [GIS]> select nodetags.v as 'name',st_latfromgeohash(st_geohash((nodes.geom),8)) as 'lat', st_longfromgeohash(st_geohash((nodes.geom),8)) as 'lng','restaurant' as 'type' FROM nodes,nodetags WHERE nodes.id = nodetags.id and match(tags) against ('+restaurant' IN BOOLEAN MODE) and nodetags.k='name' and nodes.GeoHash5 = 'xn76g' limit 10;
+--------------------------------------------+---------+----------+------------+
| name | lat | lng | type |
+--------------------------------------------+---------+----------+------------+
| 雅山 (Gazan) | 35.6603 | 139.7397 | restaurant |
| とんかつ まい泉 | 35.668 | 139.7116 | restaurant |
| 粥 「喜喜」 | 35.6727 | 139.7126 | restaurant |
| ヘンドリックス・カリー・バー | 35.675 | 139.7126 | restaurant |
| えさき (Esaki) | 35.6717 | 139.7133 | restaurant |
| Cardenas/charcoal grill | 35.6478 | 139.7075 | restaurant |
| ROYAL PALACE | 35.6475 | 139.707 | restaurant |
| 長崎ちゃんぽんリンガーハット | 35.6478 | 139.746 | restaurant |
| 増田屋 (Masudaya) | 35.6705 | 139.7136 | restaurant |
| Las Chicas | 35.6634 | 139.708 | restaurant |
+--------------------------------------------+---------+----------+------------+
10 rows in set (0.04 sec)
PHPにSQLをコピーしてGoogle Map APIに渡して、地図を確認してみると、問題なく、同じGeohashにあるレストランが表示されているようです。
インデックスを併用すれば、結構レスポンスの良い空間データベースになりそうです。
上記処理を実行した時の、MySQLで実行プランは以下のような感じです
以上で確認は終了ですが、ついでにSRIDを指定してテーブルを作成する事が出来るようになっていたので確認してみた。
緯度-経度の順番でINSERTする場合は、列にSRIDを指定して作成した方が良いようです。
反対に経度-緯度の場合はDefaultの0のままで良い。データINSERT時に緯度と経度の順番のバリデーションが無いのであった方がよさそうです。
root@localhost [GIS]> CREATE TABLE geom (
-> p POINT NOT NULL SRID 0,
-> g GEOMETRY NOT NULL SRID 4326,
-> GeoHash5 varchar(5) GENERATED ALWAYS AS (st_geohash(`g`,5)) VIRTUAL,
-> GeoHash8 varchar(8) GENERATED ALWAYS AS (st_geohash(`g`,8)) VIRTUAL
-> );
root@localhost [GIS]> INSERT INTO geom(p,g) VALUES(ST_GeomFromText('POINT(139.718695 35.671482)'),ST_GeomFromText('POINT(35.671482 139.718695)',4326));
Query OK, 1 row affected (0.00 sec)
root@localhost [GIS]> select * from geom;
+---------------------------+---------------------------+----------+----------+
| p | g | GeoHash5 | GeoHash8 |
+-------- @$----------------+ @$------------------------+----------+----------+
| A@ | A@ | xn76g | xn76gmu1 |
+---------------------------+---------------------------+----------+----------+
1 row in set (0.00 sec)
root@localhost [GIS]> select st_geohash(p,8),st_geohash(g,8),GeoHash5,GeoHash8 from geom;
+-----------------+-----------------+----------+----------+
| st_geohash(p,8) | st_geohash(g,8) | GeoHash5 | GeoHash8 |
+-----------------+-----------------+----------+----------+
| xn76gmu1 | xn76gmu1 | xn76g | xn76gmu1 |
+-----------------+-----------------+----------+----------+
1 row in set (0.00 sec)
root@localhost [GIS]> select 'xn76g' as 'geohash',st_latfromgeohash('xn76g') as '緯度',st_longfromgeohash('xn76g') as '経度';
+---------+--------+--------+
| geohash | 緯度 | 経度 |
+---------+--------+--------+
| xn76g | 35.66 | 139.72 |
+---------+--------+--------+
1 row in set (0.01 sec)
参照：
12.15.10 Spatial Geohash Functions
https://dev.mysql.com/doc/refman/8.0/en/spatial-geohash-functions.html
geohash.org
http://geohash.org/