以下の内容はhttps://sakaik.hateblo.jp/より取得しました。

MySQL GIS拡張関数： STX_RemoveRepeatedPoints()

mysql gis spatial newfunc

自作の、Spatial(GIS)関連の関数をMySQLに追加するプラグインの関数をひとつひとつ動作確認しながら紹介するシリーズ。
https://sakaik.hateblo.jp/entry/20260215/mysql_spatial_functions_plugin

STX_RemoveRepeatedPoints(geom [, tol])

　連続する重複頂点を除去する関数。

動作紹介

  • 点が重複するLINESTRINGから重複部分を除去
mysql> SET @g=ST_GeomFromText('LINESTRING(0 0, 0 0, 1 1, 1 1, 2 2)');
mysql> SELECT ST_AsText(STX_Removerepeatedpoints(@g));
+-----------------------------------------+
| ST_AsText(STX_Removerepeatedpoints(@g)) |
+-----------------------------------------+
| LINESTRING(0 0,1 1,2 2)                 |
+-----------------------------------------+
1 row in set (0.000 sec)
  • 第2引数を与えると、その距離の範囲内にある近傍点も「重複」と見做される
mysql> SET @g=ST_GeomFromText('LINESTRING(0 0, 0.1 0, 1 0, 1.05 0, 2 0)');
mysql> SELECT ST_AsText(STX_Removerepeatedpoints(@g, 0.2));
+----------------------------------------------+
| ST_AsText(STX_Removerepeatedpoints(@g, 0.2)) |
+----------------------------------------------+
| LINESTRING(0 0,1 0,2 0)                      |
+----------------------------------------------+
1 row in set (0.000 sec)
  • 近い点が続く場合は、前から順に集約（重複除去）しながら、指定距離から離れたものを生かす動作の模様
mysql> SET @g=ST_GeomFromText('LINESTRING(0 0, 0.1 0, 0.2 0, 0.2 0.1, 2 0)');
mysql> SELECT ST_AsText(STX_Removerepeatedpoints(@g, 0.2));
+----------------------------------------------+
| ST_AsText(STX_Removerepeatedpoints(@g, 0.2)) |
+----------------------------------------------+
| LINESTRING(0 0,0.2 0.1,2 0)                  |
+----------------------------------------------+
1 row in set (0.000 sec)
  • MULTIPOINTでも重複除去してくれる（MULTIPOINTには順序がないので、間に他のものを挟んでも除去してくれる）
mysql> SET @g=ST_GeomFromText('MULTIPOINT ((1 1), (2 2), (3 3), (2 2))');
mysql> SELECT ST_AsText(STX_Removerepeatedpoints(@g));
+-----------------------------------------+
| ST_AsText(STX_Removerepeatedpoints(@g)) |
+-----------------------------------------+
| MULTIPOINT((1 1),(2 2),(3 3))           |
+-----------------------------------------+
1 row in set (0.000 sec)

開発秘話

　当初、GEOMETRY COLLECTIONに対して重複の除去が行われなかったため、修正した。

  • MULTIPOINTに対して重複除去が行われなかった例（バグ動作。現在は修正済み）
mysql> SET @g=ST_GeomFromText('MULTIPOINT ((1 1), (2 2), (3 3), (2 2))');
mysql> SELECT ST_AsText(STX_Removerepeatedpoints(@g));
+-----------------------------------------+
| ST_AsText(STX_Removerepeatedpoints(@g)) |
+-----------------------------------------+
| MULTIPOINT((1 1),(2 2),(3 3),(2 2))     |
+-----------------------------------------+
  • 修正後
mysql> SET @g=ST_GeomFromText('MULTIPOINT ((1 1), (2 2), (3 3), (2 2))');
mysql> SELECT ST_AsText(STX_Removerepeatedpoints(@g));
+-----------------------------------------+
| ST_AsText(STX_Removerepeatedpoints(@g)) |
+-----------------------------------------+
| MULTIPOINT((1 1),(2 2),(3 3))           |
+-----------------------------------------+
1 row in set (0.000 sec)

MySQL GIS拡張関数： STX_HexGrid()

mysql gis spatial newfunc

自作の、Spatial(GIS)関連の関数をMySQLに追加するプラグインの関数をひとつひとつ動作確認しながら紹介するシリーズ。
https://sakaik.hateblo.jp/entry/20260215/mysql_spatial_functions_plugin

STX_HexGrid(size, geom)

　与えられたgeomのバウンディングボックスを覆うように、与えられた一辺のサイズの六角形を生成し、マルチポリゴンとして返す。ポリゴンそのものではなく、それを覆う矩形を覆うように、というところが気持ち悪さを感じるが、PostGISの ST_HexagonGrid()でもそのような動作らしい。

動作紹介

  • 「C」の字のようなPolygonのバウンディングボックスを覆うようにHexGridのポリゴンを生成して返す
mysql> SET @g=ST_GeomFromText('POLYGON((35 135, 35.02 135.05, 35.05 135.02, 35.07 135.06, 35.09 135.02, 35.04 134.97, 35 135))',6668);
mysql> SELECT ST_AsText(STX_hexgrid(0.05, @g))\G
*************************** 1. row ***************************
ST_AsText(STX_hexgrid(0.05, @g)): GEOMETRYCOLLECTION(
POLYGON((35.03072758308055 134.97500000000002,35.07402885326977 134.95000000000002,35.07402885326977 134.9,35.03072758308055 134.875,34.98742631289132 134.9,34.98742631289132 134.95000000000002,35.03072758308055 134.97500000000002)),
POLYGON((34.98742631289132 135.05000000000004,35.03072758308055 135.02500000000003,35.03072758308055 134.97500000000002,34.98742631289132 134.95000000000002,34.9441250427021 134.97500000000002,34.9441250427021 135.02500000000003,34.98742631289132 135.05000000000004)),
POLYGON((35.074028853269766 135.05000000000004,35.11733012345899 135.02500000000003,35.11733012345899 134.97500000000002,35.074028853269766 134.95000000000002,35.03072758308054 134.97500000000002,35.03072758308054 135.02500000000003,35.074028853269766 135.05000000000004)),
POLYGON((35.03072758308055 135.12500000000003,35.07402885326977 135.10000000000002,35.07402885326977 135.05,35.03072758308055 135.025,34.98742631289132 135.05,34.98742631289132 135.10000000000002,35.03072758308055 135.12500000000003)),
POLYGON((35.11733012345899 135.12500000000003,35.16063139364822 135.10000000000002,35.16063139364822 135.05,35.11733012345899 135.025,35.074028853269766 135.05,35.074028853269766 135.10000000000002,35.11733012345899 135.12500000000003)))

  • 生成するHexagonの1辺サイズを小さくすると（先ほどの5分の1）たくさんのPOLYGONに分割される。
