2021-08-26

mysql判断点是否在范围内

CREATE TABLE`test` (

`id`int(11) NOT NULL auto_increment,

`ploygongeo`text NOT NULL,PRIMARY KEY(`id`)

)

insert into ci_special_zone(ploygongeo) values('POLYGON((106.509883 29.679301,106.509647 29.679297,106.509412 29.679287,106.509177 29.679269,106.508942 29.679244,106.508708 29.679212,106.508476 29.679173,106.508245 29.679127,106.508015 29.679074,106.507787 29.679014,106.507561 29.678948,106.507337 29.678874,106.507115 29.678794,106.506896 29.678707,106.50668 29.678613,106.506466 29.678513,106.506256 29.678407,106.506049 29.678294,106.505845 29.678175,106.505645 29.67805,106.505449 29.677919,106.505257 29.677783,106.505069 29.67764,106.504886 29.677492,106.504707 29.677338,106.504533 29.677179,106.504364 29.677015,106.5042 29.676846,106.504041 29.676672,106.503887 29.676493,106.503739 29.676309,106.503596 29.676122,106.503459 29.67593,106.503329 29.675734,106.503204 29.675534,106.503085 29.67533,106.502972 29.675123,106.502865 29.674913,106.502766 29.674699,106.502672 29.674483,106.502585 29.674264,106.502505 29.674042,106.502431 29.673818,106.502365 29.673592,106.502305 29.673364,106.502252 29.673134,106.502206 29.672903,106.502167 29.67267,106.502135 29.672437,106.50211 29.672202,106.502092 29.671967,106.502081 29.671732,106.502078 29.671496,106.502081 29.67126,106.502092 29.671025,106.50211 29.67079,106.502135 29.670555,106.502167 29.670322,106.502206 29.670089,106.502252 29.669858,106.502305 29.669628,106.502365 29.6694,106.502431 29.669174,106.502505 29.66895,106.502585 29.668728,106.502672 29.668509,106.502766 29.668293,106.502865 29.668079,106.502972 29.667869,106.503085 29.667662,106.503204 29.667458,106.503329 29.667258,106.503459 29.667062,106.503596 29.66687,106.503739 29.666683,106.503887 29.666499,106.504041 29.66632,106.5042 29.666146,106.504364 29.665977,106.504533 29.665813,106.504707 29.665654,106.504886 29.6655,106.505069 29.665352,106.505257 29.665209,106.505449 29.665073,106.505645 29.664942,106.505845 29.664817,106.506049 29.664698,106.506256 29.664585,106.506466 29.664479,106.50668 29.664379,106.506896 29.664285,106.507115 29.664198,106.507337 29.664118,106.507561 29.664044,106.507787 29.663978,106.508015 29.663918,106.508245 29.663865,106.508476 29.663819,106.508708 29.66378,106.508942 29.663748,106.509177 29.663723,106.509412 29.663705,106.509647 29.663695,106.509883 29.663691,106.510119 29.663695,106.510354 29.663705,106.510589 29.663723,106.510824 29.663748,106.511057 29.66378,106.51129 29.663819,106.511521 29.663865,106.511751 29.663918,106.511979 29.663978,106.512205 29.664044,106.512429 29.664118,106.512651 29.664198,106.51287 29.664285,106.513086 29.664379,106.5133 29.664479,106.51351 29.664585,106.513717 29.664698,106.513921 29.664817,106.514121 29.664942,106.514317 29.665073,106.514509 29.665209,106.514696 29.665352,106.51488 29.6655,106.515058 29.665654,106.515233 29.665813,106.515402 29.665977,106.515566 29.666146,106.515725 29.66632,106.515879 29.666499,106.516027 29.666683,106.516169 29.66687,106.516306 29.667062,106.516437 29.667258,106.516562 29.667458,106.516681 29.667662,106.516794 29.667869,106.5169 29.668079,106.517 29.668293,106.517094 29.668509,106.517181 29.668728,106.517261 29.66895,106.517334 29.669174,106.517401 29.6694,106.517461 29.669628,106.517514 29.669858,106.51756 29.670089,106.517599 29.670322,106.517631 29.670555,106.517656 29.67079,106.517674 29.671025,106.517684 29.67126,106.517688 29.671496,106.517684 29.671732,106.517674 29.671967,106.517656 29.672202,106.517631 29.672437,106.517599 29.67267,106.51756 29.672903,106.517514 29.673134,106.517461 29.673364,106.517401 29.673592,106.517334 29.673818,106.517261 29.674042,106.517181 29.674264,106.517094 29.674483,106.517 29.674699,106.5169 29.674913,106.516794 29.675123,106.516681 29.67533,106.516562 29.675534,106.516437 29.675734,106.516306 29.67593,106.516169 29.676122,106.516027 29.676309,106.515879 29.676493,106.515725 29.676672,106.515566 29.676846,106.515402 29.677015,106.515233 29.677179,106.515058 29.677338,106.51488 29.677492,106.514696 29.67764,106.514509 29.677783,106.514317 29.677919,106.514121 29.67805,106.513921 29.678175,106.513717 29.678294,106.51351 29.678407,106.5133 29.678513,106.513086 29.678613,106.51287 29.678707,106.512651 29.678794,106.512429 29.678874,106.512205 29.678948,106.511979 29.679014,106.511751 29.679074,106.511521 29.679127,106.51129 29.679173,106.511057 29.679212,106.510824 29.679244,106.510589 29.679269,106.510354 29.679287,106.510119 29.679297,106.509883 29.679301))');

SELECT substring(ploygongeo,10,length(ploygongeo)-11) from test where MBRContains(PolygonFromText(ploygongeo),PolygonFromText('Point(106.51129 29.679172)')) > 0 and id =1

结果不为空表示在线