使用MySQL的geometry类型处理经纬度距离问题
建表
CREATE TABLE `map` ( `id` int(11) NOT NULL, `address` varchar(255) NOT NULL DEFAULT '', `location` geometry NOT NULL, PRIMARY KEY (`id`), SPATIAL KEY `idx_location` (`location`) )
插入
INSERT INTO map (id, address, location) VALUES (1, 'somewhere', ST_GeomFromText('POINT(121.366961 31.190049)'));注意必须使用
ST_GeomFromText
函数,且 POINT()
里面是:经度+空格+纬度查询
1. 查看经纬度
SELECT address, ST_AsText(location) AS location FROM map;
2. 计算两点之间的距离
SELECT ST_Distance_Sphere(POINT(121.590347, 31.388094),location) AS distant FROM map;算出来的结果,单位是米
注意现在POINT()里面经纬度之间是逗号分隔的
3. 查询距离小于1000m的地点,并由远及近排序
SELECT id, address, ST_Distance_Sphere(POINT(121.590347, 31.388094),location) AS distant FROM map WHERE ST_Distance_Sphere(POINT(121.590347, 31.388094),location) < 1000 ORDER BY distant;
相关推荐
guoyanga 2020-11-10
ALLENJIAO 2020-06-13
shilongdred 2019-11-19
nellson 2019-11-19
songyaping0 2011-07-19
xcjing 2015-11-12
有梦就能实现 2019-09-03
随手一记 2013-08-08
xiaoshe 2014-07-10
deusrwvf 2011-10-08
wildelf 2015-09-17
submarineas 2019-06-30
ZZZhangbingyi 2019-06-29
Donutsapps 2019-06-28
silencexiao 2015-11-12
zhaocen0 2019-06-19
xing00 2009-08-11
数据与算法之美 2011-02-26