緯度と経度それぞれが別々のカラムに格納されているデータに対して、生成列を使ってGEOMETRY型のカラムとして扱えるようにする方法を考えてみました。速度面で何かディスアドバンテージがあるのか否かは未知数ですが、こんな方法もあるのだというヒントになればと思い、書いてみました。
元データの用意
まず、緯度、経度それぞれが別々のカラムに格納されているテーブル spot1 を作成し、データを投入します。
DROP TABLE IF EXISTS spot1; CREATE TABLE spot1 ( id integer auto_increment, name varchar(256) , lat double , lng double , PRIMARY KEY (id) ); INSERT INTO spot1 (name, lat, lng) VALUES ('サークルハッピー寺院', 35.16110304 ,136.87678426); INSERT INTO spot1 (name, lat, lng) VALUES ('旋法学園螺旋', 35.16814112 ,136.88577498); INSERT INTO spot1 (name, lat, lng) VALUES ('商店街入口', 35.17620956 ,136.89037992); INSERT INTO spot1 (name, lat, lng) VALUES ('中央市場', 35.17004094 ,136.88814694); INSERT INTO spot1 (name, lat, lng) VALUES ('乗り茸フォレスト', 35.1798643 ,136.8815835); INSERT INTO spot1 (name, lat, lng) VALUES ('銭湯タワー', 35.1750197 ,136.8810447); INSERT INTO spot1 (name, lat, lng) VALUES ('中四角', 35.1700075 ,136.8852028);
内容確認。
mysql> SELECT * FROM spot1; +----+--------------------------------+-------------+--------------+ | id | name | lat | lng | +----+--------------------------------+-------------+--------------+ | 1 | サークルハッピー寺院 | 35.16110304 | 136.87678426 | | 2 | 旋法学園螺旋 | 35.16814112 | 136.88577498 | | 3 | 商店街入口 | 35.17620956 | 136.89037992 | | 4 | 中央市場 | 35.17004094 | 136.88814694 | | 5 | 乗り茸フォレスト | 35.1798643 | 136.8815835 | | 6 | 銭湯タワー | 35.1750197 | 136.8810447 | | 7 | 中四角 | 35.1700075 | 136.8852028 | +----+--------------------------------+-------------+--------------+ 7 rows in set (0.00 sec)
生成列でGEOMETRY型カラムを追加
spot1テーブルに対して ALTER TABLE で列を追加しても良いのですが、ここでは新たに別途 spot2テーブルを作成してみることにします。
DROP TABLE IF EXISTS spot2; CREATE TABLE spot2 ( id integer auto_increment, name varchar(256) , lat double , lng double , p geometry GENERATED ALWAYS AS (ST_GeomFromText(CONCAT("POINT(",lat," ",lng,")"),4326)), PRIMARY KEY (id) );
spot2テーブルに spot1テーブルから値を登録。
INSERT INTO spot2 (id, name, lng, lat) SELECT id, name, lng, lat FROM spot1;
一応内容確認。POINT値っぽい雰囲気のHEXが出ていますね。よさげ。
mysql> SELECT * FROM spot2; +----+--------------------------------+-------------+--------------+------------------------------------------------------+ | id | name | lat | lng | p | +----+--------------------------------+-------------+--------------+------------------------------------------------------+ | 1 | サークルハッピー寺院 | 35.16110304 | 136.87678426 | 0xE610000001010000001F4BDD9D0E1C6140080B40069F944140 | | 2 | 旋法学園螺旋 | 35.16814112 | 136.88577498 | 0xE61000000101000000E256C544581C6140A4C1F1A585954140 | | 3 | 商店街入口 | 35.17620956 | 136.89037992 | 0xE6100000010100000048AD07FE7D1C6140A5B8EC088E964140 | | 4 | 中央市場 | 35.17004094 | 136.88814694 | 0xE61000000101000000F6AA21B36B1C6140FB23CAE6C3954140 | | 5 | 乗り茸フォレスト | 35.1798643 | 136.8815835 | 0xE610000001010000002FA699EE351C6140E51B1BCB05974140 | | 6 | 銭湯タワー | 35.1750197 | 136.8810447 | 0xE610000001010000000D9AA784311C6140EFD3A70B67964140 | | 7 | 中四角 | 35.1700075 | 136.8852028 | 0xE610000001010000007C8AD294531C6140904946CEC2954140 | +----+--------------------------------+-------------+--------------+------------------------------------------------------+ 7 rows in set (0.00 sec)
Spatialの関数を使ったクエリだってバッチリ
試しに、(35.170139 136.8808)な場所からの距離を計算してみましょう。
mysql> SELECT id, name, ST_AsText(p), -> ST_Distance(ST_GeomFromText("POINT(35.170139 136.8808)",4326), p) dist -> FROM spot2 ORDER BY dist; +----+--------------------------------+---------------------------------+--------------------+ | id | name | ST_AsText(p) | dist | +----+--------------------------------+---------------------------------+--------------------+ | 7 | 中四角 | POINT(35.1700075 136.8852028) | 401.3554092727646 | | 2 | 旋法学園螺旋 | POINT(35.16814112 136.88577498) | 504.5178361627275 | | 6 | 銭湯タワー | POINT(35.1750197 136.8810447) | 541.945760266082 | | 4 | 中央市場 | POINT(35.17004094 136.88814694) | 669.3863039818294 | | 1 | サークルハッピー寺院 | POINT(35.16110304 136.87678426) | 1067.1591321918067 | | 5 | 乗り茸フォレスト | POINT(35.1798643 136.8815835) | 1081.3275537403395 | | 3 | 商店街入口 | POINT(35.17620956 136.89037992) | 1102.3489276382384 | +----+--------------------------------+---------------------------------+--------------------+ 7 rows in set (0.00 sec)
バッチリ！
一応簡単に生成列の解説
ポイントは生成列を以下のようにポイントとして扱うように指定する部分です。
p geometry GENERATED ALWAYS AS (ST_GeomFromText(CONCAT("POINT(",lat," ",lng,")"),4326)),
上の列定義は、pという名前の列を GEOMETRY型で宣言（ここまでは通常の列と同じ）し、その後で、それが生成列（GENERATEDカラム）であることを宣言します。ASの後ろがその生成ルールで。やりたことは、単に
ST_GeomFromText('POINT(latカラム値 lngカラム値)', 4326)
という形でルールを指定することなのですが、 POINT() の文字列（WKT）を作る部分に CONCAT を使っているのが、ここでの工夫です。Oracle的に書くなら（MySQLで動作オプションを変更しているなら） || を使って文字列結合しても良いでしょう。
なお、上記列定義では、省略可能なキーワードなどもありますが、省略しすぎると何がなんだかぱっと見わからなくなるので、私は上記の書き方がおすすめ。
また、lat/lng列の値に不正がないことは事前に確認（保証）済であることをここでの前提にしています。
