关于mysql: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;