【位置情報】MySQLのgeometry型を使って緯度経度の2点間距離をkmで計算・算出する方法【ST_GEOMFROMTEXT】

YouTubeも見てね♪

ねこじゃすり

ワタオカねこじゃすりライトグレー

created by Rinker

PEPPY(ペピイ)

¥3,850 (2025/01/05 12:56:12時点 Amazon調べ-詳細)

猫を魅了する魔法の装備品！

Anker PowerCor

Anker PowerCore モバイルバッテリー 20000mAh iPhone iPad Android

created by Rinker

Anker

¥3,990 (2025/01/05 12:09:11時点 Amazon調べ-詳細)

旅行には必須の大容量モバイルバッテリー！

【最新機種】GoPro hero11 Black

【最新機種】GoPro HERO13 Black【完全防水】

created by Rinker

¥61,300 (2025/01/05 20:59:47時点楽天市場調べ-詳細)

最新機種でVlogの思い出を撮影しよう！

[ノースフェイス] THE NORTH FACE メンズアウターマウンテンライトジャケット

created by Rinker

THE NORTH FACE(ザノースフェイス)

¥33,000 (2025/01/05 13:22:08時点 Amazon調べ-詳細)

防水暴風で耐久性抜群なので旅行で大活躍です！

ペヤングソースやきそば 120g×18個

created by Rinker

ペヤング

¥3,280 (2025/01/05 12:33:38時点 Amazon調べ-詳細)

とりあえず保存食として買っておけば間違いなし！

MOFT X 【新型ミニマム版】 iPhone対応スマホスタンド

created by Rinker

MOFT

Amazon一番人気のスマホスタンド！カード類も収納出来てかさ張らないのでオススメです！

サンディスク microSD 128GB

サンディスク microSD 128GB UHS-I Class10 Nintendo Switch SanDisk Ultra SDSQUA4-128G-EPK

created by Rinker

SanDisk

¥3,279 (2025/01/05 12:33:41時点 Amazon調べ-詳細)

スマホからSwitchまで使える大容量MicroSDカード！

1 MySQLのgeometry型を使ってみよう
- 1.1 double型でも実現は可能
2 やり方
- 2.1 前提
  - 2.1.1 GEOMFROMTEXTは非推奨
- 2.2 SQL
3 終わりに

MySQLのgeometry型を使ってみよう

double型でも実現は可能

先日、緯度経度を用いた近隣検索を実現する際に、MySQLのdouble型に緯度経度をそれぞれ保存して実現する方法をご紹介しました。

参考

【MySQL】geometry型ではなくdouble型の緯度経度から2点間距離を計算して算出する方法【GPS位置情報】

しかし、せっかく位置情報を取り扱いやすくするためのgeometry型が用意されているのであれば、せっかくなので使ってみよう！という事で、試してみようと思います。

やり方

前提

今回はMySQL5.7を使用し、前回使用した県庁所在地テーブルにpointというgeometryカラムを追加し、操作を行ってみようと思います。

CREATE TABLE `prefectural_office` (
    `id` BIGINT(20) NOT NULL COMMENT '県庁所在地ID',
    `prefectureName` VARCHAR(256) NOT NULL COMMENT '都道府県名',
    `latitude` DOUBLE(9 , 6 ) COMMENT '緯度',
    `longitude` DOUBLE(9 , 6 ) COMMENT '経度',
    `point` geometry NOT NULL COMMENT '位置情報',  -- 追加
    `createdAt` DATETIME NOT NULL COMMENT '作成日',
    `updatedAt` DATETIME NOT NULL COMMENT '更新日',
    PRIMARY KEY (`id`)
)  ENGINE=INNODB DEFAULT CHARSET=UTF8MB4 COMMENT='県庁所在地テーブル';

CREATE TABLE `prefectural_office` (

`id` BIGINT(20) NOT NULL COMMENT '県庁所在地ID',

`prefectureName` VARCHAR(256) NOT NULL COMMENT '都道府県名',

`latitude` DOUBLE(9 , 6 ) COMMENT '緯度',

`longitude` DOUBLE(9 , 6 ) COMMENT '経度',

`point` geometry NOT NULL COMMENT '位置情報', -- 追加

`createdAt` DATETIME NOT NULL COMMENT '作成日',

`updatedAt` DATETIME NOT NULL COMMENT '更新日',

PRIMARY KEY (`id`)

) ENGINE=INNODB DEFAULT CHARSET=UTF8MB4 COMMENT='県庁所在地テーブル';

geomety型から取得したデータの比較のために、前回のlatitudeとlongitudeのカラムは残しています。

サンプルデータはこんな感じ。

