ST_Distanceの単位パラメータ使ってみる

MySQL8.0.14からGIS関数のST_Distanceが少し改良された

ST_Distance（g1、g2 [、unit]）関数に3番目のオプションのパラメータを追加されました。 公式では8.0.13のころから紹介されてたんですが、やっと試せます。 新しいパラメータは、結果に使用する[単位]を指定します。 単位については

select * from INFORMATION_SCHEMA.ST_UNITS_OF_MEASURE;

で確認することができます。

単位一覧

UNIT_NAME	UNIT_TYPE	CONVERSION_FACTOR	DESCRIPTION
British link (Sears 1922 truncated)	LINEAR	0.20116756
foot	LINEAR	0.3048
British yard (Benoit 1895 B)	LINEAR	0.9143992042898124
British yard (Sears 1922 truncated)	LINEAR	0.914398
British yard (Benoit 1895 A)	LINEAR	0.9143992
British chain (Sears 1922)	LINEAR	20.116765121552632
Clarke's link	LINEAR	0.201166195164
Clarke's yard	LINEAR	0.9143917962
British chain (Benoit 1895 A)	LINEAR	20.1167824
kilometre	LINEAR	1000
US survey foot	LINEAR	0.30480060960121924
British yard (Sears 1922)	LINEAR	0.9143984146160288
centimetre	LINEAR	0.01
millimetre	LINEAR	0.001
British foot (Sears 1922)	LINEAR	0.3047994715386762
British link (Sears 1922)	LINEAR	0.2011676512155263
fathom	LINEAR	1.8288
US survey chain	LINEAR	20.11684023368047
US survey link	LINEAR	0.2011684023368047
metre	LINEAR	1
German legal metre	LINEAR	1.0000135965
nautical mile	LINEAR	1852
British link (Benoit 1895 A)	LINEAR	0.201167824
British foot (Benoit 1895 B)	LINEAR	0.30479973476327077
British chain (Benoit 1895 B)	LINEAR	20.116782494375872
Statute mile	LINEAR	1609.344
British link (Benoit 1895 B)	LINEAR	0.2011678249437587
British foot (1865)	LINEAR	0.30480083333333335
Indian foot (1962)	LINEAR	0.3047996
Indian foot	LINEAR	0.30479951024814694
Indian foot (1937)	LINEAR	0.30479841
British foot (Benoit 1895 A)	LINEAR	0.3047997333333333
Indian foot (1975)	LINEAR	0.3047995
Indian yard (1962)	LINEAR	0.9143988
Indian yard (1975)	LINEAR	0.9143985
Indian yard	LINEAR	0.9143985307444408
link	LINEAR	0.201168
British foot (1936)	LINEAR	0.3048007491
Clarke's chain	LINEAR	20.1166195164
yard	LINEAR	0.9144
US survey mile	LINEAR	1609.3472186944375
Gold Coast foot	LINEAR	0.3047997101815088
chain	LINEAR	20.1168
Indian yard (1937)	LINEAR	0.91439523
British foot (Sears 1922 truncated)	LINEAR	0.30479933333333337
Clarke's foot	LINEAR	0.3047972654
British chain (Sears 1922 truncated)	LINEAR	20.116756

使ってみる

作業環境は勿論MySQL8.0.14です

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9577
Server version: 8.0.14 MySQL Community Server - GPL

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

東京都千代田区内幸町一丁目と内幸町二丁目の距離を単位つきで測ってみましょう。

-- 内幸町二丁目
mysql> SET @g1 = ST_GeomFromText('POINT(35.670812 139.754182)', 4326);
Query OK, 0 rows affected (0.00 sec)

-- 内幸町一丁目
mysql> SET @g2 = ST_GeomFromText('POINT(35.670839 139.758119)', 4326);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT ST_Distance(@g1, @g2);
+-----------------------+
| ST_Distance(@g1, @g2) |
+-----------------------+
|    356.45633448247673 |
+-----------------------+
1 row in set (0.00 sec)

mysql> SELECT ST_Distance(@g1, @g2, 'metre');
+--------------------------------+
| ST_Distance(@g1, @g2, 'metre') |
+--------------------------------+
|             356.45633448247673 |
+--------------------------------+
1 row in set (0.00 sec)

単位無しはメートル単位と同じです。

キロメートル単位

mysql> SELECT ST_Distance(@g1, @g2, 'kilometre');
+------------------------------------+
| ST_Distance(@g1, @g2, 'kilometre') |
+------------------------------------+
|                0.35645633448247677 |
+------------------------------------+
1 row in set (0.00 sec)

1歩(30.48cm)単位

mysql> SELECT ST_Distance(@g1, @g2, 'foot');
+-------------------------------+
| ST_Distance(@g1, @g2, 'foot') |
+-------------------------------+
|            1169.4761630002515 |
+-------------------------------+
1 row in set (0.00 sec)

ヤード単位

mysql> SELECT ST_Distance(@g1, @g2, 'yard');
+-------------------------------+
| ST_Distance(@g1, @g2, 'yard') |
+-------------------------------+
|             389.8253876667506 |
+-------------------------------+
1 row in set (0.00 sec)

地味だけど、自分で計算しなくていいのは良いですね。