MySQLで学ぶGIS入門第30回

MySQLの「ST_GeometryType()」「ST_IsValid()」などの関数でジオメトリデータの情報を取り出してみよう

連載第30回の今回は、「ST_GeometryType()」「ST_Latitude()」「ST_IsValid()」などの関数を使ってジオメトリの型・座標・正当性・閉合判定といった情報を取り出す方法について解説します。

坂井恵 (さかいけい)

6:30

はじめに

地理情報データは、MySQLの中にバイナリで格納されています。「ST_GeomFromText()で登録して、ST_AsText()で取り出す」という一手間を、ここまで何回も繰り返してきました。

これら以外にも、MySQLにはデータの特徴を知る関数がたくさんあります。今回は地理情報データとさらに親密になれる、そういった関数群を紹介します。

今回紹介する関数

今回は、以下の関数を紹介します。

　ST_GeometryType(g)  　ジオメトリの種類
　ST_SRID(g)          　空間参照系のID
　ST_Dimension(g)     　図形の次元
　ST_Latitude(p)      　点の緯度
　ST_Longitude(p)     　点の経度
　ST_X(p)             　点のX座標
　ST_Y(p)             　点のY座標
　ST_NumPoints(g)     　図形を構成する点の数
　ST_IsValid(g)       　図形として壊れていないか
　ST_IsClosed(g)      　始点と終点が一致しているか

それぞれの関数は、POLYGONだけに使えるもの、LINESTRINGだけに使えるもの、POINTだけに使えるものなどがあり、また、平面のみを対象にした関数もあります。順にこれらの関数の動作を見ていきましょう。

実験用のデータを用意する

いろいろな種類のジオメトリを並べて試すために、実験用のテーブルを作ります。

実務では、1つの列にさまざまなジオメトリタイプを混ぜて入れることはお勧めできませんが、今回は関数の効き方を見比べる目的で、あえてSRIDを指定したものと指定しないもの(SRID 0)を組み合わせて登録します。

mysql> CREATE TABLE g30 (
    ->   id INTEGER PRIMARY KEY,
    ->   memo VARCHAR(20),
    ->   geom GEOMETRY);
Query OK, 0 rows affected (0.183 sec)

mysql> INSERT INTO g30 VALUES (1, 'point_non_srs', ST_GeomFromText('POINT(3 4)'));
mysql> INSERT INTO g30 VALUES (2, 'point_with_srs', ST_GeomFromText('POINT(35.005 135.05)',6668));
mysql> INSERT INTO g30 VALUES (3, 'line_non_srs', ST_GeomFromText('LINESTRING(1 1, 2 1, 3 2, 3 4, 2 5)'));
mysql> INSERT INTO g30 VALUES (4, 'line_with_srs', ST_GeomFromText('LINESTRING(35 135, 35.002 135.003, 35.005 135.002, 35.007 135.006, 35.01 135.01)', 6668));
mysql> INSERT INTO g30 VALUES (5, 'linering_w_srs', ST_GeomFromText('LINESTRING(35 135, 35 135.01, 35.01 135.01, 35.01 135, 35 135)', 6668));
mysql> INSERT INTO g30 VALUES (6, 'poly_non_srs', ST_GeomFromText('POLYGON((1 1, 3 2, 4 4, 2 3, 1 1))'));
mysql> INSERT INTO g30 VALUES (7, 'poly_with_srs', ST_GeomFromText('POLYGON((35.002 135.003, 35.005 135.002, 35.007 135.006, 35.01 135.01, 35.002 135.003))', 6668));
mysql> INSERT INTO g30 VALUES (8, 'poly_inval_w_srs', ST_GeomFromText('POLYGON((35 135, 35.002 135.003, 35.005 135.002, 35.007 135.006, 35.01 135.01, 35 135))', 6668));
mysql> INSERT INTO g30 VALUES (9, 'poly_donut', ST_GeomFromText('POLYGON((35 135, 35 135.01, 35.01 135.01, 35.01 135, 35 135),(35.003 135.003, 35.007 135.003, 35.007 135.007, 35.003 135.007, 35.003 135.003))', 6668));

5番は始点と終点が同じ、閉じたLINESTRINGです。8番はわざと自己交差させた「壊れた」ポリゴン、9番は真ん中に穴が空いたポリゴンです。

まず、素性を知る

いちばん基本になるのが、この3つです。

