Mysql 关于坐标空间相关的操作实践

卜木收录于类别 MySQL

2016-09-18 2016-09-18 约 160 字预计阅读 1 分钟

先建立一张地址表，用来存放点的坐标数据

1
2
3
4
5
6


CREATE TABLE `address` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `pos` point DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `pos` (`pos`(25))
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

插入坐标数据

1

INSERT INTO `address` (`pos`) VALUES (GeomFromText('POINT(31.123456 104.123456)'));

查询

 1
 2
 3
 4
 5
 6
 7
 8
 9
10


#单条查询
SELECT AsText(pos) FROM `address`;

#范围查询
SELECT * FROM address WHERE  MBRContains(
	LineString(
			Point (30.000000 + 10 / ( 111.1 / COS(RADIANS(104.000000))), 104.000000 + 10 / 111.1 ),
			Point (32.000000 - 10 / ( 111.1 / COS(RADIANS(106.000000))), 106.000000 - 10 / 111.1 )
		),
	pos)

计算坐标距离参考：查找附近的 xxx 球面距离以及 Geohash 方案探讨 http://www.wubiao.info/372

 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
37
38
39
40
41
42
43


DELIMITER $$

CREATE DEFINER=`root`@`%` FUNCTION `GETDISTANCE`(lat1 DOUBLE, lng1 DOUBLE, lat2 DOUBLE, lng2 DOUBLE) RETURNS double

READS SQL DATA

DETERMINISTIC

BEGIN

DECLARE RAD DOUBLE;

DECLARE EARTH_RADIUS DOUBLE DEFAULT 6378137;

DECLARE radLat1 DOUBLE;

DECLARE radLat2 DOUBLE;

DECLARE radLng1 DOUBLE;

DECLARE radLng2 DOUBLE;

DECLARE s DOUBLE;

SET RAD = PI() / 180.0;

SET radLat1 = lat1 * RAD;

SET radLat2 = lat2 * RAD;

SET radLng1 = lng1 * RAD;

SET radLng2 = lng2 * RAD;

SET s = ACOS(COS(radLat1)*COS(radLat2)*COS(radLng1-radLng2)+SIN(radLat1)*SIN(radLat2))*EARTH_RADIUS;

SET s = ROUND(s * 10000) / 10000;

RETURN s;

END$$

DELIMITER ;

目录

Mysql 关于坐标空间相关的操作实践