mysql> SELECT ST_AsText(STX_hexgrid(0.01, @g))\G
*************************** 1. row ***************************
ST_AsText(STX_hexgrid(0.01, @g)): GEOMETRYCOLLECTION(POLYGON((35.00474682096701 134.98,35.01340707500485 134.975,35.01340707500485 134.965,35.00474682096701 134.96,34.996086566929165 134.965,34.996086566929165 134.975,35.00474682096701 134.98)),POLYGON((35.0220673290427 134.98,35.03072758308054 134.975,35.03072758308054 134.965,35.0220673290427 134.96,35.01340707500486 134.965,35.01340707500486 134.975,35.0220673290427 134.98)),POLYGON((35.03938783711839 134.98,35.04804809115623 134.975,35.04804809115623 134.965,35.03938783711839 134.96,35.03072758308055 134.965,35.03072758308055 134.975,35.03938783711839 134.98)),POLYGON((35.056708345194075 134.98,35.06536859923192 134.975,35.06536859923192 134.965,35.056708345194075 134.96,35.04804809115623 134.965,35.04804809115623 134.975,35.056708345194075 134.98)),POLYGON((35.074028853269766 134.98,35.08268910730761 134.975,35.08268910730761 134.965,35.074028853269766 134.96,35.065368599231924 134.965,35.065368599231924 134.975,35.074028853269766 134.98)),POLYGON((35.09134936134546 134.98,35.1000096153833 134.975,35.1000096153833 134.965,35.09134936134546 134.96,35.082689107307615 134.965,35.082689107307615 134.975,35.09134936134546 134.98)),POLYGON((34.996086566929165 134.99499999999998,35.00474682096701 134.98999999999998,35.00474682096701 134.98,34.996086566929165 134.975,34.98742631289132 134.98,34.98742631289132 134.98999999999998,34.996086566929165 134.99499999999998)),POLYGON((35.01340707500485 134.99499999999998,35.02206732904269 134.98999999999998,35.02206732904269 134.98,35.01340707500485 134.975,35.00474682096701 134.98,35.00474682096701 134.98999999999998,35.01340707500485 134.99499999999998)),POLYGON((35.03072758308054 134.99499999999998,35.03938783711838 134.98999999999998,35.03938783711838 134.98,35.03072758308054 134.975,35.0220673290427 134.98,35.0220673290427 134.98999999999998,35.03072758308054 134.99499999999998)),POLYGON((35.04804809115623 134.99499999999998,35.056708345194075 134.98999999999998,35.056708345194075 134.98,35.04804809115623 134.975,35.03938783711839 134.98,35.03938783711839 134.98999999999998,35.04804809115623 134.99499999999998)),POLYGON((35.06536859923192 134.99499999999998,35.07402885326976 134.98999999999998,35.07402885326976 134.98,35.06536859923192 134.975,35.056708345194075 134.98,35.056708345194075 134.98999999999998,35.06536859923192 134.99499999999998)),POLYGON((35.08268910730761 134.99499999999998,35.09134936134545 134.98999999999998,35.09134936134545 134.98,35.08268910730761 134.975,35.074028853269766 134.98,35.074028853269766 134.98999999999998,35.08268910730761 134.99499999999998)),POLYGON((35.00474682096701 135.01,35.01340707500485 135.005,35.01340707500485 134.995,35.00474682096701 134.99,34.996086566929165 134.995,34.996086566929165 135.005,35.00474682096701 135.01)),POLYGON((35.0220673290427 135.01,35.03072758308054 135.005,35.03072758308054 134.995,35.0220673290427 134.99,35.01340707500486 134.995,35.01340707500486 135.005,35.0220673290427 135.01)),POLYGON((35.03938783711839 135.01,35.04804809115623 135.005,35.04804809115623 134.995,35.03938783711839 134.99,35.03072758308055 134.995,35.03072758308055 135.005,35.03938783711839 135.01)),POLYGON((35.056708345194075 135.01,35.06536859923192 135.005,35.06536859923192 134.995,35.056708345194075 134.99,35.04804809115623 134.995,35.04804809115623 135.005,35.056708345194075 135.01)),POLYGON((35.074028853269766 135.01,35.08268910730761 135.005,35.08268910730761 134.995,35.074028853269766 134.99,35.065368599231924 134.995,35.065368599231924 135.005,35.074028853269766 135.01)),POLYGON((35.09134936134546 135.01,35.1000096153833 135.005,35.1000096153833 134.995,35.09134936134546 134.99,35.082689107307615 134.995,35.082689107307615 135.005,35.09134936134546 135.01)),POLYGON((34.996086566929165 135.02499999999998,35.00474682096701 135.01999999999998,35.00474682096701 135.01,34.996086566929165 135.005,34.98742631289132 135.01,34.98742631289132 135.01999999999998,34.996086566929165 135.02499999999998)),POLYGON((35.01340707500485 135.02499999999998,35.02206732904269 135.01999999999998,35.02206732904269 135.01,35.01340707500485 135.005,35.00474682096701 135.01,35.00474682096701 135.01999999999998,35.01340707500485 135.02499999999998)),POLYGON((35.03072758308054 135.02499999999998,35.03938783711838 135.01999999999998,35.03938783711838 135.01,35.03072758308054 135.005,35.0220673290427 135.01,35.0220673290427 135.01999999999998,35.03072758308054 135.02499999999998)),POLYGON((35.04804809115623 135.02499999999998,35.056708345194075 135.01999999999998,35.056708345194075 135.01,35.04804809115623 135.005,35.03938783711839 135.01,35.03938783711839 135.01999999999998,35.04804809115623 135.02499999999998)),POLYGON((35.06536859923192 135.02499999999998,35.07402885326976 135.01999999999998,35.07402885326976 135.01,35.06536859923192 135.005,35.056708345194075 135.01,35.056708345194075 135.01999999999998,35.06536859923192 135.02499999999998)),POLYGON((35.08268910730761 135.02499999999998,35.09134936134545 135.01999999999998,35.09134936134545 135.01,35.08268910730761 135.005,35.074028853269766 135.01,35.074028853269766 135.01999999999998,35.08268910730761 135.02499999999998)),POLYGON((35.00474682096701 135.04,35.01340707500485 135.035,35.01340707500485 135.025,35.00474682096701 135.02,34.996086566929165 135.025,34.996086566929165 135.035,35.00474682096701 135.04)),POLYGON((35.0220673290427 135.04,35.03072758308054 135.035,35.03072758308054 135.025,35.0220673290427 135.02,35.01340707500486 135.025,35.01340707500486 135.035,35.0220673290427 135.04)),POLYGON((35.03938783711839 135.04,35.04804809115623 135.035,35.04804809115623 135.025,35.03938783711839 135.02,35.03072758308055 135.025,35.03072758308055 135.035,35.03938783711839 135.04)),POLYGON((35.056708345194075 135.04,35.06536859923192 135.035,35.06536859923192 135.025,35.056708345194075 135.02,35.04804809115623 135.025,35.04804809115623 135.035,35.056708345194075 135.04)),POLYGON((35.074028853269766 135.04,35.08268910730761 135.035,35.08268910730761 135.025,35.074028853269766 135.02,35.065368599231924 135.025,35.065368599231924 135.035,35.074028853269766 135.04)),POLYGON((35.09134936134546 135.04,35.1000096153833 135.035,35.1000096153833 135.025,35.09134936134546 135.02,35.082689107307615 135.025,35.082689107307615 135.035,35.09134936134546 135.04)),POLYGON((34.996086566929165 135.05499999999998,35.00474682096701 135.04999999999998,35.00474682096701 135.04,34.996086566929165 135.035,34.98742631289132 135.04,34.98742631289132 135.04999999999998,34.996086566929165 135.05499999999998)),POLYGON((35.01340707500485 135.05499999999998,35.02206732904269 135.04999999999998,35.02206732904269 135.04,35.01340707500485 135.035,35.00474682096701 135.04,35.00474682096701 135.04999999999998,35.01340707500485 135.05499999999998)),POLYGON((35.03072758308054 135.05499999999998,35.03938783711838 135.04999999999998,35.03938783711838 135.04,35.03072758308054 135.035,35.0220673290427 135.04,35.0220673290427 135.04999999999998,35.03072758308054 135.05499999999998)),POLYGON((35.04804809115623 135.05499999999998,35.056708345194075 135.04999999999998,35.056708345194075 135.04,35.04804809115623 135.035,35.03938783711839 135.04,35.03938783711839 135.04999999999998,35.04804809115623 135.05499999999998)),POLYGON((35.06536859923192 135.05499999999998,35.07402885326976 135.04999999999998,35.07402885326976 135.04,35.06536859923192 135.035,35.056708345194075 135.04,35.056708345194075 135.04999999999998,35.06536859923192 135.05499999999998)),POLYGON((35.08268910730761 135.05499999999998,35.09134936134545 135.04999999999998,35.09134936134545 135.04,35.08268910730761 135.035,35.074028853269766 135.04,35.074028853269766 135.04999999999998,35.08268910730761 135.05499999999998)),POLYGON((35.00474682096701 135.07,35.01340707500485 135.065,35.01340707500485 135.055,35.00474682096701 135.05,34.996086566929165 135.055,34.996086566929165 135.065,35.00474682096701 135.07)),POLYGON((35.0220673290427 135.07,35.03072758308054 135.065,35.03072758308054 135.055,35.0220673290427 135.05,35.01340707500486 135.055,35.01340707500486 135.065,35.0220673290427 135.07)),POLYGON((35.03938783711839 135.07,35.04804809115623 135.065,35.04804809115623 135.055,35.03938783711839 135.05,35.03072758308055 135.055,35.03072758308055 135.065,35.03938783711839 135.07)),POLYGON((35.056708345194075 135.07,35.06536859923192 135.065,35.06536859923192 135.055,35.056708345194075 135.05,35.04804809115623 135.055,35.04804809115623 135.065,35.056708345194075 135.07)),POLYGON((35.074028853269766 135.07,35.08268910730761 135.065,35.08268910730761 135.055,35.074028853269766 135.05,35.065368599231924 135.055,35.065368599231924 135.065,35.074028853269766 135.07)),POLYGON((35.09134936134546 135.07,35.1000096153833 135.065,35.1000096153833 135.055,35.09134936134546 135.05,35.082689107307615 135.055,35.082689107307615 135.065,35.09134936134546 135.07)))
1 row in set (0.001 sec)

