ペチパーノート

WEB系Tipsブログです。PHP、MySQL、Linux、js等。なんだかよくわからないけどやってみる。がポリシーです。

ジオメトリ型のカラムを用いて2点間の距離を求める

位置情報をMySQLに保存し、2点間の距離を調べたい場合、どうしたらよいのだろう?

f:id:butterbull:20140612183119j:plain

とにかくやってみる

テーブルを作成

CREATE TABLE places (
  id int(11) NOT NULL,
  name varchar(32) NOT NULL,
  latlon geometry NOT NULL,
  PRIMARY KEY (id)
);

POINT型(緯度経度)で場所を登録

INSERT INTO places VALUES (1, '岩手県庁', GeomFromText('POINT(39.703595 141.152526)'));
INSERT INTO places VALUES (2, '岩手県水産会館', GeomFromText('POINT(39.703736 141.153755)'));

素直にSELECTしてみると文字化けしました。 ジオメトリ型にはバイナリが入ってます。

mysql> select * from places;
+----+-----------------------+---------------------------+
| id | name                  | latlon                    |
+----+-----------------------+---------------------------+
|  1 | 岩手県庁              |        P・fレC@Eケ4~皃a@       |
|  2 | 岩手県水産会館        |        IpレC@・a@       |
+----+-----------------------+---------------------------+
2 rows in set (0.00 sec)

ASTEXTをかませばOK

mysql> SELECT id, name, ASTEXT(latlon) FROM places;
+----+-----------------------+-----------------------------+
| id | name                  | ASTEXT(latlon)              |
+----+-----------------------+-----------------------------+
|  1 | 岩手県庁              | POINT(39.703595 141.152526) |
|  2 | 岩手県水産会館        | POINT(39.703736 141.153755) |
+----+-----------------------+-----------------------------+
2 rows in set (0.00 sec)

経度だけ、緯度だけを取得するときはX、Yをかまします。

mysql> SELECT id, name, ASTEXT(latlon), X(latlon), Y(latlon) FROM places;
+----+-----------------------+-----------------------------+-----------+------------+
| id | name                  | ASTEXT(latlon)              | X(latlon) | Y(latlon)  |
+----+-----------------------+-----------------------------+-----------+------------+
|  1 | 岩手県庁              | POINT(39.703595 141.152526) | 39.703595 | 141.152526 |
|  2 | 岩手県水産会館        | POINT(39.703736 141.153755) | 39.703736 | 141.153755 |
+----+-----------------------+-----------------------------+-----------+------------+
2 rows in set (0.00 sec)

あとはプログラムでごにょごにょしてもらい
最終的に以下のようなSQLを発行すると2点間の距離が求められました。

mysql> SELECT Glength(GeomFromText('LineString(141.152526 39.703595, 141.153755 39.703736)')) ;
+---------------------------------------------------------------------------------+
| Glength(GeomFromText('LineString(141.152526 39.703595, 141.153755 39.703736)')) |
+---------------------------------------------------------------------------------+
|                                                            0.001237061841618116 |
+---------------------------------------------------------------------------------+
1 row in set (0.00 sec)

ただし、この値の単位は(1度 = 112.12km)
kmにするには112.12をかけて、さらにmにするには1000をかけます。

mysql> SELECT Glength(GeomFromText('LineString(141.152526 39.703595, 141.153755 39.703736)')) * 112.12 * 1000 AS meter;
+--------------------+
| meter              |
+--------------------+
| 138.69937368222315 |
+--------------------+

約138メートルでした。