INSERT INTO `prefectural_office`												
(`id`,`prefectureName`,`latitude`,`longitude`, `point`,`createdAt`,`updatedAt`)												
VALUES												
(	1	,"北海道",		43.064281	,	141.346797	,ST_GEOMFROMTEXT('POINT(141.346797 43.064281)'),NOW(),NOW()),	
(	2	,"青森県",		40.824354	,	140.739966	,ST_GEOMFROMTEXT('POINT(140.739966 40.824354)'),NOW(),NOW()),	
(	3	,"岩手県",		39.703584	,	141.152709	,ST_GEOMFROMTEXT('POINT(141.152709 39.703584)'),NOW(),NOW()),	
(	4	,"宮城県",		38.268926	,	140.872006	,ST_GEOMFROMTEXT('POINT(140.872006 38.268926)'),NOW(),NOW()),	
(	5	,"秋田県",		39.718031	,	140.103178	,ST_GEOMFROMTEXT('POINT(140.103178 39.718031)'),NOW(),NOW()),	
(	6	,"山形県",		38.240489	,	140.363428	,ST_GEOMFROMTEXT('POINT(140.363428 38.240489)'),NOW(),NOW()),	
(	7	,"福島県",		37.74998	,	140.467582	,ST_GEOMFROMTEXT('POINT(140.467582 37.74998)'),NOW(),NOW()),	
(	8	,"茨城県",		36.3416	,	140.446619	,ST_GEOMFROMTEXT('POINT(140.446619 36.3416)'),NOW(),NOW()),	
(	9	,"栃木県",		36.565485	,	139.883086	,ST_GEOMFROMTEXT('POINT(139.883086 36.565485)'),NOW(),NOW()),	
(	10	,"群馬県",		36.390601	,	139.060436	,ST_GEOMFROMTEXT('POINT(139.060436 36.390601)'),NOW(),NOW()),	
(	11	,"埼玉県",		35.856928	,	139.648787	,ST_GEOMFROMTEXT('POINT(139.648787 35.856928)'),NOW(),NOW()),	
(	12	,"千葉県",		35.604589	,	140.123183	,ST_GEOMFROMTEXT('POINT(140.123183 35.604589)'),NOW(),NOW()),	
(	13	,"東京都",		35.689567	,	139.691722	,ST_GEOMFROMTEXT('POINT(139.691722 35.689567)'),NOW(),NOW()),	
(	14	,"神奈川県",		35.447711	,	139.642536	,ST_GEOMFROMTEXT('POINT(139.642536 35.447711)'),NOW(),NOW()),	
(	15	,"新潟県",		37.902435	,	139.023825	,ST_GEOMFROMTEXT('POINT(139.023825 37.902435)'),NOW(),NOW()),	
(	16	,"富山県",		36.695374	,	137.211194	,ST_GEOMFROMTEXT('POINT(137.211194 36.695374)'),NOW(),NOW()),	
(	17	,"石川県",		36.594283	,	136.625714	,ST_GEOMFROMTEXT('POINT(136.625714 36.594283)'),NOW(),NOW()),	
(	18	,"福井県",		36.065422	,	136.221833	,ST_GEOMFROMTEXT('POINT(136.221833 36.065422)'),NOW(),NOW()),	
(	19	,"山梨県",		35.663944	,	138.568386	,ST_GEOMFROMTEXT('POINT(138.568386 35.663944)'),NOW(),NOW()),	
(	20	,"長野県",		36.651314	,	138.180997	,ST_GEOMFROMTEXT('POINT(138.180997 36.651314)'),NOW(),NOW()),	
(	21	,"岐阜県",		35.391192	,	136.722153	,ST_GEOMFROMTEXT('POINT(136.722153 35.391192)'),NOW(),NOW()),	
(	22	,"静岡県",		34.976911	,	138.383025	,ST_GEOMFROMTEXT('POINT(138.383025 34.976911)'),NOW(),NOW()),	
(	23	,"愛知県",		35.180197	,	136.906742	,ST_GEOMFROMTEXT('POINT(136.906742 35.180197)'),NOW(),NOW()),	
(	24	,"三重県",		34.730275	,	136.508597	,ST_GEOMFROMTEXT('POINT(136.508597 34.730275)'),NOW(),NOW()),	
(	25	,"滋賀県",		35.004392	,	135.868303	,ST_GEOMFROMTEXT('POINT(135.868303 35.004392)'),NOW(),NOW()),	
(	26	,"京都府",		35.021281	,	135.755636	,ST_GEOMFROMTEXT('POINT(135.755636 35.021281)'),NOW(),NOW()),	
(	27	,"大阪府",		34.68632	,	135.520022	,ST_GEOMFROMTEXT('POINT(135.520022 34.68632)'),NOW(),NOW()),	
(	28	,"兵庫県",		34.691102	,	135.182852	,ST_GEOMFROMTEXT('POINT(135.182852 34.691102)'),NOW(),NOW()),	
(	29	,"奈良県",		34.684714	,	135.832771	,ST_GEOMFROMTEXT('POINT(135.832771 34.684714)'),NOW(),NOW()),	
(	30	,"和歌山県",		34.226035	,	135.167494	,ST_GEOMFROMTEXT('POINT(135.167494 34.226035)'),NOW(),NOW()),	
(	31	,"鳥取県",		35.503637	,	134.238198	,ST_GEOMFROMTEXT('POINT(134.238198 35.503637)'),NOW(),NOW()),	
(	32	,"島根県",		35.472217	,	133.050531	,ST_GEOMFROMTEXT('POINT(133.050531 35.472217)'),NOW(),NOW()),	
(	33	,"岡山県",		34.661764	,	133.9349	,ST_GEOMFROMTEXT('POINT(133.9349 34.661764)'),NOW(),NOW()),	
(	34	,"広島県",		34.396275	,	132.459375	,ST_GEOMFROMTEXT('POINT(132.459375 34.396275)'),NOW(),NOW()),	
(	35	,"山口県",		34.185802	,	131.47155	,ST_GEOMFROMTEXT('POINT(131.47155 34.185802)'),NOW(),NOW()),	
(	36	,"徳島県",		34.065733	,	134.559489	,ST_GEOMFROMTEXT('POINT(134.559489 34.065733)'),NOW(),NOW()),	
(	37	,"香川県",		34.340035	,	134.043279	,ST_GEOMFROMTEXT('POINT(134.043279 34.340035)'),NOW(),NOW()),	
(	38	,"愛媛県",		33.841644	,	132.766103	,ST_GEOMFROMTEXT('POINT(132.766103 33.841644)'),NOW(),NOW()),	
(	39	,"高知県",		33.559756	,	133.531117	,ST_GEOMFROMTEXT('POINT(133.531117 33.559756)'),NOW(),NOW()),	
(	40	,"福岡県",		33.606261	,	130.418117	,ST_GEOMFROMTEXT('POINT(130.418117 33.606261)'),NOW(),NOW()),	
(	41	,"佐賀県",		33.249328	,	130.298806	,ST_GEOMFROMTEXT('POINT(130.298806 33.249328)'),NOW(),NOW()),	
(	42	,"長崎県",		32.74509	,	129.873641	,ST_GEOMFROMTEXT('POINT(129.873641 32.74509)'),NOW(),NOW()),	
(	43	,"熊本県",		32.789765	,	130.741662	,ST_GEOMFROMTEXT('POINT(130.741662 32.789765)'),NOW(),NOW()),	
(	44	,"大分県",		33.238123	,	131.61259	,ST_GEOMFROMTEXT('POINT(131.61259 33.238123)'),NOW(),NOW()),	
(	45	,"宮崎県",		31.911007	,	131.423882	,ST_GEOMFROMTEXT('POINT(131.423882 31.911007)'),NOW(),NOW()),	
(	46	,"鹿児島県",		31.56022	,	130.558281	,ST_GEOMFROMTEXT('POINT(130.558281 31.56022)'),NOW(),NOW()),	
(	47	,"沖縄県",		26.212375	,	127.680981	,ST_GEOMFROMTEXT('POINT(127.680981 26.212375)'),NOW(),NOW())	
;

INSERT INTO `prefectural_office`

(`id`,`prefectureName`,`latitude`,`longitude`, `point`,`createdAt`,`updatedAt`)

VALUES

( 1 ,"北海道", 43.064281 , 141.346797 ,ST_GEOMFROMTEXT('POINT(141.346797 43.064281)'),NOW(),NOW()),

( 2 ,"青森県", 40.824354 , 140.739966 ,ST_GEOMFROMTEXT('POINT(140.739966 40.824354)'),NOW(),NOW()),

( 3 ,"岩手県", 39.703584 , 141.152709 ,ST_GEOMFROMTEXT('POINT(141.152709 39.703584)'),NOW(),NOW()),

( 4 ,"宮城県", 38.268926 , 140.872006 ,ST_GEOMFROMTEXT('POINT(140.872006 38.268926)'),NOW(),NOW()),

( 5 ,"秋田県", 39.718031 , 140.103178 ,ST_GEOMFROMTEXT('POINT(140.103178 39.718031)'),NOW(),NOW()),

( 6 ,"山形県", 38.240489 , 140.363428 ,ST_GEOMFROMTEXT('POINT(140.363428 38.240489)'),NOW(),NOW()),

( 7 ,"福島県", 37.74998 , 140.467582 ,ST_GEOMFROMTEXT('POINT(140.467582 37.74998)'),NOW(),NOW()),

( 8 ,"茨城県", 36.3416 , 140.446619 ,ST_GEOMFROMTEXT('POINT(140.446619 36.3416)'),NOW(),NOW()),

( 9 ,"栃木県", 36.565485 , 139.883086 ,ST_GEOMFROMTEXT('POINT(139.883086 36.565485)'),NOW(),NOW()),

( 10 ,"群馬県", 36.390601 , 139.060436 ,ST_GEOMFROMTEXT('POINT(139.060436 36.390601)'),NOW(),NOW()),

( 11 ,"埼玉県", 35.856928 , 139.648787 ,ST_GEOMFROMTEXT('POINT(139.648787 35.856928)'),NOW(),NOW()),

( 12 ,"千葉県", 35.604589 , 140.123183 ,ST_GEOMFROMTEXT('POINT(140.123183 35.604589)'),NOW(),NOW()),

( 13 ,"東京都", 35.689567 , 139.691722 ,ST_GEOMFROMTEXT('POINT(139.691722 35.689567)'),NOW(),NOW()),

( 14 ,"神奈川県", 35.447711 , 139.642536 ,ST_GEOMFROMTEXT('POINT(139.642536 35.447711)'),NOW(),NOW()),

( 15 ,"新潟県", 37.902435 , 139.023825 ,ST_GEOMFROMTEXT('POINT(139.023825 37.902435)'),NOW(),NOW()),

( 16 ,"富山県", 36.695374 , 137.211194 ,ST_GEOMFROMTEXT('POINT(137.211194 36.695374)'),NOW(),NOW()),

( 17 ,"石川県", 36.594283 , 136.625714 ,ST_GEOMFROMTEXT('POINT(136.625714 36.594283)'),NOW(),NOW()),