mysql> SELECT id, memo, ST_GeometryType(geom) type, ST_SRID(geom) srid, ST_Dimension(geom) dim FROM g30;
+----+------------------+------------+------+------+
| id | memo             | type       | srid | dim  |
+----+------------------+------------+------+------+
|  1 | point_non_srs    | POINT      |    0 |    0 |
|  2 | point_with_srs   | POINT      | 6668 |    0 |
|  3 | line_non_srs     | LINESTRING |    0 |    1 |
|  4 | line_with_srs    | LINESTRING | 6668 |    1 |
|  5 | linering_w_srs   | LINESTRING | 6668 |    1 |
|  6 | poly_non_srs     | POLYGON    |    0 |    2 |
|  7 | poly_with_srs    | POLYGON    | 6668 |    2 |
|  8 | poly_inval_w_srs | POLYGON    | 6668 |    2 |
|  9 | poly_donut       | POLYGON    | 6668 |    2 |
+----+------------------+------------+------+------+
9 rows in set (0.002 sec)

ST_GeometryType()とST_SRID()は、これまでも何度か登場しました。この2つはどんなジオメトリにも使えるので、素性の分からないデータに出会ったら、とりあえずこれで確認してみると良いでしょう。

ST_Dimension()は図形の「次元」を返します。POINT/LINESTRING/POLYGON がそれぞれ 0/1/2 となります。

実はこの「次元」という考え方は、第27回・第28回でも登場しました。「ST_Crosses()は第1引数の次元が2だとNULLを返す、ST_Overlaps()は2つの引数の次元が異なるとNULLを返す」という話でした。その「次元」を確認できるのがこの関数です。ST_Crosses()の実施前に確認のために利用できますね。

点の座標を取り出す

POINTから緯度と経度を取り出してみましょう。ST_Latitude()とST_Longitude()です。第14回で一度登場しています。まずはテーブル全体に実施してみましょう。

mysql> SELECT id, memo, ST_Latitude(geom) lat, ST_Longitude(geom) lon FROM g30;
ERROR 3726 (22S00): Function st_latitude is only defined for geographic spatial reference systems, but one of its arguments is in SRID 0, which is not geographic.

エラーとなりました。geographic SRS(地理座標系)だけで使えるというメッセージです。今回は SRIDが0のデータが混ざっているのでエラーとなったというわけです。意味を考えればこれは当然で、SRID 0の世界には緯度も経度もありませんね。ないものは取り出せないというわけです(NULLになっても良いとは思いますが、実装がそのようになっているということで)。

ということで、SRIDが0のものを除外して再度実施してみます。先ほど登場した ST_SRID()をさっそく WHERE句で使用しています。

mysql> SELECT id, memo, ST_Latitude(geom) lat, ST_Longitude(geom) lon FROM g30 WHERE ST_SRID(geom)<>0;
ERROR 3516 (22S01): POINT value is a geometry of unexpected type LINESTRING in st_latitude.

また叱られました。まだ問題があるようです。意訳すると「LINESTRINGが与えられるなんて、聞いてないよ～」という文句ですね。POINT型に限定して動作する関数のようです。

では、さらに条件として POINT型のものだけとなるようにして、三たび実施してみます。先ほど登場した ST_GeometryType()が活躍します。

mysql> SELECT id, memo, ST_Latitude(geom) lat, ST_Longitude(geom) lon FROM g30 WHERE ST_SRID(geom)<>0 AND ST_GeometryType(geom)='POINT';
+----+----------------+--------+--------+
| id | memo           | lat    | lon    |
+----+----------------+--------+--------+
|  2 | point_with_srs | 35.005 | 135.05 |
+----+----------------+--------+--------+
1 row in set (0.000 sec)

ようやくlatitudeとlongitudeが取れました。

一方、先ほどエラーとなってしまったような平面のデータには、汎用的なST_X()とST_Y()を使うことができます。

mysql> SELECT id, memo, ST_X(geom) x, ST_Y(geom) y
    ->   FROM g30 WHERE ST_GeometryType(geom)='POINT';
+----+----------------+--------+--------+
| id | memo           | x      | y      |
+----+----------------+--------+--------+
|  1 | point_non_srs  |      3 |      4 |
|  2 | point_with_srs | 35.005 | 135.05 |
+----+----------------+--------+--------+
2 rows in set (0.000 sec)

