Mysql|mysql 5.7.28 地理位置计算详解数据库|mysql

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;

创建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;

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))' ), '济南市历城区第一区域' );

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)' ));

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

以上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 whereMBRWithin(s.spot,p.polygongeo)

结果如下：

搜索结果：
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 whereMBRWithin(s.spot,p.polygongeo)

结果如下：

搜索结果：
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
结论：该坐标点位于该多边形内
备注：如果返回值为 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
结论：两个多边形相交
备注：如果返回值为 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
结论：多边形@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);

结果如下：