( 18 ,"福井県", 36.065422 , 136.221833 ,ST_GEOMFROMTEXT('POINT(136.221833 36.065422)'),NOW(),NOW()),

( 19 ,"山梨県", 35.663944 , 138.568386 ,ST_GEOMFROMTEXT('POINT(138.568386 35.663944)'),NOW(),NOW()),

( 20 ,"長野県", 36.651314 , 138.180997 ,ST_GEOMFROMTEXT('POINT(138.180997 36.651314)'),NOW(),NOW()),

( 21 ,"岐阜県", 35.391192 , 136.722153 ,ST_GEOMFROMTEXT('POINT(136.722153 35.391192)'),NOW(),NOW()),

( 22 ,"静岡県", 34.976911 , 138.383025 ,ST_GEOMFROMTEXT('POINT(138.383025 34.976911)'),NOW(),NOW()),

( 23 ,"愛知県", 35.180197 , 136.906742 ,ST_GEOMFROMTEXT('POINT(136.906742 35.180197)'),NOW(),NOW()),

( 24 ,"三重県", 34.730275 , 136.508597 ,ST_GEOMFROMTEXT('POINT(136.508597 34.730275)'),NOW(),NOW()),

( 25 ,"滋賀県", 35.004392 , 135.868303 ,ST_GEOMFROMTEXT('POINT(135.868303 35.004392)'),NOW(),NOW()),

( 26 ,"京都府", 35.021281 , 135.755636 ,ST_GEOMFROMTEXT('POINT(135.755636 35.021281)'),NOW(),NOW()),

( 27 ,"大阪府", 34.68632 , 135.520022 ,ST_GEOMFROMTEXT('POINT(135.520022 34.68632)'),NOW(),NOW()),

( 28 ,"兵庫県", 34.691102 , 135.182852 ,ST_GEOMFROMTEXT('POINT(135.182852 34.691102)'),NOW(),NOW()),

( 29 ,"奈良県", 34.684714 , 135.832771 ,ST_GEOMFROMTEXT('POINT(135.832771 34.684714)'),NOW(),NOW()),

( 30 ,"和歌山県", 34.226035 , 135.167494 ,ST_GEOMFROMTEXT('POINT(135.167494 34.226035)'),NOW(),NOW()),

( 31 ,"鳥取県", 35.503637 , 134.238198 ,ST_GEOMFROMTEXT('POINT(134.238198 35.503637)'),NOW(),NOW()),

( 32 ,"島根県", 35.472217 , 133.050531 ,ST_GEOMFROMTEXT('POINT(133.050531 35.472217)'),NOW(),NOW()),

( 33 ,"岡山県", 34.661764 , 133.9349 ,ST_GEOMFROMTEXT('POINT(133.9349 34.661764)'),NOW(),NOW()),

( 34 ,"広島県", 34.396275 , 132.459375 ,ST_GEOMFROMTEXT('POINT(132.459375 34.396275)'),NOW(),NOW()),

( 35 ,"山口県", 34.185802 , 131.47155 ,ST_GEOMFROMTEXT('POINT(131.47155 34.185802)'),NOW(),NOW()),

( 36 ,"徳島県", 34.065733 , 134.559489 ,ST_GEOMFROMTEXT('POINT(134.559489 34.065733)'),NOW(),NOW()),

( 37 ,"香川県", 34.340035 , 134.043279 ,ST_GEOMFROMTEXT('POINT(134.043279 34.340035)'),NOW(),NOW()),

( 38 ,"愛媛県", 33.841644 , 132.766103 ,ST_GEOMFROMTEXT('POINT(132.766103 33.841644)'),NOW(),NOW()),

( 39 ,"高知県", 33.559756 , 133.531117 ,ST_GEOMFROMTEXT('POINT(133.531117 33.559756)'),NOW(),NOW()),

( 40 ,"福岡県", 33.606261 , 130.418117 ,ST_GEOMFROMTEXT('POINT(130.418117 33.606261)'),NOW(),NOW()),

( 41 ,"佐賀県", 33.249328 , 130.298806 ,ST_GEOMFROMTEXT('POINT(130.298806 33.249328)'),NOW(),NOW()),

( 42 ,"長崎県", 32.74509 , 129.873641 ,ST_GEOMFROMTEXT('POINT(129.873641 32.74509)'),NOW(),NOW()),

( 43 ,"熊本県", 32.789765 , 130.741662 ,ST_GEOMFROMTEXT('POINT(130.741662 32.789765)'),NOW(),NOW()),

( 44 ,"大分県", 33.238123 , 131.61259 ,ST_GEOMFROMTEXT('POINT(131.61259 33.238123)'),NOW(),NOW()),

( 45 ,"宮崎県", 31.911007 , 131.423882 ,ST_GEOMFROMTEXT('POINT(131.423882 31.911007)'),NOW(),NOW()),

( 46 ,"鹿児島県", 31.56022 , 130.558281 ,ST_GEOMFROMTEXT('POINT(130.558281 31.56022)'),NOW(),NOW()),

( 47 ,"沖縄県", 26.212375 , 127.680981 ,ST_GEOMFROMTEXT('POINT(127.680981 26.212375)'),NOW(),NOW())

;

GEOMFROMTEXTは非推奨

INSERTする際に、GEOMFROMTEXT('POINT(xxx yyy)')を使いように記載されているブログがあると思いますが、現在は非推奨となっており、ST_GEOMFROMTEXTを使うように警告が出ると思います。

'GEOMFROMTEXT' is deprecated and will be removed in a future release. Please use ST_GEOMFROMTEXT instead

1	'GEOMFROMTEXT' is deprecated and will be removed in a future release. Please use ST_GEOMFROMTEXT instead

これから新しく実装する方はST_GEOMFROMTEXTを使うようにしましょう。

SQL

geometry型から緯度経度を取得

まずはgeometry型から緯度経度をそれぞれ取得してみましょう。

取得するにはX関数とY関数を利用します。

関数名	関数名
X関数	経度
Y関数	緯度

気をつけるべきは、Xが経度(longitude)、Yが緯度(latitude)なので緯度経度という呼び方と混乱して逆にならないように気をつけましょう!

