MySQL测试判断多边形(Polygon)及镂空多边形(MultiPolygon)是否包含点

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
-- 1.创建测试表
CREATE TABLE zone (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
polygongeo geometry NOT NULL,
PRIMARY KEY (id)
) ENGINE=MYISAM DEFAULT CHARSET=utf8;
 
-- 2.插入多边形数据
insert into zone(polygongeo) values(ST_GeomFromText('POLYGON((1 1,1 5,5 5,5 1,1 1))'));
 
-- 3.判断点是否在多边形区域
--   3.1 测试 POINT(3, 4),在多边形内
select ST_AsText(polygongeo) from zone where MBRWithin(ST_GeomFromText('POINT(3 4)'),polygongeo);
 
--   3.2 测试 POINT(6, 1)  在多边形外
select ST_AsText(polygongeo) from zone where MBRWithin(ST_GeomFromText('POINT(6 1)'),polygongeo);

不入库,直接测试

1
SELECT ST_Contains(ST_GeomFromText('MULTIPOLYGON(((103.236631342762 28.8176761290501,103.223402196754 28.8067721559103,103.209070714842 28.8142367294416,103.180091207643 28.798947455161,103.14654213721 28.824723076309,103.130331635197 28.828001337192,103.134297395409 28.8452254641839,103.167231778324 28.8643948670295,103.176539598724 28.8840813692598,103.169919000262 28.9045950607135,103.172633909627 28.932211552721,103.146715816082 28.9448446028962,103.13471930785 28.9692321042783,103.150374074704 28.9703423083509,103.161580849577 28.9891424738461,103.15734975159 29.0005456111911,103.177684162294 29.032308821395,103.176350962327 29.0694099108388,103.160365421227 29.0944466696781,103.180453467154 29.1056188206521,103.2017136031 29.1071235950779,103.201687387863 29.0940476602714,103.191523992039 29.0799538880615,103.199963856947 29.0731856649865,103.219618568142 29.0694447325884,103.227025387212 29.0507195660552,103.259157118007 29.0312680863532,103.281320228146 28.996955444738,103.274604182837 28.9814130607642,103.25969996678 28.9740033773541,103.245668694754 28.9457143516361,103.251277767255 28.9258500057732,103.245698980324 28.915184451901,103.272321285299 28.8964177393872,103.246358179786 28.870089342749,103.228214409667 28.8654235673438,103.236631342762 28.8176761290501)))'),ST_GeomFromText('POINT(103.2366313 28.81767612)'))
1
2
3
4
5
SELECT ST_Contains(ST_GeomFromText('MULTIPOLYGON(((98.9208984375  30.41078179084589,97.327880859375  28.825425374477224,98.052978515625  28.120438687101064,97.7783203125  26.303264239389534,99.151611328125  25.997549919572112,100.535888671875  26.303264239389534,101.865234375  27.235094607795503,103.095703125  26.391869671769022,103.89770507812499  27.712710260887476,103.60107421874999  28.700224692776988,101.88720703125  30.14512718337613,100.51391601562499  30.92107637538488,101.129150390625  28.9120147012556,100.0634765625  28.033197847676377,99.90966796875  29.6594160549124,98.9208984375  30.41078179084589),(101.7059326171875 28.023500048883022,102.72766113281249 28.023500048883022,102.72766113281249 28.700224692776988,101.7059326171875 28.700224692776988,101.7059326171875 28.023500048883022)))'),ST_GeomFromText('POINT(101.854248046875 29.084976575985912)'))
--点1:101.854248046875 29.084976575985912    输出: 1
--点2:100.5029296875 28.969700808694157      输出: 0
--点3:103.91555786132812 27.70784710660343   输出: 0
--点4:102.26074218749999 28.372068829631633  输出: 0
点的位置 点3位置

上面测试中的镂空多边形geojson数据如下

1
{"type":"FeatureCollection","features":[{"type":"Feature","properties":{},"geometry":{"type":"Polygon","coordinates":[[[98.9208984375,30.41078179084589],[97.327880859375,28.825425374477224],[98.052978515625,28.120438687101064],[97.7783203125,26.303264239389534],[99.151611328125,25.997549919572112],[100.535888671875,26.303264239389534],[101.865234375,27.235094607795503],[103.095703125,26.391869671769022],[103.89770507812499,27.712710260887476],[103.60107421874999,28.700224692776988],[101.88720703125,30.14512718337613],[100.51391601562499,30.92107637538488],[101.129150390625,28.9120147012556],[100.0634765625,28.033197847676377],[99.90966796875,29.6594160549124],[98.9208984375,30.41078179084589]],[[101.7059326171875,28.023500048883022],[102.72766113281249,28.023500048883022],[102.72766113281249,28.700224692776988],[101.7059326171875,28.700224692776988],[101.7059326171875,28.023500048883022]]]}}]}