MySQL GIS拡張関数： STX_SquareGrid()

mysql gis spatial newfunc

自作の、Spatial(GIS)関連の関数をMySQLに追加するプラグインの関数をひとつひとつ動作確認しながら紹介するシリーズ。
https://sakaik.hateblo.jp/entry/20260215/mysql_spatial_functions_plugin

STX_SquareGrid(size, geom)

　指定されたgeomを覆うような矩形グリッドを MULTIPOLYGONで返す。矩形は、第1引数で与えられた長さが一辺となるように組み立てられる。原点(0,0)を基準としたグリッドになるように位置調整される。

動作紹介

  • 一辺10のPOLYGON全体を覆うような、一辺4の矩形ポリゴン群を返す（0～12の範囲を 3x3に区切ることになる）
mysql> SET @g=ST_GeomFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))');
mysql> SELECT ST_AsText(STX_SquareGrid(4, @g))\G
*************************** 1. row ***************************
ST_AsText(STX_SquareGrid(4, @g)): 
 GEOMETRYCOLLECTION(
  POLYGON((0 0,4 0,4 4,0 4,0 0)),
  POLYGON((4 0,8 0,8 4,4 4,4 0)),
  POLYGON((8 0,12 0,12 4,8 4,8 0)),
  POLYGON((0 4,4 4,4 8,0 8,0 4)),
  POLYGON((4 4,8 4,8 8,4 8,4 4)),
  POLYGON((8 4,12 4,12 8,8 8,8 4)),
  POLYGON((0 8,4 8,4 12,0 12,0 8)),
  POLYGON((4 8,8 8,8 12,4 12,4 8)),
  POLYGON((8 8,12 8,12 12,8 12,8 8)))
1 row in set (0.000 sec)


↑もとの四角形全体を覆うような、一辺4の長さの矩形群が返されている。左下が(0,0)なので左辺と下辺は一致している。


  • いつもこのブログで使っている35度135度付近の変な形のPOLYGONを覆うように一辺「3」の長さの矩形で覆う指示。
mysql> SET @g=ST_GeomFromText('POLYGON((35 135, 35.02 135.05, 35.05 135.02, 35.07 135.06, 35.09 135.02, 35.04 134.97, 35 135))',6668);
mysql> SELECT ST_AsText(STX_SquareGrid(3, @g));
+-----------------------------------------------------------------------------------------------------------------+
| ST_AsText(STX_Squaregrid(3, @g))                                                                                |
+-----------------------------------------------------------------------------------------------------------------+
| GEOMETRYCOLLECTION(POLYGON((33 132,33 135,36 135,36 132,33 132)),POLYGON((33 135,33 138,36 138,36 135,33 135))) |
+-----------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)