+----+----------------+-----------+-------------+------------+-------------+-----------------------------+
| id | prefectureName | latitude  | Y(point) | longitude  | X(point) | ASTEXT(geometry)            |
+----+----------------+-----------+-------------+------------+-------------+-----------------------------+
|  1 | 北海道         | 43.064281 |   43.064281 | 141.346797 |  141.346797 | POINT(141.346797 43.064281) |
|  2 | 青森県         | 40.824354 |   40.824354 | 140.739966 |  140.739966 | POINT(140.739966 40.824354) |
|  3 | 岩手県         | 39.703584 |   39.703584 | 141.152709 |  141.152709 | POINT(141.152709 39.703584) |
|  4 | 宮城県         | 38.268926 |   38.268926 | 140.872006 |  140.872006 | POINT(140.872006 38.268926) |
|  5 | 秋田県         | 39.718031 |   39.718031 | 140.103178 |  140.103178 | POINT(140.103178 39.718031) |
|  6 | 山形県         | 38.240489 |   38.240489 | 140.363428 |  140.363428 | POINT(140.363428 38.240489) |
|  7 | 福島県         | 37.749980 |    37.74998 | 140.467582 |  140.467582 | POINT(140.467582 37.74998)  |
|  8 | 茨城県         | 36.341600 |     36.3416 | 140.446619 |  140.446619 | POINT(140.446619 36.3416)   |
|  9 | 栃木県         | 36.565485 |   36.565485 | 139.883086 |  139.883086 | POINT(139.883086 36.565485) |
| 10 | 群馬県         | 36.390601 |   36.390601 | 139.060436 |  139.060436 | POINT(139.060436 36.390601) |
| 11 | 埼玉県         | 35.856928 |   35.856928 | 139.648787 |  139.648787 | POINT(139.648787 35.856928) |
| 12 | 千葉県         | 35.604589 |   35.604589 | 140.123183 |  140.123183 | POINT(140.123183 35.604589) |
| 13 | 東京都         | 35.689567 |   35.689567 | 139.691722 |  139.691722 | POINT(139.691722 35.689567) |
| 14 | 神奈川県       | 35.447711 |   35.447711 | 139.642536 |  139.642536 | POINT(139.642536 35.447711) |
| 15 | 新潟県         | 37.902435 |   37.902435 | 139.023825 |  139.023825 | POINT(139.023825 37.902435) |
| 16 | 富山県         | 36.695374 |   36.695374 | 137.211194 |  137.211194 | POINT(137.211194 36.695374) |
| 17 | 石川県         | 36.594283 |   36.594283 | 136.625714 |  136.625714 | POINT(136.625714 36.594283) |
| 18 | 福井県         | 36.065422 |   36.065422 | 136.221833 |  136.221833 | POINT(136.221833 36.065422) |
| 19 | 山梨県         | 35.663944 |   35.663944 | 138.568386 |  138.568386 | POINT(138.568386 35.663944) |
| 20 | 長野県         | 36.651314 |   36.651314 | 138.180997 |  138.180997 | POINT(138.180997 36.651314) |
| 21 | 岐阜県         | 35.391192 |   35.391192 | 136.722153 |  136.722153 | POINT(136.722153 35.391192) |
| 22 | 静岡県         | 34.976911 |   34.976911 | 138.383025 |  138.383025 | POINT(138.383025 34.976911) |
| 23 | 愛知県         | 35.180197 |   35.180197 | 136.906742 |  136.906742 | POINT(136.906742 35.180197) |
| 24 | 三重県         | 34.730275 |   34.730275 | 136.508597 |  136.508597 | POINT(136.508597 34.730275) |
| 25 | 滋賀県         | 35.004392 |   35.004392 | 135.868303 |  135.868303 | POINT(135.868303 35.004392) |
| 26 | 京都府         | 35.021281 |   35.021281 | 135.755636 |  135.755636 | POINT(135.755636 35.021281) |
| 27 | 大阪府         | 34.686320 |    34.68632 | 135.520022 |  135.520022 | POINT(135.520022 34.68632)  |
| 28 | 兵庫県         | 34.691102 |   34.691102 | 135.182852 |  135.182852 | POINT(135.182852 34.691102) |
| 29 | 奈良県         | 34.684714 |   34.684714 | 135.832771 |  135.832771 | POINT(135.832771 34.684714) |
| 30 | 和歌山県       | 34.226035 |   34.226035 | 135.167494 |  135.167494 | POINT(135.167494 34.226035) |
| 31 | 鳥取県         | 35.503637 |   35.503637 | 134.238198 |  134.238198 | POINT(134.238198 35.503637) |
| 32 | 島根県         | 35.472217 |   35.472217 | 133.050531 |  133.050531 | POINT(133.050531 35.472217) |
| 33 | 岡山県         | 34.661764 |   34.661764 | 133.934900 |    133.9349 | POINT(133.9349 34.661764)   |
| 34 | 広島県         | 34.396275 |   34.396275 | 132.459375 |  132.459375 | POINT(132.459375 34.396275) |
| 35 | 山口県         | 34.185802 |   34.185802 | 131.471550 |   131.47155 | POINT(131.47155 34.185802)  |
| 36 | 徳島県         | 34.065733 |   34.065733 | 134.559489 |  134.559489 | POINT(134.559489 34.065733) |
| 37 | 香川県         | 34.340035 |   34.340035 | 134.043279 |  134.043279 | POINT(134.043279 34.340035) |
| 38 | 愛媛県         | 33.841644 |   33.841644 | 132.766103 |  132.766103 | POINT(132.766103 33.841644) |
| 39 | 高知県         | 33.559756 |   33.559756 | 133.531117 |  133.531117 | POINT(133.531117 33.559756) |
| 40 | 福岡県         | 33.606261 |   33.606261 | 130.418117 |  130.418117 | POINT(130.418117 33.606261) |
| 41 | 佐賀県         | 33.249328 |   33.249328 | 130.298806 |  130.298806 | POINT(130.298806 33.249328) |
| 42 | 長崎県         | 32.745090 |    32.74509 | 129.873641 |  129.873641 | POINT(129.873641 32.74509)  |
| 43 | 熊本県         | 32.789765 |   32.789765 | 130.741662 |  130.741662 | POINT(130.741662 32.789765) |
| 44 | 大分県         | 33.238123 |   33.238123 | 131.612590 |   131.61259 | POINT(131.61259 33.238123)  |
| 45 | 宮崎県         | 31.911007 |   31.911007 | 131.423882 |  131.423882 | POINT(131.423882 31.911007) |
| 46 | 鹿児島県       | 31.560220 |    31.56022 | 130.558281 |  130.558281 | POINT(130.558281 31.56022)  |
| 47 | 沖縄県         | 26.212375 |   26.212375 | 127.680981 |  127.680981 | POINT(127.680981 26.212375) |
+----+----------------+-----------+-------------+------------+-------------+-----------------------------+
47 rows in set, 3 warnings (0.00 sec)

+----+----------------+-----------+-------------+------------+-------------+-----------------------------+

+----+----------------+-----------+-------------+------------+-------------+-----------------------------+

| 1 | 北海道 | 43.064281 | 43.064281 | 141.346797 | 141.346797 | POINT(141.346797 43.064281) |

| 2 | 青森県 | 40.824354 | 40.824354 | 140.739966 | 140.739966 | POINT(140.739966 40.824354) |

| 3 | 岩手県 | 39.703584 | 39.703584 | 141.152709 | 141.152709 | POINT(141.152709 39.703584) |

| 4 | 宮城県 | 38.268926 | 38.268926 | 140.872006 | 140.872006 | POINT(140.872006 38.268926) |

| 5 | 秋田県 | 39.718031 | 39.718031 | 140.103178 | 140.103178 | POINT(140.103178 39.718031) |

| 6 | 山形県 | 38.240489 | 38.240489 | 140.363428 | 140.363428 | POINT(140.363428 38.240489) |

| 7 | 福島県 | 37.749980 | 37.74998 | 140.467582 | 140.467582 | POINT(140.467582 37.74998) |

| 8 | 茨城県 | 36.341600 | 36.3416 | 140.446619 | 140.446619 | POINT(140.446619 36.3416) |

| 9 | 栃木県 | 36.565485 | 36.565485 | 139.883086 | 139.883086 | POINT(139.883086 36.565485) |

| 10 | 群馬県 | 36.390601 | 36.390601 | 139.060436 | 139.060436 | POINT(139.060436 36.390601) |

| 11 | 埼玉県 | 35.856928 | 35.856928 | 139.648787 | 139.648787 | POINT(139.648787 35.856928) |

| 12 | 千葉県 | 35.604589 | 35.604589 | 140.123183 | 140.123183 | POINT(140.123183 35.604589) |

| 13 | 東京都 | 35.689567 | 35.689567 | 139.691722 | 139.691722 | POINT(139.691722 35.689567) |

| 14 | 神奈川県 | 35.447711 | 35.447711 | 139.642536 | 139.642536 | POINT(139.642536 35.447711) |

| 15 | 新潟県 | 37.902435 | 37.902435 | 139.023825 | 139.023825 | POINT(139.023825 37.902435) |

| 16 | 富山県 | 36.695374 | 36.695374 | 137.211194 | 137.211194 | POINT(137.211194 36.695374) |

| 17 | 石川県 | 36.594283 | 36.594283 | 136.625714 | 136.625714 | POINT(136.625714 36.594283) |

| 18 | 福井県 | 36.065422 | 36.065422 | 136.221833 | 136.221833 | POINT(136.221833 36.065422) |

| 19 | 山梨県 | 35.663944 | 35.663944 | 138.568386 | 138.568386 | POINT(138.568386 35.663944) |

