MySQL 空间位置坐标 geometry 的使用
文章目录
建表语句
CREATE TABLE `map` (
`id` int(11) NOT NULL,
`location` geometry NOT NULL,
PRIMARY KEY (`id`),
SPATIAL KEY `idx_location` (`location`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='空间位置信息';插入数据
INSERT INTO map (id, location) VALUES (1, ST_GeomFromText('POINT(121.366961 31.190049)'));更新数据
UPDATE map SET location = ST_GeomFromText('POINT(121.366961 31.190049)') where id = 1;查询数据
SELECT id, ST_AsText(location) AS location FROM map;ST_AsText()函数是将geometry类型转化为字符串
计算两点距离
// 计算结果单位是米,POINT()里经纬度使用逗号分隔
SELECT ST_Distance_Sphere(POINT(121.590347, 31.388094), location) AS distance FROM map;
SELECT ST_Distance_Sphere((SELECT location FROM map WHERE id = 1), location) AS distance FROM map WHERE id = 2;查询指定距离范围内的数据
SELECT
id,
ST_Distance_Sphere(POINT(121.590347, 31.388094), location) AS distance,
ST_AsText(location) AS location
FROM map
WHERE distance < 1000
ORDER BY distance ASC;
评论
登录后参与技术讨论,分享你的经验与见解。
登录 / 注册暂无评论