↑青枠は、(35 135)から(36 136)への1度の矩形（参考情報）。ちょうど一辺がその3倍の長さの矩形が2つ返されている事が分かります。



  • 一辺を短く（0.05）するよう指示したもの。元のPOLYGONを覆うように6つの矩形が返される
mysql> SELECT ST_AsText(STX_SquareGrid(0.05, @g))\G
*************************** 1. row ***************************
ST_AsText(STX_Squaregrid(0.05, @g)): GEOMETRYCOLLECTION(
POLYGON((35 134.95000000000002,35 135.00000000000003,35.05 135.00000000000003,35.05 134.95000000000002,35 134.95000000000002)),
POLYGON((35 135.00000000000003,35 135.05000000000004,35.05 135.05000000000004,35.05 135.00000000000003,35 135.00000000000003)),
POLYGON((35 135.05000000000004,35 135.10000000000005,35.05 135.10000000000005,35.05 135.05000000000004,35 135.05000000000004)),
POLYGON((35.05 134.95000000000002,35.05 135.00000000000003,35.099999999999994 135.00000000000003,35.099999999999994 134.95000000000002,35.05 134.95000000000002)),
POLYGON((35.05 135.00000000000003,35.05 135.05000000000004,35.099999999999994 135.05000000000004,35.099999999999994 135.00000000000003,35.05 135.00000000000003)),
POLYGON((35.05 135.05000000000004,35.05 135.10000000000005,35.099999999999994 135.10000000000005,35.099999999999994 135.05000000000004,35.05 135.05000000000004)))
1 row in set (0.000 sec)


(青線は先ほどと同様、一辺が1度の矩形(の一部)）

MySQL GIS拡張関数： STX_MinimumBoundingCircle()

mysql gis spatial newfunc

自作の、Spatial(GIS)関連の関数をMySQLに追加するプラグインの関数をひとつひとつ動作確認しながら紹介するシリーズ。
https://sakaik.hateblo.jp/entry/20260215/mysql_spatial_functions_plugin

STX_MinimumBoundingCircle(geom [, segs])

　最小外接円を求めて、Polygon として返す関数。

動作紹介

  • 正方形の最小外接円。大量のPOINTにより擬似的に円を表現していることがわかる
mysql> SET @g=ST_GeomFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))');