| 20 | 長野県 | 36.651314 | 36.651314 | 138.180997 | 138.180997 | POINT(138.180997 36.651314) |

| 21 | 岐阜県 | 35.391192 | 35.391192 | 136.722153 | 136.722153 | POINT(136.722153 35.391192) |

| 22 | 静岡県 | 34.976911 | 34.976911 | 138.383025 | 138.383025 | POINT(138.383025 34.976911) |

| 23 | 愛知県 | 35.180197 | 35.180197 | 136.906742 | 136.906742 | POINT(136.906742 35.180197) |

| 24 | 三重県 | 34.730275 | 34.730275 | 136.508597 | 136.508597 | POINT(136.508597 34.730275) |

| 25 | 滋賀県 | 35.004392 | 35.004392 | 135.868303 | 135.868303 | POINT(135.868303 35.004392) |

| 26 | 京都府 | 35.021281 | 35.021281 | 135.755636 | 135.755636 | POINT(135.755636 35.021281) |

| 27 | 大阪府 | 34.686320 | 34.68632 | 135.520022 | 135.520022 | POINT(135.520022 34.68632) |

| 28 | 兵庫県 | 34.691102 | 34.691102 | 135.182852 | 135.182852 | POINT(135.182852 34.691102) |

| 29 | 奈良県 | 34.684714 | 34.684714 | 135.832771 | 135.832771 | POINT(135.832771 34.684714) |

| 30 | 和歌山県 | 34.226035 | 34.226035 | 135.167494 | 135.167494 | POINT(135.167494 34.226035) |

| 31 | 鳥取県 | 35.503637 | 35.503637 | 134.238198 | 134.238198 | POINT(134.238198 35.503637) |

| 32 | 島根県 | 35.472217 | 35.472217 | 133.050531 | 133.050531 | POINT(133.050531 35.472217) |

| 33 | 岡山県 | 34.661764 | 34.661764 | 133.934900 | 133.9349 | POINT(133.9349 34.661764) |

| 34 | 広島県 | 34.396275 | 34.396275 | 132.459375 | 132.459375 | POINT(132.459375 34.396275) |

| 35 | 山口県 | 34.185802 | 34.185802 | 131.471550 | 131.47155 | POINT(131.47155 34.185802) |

| 36 | 徳島県 | 34.065733 | 34.065733 | 134.559489 | 134.559489 | POINT(134.559489 34.065733) |

| 37 | 香川県 | 34.340035 | 34.340035 | 134.043279 | 134.043279 | POINT(134.043279 34.340035) |

| 38 | 愛媛県 | 33.841644 | 33.841644 | 132.766103 | 132.766103 | POINT(132.766103 33.841644) |

| 39 | 高知県 | 33.559756 | 33.559756 | 133.531117 | 133.531117 | POINT(133.531117 33.559756) |

| 40 | 福岡県 | 33.606261 | 33.606261 | 130.418117 | 130.418117 | POINT(130.418117 33.606261) |

| 41 | 佐賀県 | 33.249328 | 33.249328 | 130.298806 | 130.298806 | POINT(130.298806 33.249328) |

| 42 | 長崎県 | 32.745090 | 32.74509 | 129.873641 | 129.873641 | POINT(129.873641 32.74509) |

| 43 | 熊本県 | 32.789765 | 32.789765 | 130.741662 | 130.741662 | POINT(130.741662 32.789765) |

| 44 | 大分県 | 33.238123 | 33.238123 | 131.612590 | 131.61259 | POINT(131.61259 33.238123) |

| 45 | 宮崎県 | 31.911007 | 31.911007 | 131.423882 | 131.423882 | POINT(131.423882 31.911007) |

| 46 | 鹿児島県 | 31.560220 | 31.56022 | 130.558281 | 130.558281 | POINT(130.558281 31.56022) |

| 47 | 沖縄県 | 26.212375 | 26.212375 | 127.680981 | 127.680981 | POINT(127.680981 26.212375) |

+----+----------------+-----------+-------------+------------+-------------+-----------------------------+

47 rows in set, 3 warnings (0.00 sec)

しっかりと同じ値が関数経由でも取得出来ていますね♪

2点間距離の取得

次は2点間距離を算出してみましょう。

試しに東京タワーからの距離を出してみます。

SELECT id, prefectureName, ASTEXT(point),
(
         6371 * acos(
           cos(radians(35.658581))
           * cos(radians(latitude))
           * cos(radians(longitude) - radians(139.745433))
           + sin(radians(35.658581))
           * sin(radians(latitude))
         )
       ) AS distanceFromDouble,
GLENGTH(GEOMFROMTEXT(
      CONCAT('LINESTRING(139.745433 35.658581, ',
          X( point ),' ',Y( point ),
      ')'
  ))
) AS distanceFromGemetry FROM prefectural_office;

SELECT id, prefectureName, ASTEXT(point),

(

6371 * acos(

cos(radians(35.658581))

* cos(radians(latitude))

* cos(radians(longitude) - radians(139.745433))

+ sin(radians(35.658581))

* sin(radians(latitude))

)

) AS distanceFromDouble,

GLENGTH(GEOMFROMTEXT(

CONCAT('LINESTRING(139.745433 35.658581, ',

X( point ),' ',Y( point ),

')'

))

) AS distanceFromGemetry FROM prefectural_office;