POINT(3 4)から、そのまま3と4が取り出せました。地理座標系データのid=2からも、latitude, longitudeの値が x,yのものとして得られています。なお、この関数も型で絞らずにLineStringやPolygonを与えて実行すると、ST_Latitude()らと同じERROR 3516となります。

図形の正当性判定と、構成する点の数

次は、 ST_NumPoints()と ST_IsValid()を見てみます。

mysql> SELECT id, memo, ST_NumPoints(geom) numpoints, ST_IsValid(geom) valid FROM g30;
+----+------------------+-----------+-------+
| id | memo             | numpoints | valid |
+----+------------------+-----------+-------+
|  1 | point_non_srs    |      NULL |     1 |
|  2 | point_with_srs   |      NULL |     1 |
|  3 | line_non_srs     |         5 |     1 |
|  4 | line_with_srs    |         5 |     1 |
|  5 | linering_w_srs   |         5 |     1 |
|  6 | poly_non_srs     |      NULL |     1 |
|  7 | poly_with_srs    |      NULL |     1 |
|  8 | poly_inval_w_srs |      NULL |     0 |
|  9 | poly_donut       |      NULL |     1 |
+----+------------------+-----------+-------+
9 rows in set (0.000 sec)

ST_IsValid(geom)で1つだけ 0(FALSE)判定となったものがあります。id=8のポリゴンですね。実はこのデータ、あえてポリゴンとしては変な順序で頂点を結んだものなのです。下図のように蝶ネクタイのような形をしています。ポリゴンはエリアを囲むものですから自己交差してはいけません。ST_IsValid()関数によって確かに正しく invalidと判定されたことが確認できました。

ST_NumPoints()は、名前から想像すると頂点の数を返す関数のように見えますから、POLYGONでも値を返してほしいところです。実行してみると実際にはLineString専用のようで、POLYGONではNULLになってしまいました。ただし、先ほどのST_Longitude()とは異なり、対応していないタイプでもエラーにはならずNULLが返されます。このあたりの挙動は関数によって異なるようですね。

次は、LineStringが閉じているかどうかを検査する関数、ST_IsClosed()です。

mysql> SELECT id, memo, ST_IsClosed(geom) closed  FROM g30 WHERE ST_GeometryType(geom)='LINESTRING';
ERROR 3618 (22S00): st_isclosed(LINESTRING) has not been implemented for geographic spatial reference systems.

また新たなエラーに遭遇しました。地理情報データの関数を検証していると、このように「これはあの型では使えない、これは平面だけである」といったエラーに何度も遭遇します。今回は「ST_IsClosed()は地理座標系に対応していない」というエラーですね。そういうことなら、まず、SRID=0のものがエラーにならないかを確認してみましょう。

mysql> SELECT id, memo, ST_IsClosed(geom) closed  FROM g30 WHERE ST_SRID(geom)=0;
+----+---------------+--------+
| id | memo          | closed |
+----+---------------+--------+
|  1 | point_non_srs |   NULL |
|  3 | line_non_srs  |      0 |
|  6 | poly_non_srs  |   NULL |
+----+---------------+--------+
3 rows in set (0.000 sec)

id=3のLineString(SRID=0のもの)が0(FALSE)と判定されました。これは行きっぱなしの1本の線、つまり閉じていない線であるためです。他の2行もSRID=0ですがLineStringではないため結果は得られません。ただし、エラーにはならずNULLが返ってくるという挙動であることも分かります。

SRIDが0であればST_IsClosed()は動作することが分かったので、最初にエラーになったデータのClose判定をする方法を考えてみましょう。この判定は実際の長さや大きさではなく図形としての性質を見るものなので、前回紹介したSRIDを0と見なす手法を使えば良さそうですね。

mysql> SELECT id, memo, ST_IsClosed(ST_SRID(geom,0)) closed  FROM g30 WHERE ST_GeometryType(geom)='LINESTRING';
+----+----------------+--------+
| id | memo           | closed |
+----+----------------+--------+
|  3 | line_non_srs   |      0 |
|  4 | line_with_srs  |      0 |
|  5 | linering_w_srs |      1 |
+----+----------------+--------+
3 rows in set (0.000 sec)

3つのLineStringのうち、id=5のものが「閉じている」と判定されました。このように、地理情報関数をよく知っていると、必要に応じて変換をしながら目的を達成できるようになります。