mysql> SELECT ST_AsText(STX_MinimumBoundingCircle(@g))\G
*************************** 1. row ***************************
ST_AsText(STX_MinimumBoundingCircle(@g)): POLYGON((12.071067811865476 5,12.067281873603536 5.231358853374822,12.05592811289523 5.462469961894542,12.037018687631914 5.693085845995458,12.010573846499778 5.9229595564125725,11.976621907297087 6.151844938619066,11.935199226610738 6.3794968964147145,11.886350160884735 6.605671654381054,11.830127018922195 6.830127018922193,11.766590005871763 7.052622637611787,11.695807158758448 7.272920256568435,11.617854273627852 7.490783975583906,11.532814824381884 7.705980500730985,11.440779873392795 7.918279394180461,11.341847973991337 8.12745332095972,11.236125064933358 8.333278292388732,11.123724356957947 8.535533905932738,11.004766211558534 8.734003581214802,10.879378012096794 8.92847479193551,10.747694027397339 9.118739293451451,10.60985526796931 9.304593345768794,10.466009335008788 9.485837931713181,10.316310262343734 9.662278970042301,10.160918351490709 9.833727523272948,10 10,9.833727523272948 10.160918351490709,9.662278970042301 10.316310262343734,9.485837931713181 10.466009335008788,9.304593345768794 10.60985526796931,9.118739293451451 10.747694027397339,8.928474791935512 10.879378012096794,8.734003581214804 11.004766211558534,8.535533905932738 11.123724356957945,8.333278292388732 11.236125064933358,8.12745332095972 11.341847973991337,7.918279394180461 11.440779873392795,7.705980500730986 11.532814824381884,7.490783975583906 11.617854273627852,7.272920256568436 11.695807158758448,7.052622637611788 11.766590005871763,6.830127018922193 11.830127018922195,6.605671654381056 11.886350160884735,6.379496896414715 11.935199226610738,6.151844938619066 11.976621907297087,5.922959556412573 12.010573846499778,5.693085845995457 12.037018687631914,5.462469961894544 12.05592811289523,5.231358853374822 12.067281873603536,5 12.071067811865476,4.768641146625179 12.067281873603536,4.537530038105457 12.05592811289523,4.306914154004544 12.037018687631914,4.0770404435874275 12.010573846499778,3.8481550613809348 11.976621907297087,3.6205031035852855 11.935199226610738,3.394328345618945 11.886350160884735,3.1698729810778077 11.830127018922195,2.9473773623882145 11.766590005871764,2.7270797434315646 11.695807158758448,2.509216024416095 11.617854273627852,2.294019499269017 11.532814824381884,2.0817206058195397 11.440779873392797,1.8725466790402807 11.341847973991337,1.6667217076112673 11.236125064933358,1.464466094067264 11.123724356957947,1.2659964187851975 11.004766211558534,1.0715252080644886 10.879378012096794,0.8812607065485496 10.747694027397339,0.6954066542312054 10.60985526796931,0.5141620682868195 10.466009335008788,0.3377210299576978 10.316310262343734,0.1662724767270518 10.160918351490709,0 10,-0.16091835149070732 9.83372752327295,-0.31631026234373216 9.662278970042303,-0.4660093350087875 9.485837931713181,-0.6098552679693094 9.304593345768797,-0.7476940273973387 9.118739293451451,-0.879378012096792 8.928474791935514,-1.0047662115585334 8.734003581214804,-1.123724356957946 8.535533905932738,-1.236125064933356 8.333278292388734,-1.3418479739913352 8.127453320959724,-1.4407798733927972 7.918279394180461,-1.5328148243818829 7.705980500730986,-1.6178542736278532 7.490783975583906,-1.6958071587584476 7.272920256568437,-1.7665900058717625 7.05262263761179,-1.8301270189221928 6.830127018922195,-1.8863501608847342 6.605671654381055,-1.9351992266107372 6.3794968964147145,-1.976621907297087 6.151844938619067,-2.0105738464997787 5.922959556412575,-2.037018687631912 5.6930858459954585,-2.0559281128952316 5.462469961894543,-2.0672818736035357 5.231358853374821,-2.0710678118654755 5.000000000000001,-2.0672818736035357 4.768641146625181,-2.0559281128952316 4.537530038105459,-2.037018687631913 4.306914154004542,-2.0105738464997787 4.077040443587427,-1.976621907297087 3.848155061380935,-1.935199226610738 3.6205031035852873,-1.8863501608847342 3.3943283456189466,-1.8301270189221936 3.1698729810778064,-1.7665900058717634 2.947377362388212,-1.6958071587584485 2.727079743431565,-1.617854273627854 2.5092160244160966,-1.5328148243818838 2.2940194992690155,-1.4407798733927972 2.0817206058195414,-1.341847973991336 1.872546679040278,-1.2361250649333568 1.6667217076112673,-1.123724356957947 1.464466094067264,-1.0047662115585343 1.2659964187851984,-0.8793780120967956 1.0715252080644908,-0.7476940273973378 0.8812607065485469,-0.6098552679693103 0.6954066542312054,-0.4660093350087884 0.5141620682868204,-0.31631026234373394 0.3377210299576978,-0.16091835149071088 0.16627247672705447,-2.6645352591003757e-15 2.6645352591003757e-15,0.1662724767270527 -0.1609183514907091,0.33772102995769604 -0.31631026234373216,0.5141620682868151 -0.4660093350087857,0.6954066542312036 -0.6098552679693086,0.8812607065485478 -0.7476940273973387,1.0715252080644895 -0.8793780120967938,1.2659964187851993 -1.0047662115585352,1.4644660940672591 -1.1237243569579434,1.666721707611266 -1.236125064933356,1.872546679040279 -1.341847973991336,2.081720605819539 -1.4407798733927972,2.294019499269017 -1.5328148243818838,2.5092160244160917 -1.6178542736278523,2.7270797434315632 -1.6958071587584476,2.9473773623882127 -1.7665900058717634,3.1698729810778077 -1.8301270189221936,3.3943283456189475 -1.886350160884735,3.620503103585282 -1.9351992266107363,3.848155061380933 -1.976621907297087,4.0770404435874275 -2.0105738464997787,4.306914154004538 -2.037018687631912,4.5375300381054595 -2.0559281128952316,4.768641146625176 -2.0672818736035357,4.999999999999999 -2.0710678118654755,5.231358853374822 -2.0672818736035357,5.462469961894538 -2.0559281128952325,5.69308584599546 -2.037018687631912,5.92295955641257 -2.0105738464997795,6.151844938619064 -1.976621907297087,6.379496896414715 -1.9351992266107372,6.60567165438105 -1.886350160884736,6.83012701892219 -1.8301270189221945,7.052622637611785 -1.7665900058717643,7.272920256568435 -1.6958071587584485,7.490783975583906 -1.6178542736278523,7.7059805007309805 -1.5328148243818847,7.918279394180458 -1.4407798733927972,8.127453320959718 -1.3418479739913378,8.333278292388732 -1.2361250649333568,8.535533905932738 -1.1237243569579451,8.734003581214798 -1.004766211558537,8.928474791935509 -0.8793780120967956,9.11873929345145 -0.7476940273973396,9.30459334576879 -0.6098552679693139,9.485837931713178 -0.46600933500879105,9.662278970042305 -0.3163102623437313,9.833727523272952 -0.16091835149070732,10 -8.881784197001252e-16,10.160918351490709 0.1662724767270518,10.316310262343734 0.3377210299576987,10.466009335008785 0.5141620682868151,10.609855267969309 0.6954066542312036,10.747694027397339 0.8812607065485478,10.87937801209679 1.0715252080644837,11.004766211558533 1.265996418785194,11.123724356957943 1.4644660940672591,11.236125064933358 1.6667217076112708,11.341847973991335 1.8725466790402785,11.440779873392795 2.081720605819539,11.532814824381884 2.2940194992690164,11.617854273627852 2.5092160244160913,11.695807158758448 2.727079743431563,11.766590005871763 2.9473773623882122,11.830127018922191 3.169872981077801,11.886350160884735 3.3943283456189413,11.935199226610736 3.620503103585282,11.976621907297087 3.8481550613809388,12.010573846499778 4.077040443587427,12.037018687631914 4.306914154004543,12.05592811289523 4.5375300381054595,12.067281873603536 4.768641146625175,12.071067811865476 4.999999999999998))
1 row in set (0.000 sec)


  • 第2引数を与えると経由するポイント数を減らし、円はカクカクとなる（ここでは4分円あたり3点を指定）。外接円として正しく外接しているが、間を端折ったせいで、「外接多角形」にはなっていない（四角がはみ出している）
mysql> SELECT ST_AsText(STX_MinimumBoundingCircle(@g, 3))\G
*************************** 1. row ***************************
ST_AsText(STX_MinimumBoundingCircle(@g, 3)): POLYGON((12.071067811865476 5,11.123724356957947 8.535533905932738,8.535533905932738 11.123724356957945,5 12.071067811865476,1.464466094067264 11.123724356957947,-1.123724356957946 8.535533905932738,-2.0710678118654755 5.000000000000001,-1.123724356957947 1.464466094067264,1.4644660940672591 -1.1237243569579434,4.999999999999999 -2.0710678118654755,8.535533905932738 -1.1237243569579451,11.123724356957943 1.4644660940672591,12.071067811865476 4.999999999999998))
1 row in set (0.000 sec)

  • 地理座標形でも大丈夫
mysql> SET @g=ST_GeomFromText('POLYGON((35 135, 35.02 135.05, 35.05 135.02, 35.07 135.06, 35.09 135.02, 35.04 134.97, 35 135))',6668);