+----+----------------+-----------------------------+--------------------+---------------------+
| id | prefectureName | ASTEXT(point)            | distanceFromDouble(km) | distanceFromGemetry(度) |
+----+----------------+-----------------------------+--------------------+---------------------+
|  1 | 北海道         | POINT(141.346797 43.064281) |  834.8571378337715 |    7.57685681206238 |
|  2 | 青森県         | POINT(140.739966 40.824354) |  580.9247160833129 |   5.260637468560065 |
|  3 | 岩手県         | POINT(141.152709 39.703584) |   466.502623601523 |   4.282811577712122 |
|  4 | 宮城県         | POINT(140.872006 38.268926) |  307.0225258167587 |  2.8430736436740482 |
|  5 | 秋田県         | POINT(140.103178 39.718031) |  452.4852998510215 |   4.075182914609486 |
|  6 | 山形県         | POINT(140.363428 38.240489) | 292.29790529837885 |  2.6548383642867983 |
|  7 | 福島県         | POINT(140.467582 37.74998)  |   241.296943791094 |  2.2125661471246487 |
|  8 | 茨城県         | POINT(140.446619 36.3416)   |  98.72580516123963 |  0.9788650371511958 |
|  9 | 栃木県         | POINT(139.883086 36.565485) | 101.59838451303102 |  0.9172912370806819 |
| 10 | 群馬県         | POINT(139.060436 36.390601) | 102.07904543386358 |  1.0025338749433719 |
| 11 | 埼玉県         | POINT(139.648787 35.856928) |  23.71671427178685 |  0.2206399368314828 |
| 12 | 千葉県         | POINT(140.123183 35.604589) |   34.6637438158627 |  0.3815890440827873 |
| 13 | 東京都         | POINT(139.691722 35.689567) |  5.950625481171745 | 0.06200809396360393 |
| 14 | 神奈川県       | POINT(139.642536 35.447711) |  25.22783961062546 | 0.23463578053868245 |
| 15 | 新潟県         | POINT(139.023825 37.902435) |  257.6462044373321 |  2.3570317942234045 |
| 16 | 富山県         | POINT(137.211194 36.695374) |  254.9981890193849 |  2.7381210773028157 |
| 17 | 石川県         | POINT(136.625714 36.594283) |  298.8700798944938 |  3.2570208583558413 |
| 18 | 福井県         | POINT(136.221833 36.065422) |  320.7195086989554 |    3.54700952342687 |
| 19 | 山梨県         | POINT(138.568386 35.663944) | 106.33948479828305 |  1.1770592177023085 |
| 20 | 長野県         | POINT(138.180997 36.651314) | 178.63884533543978 |   1.852829945619674 |
| 21 | 岐阜県         | POINT(136.722153 35.391192) |  275.1986596888009 |   3.035081355700535 |
| 22 | 静岡県         | POINT(138.383025 34.976911) | 144.99856059861196 |  1.5234269090980253 |
| 23 | 愛知県         | POINT(136.906742 35.180197) |   262.663603602861 |   2.878718090563385 |
| 24 | 三重県         | POINT(136.508597 34.730275) |  311.6940135546833 |   3.367322277497639 |
| 25 | 滋賀県         | POINT(135.868303 35.004392) |  359.1309463878057 |  3.9319334028720463 |
| 26 | 京都府         | POINT(135.755636 35.021281) |  368.7410271001972 |   4.040375154760861 |
| 27 | 大阪府         | POINT(135.520022 34.68632)  |  398.9483006847132 |  4.3358262846938205 |
| 28 | 兵庫県         | POINT(135.182852 34.691102) | 428.37485993933143 |   4.664028408682985 |
| 29 | 奈良県         | POINT(135.832771 34.684714) | 371.72686754619974 |   4.032039292954977 |
| 30 | 和歌山県       | POINT(135.167494 34.226035) |  446.5892595756499 |   4.796844121903156 |
| 31 | 鳥取県         | POINT(134.238198 35.503637) |  498.2740045758765 |   5.509414214629427 |
| 32 | 島根県         | POINT(133.050531 35.472217) |  605.8028384722828 |   6.697495377385474 |
| 33 | 岡山県         | POINT(133.9349 34.661764)   |  539.6319451989946 |   5.895416683795804 |
| 34 | 広島県         | POINT(132.459375 34.396275) |  677.9409956240623 |   7.394596514820802 |
| 35 | 山口県         | POINT(131.47155 34.185802)  |   771.658681940248 |   8.403940616194864 |
| 36 | 徳島県         | POINT(134.559489 34.065733) |  505.1378956725802 |  5.4250511446658045 |
| 37 | 香川県         | POINT(134.043279 34.340035) |  539.5901864769002 |   5.852616832992892 |
| 38 | 愛媛県         | POINT(132.766103 33.841644) |  668.7080193259349 |   7.211955858910192 |
| 39 | 高知県         | POINT(133.531117 33.559756) |  614.5919508932951 |  6.5591759946262265 |
| 40 | 福岡県         | POINT(130.418117 33.606261) |  882.9671997424465 |   9.550436699243438 |
| 41 | 佐賀県         | POINT(130.298806 33.249328) |  906.1897556389888 |   9.749013370343565 |
| 42 | 長崎県         | POINT(129.873641 32.74509)  |  963.3773037576652 |  10.292750220341743 |
| 43 | 熊本県         | POINT(130.741662 32.789765) |  886.7054081831598 |   9.449761767489008 |
| 44 | 大分県         | POINT(131.61259 33.238123)  |  792.5108882098315 |   8.485384622538488 |
| 45 | 宮崎県         | POINT(131.423882 31.911007) |  874.2327147262182 |    9.12647368544264 |
| 46 | 鹿児島県       | POINT(130.558281 31.56022)  |  964.5350593543586 |   10.05983721326667 |
| 47 | 沖縄県         | POINT(127.680981 26.212375) | 1555.5654256120438 |  15.322591486257789 |
+----+----------------+-----------------------------+--------------------+---------------------+
47 rows in set, 5 warnings (0.00 sec)

+----+----------------+-----------------------------+--------------------+---------------------+

+----+----------------+-----------------------------+--------------------+---------------------+

| 1 | 北海道 | POINT(141.346797 43.064281) | 834.8571378337715 | 7.57685681206238 |

| 2 | 青森県 | POINT(140.739966 40.824354) | 580.9247160833129 | 5.260637468560065 |

| 3 | 岩手県 | POINT(141.152709 39.703584) | 466.502623601523 | 4.282811577712122 |

| 4 | 宮城県 | POINT(140.872006 38.268926) | 307.0225258167587 | 2.8430736436740482 |

| 5 | 秋田県 | POINT(140.103178 39.718031) | 452.4852998510215 | 4.075182914609486 |

| 6 | 山形県 | POINT(140.363428 38.240489) | 292.29790529837885 | 2.6548383642867983 |

| 7 | 福島県 | POINT(140.467582 37.74998) | 241.296943791094 | 2.2125661471246487 |

| 8 | 茨城県 | POINT(140.446619 36.3416) | 98.72580516123963 | 0.9788650371511958 |

| 9 | 栃木県 | POINT(139.883086 36.565485) | 101.59838451303102 | 0.9172912370806819 |

| 10 | 群馬県 | POINT(139.060436 36.390601) | 102.07904543386358 | 1.0025338749433719 |

| 11 | 埼玉県 | POINT(139.648787 35.856928) | 23.71671427178685 | 0.2206399368314828 |

| 12 | 千葉県 | POINT(140.123183 35.604589) | 34.6637438158627 | 0.3815890440827873 |

| 13 | 東京都 | POINT(139.691722 35.689567) | 5.950625481171745 | 0.06200809396360393 |

| 14 | 神奈川県 | POINT(139.642536 35.447711) | 25.22783961062546 | 0.23463578053868245 |

| 15 | 新潟県 | POINT(139.023825 37.902435) | 257.6462044373321 | 2.3570317942234045 |

| 16 | 富山県 | POINT(137.211194 36.695374) | 254.9981890193849 | 2.7381210773028157 |

| 17 | 石川県 | POINT(136.625714 36.594283) | 298.8700798944938 | 3.2570208583558413 |

| 18 | 福井県 | POINT(136.221833 36.065422) | 320.7195086989554 | 3.54700952342687 |

| 19 | 山梨県 | POINT(138.568386 35.663944) | 106.33948479828305 | 1.1770592177023085 |

| 20 | 長野県 | POINT(138.180997 36.651314) | 178.63884533543978 | 1.852829945619674 |

| 21 | 岐阜県 | POINT(136.722153 35.391192) | 275.1986596888009 | 3.035081355700535 |

| 22 | 静岡県 | POINT(138.383025 34.976911) | 144.99856059861196 | 1.5234269090980253 |

| 23 | 愛知県 | POINT(136.906742 35.180197) | 262.663603602861 | 2.878718090563385 |

| 24 | 三重県 | POINT(136.508597 34.730275) | 311.6940135546833 | 3.367322277497639 |

| 25 | 滋賀県 | POINT(135.868303 35.004392) | 359.1309463878057 | 3.9319334028720463 |

| 26 | 京都府 | POINT(135.755636 35.021281) | 368.7410271001972 | 4.040375154760861 |

| 27 | 大阪府 | POINT(135.520022 34.68632) | 398.9483006847132 | 4.3358262846938205 |

| 28 | 兵庫県 | POINT(135.182852 34.691102) | 428.37485993933143 | 4.664028408682985 |

| 29 | 奈良県 | POINT(135.832771 34.684714) | 371.72686754619974 | 4.032039292954977 |

| 30 | 和歌山県 | POINT(135.167494 34.226035) | 446.5892595756499 | 4.796844121903156 |

| 31 | 鳥取県 | POINT(134.238198 35.503637) | 498.2740045758765 | 5.509414214629427 |

| 32 | 島根県 | POINT(133.050531 35.472217) | 605.8028384722828 | 6.697495377385474 |

| 33 | 岡山県 | POINT(133.9349 34.661764) | 539.6319451989946 | 5.895416683795804 |

| 34 | 広島県 | POINT(132.459375 34.396275) | 677.9409956240623 | 7.394596514820802 |

| 35 | 山口県 | POINT(131.47155 34.185802) | 771.658681940248 | 8.403940616194864 |

| 36 | 徳島県 | POINT(134.559489 34.065733) | 505.1378956725802 | 5.4250511446658045 |

| 37 | 香川県 | POINT(134.043279 34.340035) | 539.5901864769002 | 5.852616832992892 |

