自作の、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型にするとこまでできてしまうといいんですけどね。
