mysql 5.7.28 地理位置计算详解

mysql 5.7.28 空间地理位置计算
1. 创建测试表
2. 插入数据
3. 计算两个坐标点之间的距离
4. 计算指定的坐标点是否位于多边形（面）中
4.1 第一种方式
4.2 第二种方式
5. 计算两个多边形（面）是否相交 （st_intersects ）
6. 计算两个多边形（面）是否包含（st_contains ）
7. 计算一个多边形（面）是否在另一个多边形（面）内（st_within）
8. 遇到的异常
1. MySQL: Invalid GIS data provided to function st_geometryfromtext
1. 创建测试表
创建polygon表，该表用于存储多边形（面）数据：

CREATE TABLE `polygon` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`polygongeo` polygon NOT NULL,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
1
2
3
4
5
6
创建spots表，该表用于存储坐标点数据：

CREATE TABLE `spots` (
`id` int(11) NOT NULL,
`name` varchar(255) DEFAULT NULL COMMENT ‘地点名称’,
`spot` point DEFAULT NULL COMMENT ‘经纬度点’,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1
2
3
4
5
6
2. 插入数据
polygon表：

INSERT INTO polygon ( polygongeo, NAME )
VALUES( POLYGONFROMTEXT ( ‘POLYGON((36.6882437874 117.1774291992,36.6915474355 117.2364807129,36.6607078682 117.1499633789,36.6882437874 117.1774291992))’ ), ‘济南市历下区第一区域’ );

INSERT INTO polygon ( polygongeo, NAME )
VALUES( POLYGONFROMTEXT ( ‘POLYGON((36.8588150600 117.0421600300, 36.8565487700 117.2181129500, 36.7488396900 117.1599519300, 36.7642261500 117.0372676800, 36.8588150600 117.0421600300))’ ), ‘济南市历城区第一区域’ );
1
2
3
4
5
spots表：

INSERT INTO spots ( `name`, `spot` )
VALUES(‘山东省济南市济南高新技术产业开发区遥墙街道马官寨村’,ST_GeomFromText ( ‘POINT(36.8370421400 117.1580314600)’ ));

INSERT INTO spots ( `name`, `spot` )
VALUES(‘山东省济南市济南高新技术产业开发区舜华路街道齐鲁软件园’,ST_GeomFromText ( ‘POINT(36.6718611000 117.1339130400)’ ));
1
2
3
4
5
3. 计算两个坐标点之间的距离
select a.name,b.name,truncate(st_distance(a.spot,b.spot) * 111195 / 1000,2) as distance from (select * from spots where name=’山东省济南市济南高新技术产业开发区舜华路街道齐鲁软件园’) a, (select * from spots where name=’山东省济南市济南高新技术产业开发区遥墙街道马官寨村’) b
1
以上sql以spots表中的两个坐标点为起始点，使用st_distance函数进行两点之间的距离计算。结果如下：

地点一 地点二 距离（km）
山东省济南市济南高新技术产业开发区舜华路街道齐鲁软件园 山东省济南市济南高新技术产业开发区遥墙街道马官寨村 18.56
备注：mysql内置的 st_distance 函数计算的结果单位是度，需要乘111195（地球半径6371000*PI/180）是将值转化为米，除以1000换算为千米

4. 计算指定的坐标点是否位于多边形（面）中
4.1 第一种方式
利用mysql内置函数 MBRWithin(点，面) 计算，具体如下：

select AsText(p.polygongeo) from polygon p,(select spot from spots where name=’山东省济南市济南高新技术产业开发区遥墙街道马官寨村’) s where MBRWithin(s.spot,p.polygongeo)
1
结果如下：

搜索结果：
POLYGON((36.85881506 117.04216003,36.85654877 117.21811295,36.74883969 117.15995193,36.76422615 117.03726768,36.85881506 117.04216003))
以上结果表示该坐标点位于多边形（面）内

执行如下sql：

select AsText(p.polygongeo) from polygon p,(select spot from spots where name=’山东省济南市济南高新技术产业开发区舜华路街道齐鲁软件园’) s where MBRWithin(s.spot,p.polygongeo)
1
结果如下：

搜索结果：
POLYGON：null
以上结果表示该坐标点位于多边形（面）外

4.2 第二种方式
利用mysql内置函数 MBRContains(面，点) 计算，具体如下：

SET @g1 = geomFromText(‘POLYGON((121.474243 31.234504,121.471775 31.233348,121.470724 31.23155,121.471603 31.230229,121.472655 31.230357,121.475777 31.232045,121.474243 31.234504))’);
SET @g2 = geomFromText(‘POINT(121.471775 31.233348)’);
select MBRContains(@g1,@g2);
1
2
3
结果如下：

结果： 1
结论：该坐标点位于该多边形内
备注：如果返回值为 0 ，说明该坐标点不在多边形内，返回值为 1 ，说明坐标点位于多边形内。

5. 计算两个多边形（面）是否相交 （st_intersects ）
利用mysql内置函数 st_intersects 判断两个空间地理位置多边形（面）是否相交，具体如下：

SET @g1 = geomFromText(‘POLYGON((121.474243 31.234504,121.471775 31.233348,121.470724 31.23155,121.471603 31.230229,121.472655 31.230357,121.475777 31.232045,121.474243 31.234504))’);
SET @g2 = geomFromText(‘POLYGON((121.474243 31.234804,121.471775 31.233948,121.471724 31.23155,121.471903 31.230229,121.472655 31.230157,121.475777 31.231045,121.474243 31.234804))’);

SELECT st_intersects(@g1,@g2);
1
2
3
4
结果如下：

结果： 1
结论：两个多边形相交
备注：如果返回值为 0 ，说明两个多边形未相交，返回值为 1 ，说明两个多边形相交。

6. 计算两个多边形（面）是否包含（st_contains ）
利用mysql内置函数 st_contains 进行空间地理位置多边形（面）包含计算，具体如下：

SET @g1 = geomFromText(‘POLYGON((121.474243 31.234504,121.471775 31.233348,121.470724 31.23155,121.471603 31.230229,121.472655 31.230357,121.475777 31.232045,121.474243 31.234504))’);
SET @g2 = geomFromText(‘POLYGON((121.474243 31.234504,121.471775 31.233348,121.470724 31.23155,121.471603 31.230229,121.474243 31.234504))’);

SELECT st_contains (@g1,@g2);
1
2
3
4
结果如下：

结果：1
结论：多边形@g1 完全包含 @g2
备注：该函数的参数区分先后顺序，即结果为0：参数一 不包含 参数二，结果为1：参数一完全包含参数二

7. 计算一个多边形（面）是否在另一个多边形（面）内（st_within）
利用mysql内置函数 st_within进行空间地理位置多边形（面）包含计算，具体如下：

SET @g1 = geomFromText(‘POLYGON((121.474243 31.234504,121.471775 31.233348,121.470724 31.23155,121.471603 31.230229,121.472655 31.230357,121.475777 31.232045,121.474243 31.234504))’);
SET @g2 = geomFromText(‘POLYGON((121.474243 31.234504,121.471775 31.233348,121.470724 31.23155,121.471603 31.230229,121.474243 31.234504))’);

SELECT st_within(@g1,@g2);
1
2
3
4
结果如下：

结果：1
结论：多边形@g1 在 @g2 内
备注：该函数的参数区分先后顺序，即结果为0：参数一 不在 参数二内，结果为1：参数一在参数二内

8. 遇到的异常
1. MySQL: Invalid GIS data provided to function st_geometryfromtext
空间地理位置参数不完整，造成该问题的原因可能有：
1、面状WKT字符串本身不完整、缺失内容。
2、面状WKT字符串的面要素中首尾点没有重合。
3、面状WKT字符串存在岛洞现象，或者自相交现象。

————————————————
版权声明：本文为CSDN博主「举个栗子の不容易」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/xu_cxiang/article/details/104541186