初めに
この記事はRDBMS-GIS(MySQL,PostgreSQLなど) Advent Calendar 2020のエントリーです。
目次
とある日
ネタ探しのために、いろいろなMySQLのGISについて調べてた。
GIS使ったことないので、チュートリアル的内容にしようかなとか、空間インデックスについて調べてみるとか、MySQL8.0にアップグレードする前に空間関数の互換性のない使用を検出するとかなどなど。
ずっと悩んでいて、MySQL徹底入門 第4版 MySQL 8.0対応を見てて、ST_Distanceについての解説を読んでいた。
「距離を返します」...え、座標の距離を測るって二点間の距離求められるじゃん。
ってことで、GISの本来の使い方ではない気もしますが、MySQLのGISの機能を使って、二点間の距離や座標三角形の面積を求めたいと思います。
環境
今回それぞれ使用するアプリケーション等のバージョンを明記しています。
VirtualBox 内の仮想環境下で、MySQL を構築しています。
Oracle VM VirtualBox
| 項目名 | 値 |
|---|---|
| バージョン | 6.0.14 r133895 (Qt5.6.2) |
DBeaver
| 項目名 | 値 |
|---|---|
| バージョン | 7.2.0.202008302047 |
ホスト PC
| 項目名 | 値 |
|---|---|
| OS | Windows 10 Home |
| CPU | Intel(R) Core(TM) i7-8550U CPU @ 1.80GHz |
| メモリ | 8.0 GB |
ゲスト PC
VirtualBox の仮想環境内に、テスト環境を構築しています。
OS
mysql> \! cat /etc/os-release NAME="CentOS Linux" VERSION="8 (Core)" ID="centos" ID_LIKE="rhel fedora" VERSION_ID="8" PLATFORM_ID="platform:el8" PRETTY_NAME="CentOS Linux 8 (Core)" ANSI_COLOR="0;31" CPE_NAME="cpe:/o:centos:centos:8" HOME_URL="https://www.centos.org/" BUG_REPORT_URL="https://bugs.centos.org/" CENTOS_MANTISBT_PROJECT="CentOS-8" CENTOS_MANTISBT_PROJECT_VERSION="8" REDHAT_SUPPORT_PRODUCT="centos" REDHAT_SUPPORT_PRODUCT_VERSION="8"
メモリ
2048MB
ストレージ
20.00 GB
MySQL
mysql> status; -------------- mysql Ver 8.0.21 for Linux on x86_64 (Source distribution) Connection id: 27 Current database: hobby Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 8.0.21 Source distribution Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: utf8mb4 Db characterset: utf8mb4 Client characterset: utf8mb4 Conn. characterset: utf8mb4 UNIX socket: /var/lib/mysql/mysql.sock Binary data as: Hexadecimal Uptime: 11 hours 56 min 31 sec Threads: 5 Questions: 1817 Slow queries: 0 Opens: 409 Flush tables: 3 Open tables: 312 Queries per second avg: 0.042
二点間の距離
ある2点間を（道路状態や地形、建築物等を一切無視し）直線状に測ったときの長さを直線距離という。このとき直線距離は2点間における最短の長さ、即ち最短距離であり、これ以外の方法を用いて2点間の長さを測定しても、直線距離より短くなることはない。
公式
2次元空間の2点間の直線距離は以下の通り。
いざ求める！
問題
２点A(0, 0), B(3,3)間の距離
グラフを使用して2点を表すと上記の画像の様になる。
今回はあくまでも検証のため問題は簡単にしてある。
2点間の距離MySQL GISバージョン
mysql> SELECT st_distance(st_geomfromtext('point(0 0)'),st_geomfromtext('point(3 3)')) as dist; +-------------------+ | dist | +-------------------+ | 4.242640687119285 | +-------------------+ 1 row in set (0.00 sec)
2点間の距離公式バージョン
mysql> SELECT sqrt(pow((3 - 0),2 )+ pow((3 - 0),2)) as sq from dual ; +-------------------+ | sq | +-------------------+ | 4.242640687119285 | +-------------------+ 1 row in set (0.00 sec)
解説
Point Class
A
Pointは、座標空間内の単一の場所を表すジオメトリです。
Pointプロパティ
- X座標値。
- Y座標値。
座標を表す点を定義する型。
point(0 1) → 0：X座標、1：Y座標
ST_GeometryFromText
ST_GeometryFromText(wkt[, srid[, options]])WKT表現とSRIDを使用して、任意のタイプのジオメトリ値を作成します。
これらの関数は、このセクションの概要で説明されているように引数を処理します。
WKTから空間データを生成する関数 Text -> Geometry
ST_Distanceの引数が、Text型ではなくGeometry型を求めるので変換している。
ST_Distance
g1との 間の距離を返します。これg2は、ジオメトリ引数の空間参照系（SRS）の長さの単位、またはunit指定されている場合はオプションの引数の単位で測定され ます。
本題の二点間の距離を求める関数。
座標三角形の面積
座標法（ざひょうほう）とは、平面において多角形の頂点座標によってその面積を求める数学的アルゴリズム。測量における用語の一つ。 靴紐公式、靴紐の方法、靴紐のアルゴリズム、ガウスの面積公式とも呼ばれる。
公式
三角形の面積を求める公式は下記の通り。
座標平面上の３点 (x1,y1),(x2,y2),(0,0)を頂点とする三角形の面積は、
いざ求める！
問題
A(0,0),B(3,1),C(2,3) を３つの頂点とする三角形の面積を求めよ。
座標三角形の面積MySQL GISバージョン
mysql> SET @poly = 'Polygon((0 0,3 1,2 3,0 0))'; Query OK, 0 rows affected (0.00 sec) mysql> select ST_Area(ST_GeomFromText(@poly)); +---------------------------------+ | ST_Area(ST_GeomFromText(@poly)) | +---------------------------------+ | 3.5 | +---------------------------------+ 1 row in set (0.00 sec)
座標三角形の面積公式バージョン
座標平面上の３点 (x1,y1),(x2,y2),(0,0)を頂点とする三角形の面積の求め方で行っています。
mysql> select 1/2 * abs(3 * 3 - 2 * 1); +--------------------------+ | 1/2 * abs(3 * 3 - 2 * 1) | +--------------------------+ | 3.5000 | +--------------------------+ 1 row in set (0.00 sec)
解説
ST_GeomFromTextは先ほど紹介したので省略。
Polygon
Polygonは、多辺の幾何図形を表す平面Surfaceです。これは 1 個の外側の境界と 0 個以上の内側の境界で定義されますが、それらの内側の各境界によってPolygon内の 1 個の穴が定義されます。
Polygonの例
- 地域マップで、
Polygonオブジェクトは森林や区域などを表すことができます。
使用注意
- 最初と最後で同じ点を指す必要がある
Polygon((1 2,3 1,2 3,1 2))
(1 2)が最初と最後で二回記述している。
ST_Area
空間参照系で測定された 、
PolygonまたはMultiPolygon引数の面積を示す倍精度数を返します 。
面積を求める関数。
〆
MySQL GISを今回はじめて使用してみた。
初歩的内容ではあったが、GISについて少し理解できた。
MySQL GIS 5.xと8.xでは関数の仕様がぜんぜん違うので注意が必要。
読んでいただいてどうもありがとうございます。
参考
https://www.esrij.com/getting-started/what-is-gis/
https://qiita.com/onunu/items/59ef2c050b35773ced0d
https://dev.mysql.com/doc/refman/5.6/ja/spatial-extensions.html
https://dev.mysql.com/doc/refman/5.6/ja/spatial-analysis-functions.html
https://www.slideshare.net/sakaik/mysql80gis
https://dev.mysql.com/doc/refman/5.6/ja/spatial-function-reference.html
https://dev.mysql.com/doc/refman/8.0/en/spatial-function-reference.html