mysql> SELECT ST_AsText(STX_MinimumBoundingCircle(@g))\G
*************************** 1. row ***************************
ST_AsText(STX_MinimumBoundingCircle(@g)): POLYGON((35.045000000004805 135.06692459913182,35.046589861654795 135.06689858275956,35.04817802084111 135.06682056150183,35.0497627769231 135.06669061890565,35.051342432904285 135.0665088941169,35.052915297249456 135.06627558173102,35.05447968569609 135.0659909315847,35.05603392305783 135.0656552484885,35.057576345018404 135.06526889190025,35.05910529991373 135.0648322755403,35.06061915050062 135.06434586694837,35.06211627570994 135.06381018698295,35.06359507238252 135.06322580926357,35.065053956985835 135.06259335955653,35.06649136730972 135.06191351510483,35.067905764139205 135.06118700390294,35.06929563290272 135.06041460391722,35.07065948529399 135.05959714225295,35.07199586086571 135.05873549426855,35.073303328593454 135.05783058263825,35.07458048840802 135.05688337636408,35.07582597269469 135.05589488973828,35.0770384477577 135.0548661812571,35.07821661524838 135.05379835248726,35.07935921355547 135.05269254688665,35.080465019156094 135.05154994857958,35.0815328479259 135.05037178108887,35.08256155640711 135.04915930602587,35.08355004303291 135.0479138217392,35.084497249307056 135.04663666192462,35.085402160937356 135.04532919419688,35.086263808921764 135.04399281862518,35.08708127058603 135.0426289662339,35.08785367057175 135.0412390974704,35.08858018177365 135.0398247006409,35.089260026225354 135.03838729031702,35.08989247593239 135.0369284057137,35.090476853651765 135.0354496090411,35.09101253361718 135.0339524838318,35.09149894220912 135.0324386332449,35.09193555856907 135.0309096783496,35.09232191515731 135.029367256389,35.09265759825352 135.02781301902726,35.09294224839982 135.02624863058065,35.09317556078573 135.02467576623548,35.093357285574484 135.0230961102543,35.09348722817063 135.02151135417228,35.09356524942839 135.01992319498598,35.09359126580063 135.018333333336,35.09356524942839 135.016743471686,35.09348722817063 135.0151553124997,35.093357285574484 135.01357055641768,35.09317556078573 135.0119909004365,35.09294224839982 135.01041803609132,35.09265759825352 135.0088536476447,35.09232191515731 135.00729941028297,35.09193555856907 135.0057569883224,35.09149894220912 135.00422803342707,35.09101253361718 135.00271418284018,35.090476853651765 135.00121705763087,35.08989247593239 134.99973826095828,35.089260026225354 134.99827937635496,35.08858018177365 134.99684196603107,35.08785367057175 134.99542756920158,35.08708127058603 134.99403770043807,35.086263808921764 134.9926738480468,35.085402160937356 134.9913374724751,35.084497249307056 134.99003000474735,35.08355004303291 134.98875284493278,35.08256155640711 134.9875073606461,35.0815328479259 134.9862948855831,35.080465019156094 134.9851167180924,35.07935921355547 134.98397411978533,35.07821661524838 134.9828683141847,35.0770384477577 134.98180048541488,35.07582597269469 134.9807717769337,35.07458048840802 134.9797832903079,35.073303328593454 134.97883608403373,35.07199586086571 134.97793117240343,35.07065948529399 134.97706952441902,35.06929563290272 134.97625206275475,35.067905764139205 134.97547966276903,35.06649136730972 134.97475315156714,35.065053956985835 134.97407330711545,35.06359507238252 134.9734408574084,35.06211627570994 134.97285647968903,35.06061915050062 134.9723207997236,35.05910529991373 134.97183439113167,35.057576345018404 134.97139777477173,35.05603392305783 134.97101141818348,35.05447968569609 134.97067573508727,35.052915297249456 134.97039108494096,35.051342432904285 134.97015777255507,35.0497627769231 134.96997604776632,35.04817802084111 134.96984610517015,35.046589861654795 134.9697680839124,35.045000000004805 134.96974206754015,35.043410138354815 134.9697680839124,35.0418219791685 134.96984610517015,35.04023722308651 134.96997604776632,35.038657567105325 134.97015777255507,35.037084702760154 134.97039108494096,35.03552031431352 134.97067573508727,35.03396607695178 134.97101141818348,35.032423654991206 134.97139777477173,35.03089470009588 134.97183439113167,35.02938084950899 134.9723207997236,35.02788372429967 134.97285647968903,35.02640492762709 134.9734408574084,35.024946043023775 134.97407330711545,35.02350863269989 134.97475315156714,35.022094235870405 134.97547966276903,35.02070436710689 134.97625206275475,35.01934051471562 134.97706952441902,35.0180041391439 134.97793117240343,35.016696671416156 134.97883608403373,35.01541951160159 134.9797832903079,35.01417402731492 134.9807717769337,35.01296155225191 134.98180048541488,35.01178338476123 134.9828683141847,35.01064078645414 134.98397411978533,35.009534980853516 134.9851167180924,35.00846715208371 134.9862948855831,35.0074384436025 134.9875073606461,35.0064499569767 134.98875284493278,35.005502750702554 134.99003000474735,35.004597839072254 134.9913374724751,35.003736191087846 134.9926738480468,35.00291872942358 134.99403770043807,35.00214632943786 134.99542756920158,35.00141981823596 134.99684196603107,35.000739973784256 134.99827937635496,35.00010752407722 134.99973826095828,34.999523146357845 135.00121705763087,34.99898746639243 135.00271418284018,34.99850105780049 135.00422803342707,34.99806444144054 135.0057569883224,34.9976780848523 135.00729941028297,34.99734240175609 135.0088536476447,34.99705775160979 135.01041803609132,34.99682443922388 135.0119909004365,34.996642714435126 135.01357055641768,34.99651277183898 135.0151553124997,34.99643475058122 135.016743471686,34.99640873420898 135.018333333336,34.99643475058122 135.01992319498598,34.99651277183898 135.02151135417228,34.996642714435126 135.0230961102543,34.99682443922388 135.02467576623548,34.99705775160979 135.02624863058065,34.99734240175609 135.02781301902726,34.9976780848523 135.029367256389,34.99806444144054 135.0309096783496,34.99850105780049 135.0324386332449,34.99898746639243 135.0339524838318,34.999523146357845 135.0354496090411,35.00010752407722 135.0369284057137,35.000739973784256 135.03838729031702,35.00141981823596 135.0398247006409,35.00214632943786 135.0412390974704,35.00291872942358 135.0426289662339,35.003736191087846 135.04399281862518,35.004597839072254 135.04532919419688,35.005502750702554 135.04663666192462,35.0064499569767 135.0479138217392,35.0074384436025 135.04915930602587,35.00846715208371 135.05037178108887,35.009534980853516 135.05154994857958,35.01064078645414 135.05269254688665,35.01178338476123 135.05379835248726,35.01296155225191 135.0548661812571,35.01417402731492 135.05589488973828,35.01541951160159 135.05688337636408,35.016696671416156 135.05783058263825,35.0180041391439 135.05873549426855,35.01934051471562 135.05959714225295,35.02070436710689 135.06041460391722,35.022094235870405 135.06118700390294,35.02350863269989 135.06191351510483,35.024946043023775 135.06259335955653,35.02640492762709 135.06322580926357,35.02788372429967 135.06381018698295,35.02938084950899 135.06434586694837,35.03089470009588 135.0648322755403,35.032423654991206 135.06526889190025,35.03396607695178 135.0656552484885,35.03552031431352 135.0659909315847,35.037084702760154 135.06627558173102,35.038657567105325 135.0665088941169,35.04023722308651 135.06669061890565,35.0418219791685 135.06682056150183,35.043410138354815 135.06689858275956,35.045000000004805 135.06692459913182))
1 row in set (0.000 sec)


