mysql距离计算

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
 
 CREATE TABLE `points` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `location` point DEFAULT NULL,
  `lng` varchar(255) DEFAULT NULL,
  `lat` varchar(255) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_local` (`location`(25))
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;

INSERT INTO  `points` (`id`, `location`, `lng`, `lat`, `name`) VALUES (1, ST_GeomFromText('POINT(113.305871 23.146077)'), '113.305871', '23.146077', '广州动物园');
INSERT INTO  `points` (`id`, `location`, `lng`, `lat`, `name`) VALUES (2, ST_GeomFromText('POINT(113.390965 23.055686)'), '113.390965', '23.055686', '小谷围岛');


-- 查询 越秀金融大厦与广州动物园距离 3km+ , 距离小谷围岛 10km+
 -- 方法一、 存储是  ponit类型
SELECT
 name ,
	ST_Distance (
		location,
	POINT ( 113.326318, 23.123985 )) * 111195 AS distance 
FROM
	points 
   HAVING  distance < 5000;
	 
	
 -- 方法二、 存储是  数字 
SELECT
 name ,
	ST_Distance (
		 POINT ( lng, lat ),
	POINT ( 113.326318, 23.123985 )) * 111195 AS distance 
FROM
	points 
   HAVING  distance < 5000;