MySQL空间函数ST_Distance_Sphere(g1, g2 [, radius]) :返回球体上两个点和/或多点之间的最小球面距离(以米为单位)
官网(https://dev.mysql.com/doc/refman/5.7/en/spatial-convenience-functions.html)
测试
表结构如下:
create table t_geo_test
(
ID int auto_increment
primary key,
NAME varchar(64) not null,
SHAPE geometry not null
)
engine = InnoDB
charset = utf8;
测试数据:(插入坐标时,以下两种写法都可以)
INSERT INTO t_geo_test VALUES (1, '测试', POINT(121.590347, 31.388094));
INSERT INTO t_geo_test VALUES (2, 'somewhere', ST_GeomFromText('POINT(121.366961 31.190049)'));
计算距离:
SELECT st_distance_sphere(POINT(121.590347, 31.388094),SHAPE) AS distant FROM t_geo_test;
查询到某点的距离小于一个值的所有数据:
SELECT *, ST_Distance_Sphere(POINT(121.590347, 31.388094),SHAPE) AS distant FROM t_geo_test WHERE ST_Distance_Sphere(POINT(121.590347, 31.388094),SHAPE) < 1000 ORDER BY distant;
注意:
坐标不能随意写测试数据,注意官网中的说的条件:
几何参数应由指定(经度,纬度)坐标值的点组成:
经度和纬度分别是该点的第一和第二坐标。
两个坐标均以度为单位。
经度值必须在(-180,180]范围内。正值位于本初子午线以东。
纬度值必须在[-90,90]范围内。正值位于赤道以北。
否则则会报错:
[HY000][1210] Incorrect arguments to st_distance_sphere