「緯度」「経度」を「仮想列」で地理情報型へ変換してみよう

仮想列の活用

前回は「緯度」と「経度」それぞれの値を格納したカラムから、ST_GeomFromText()関数を使って地理情報型の値へと変換する方法を紹介しました。今回はMySQLの「仮想列」(Generated Column)という仕組み使った方法を試してみましょう。

Generated Columnとは、既存のカラムのデータを利用して値が自動的に生成されるカラムのことです。計算結果は実際のディスク上には保存せずに必要時に都度都度変換を行う「VIRTUAL」タイプと、計算結果をディスクに保存しておく「STORED」タイプの2種類があります。今回の試みはGenerated Columnを使って、ST_GeomFromText()の処理をラクにしようという発想です。

テーブルの用意

データを格納するためのテーブルを作ります。id、name、lat、lonまでは前回と同じです。今回は「pos」という列を新たに加えました。pos列はGenerated Columnとして定義し、lat列、lon列の値を使ってST_GeomFromText()で地理情報型へと変換しています。

CREATE TABLE g13 (
  id   INTEGER,
  name VARCHAR(30),
  lat  DOUBLE,
  lon  DOUBLE,
  pos  GEOMETRY GENERATED ALWAYS AS (ST_GeomFromText(CONCAT('POINT(',lat,' ',lon,')'),6668) )
); 

前回と同様のデータを投入してみましょう。登録は、pos列を除いた列に行います。

INSERT INTO g13 (id, name, lat, lon) VALUES (1, '日本のへそ', 35, 135); 
INSERT INTO g13 (id, name, lat, lon) VALUES (2, 'インプレス', 35.694572, 139.760397); 

テーブルデータを確認してみると、データを与えていないpos列に値が入っていることが分かります。バイナリの16進数表記を前回の結果と見比べてみると一致していることから、正しい値を得ることができていると判断できます。

mysql> SELECT * FROM g13;
+------+-----------------+-----------+------------+------------------------------------------------------+
| id   | name            | lat       | lon        | pos                                                  |
+------+-----------------+-----------+------------+------------------------------------------------------+
|    1 | 日本のへそ      |        35 |        135 | 0x0C1A000001010000000000000000E060400000000000804140 |
|    2 | インプレス      | 35.694572 | 139.760397 | 0x0C1A0000010100000040DF162C55786140D15B3CBCE7D84140 |
+------+-----------------+-----------+------------+------------------------------------------------------+
2 rows in set (0.000 sec)

仮想列の2種類のタイプ

前述した通り、生成列にはVIRTUALタイプとSTOREDタイプの2種類があります。両方を試してみましょう。

VIRTUALタイプ

先ほどのGenerated Columnの定義の最後に「VIRTUAL」というキーワードを指定することでVIRTUAL列となります。テーブルを作ってデータを登録してみましょう。

テーブル作成： 

CREATE TABLE g13_virtual ( id INTEGER, name VARCHAR(30), lat DOUBLE, lon DOUBLE, pos GEOMETRY GENERATED ALWAYS AS (ST_GeomFromText(CONCAT('POINT(',lat,' ',lon,')'),6668)) VIRTUAL );

データ登録：

INSERT INTO g13_virtual (id, name, lat, lon) VALUES (1, '日本のへそ', 35, 135); 
INSERT INTO g13_virtual (id, name, lat, lon) VALUES (2, 'インプレス', 35.694572, 139.760397); 

内容を確認すると、先ほどと同様にpos列として地理情報型のデータが参照可能になっていることが分かります。このpos列の内容は実際にストレージ上に格納されているわけではなく、SELECT文などでこの列が参照される度に、定義されていたST_GeomFromText()関数が実行されてその結果を返しています。

mysql> SELECT * FROM g13_virtual;
+------+-----------------+-----------+------------+------------------------------------------------------+
| id   | name            | lat       | lon        | pos                                                  |
+------+-----------------+-----------+------------+------------------------------------------------------+
|    1 | 日本のへそ      |        35 |        135 | 0x0C1A000001010000000000000000E060400000000000804140 |
|    2 | インプレス      | 35.694572 | 139.760397 | 0x0C1A0000010100000040DF162C55786140D15B3CBCE7D84140 |
+------+-----------------+-----------+------------+------------------------------------------------------+
2 rows in set (0.000 sec)

STOREDタイプ

Generated Columnの定義の最後に「STORED」というキーワードを指定することでSTORED列となります。テーブルを作ってデータを登録してみましょう。