最小外接円ってどういうときに使うんでしょうね。。
エンベロープなら、まず絞り込みに使うとか分からなくもないのですが、、

MySQL GIS拡張関数： STX_GeneratePoints()

mysql gis spatial newfunc

自作の、Spatial(GIS)関連の関数をMySQLに追加するプラグインの関数をひとつひとつ動作確認しながら紹介するシリーズ。
https://sakaik.hateblo.jp/entry/20260215/mysql_spatial_functions_plugin

STX_GeneratePoints(geom, n [, seed])

　与えられたPOLYGON内に、与えられた個数（ｎ個）のPOINTを生成し、MULTIPOINT型として返す。

動作紹介

mysql> SET @g=ST_GeomFromText('POLYGON((1 1, 5 1, 5 5, 1 5, 1 1))');
mysql> SELECT ST_AsText(STX_GeneratePoints(@g, 10));
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ST_AsText(STX_GeneratePoints(@g, 10))                                                                                                                                                                                                                                                                                                                                                                            |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| MULTIPOINT((1.536606567498958 1.2769839499277182),(2.9712981431310195 4.786585611511352),(1.635724950072134 3.303402818122083),(1.647560028699381 3.1670223162108635),(3.548995975383622 3.1972633238527464),(3.1700494218025876 4.249436174834232),(4.3513643190347455 1.8149834439729293),(2.8421959412429034 4.61838211876393),(1.135523140096096 3.2409218526511614),(4.159315107528018 3.6496019342026154)) |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)


.

  • 座標系を指定してももちろん動作します。特定の市区町村内に指定数のMULTIPOINTデータを作成するのに活用できます。
mysql> SET @g=ST_GeomFromText('POLYGON((35 135, 35.02 135.05, 35.05 135.02, 35.07 135.06, 35.09 135.02, 35.04 134.97, 35 135))',6668);
mysql> SELECT ST_AsText(STX_GeneratePoints(@g, 50))\G
*************************** 1. row ***************************
ST_AsText(STX_GeneratePoints(@g, 50)): MULTIPOINT((35.006152851062964 135.00717744328676),(35.072968451763 135.0501764723662),(35.00442715298948 135.0029433619069),(35.056265002698034 134.98998294940077),(35.02558734704913 134.99549716491015),(35.08771259969821 135.01885939650302),(35.06614680083442 135.02226288026645),(35.00970919317443 135.00864693097148),(35.03337578604534 135.0022947474918),(35.07775948597897 135.01570801305607),(35.06447959225296 135.01390458054004),(35.0148465346705 135.01988572085844),(35.06253447794682 135.02511125071783),(35.07058733259466 135.03908700972758),(35.07143792927397 135.025295268453),(35.00512300146973 135.01163184991898),(35.04440628729868 135.0052381892263),(35.04875139614526 135.01354507210482),(35.01537506689707 135.01245545063193),(35.00778291809191 135.01778460433673),(35.04887307059056 135.00097739776598),(35.03139484931312 135.0048595887275),(35.085654817689395 135.0224247966779),(35.07971153214747 135.01031898015788),(35.04497060665884 134.98241920366243),(35.04816637513451 134.9943739469077),(35.013008546947404 135.0308051188784),(35.05217835445516 135.02032836077467),(35.08429665804306 135.0248063592764),(35.05523572822296 135.023948447541),(35.05967199557589 135.02346416070327),(35.07491438588816 135.0052336493401),(35.04674975325348 135.00304808505),(35.033788809074586 135.00123645210877),(35.02806573412757 135.01055526568413),(35.02145647137828 135.01309460267552),(35.06492575294715 135.00216455164778),(35.01741594321714 134.99937350620567),(35.02514950733561 134.99551085659934),(35.08587802791717 135.02089499001335),(35.007595024498684 135.00658125165162),(35.04264826792682 135.00109012746927),(35.05472063381698 135.01877751545834),(35.010055472165305 135.0054596142378),(35.06247926225493 135.02263149515034),(35.02793349293655 135.0287240701578),(35.01928581026693 135.03306992390213),(35.07738690361052 135.04394931731525),(35.06283978996927 135.04199801391061),(35.029951139348086 135.00014829152647))
1 row in set (0.000 sec)


.

応用例

　STX_GeneratePoints()は MULTIPOINTを返しますが、このままでは使いにくいケースもあるでしょう。個人的にはもっとも多いのが、指定したエリア（自治体だったり矩形だったり）の中に指定数のPOINTを生成したいという使い方だと思います。生成させたPOINTをテーブルに入れたいかもしれませんよね。
　残念ながらMySQLのUDFでは、複数行を返すような関数を作ることはできません。試行錯誤の結果、少々複雑なクエリにはなりますが、以下のようにJSONを介すことで一応実現はできたので紹介します。

mysql> SET @g=ST_GeomFromText('POLYGON((1 1, 5 1, 5 5, 1 5, 1 1))',4326);
mysql> SELECT ST_AsText(
    ->     ST_GeomFromGeoJSON(
    ->       JSON_OBJECT('type', 'Point', 'coordinates', jt.coord)   ) )AS pt
    ->   FROM JSON_TABLE(
    ->     ST_AsGeoJSON(STX_GeneratePoints(@g, 5, 42), 15, 2),
    ->     '$.coordinates[*]' COLUMNS(coord JSON PATH '$')
    ->   ) AS jt;
+--------------------------------------------+
| pt                                         |
+--------------------------------------------+
| POINT(1.734299095173937 4.186171937151384) |
| POINT(3.389222560430685 4.118763990450646) |
| POINT(1.400204860476645 2.783331030193734) |
| POINT(2.335853107821295 2.836995551416728) |
| POINT(3.605540762386336 1.571467257664646) |
+--------------------------------------------+
5 rows in set (0.000 sec)


以下のようにいったんテーブルに入れることもできます（@gの定義は省略；上と同じです）。

CREATE TABLE x AS
SELECT ST_AsText(
    ST_GeomFromGeoJSON(
      JSON_OBJECT('type', 'Point', 'coordinates', jt.coord) ) )AS pt
  FROM JSON_TABLE(
    ST_AsGeoJSON(STX_GeneratePoints(@g, 5, 42), 15, 2),
    '$.coordinates[*]' COLUMNS(coord JSON PATH '$')
  ) AS jt;