| 38 | 愛媛県 | POINT(132.766103 33.841644) | 668.7080193259349 | 7.211955858910192 |

| 39 | 高知県 | POINT(133.531117 33.559756) | 614.5919508932951 | 6.5591759946262265 |

| 40 | 福岡県 | POINT(130.418117 33.606261) | 882.9671997424465 | 9.550436699243438 |

| 41 | 佐賀県 | POINT(130.298806 33.249328) | 906.1897556389888 | 9.749013370343565 |

| 42 | 長崎県 | POINT(129.873641 32.74509) | 963.3773037576652 | 10.292750220341743 |

| 43 | 熊本県 | POINT(130.741662 32.789765) | 886.7054081831598 | 9.449761767489008 |

| 44 | 大分県 | POINT(131.61259 33.238123) | 792.5108882098315 | 8.485384622538488 |

| 45 | 宮崎県 | POINT(131.423882 31.911007) | 874.2327147262182 | 9.12647368544264 |

| 46 | 鹿児島県 | POINT(130.558281 31.56022) | 964.5350593543586 | 10.05983721326667 |

| 47 | 沖縄県 | POINT(127.680981 26.212375) | 1555.5654256120438 | 15.322591486257789 |

+----+----------------+-----------------------------+--------------------+---------------------+

47 rows in set, 5 warnings (0.00 sec)

実行結果の単位が違う

ここで注意すべきは、doubule型から取得した結果の単位がkmである事に対し、GLENGTH関数の結果は度である事です。

これは、地球の外周の1/360となっています。

km単位で取得する

そこで利用するのがST_Distance_Sphere関数です。

こちらを利用する事で2点間距離をメートルで簡単に算出する事が出来ます。

あとはこちらの結果を1000で割ればkmに変換する事が出来ますね。

SELECT id, prefectureName, ASTEXT(geometry),
(
         6371 * acos(
           cos(radians(35.658581))
           * cos(radians(latitude))
           * cos(radians(longitude) - radians(139.745433))
           + sin(radians(35.658581))
           * sin(radians(latitude))
         )
       ) AS distanceFromDouble,
ST_Distance_Sphere(
    GeomFromText(ASTEXT(point)),
    GeomFromText('POINT(139.745433 35.658581)')
) / 1000  AS distanceFromGemetry  FROM prefectural_office;

SELECT id, prefectureName, ASTEXT(geometry),

(

6371 * acos(

cos(radians(35.658581))

* cos(radians(latitude))

* cos(radians(longitude) - radians(139.745433))

+ sin(radians(35.658581))

* sin(radians(latitude))

)

) AS distanceFromDouble,

ST_Distance_Sphere(

GeomFromText(ASTEXT(point)),

GeomFromText('POINT(139.745433 35.658581)')

) / 1000 AS distanceFromGemetry FROM prefectural_office;

+----+----------------+-----------------------------+--------------------+---------------------+
| id | prefectureName | ASTEXT(point)            | distanceFromDouble | distanceFromGemetry |
+----+----------------+-----------------------------+--------------------+---------------------+
|  1 | 北海道         | POINT(141.346797 43.064281) |  834.8571378337715 |   834.8553032709167 |
|  2 | 青森県         | POINT(140.739966 40.824354) |  580.9247160833129 |   580.9234395261007 |
|  3 | 岩手県         | POINT(141.152709 39.703584) |   466.502623601523 |  466.50159848196625 |
|  4 | 宮城県         | POINT(140.872006 38.268926) |  307.0225258167587 |  307.02185114788983 |
|  5 | 秋田県         | POINT(140.103178 39.718031) |  452.4852998510215 |   452.4843055339215 |
|  6 | 山形県         | POINT(140.363428 38.240489) | 292.29790529837885 |   292.2972629862389 |
|  7 | 福島県         | POINT(140.467582 37.74998)  |   241.296943791094 |  241.29641355137736 |
|  8 | 茨城県         | POINT(140.446619 36.3416)   |  98.72580516123963 |   98.72558821551446 |
|  9 | 栃木県         | POINT(139.883086 36.565485) | 101.59838451303102 |  101.59816125492286 |
| 10 | 群馬県         | POINT(139.060436 36.390601) | 102.07904543386358 |  102.07882111954436 |
| 11 | 埼玉県         | POINT(139.648787 35.856928) |  23.71671427178685 |    23.7166621553297 |
| 12 | 千葉県         | POINT(140.123183 35.604589) |   34.6637438158627 |    34.6636676438572 |
| 13 | 東京都         | POINT(139.691722 35.689567) |  5.950625481171745 |   5.950612404637792 |
| 14 | 神奈川県       | POINT(139.642536 35.447711) |  25.22783961062546 |  25.227784173458527 |
| 15 | 新潟県         | POINT(139.023825 37.902435) |  257.6462044373321 |   257.6456382708381 |
| 16 | 富山県         | POINT(137.211194 36.695374) |  254.9981890193849 |    254.997628671788 |
| 17 | 石川県         | POINT(136.625714 36.594283) |  298.8700798944938 |  298.86942314027294 |
| 18 | 福井県         | POINT(136.221833 36.065422) |  320.7195086989554 |   320.7188039315493 |
| 19 | 山梨県         | POINT(138.568386 35.663944) | 106.33948479828305 |   106.3392511218679 |
| 20 | 長野県         | POINT(138.180997 36.651314) | 178.63884533543978 |   178.6384527842367 |
| 21 | 岐阜県         | POINT(136.722153 35.391192) |  275.1986596888009 |   275.1980549515127 |
| 22 | 静岡県         | POINT(138.383025 34.976911) | 144.99856059861196 |   144.9982419704254 |
| 23 | 愛知県         | POINT(136.906742 35.180197) |   262.663603602861 |  262.66302641082103 |
| 24 | 三重県         | POINT(136.508597 34.730275) |  311.6940135546833 |   311.6933286204252 |
| 25 | 滋賀県         | POINT(135.868303 35.004392) |  359.1309463878057 |  359.13015721290344 |
| 26 | 京都府         | POINT(135.755636 35.021281) |  368.7410271001972 |   368.7402168075464 |
| 27 | 大阪府         | POINT(135.520022 34.68632)  |  398.9483006847132 |  398.94742401289693 |
| 28 | 兵庫県         | POINT(135.182852 34.691102) | 428.37485993933143 |   428.3739186039041 |
| 29 | 奈良県         | POINT(135.832771 34.684714) | 371.72686754619974 |  371.72605069231264 |
| 30 | 和歌山県       | POINT(135.167494 34.226035) |  446.5892595756499 |   446.5882782148477 |
| 31 | 鳥取県         | POINT(134.238198 35.503637) |  498.2740045758765 |  498.27290964006875 |
| 32 | 島根県         | POINT(133.050531 35.472217) |  605.8028384722828 |   605.8015072464628 |
| 33 | 岡山県         | POINT(133.9349 34.661764)   |  539.6319451989946 |   539.6307593808863 |
| 34 | 広島県         | POINT(132.459375 34.396275) |  677.9409956240623 |   677.9395058777228 |
| 35 | 山口県         | POINT(131.47155 34.185802)  |   771.658681940248 |   771.6569862532981 |
| 36 | 徳島県         | POINT(134.559489 34.065733) |  505.1378956725802 |  505.13678565366786 |
| 37 | 香川県         | POINT(134.043279 34.340035) |  539.5901864769002 |   539.5890007505598 |
| 38 | 愛媛県         | POINT(132.766103 33.841644) |  668.7080193259349 |   668.7065498686708 |
| 39 | 高知県         | POINT(133.531117 33.559756) |  614.5919508932951 |   614.5906003537763 |
| 40 | 福岡県         | POINT(130.418117 33.606261) |  882.9671997424465 |   882.9652594597918 |
| 41 | 佐賀県         | POINT(130.298806 33.249328) |  906.1897556389888 |   906.1877643257634 |
| 42 | 長崎県         | POINT(129.873641 32.74509)  |  963.3773037576652 |    963.375186777247 |
| 43 | 熊本県         | POINT(130.741662 32.789765) |  886.7054081831598 |     886.70345968595 |
| 44 | 大分県         | POINT(131.61259 33.238123)  |  792.5108882098315 |   792.5091467010454 |
| 45 | 宮崎県         | POINT(131.423882 31.911007) |  874.2327147262182 |    874.230793637222 |
| 46 | 鹿児島県       | POINT(130.558281 31.56022)  |  964.5350593543586 |   964.5329398298231 |
| 47 | 沖縄県         | POINT(127.680981 26.212375) | 1555.5654256120438 |  1555.5620073235557 |
+----+----------------+-----------------------------+--------------------+---------------------+
47 rows in set, 4 warnings (0.00 sec)

