MySQL GIS拡張関数： STX_MinimumBoundingCircle()

自作の、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()

自作の、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()

自作の、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()

自作の、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)

.

MySQL GIS拡張関数： STX_Points()

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

STX_Points(geom)

　与えられたジオメトリを構成する点をすべてPOINTにし、MULTIPOINT型として返す。LINESTRINGであればその構成点を、POLYGONの場合も同様に構成点を返します。POLYGONの場合は始点と終点が同じ点になりますが、結果にはそれぞれ（つまり同じ点が2つ）含まれます。

動作紹介

• POINT型を与えればそのままのPOINTがひとつ含まれたMULTIPOINTが得られる

mysql> SET @g=ST_GeomFromText('POINT(3 4)');
mysql> SELECT ST_AsText(STX_Points(@g)) mp;
+-------------------+
| mp                |
+-------------------+
| MULTIPOINT((3 4)) |
+-------------------+
1 row in set (0.000 sec)

.

• POLYGONの場合は、必ず始点と終点が同じ点だが結果には両方含まれる

mysql> SET @g=ST_GeomFromText('POLYGON((1 1, 5 1, 5 5, 4 6, 3 5, 2 2, 1 2,  1 1))');
mysql> SELECT ST_AsText(STX_Points(@g)) mp;
+-------------------------------------------------------------+
| mp                                                          |
+-------------------------------------------------------------+
| MULTIPOINT((1 1),(5 1),(5 5),(4 6),(3 5),(2 2),(1 2),(1 1)) |
+-------------------------------------------------------------+
1 row in set (0.000 sec)

.

• LINESTRINGからも当然POINT化できる

mysql> SET @g=ST_GeomFromText('LINESTRING(1 1, 2 2, 3 1, 4 2, 5 1)');
mysql> SELECT ST_AsText(STX_Points(@g)) mp;
+-------------------------------------------+
| mp                                        |
+-------------------------------------------+
| MULTIPOINT((1 1),(2 2),(3 1),(4 2),(5 1)) |
+-------------------------------------------+
1 row in set (0.000 sec)

.

• MULTILINESTRINGからでも同様に動作します

mysql> SET @g=ST_GeomFromText('MULTILINESTRING((1 1, 2 2, 3 1, 4 2, 5 1),(3 3, 4 3, 5 1))');
mysql> SELECT ST_AsText(STX_Points(@g)) mp;
+-------------------------------------------------------------+
| mp                                                          |
+-------------------------------------------------------------+
| MULTIPOINT((1 1),(2 2),(3 1),(4 2),(5 1),(3 3),(4 3),(5 1)) |
+-------------------------------------------------------------+
1 row in set (0.000 sec)

MySQL GIS拡張関数： STX_PointOnSurface()

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

STX_PointonSurface(geom)

　与えられたジオメトリ内にあることが保証されている点をひとつ返す。POINTでもLINESTRINGでも動作するが、おそらくPOLYGONで使うことが多そう。
「ポリゴンの内側にその点がある」というだけであり、点の位置そのものには意味はない（ようなシーンで使う）。

動作紹介

• POINT上の代表点はそのPOINTそのもの

mysql> SELECT ST_AsText(STX_PointOnSurface(ST_GeomFromText('POINT(2 5)'))) p;
+------------+
| p          |
+------------+
| POINT(2 5) |
+------------+
1 row in set (0.000 sec)

.

• LINESTRING上の代表点は線の上に採られる。この場合は始点が代表点として得られている

mysql> SELECT ST_AsText(STX_PointOnSurface(ST_GeomFromText('LINESTRING(2 5, 2 10)'))) p;
+------------+
| p          |
+------------+
| POINT(2 5) |
+------------+
1 row in set (0.000 sec)

.

• POLYGONの代表点はその重心であることが保証されているわけではないが、この場合はいいかんじに重心が得られている（アルゴリズム的にはまず重心を求めてそれがPOLYGON内なら採用、それ以外なら平行移動して求めるということをやっているので、これは正しい動き）

mysql> SELECT ST_AsText(STX_PointOnSurface(ST_GeomFromText('POLYGON((0 0, 0 5, 5 5, 5 0, 0 0))'))) p;
+----------------+
| p              |
+----------------+
| POINT(2.5 2.5) |
+----------------+
1 row in set (0.000 sec)

.

• POLYGONの重心がPOLYGON外となる場合は良きに計らってPOLYGON内であることが保証されている点を返してくれる

mysql> SET @g=ST_GeomFromText('POLYGON((1 1, 5 1, 5 5, 4 5, 4 2, 2 2, 2 3, 1 3, 1 1))');
mysql> SELECT ST_AsText(STX_PointOnSurface(@g)) s;
+--------------+
| s            |
+--------------+
| POINT(4.5 4) |
+--------------+
1 row in set (0.000 sec)

.

開発秘話（秘密という程でもない）

　当初、本関数はPOLYGONにのみ対応していた。このブログを書くための動作確認の中で発覚し、POINT/LINESTRING/POLYGONに対応するように修正を行った。

MySQL GIS拡張関数： STX_LineLocatePoint()

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

STX_LineLocatePoint(line, point)

　第2引数のPOINTから最も近い第1引数のLINESTRINGの点を求め、その位置をLINESTRING上の始点から比率で返す。戻値は0.0～1.0の範囲。

動作紹介

• 長さ10のLINESTRING上に、少し離れたPOINTから最短の位置を決定して、割合を返す

mysql> SELECT STX_LineLocatePoint(
    ->   ST_GeomFromText('LINESTRING(0 3, 10 3)'),
    ->   ST_GeomFromText('POINT(4 0)')) ratio;
+-------+
| ratio |
+-------+
|   0.4 |
+-------+
1 row in set (0.000 sec)

.

• 節を幾つも持つ（ふつうの）LINESTRINGでももちろん動作する

mysql> SELECT STX_LineLocatePoint(
    ->   ST_GeomFromText('LINESTRING(0 5, 5 5, 5 10)'),
    ->   ST_GeomFromText('POINT(7 6 )')) r;
+------+
| r    |
+------+
|  0.6 |
+------+
1 row in set (0.000 sec)

.

• 第1引数がLINESTRINGでない場合はエラー

mysql> SELECT STX_LineLocatePoint(
    ->   ST_GeomFromText('POLYGON((0 5, 5 5, 5 10, 0 5))'),
    ->   ST_GeomFromText('POINT(7 6 )')) r;
ERROR 3516 (22S01): LINESTRING value is a geometry of unexpected type POLYGON in stx_linelocatepoint.

• 第2筆数がPOINTでない場合はエラー

mysql> SELECT STX_Linelocatepoint(
    ->   ST_GeomFromText('LINESTRING(0 3, 10 3)'),
    ->   ST_GeomFromText('LINESTRING(1 2, 1 4)')) ratio;
ERROR 3516 (22S01): POINT value is a geometry of unexpected type LINESTRING in stx_linelocatepoint.

## 使い道の例
　ここで割合としてLINESTRING上の点を特定できると、この割合の値を使って STX_LinesSubstring() でLINESTRINGを分割できる。