关于mysql:MySQL-空间位置坐标-geometry-的使用

40次阅读

共计 788 个字符，预计需要花费 2 分钟才能阅读完成。

建表语句

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;

正文完