+----+----------------+-----------------------------+--------------------+---------------------+

+----+----------------+-----------------------------+--------------------+---------------------+

| 1 | 北海道 | POINT(141.346797 43.064281) | 834.8571378337715 | 834.8553032709167 |

| 2 | 青森県 | POINT(140.739966 40.824354) | 580.9247160833129 | 580.9234395261007 |

| 3 | 岩手県 | POINT(141.152709 39.703584) | 466.502623601523 | 466.50159848196625 |

| 4 | 宮城県 | POINT(140.872006 38.268926) | 307.0225258167587 | 307.02185114788983 |

| 5 | 秋田県 | POINT(140.103178 39.718031) | 452.4852998510215 | 452.4843055339215 |

| 6 | 山形県 | POINT(140.363428 38.240489) | 292.29790529837885 | 292.2972629862389 |

| 7 | 福島県 | POINT(140.467582 37.74998) | 241.296943791094 | 241.29641355137736 |

| 8 | 茨城県 | POINT(140.446619 36.3416) | 98.72580516123963 | 98.72558821551446 |

| 9 | 栃木県 | POINT(139.883086 36.565485) | 101.59838451303102 | 101.59816125492286 |

| 10 | 群馬県 | POINT(139.060436 36.390601) | 102.07904543386358 | 102.07882111954436 |

| 11 | 埼玉県 | POINT(139.648787 35.856928) | 23.71671427178685 | 23.7166621553297 |

| 12 | 千葉県 | POINT(140.123183 35.604589) | 34.6637438158627 | 34.6636676438572 |

| 13 | 東京都 | POINT(139.691722 35.689567) | 5.950625481171745 | 5.950612404637792 |

| 14 | 神奈川県 | POINT(139.642536 35.447711) | 25.22783961062546 | 25.227784173458527 |

| 15 | 新潟県 | POINT(139.023825 37.902435) | 257.6462044373321 | 257.6456382708381 |

| 16 | 富山県 | POINT(137.211194 36.695374) | 254.9981890193849 | 254.997628671788 |

| 17 | 石川県 | POINT(136.625714 36.594283) | 298.8700798944938 | 298.86942314027294 |

| 18 | 福井県 | POINT(136.221833 36.065422) | 320.7195086989554 | 320.7188039315493 |

| 19 | 山梨県 | POINT(138.568386 35.663944) | 106.33948479828305 | 106.3392511218679 |

| 20 | 長野県 | POINT(138.180997 36.651314) | 178.63884533543978 | 178.6384527842367 |

| 21 | 岐阜県 | POINT(136.722153 35.391192) | 275.1986596888009 | 275.1980549515127 |

| 22 | 静岡県 | POINT(138.383025 34.976911) | 144.99856059861196 | 144.9982419704254 |

| 23 | 愛知県 | POINT(136.906742 35.180197) | 262.663603602861 | 262.66302641082103 |

| 24 | 三重県 | POINT(136.508597 34.730275) | 311.6940135546833 | 311.6933286204252 |

| 25 | 滋賀県 | POINT(135.868303 35.004392) | 359.1309463878057 | 359.13015721290344 |

| 26 | 京都府 | POINT(135.755636 35.021281) | 368.7410271001972 | 368.7402168075464 |

| 27 | 大阪府 | POINT(135.520022 34.68632) | 398.9483006847132 | 398.94742401289693 |

| 28 | 兵庫県 | POINT(135.182852 34.691102) | 428.37485993933143 | 428.3739186039041 |

| 29 | 奈良県 | POINT(135.832771 34.684714) | 371.72686754619974 | 371.72605069231264 |

| 30 | 和歌山県 | POINT(135.167494 34.226035) | 446.5892595756499 | 446.5882782148477 |

| 31 | 鳥取県 | POINT(134.238198 35.503637) | 498.2740045758765 | 498.27290964006875 |

| 32 | 島根県 | POINT(133.050531 35.472217) | 605.8028384722828 | 605.8015072464628 |

| 33 | 岡山県 | POINT(133.9349 34.661764) | 539.6319451989946 | 539.6307593808863 |

| 34 | 広島県 | POINT(132.459375 34.396275) | 677.9409956240623 | 677.9395058777228 |

| 35 | 山口県 | POINT(131.47155 34.185802) | 771.658681940248 | 771.6569862532981 |

| 36 | 徳島県 | POINT(134.559489 34.065733) | 505.1378956725802 | 505.13678565366786 |

| 37 | 香川県 | POINT(134.043279 34.340035) | 539.5901864769002 | 539.5890007505598 |

| 38 | 愛媛県 | POINT(132.766103 33.841644) | 668.7080193259349 | 668.7065498686708 |

| 39 | 高知県 | POINT(133.531117 33.559756) | 614.5919508932951 | 614.5906003537763 |

| 40 | 福岡県 | POINT(130.418117 33.606261) | 882.9671997424465 | 882.9652594597918 |

| 41 | 佐賀県 | POINT(130.298806 33.249328) | 906.1897556389888 | 906.1877643257634 |

| 42 | 長崎県 | POINT(129.873641 32.74509) | 963.3773037576652 | 963.375186777247 |

| 43 | 熊本県 | POINT(130.741662 32.789765) | 886.7054081831598 | 886.70345968595 |

| 44 | 大分県 | POINT(131.61259 33.238123) | 792.5108882098315 | 792.5091467010454 |

| 45 | 宮崎県 | POINT(131.423882 31.911007) | 874.2327147262182 | 874.230793637222 |

| 46 | 鹿児島県 | POINT(130.558281 31.56022) | 964.5350593543586 | 964.5329398298231 |

| 47 | 沖縄県 | POINT(127.680981 26.212375) | 1555.5654256120438 | 1555.5620073235557 |

+----+----------------+-----------------------------+--------------------+---------------------+

47 rows in set, 4 warnings (0.00 sec)

これでdouble型から算出した距離(km)にかなり近づきましたね！

多少の差異は出ていますが、誤差の範囲と考えて良いでしょう。

終わりに

以上のように、double型を利用するよりもSQLがスッキリしました。

皆さんも、緯度経度をMySQLで管理する際はgeometry型を使ってみてはいかがでしょうか♪

【位置情報】MySQLのgeometry型を使って緯度経度の2点間距離をkmで計算・算出する方法【ST_GEOMFROMTEXT】

ねこじゃすり

Anker PowerCor

【最新機種】GoPro hero11 Black

[ノースフェイス] THE NORTH FACE メンズ アウター マウンテンライトジャケット

ペヤング ソースやきそば 120g×18個

MOFT X 【新型 ミニマム版】 iPhone対応 スマホスタンド

サンディスク microSD 128GB

MySQLのgeometry型を使ってみよう

double型でも実現は可能

やり方

前提

GEOMFROMTEXTは非推奨

SQL

geometry型から緯度経度を取得

2点間距離の取得

実行結果の単位が違う

km単位で取得する

終わりに

CATEGORIES & TAGS

[ノースフェイス] THE NORTH FACE メンズアウターマウンテンライトジャケット

ペヤングソースやきそば 120g×18個

MOFT X 【新型ミニマム版】 iPhone対応スマホスタンド