テーブル作成： 

CREATE TABLE g13_stored ( id INTEGER, name VARCHAR(30), lat DOUBLE, lon DOUBLE, pos GEOMETRY GENERATED ALWAYS AS (ST_GeomFromText(CONCAT('POINT(',lat,' ',lon,')'),6668)) STORED ); 

データ登録： 

INSERT INTO g13_stored (id, name, lat, lon) VALUES (1, '日本のへそ', 35, 135); 
INSERT INTO g13_stored (id, name, lat, lon) VALUES (2, 'インプレス', 35.694572, 139.760397); 

こちらも内容を確認してみましょう。先ほどと何の変化もなく、正直なところ見ても面白みはないのですが、このpos列には内部では実際にストレージに格納されているものが表示されています。SELECT文が実行されたタイミングでST_GeomFromText()は実行されていません。

mysql> SELECT * FROM g13_stored;
+------+-----------------+-----------+------------+------------------------------------------------------+
| id   | name            | lat       | lon        | pos                                                  |
+------+-----------------+-----------+------------+------------------------------------------------------+
|    1 | 日本のへそ      |        35 |        135 | 0x0C1A000001010000000000000000E060400000000000804140 |
|    2 | インプレス      | 35.694572 | 139.760397 | 0x0C1A0000010100000040DF162C55786140D15B3CBCE7D84140 |
+------+-----------------+-----------+------------+------------------------------------------------------+
2 rows in set (0.000 sec)

VIRTUALとSOTREDどっちを使う?

では、VIRTUALタイプとSOTREDタイプ、どちらを使うのが良いのでしょうか。それは要件によります。それぞれの特徴を整理してみましょう。

 このように、VIRTUALタイプとSOTREDタイプではデータの格納時に演算するか、参照時に演算するかという違いがあります。自分たちの目的に合わせてどちらを採用するか決めると良いでしょう。

私の個人的な判断基準ですが、参照時の性能に課題がないのであればVIRTUALタイプを採用し、少しでも参照時の性能を上げたい場合や非常に大量のデータを比較演算などで使用する場合はあらかじめ計算済みの値を持っているSTORED型を採用する、といったように判断しています。

VIRTUALもSTOREDも指定しなかった場合のデフォルトはVIRTUALです。

省略した書き方もある

実は、この構文では「GENERATED ALWAYS」の部分は省略可能です。列名の後ろにいきなり「AS」と記述し、生成列の演算式を記述する書き方です。

pos GEOMETRY GENERATED ALWAYS AS (ST_GeomFromText(CONCAT('POINT(',lat,' ',lon,')'),6668))  

↓

pos GEOMETRY AS (ST_GeomFromText(CONCAT('POINT(',lat,' ',lon,')'),6668))

見た目も非常にすっきりするのですが、個人的には手元で管理しているDDLが記述されたファイルから仮想列を使用している部分を洗い出したいときなどにはGENERATEDが明記されている方が便利なので、省略しない書き方をお勧めしたいところです。この省略記法は、他の人が書いたDDLを眺める際に仮想列であることを判断できるようにと紹介しました。

【コラム】日本最西端の35度

前回のコラムでは北緯35度線上にある大津駅のモニュメントを紹介しました。今回も引き続き北緯35度の旅にご案内します。

滋賀県大津市の北緯35度、兵庫県西脇市にある「日本のへそ」北緯35度、これらの線をさらに西に伸ばしていくと、どこで海にぶつかると思いますか。答えは島根県江津市(ごうつし)です。 松江から西に向かって(という感覚ですが、実際の方位はほぼ南西です)約95km、教科書にも「東京から一番遠いまち」として紹介されている市です。

産業道路の脇にひっそりと最西端の北緯35度の看板が建てられています。

Googleストリートビューでこの地点の過去を見ると、もっと古びた看板の画像を確認できました。つまり、現在の看板は新たに作り直されたものだということです。「最初に1回作って終わり」ではなく、この場所を大切にして、きちんとメンテしてくれている江津市観光協会の皆さんの心意気に嬉しくなりました。

なお、地理院地図および手持ちのハンディGPSの双方で確認したところ、実際の北緯35度点は道路沿いに40mほど南西に進んだ、小さな川を越えた地点にあります。看板を作る場所の権利上の都合でここになったのかもしれませんね。GPSロガーの数値がぴったりになるのは、やはり気持ちの良いものです。

オーツからゴーツへ北緯35度線でつながっているというのも、なんだか楽しいですね。