結果はこんな感じ。

mysql> SELECT ST_AsText(pt), ST_SRID(pt) FROM x;
+--------------------------------------------+-------------+
| ST_AsText(pt)                              | ST_SRID(pt) |
+--------------------------------------------+-------------+
| POINT(1.734299095173937 4.186171937151384) |        4326 |
| POINT(3.389222560430685 4.118763990450646) |        4326 |
| POINT(1.400204860476645 2.783331030193734) |        4326 |
| POINT(2.335853107821295 2.836995551416728) |        4326 |
| POINT(3.605540762386336 1.571467257664646) |        4326 |
+--------------------------------------------+-------------+
5 rows in set (0.000 sec)

分解するためにFROM句で複雑な事をやって、それをジオメトリ（POINT）型として解釈するためにSELECT句でまた複雑な事をやっています。 FROM句の中でPOINT型にするとこまでできてしまうといいんですけどね。

MySQL GIS拡張関数： STX_MakePolygon()

mysql gis spatial newfunc

自作の、Spatial(GIS)関連の関数をMySQLに追加するプラグインの関数をひとつひとつ動作確認しながら紹介するシリーズ。
https://sakaik.hateblo.jp/entry/20260215/mysql_spatial_functions_plugin

STX_MakePolygon(ring [, inner_rings])

　LINESTRINGを与えてPOLYGONを生成する。 MULTILINESTRINGを与えると、2つめ以降の部分はくりぬきとして扱われる。
LINESTRINGは始点と終点が一致している（閉じている）必要がある。

動作紹介

  • LINESTRINGを与えてPOLYGON化するシンプルな例
mysql> SET @g=ST_GeomFromText('LINESTRING(1 1,5 1,5 5,4 6,3 5,2 2,1 2,1 1)');
mysql> SELECT ST_AsText(STX_MakePolygon(@g));
+--------------------------------------------+
| ST_AsText(STX_MakePolygon(@g))             |
+--------------------------------------------+
| POLYGON((1 1,1 2,2 2,3 5,4 6,5 5,5 1,1 1)) |
+--------------------------------------------+
1 row in set (0.000 sec)

.

  • MULTIPOINTから、STX_MakeLine(), STX_MakePolygon を順に掛けてポリゴン生成することもできる
mysql> SET @g=ST_GeomFromText('MULTIPOINT((1 1),(5 1),(5 5),(4 6),(3 5),(2 2),(1 2),(1 1))');
mysql> SELECT ST_AsText(STX_MakePolygon(STX_MakeLine(@g)));
+----------------------------------------------+
| ST_AsText(STX_MakePolygon(STX_MakeLine(@g))) |
+----------------------------------------------+
| POLYGON((1 1,1 2,2 2,3 5,4 6,5 5,5 1,1 1))   |
+----------------------------------------------+
1 row in set (0.000 sec)

.

  • 第2引数を与えてPOLYGONの中をくり抜き
mysql> SELECT ST_AsText(STX_Makepolygon(
    ->   ST_GeomFromText('LINESTRING(0 0,10 0,10 10,0 10,0 0)'),
    ->   ST_GeomFromText('MULTILINESTRING((2 2,4 2,4 4,2 4,2 2))'))) p;
+----------------------------------------------------------+
| p                                                        |
+----------------------------------------------------------+
| POLYGON((0 0,0 10,10 10,10 0,0 0),(2 2,4 2,4 4,2 4,2 2)) |
+----------------------------------------------------------+
1 row in set (0.000 sec)

MySQL GIS拡張関数： STX_MakeLine()

mysql gis spatial newfunc

自作の、Spatial(GIS)関連の関数をMySQLに追加するプラグインの関数をひとつひとつ動作確認しながら紹介するシリーズ。
https://sakaik.hateblo.jp/entry/20260215/mysql_spatial_functions_plugin

STX_Makeline(p1, p2) / STX_Makeline(multipoint)

　与えられた複数のポイント群からLINESTRINGを生成する関数です。

動作紹介

  • 2つのPOINTを与えてLINESTRINGにする例
mysql> SET @g1=ST_GeomFromText('POINT(1 1)');
mysql> SET @g2=ST_GeomFromText('POINT(2 3)');
mysql> SELECT ST_AsText(STX_MakeLine(@g1,@g2));
+----------------------------------+
| ST_AsText(STX_MakeLine(@g1,@g2)) |
+----------------------------------+
| LINESTRING(1 1,2 3)              |
+----------------------------------+
1 row in set (0.000 sec)

.

  • 上と同じ事をMULTIPOINTを使って実施する例
mysql> SET @g=ST_GeomFromText('MULTIPOINT((1 1),(2 3))');
mysql> SELECT ST_AsText(STX_MakeLine(@g1,@g2));
+----------------------------------+
| ST_AsText(STX_MakeLine(@g1,@g2)) |
+----------------------------------+
| LINESTRING(1 1,2 3)              |
+----------------------------------+
1 row in set (0.000 sec)

.

  • 以下のようにテーブルの複数の行からPOINTをひっぱってきて、MultiPointにした後、本関数でLINESTRINGに変換、なんてこともできます。
CREATE TABLE g (id integer auto_increment primary key,
                geom GEOMETRY);

INSERT INTO g (geom)
  VALUES (ST_GeomFromText('POINT(1 2)')),
  (ST_GeomFromText('POINT(3 4)')),
  (ST_GeomFromText('POINT(5 6)')),
  (ST_GeomFromText('POINT(7 8)'));
mysql> SELECT ST_AsText(ST_Collect(geom)) FROM g;
+-------------------------------------+
| ST_AsText(ST_Collect(geom))         |
+-------------------------------------+
| MULTIPOINT((1 2),(3 4),(5 6),(7 8)) |
+-------------------------------------+
1 row in set (0.000 sec)
mysql> SELECT ST_AsText(STX_MakeLine(ST_Collect(geom))) FROM g;
+-------------------------------------------+
| ST_AsText(STX_MakeLine(ST_Collect(geom))) |
+-------------------------------------------+
| LINESTRING(1 2,3 4,5 6,7 8)               |
+-------------------------------------------+
1 row in set (0.000 sec)

.



以上の内容はhttps://sakaik.hateblo.jp/より取得しました。
このページはhttp://font.textar.tv/のウェブフォントを使用してます
不具合報告/要望等はこちらへお願いします。
モバイルやる夫Viewer Ver0.14