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/

Comments are closed.

Post Navigation