Mysql计算两个经纬度之间的距离

Mysql

DROP TABLE IF EXISTS `tutor`;
CREATE TABLE `tutor`  (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '名称',
  `lon` double(9, 6) NOT NULL COMMENT '经度',
  `lat` double(8, 6) NOT NULL COMMENT '纬度',
  `service_m` int(10) NULL DEFAULT 0 COMMENT '服务范围',
  `status` tinyint(1) NULL DEFAULT 1 COMMENT '1营业 0停业',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 8 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = 'tutor' ROW_FORMAT = Dynamic;

INSERT INTO `tutor` VALUES (1, 'tutor1', 117.315575, 39.133462, 5000, 1);
INSERT INTO `tutor` VALUES (2, 'tutor2', 116.407999, 39.894073, 4000, 0);
INSERT INTO `tutor` VALUES (3, 'tutor3', 115.557124, 38.853490, 3000, 1);
INSERT INTO `tutor` VALUES (4, 'tutor4', 114.646458, 38.072369, 2000, 0);
INSERT INTO `tutor` VALUES (5, 'tutor5', 116.367180, 40.009561, 6000, 1);
INSERT INTO `tutor` VALUES (6, 'tutor6', 116.313425, 39.973078, 10000, 1);
INSERT INTO `tutor` VALUES (7, 'tutor7', 116.329236, 39.987231, 8000, 0);

如何查询一个坐标与数据表各个坐标的距离？

tip： 其中 40.0497810000 与 116.3424590000 是待计算的经纬度

SELECT
    *,
    ROUND(
        6378.138 * 2 * ASIN(
            SQRT(
                POW(
                    SIN(
                        (
                            40.0497810000 * PI() / 180 - lat * PI() / 180
                        ) / 2
                    ),
                    2
                ) + COS(40.0497810000 * PI() / 180) * COS(lat * PI() / 180) * POW(
                    SIN(
                        (
                            116.3424590000 * PI() / 180 - lon * PI() / 180
                        ) / 2
                    ),
                    2
                )
            )
        ) * 1000
    ) AS m
FROM
    tutor
ORDER BY
    `m` ASC;

查询结果

+----+--------+------------+-----------+-----------+--------+--------+
| id | name   | lon        | lat       | service_m | status | m      |
+----+--------+------------+-----------+-----------+--------+--------+
|  5 | tutor5 | 116.367180 | 40.009561 |      6000 |      1 |   4948 |
|  7 | tutor7 | 116.329236 | 39.987231 |      8000 |      0 |   7054 |
|  6 | tutor6 | 116.313425 | 39.973078 |     10000 |      1 |   8890 |
|  2 | tutor2 | 116.407999 | 39.894073 |      4000 |      0 |  18213 |
|  1 | tutor1 | 117.315575 | 39.133462 |      5000 |      1 | 131806 |
|  3 | tutor3 | 115.557124 | 38.853490 |      3000 |      1 | 149301 |
|  4 | tutor4 | 114.646458 | 38.072369 |      2000 |      0 | 264459 |
+----+--------+------------+-----------+-----------+--------+--------+
7 rows in set (0.05 sec)

如何筛选出距离小于10000米的?

SELECT
    *,
    ROUND(
        6378.138 * 2 * ASIN(
            SQRT(
                POW(
                    SIN(
                        (
                            40.0497810000 * PI() / 180 - lat * PI() / 180
                        ) / 2
                    ),
                    2
                ) + COS(40.0497810000 * PI() / 180) * COS(lat * PI() / 180) * POW(
                    SIN(
                        (
                            116.3424590000 * PI() / 180 - lon * PI() / 180
                        ) / 2
                    ),
                    2
                )
            )
        ) * 1000
    ) AS m
FROM
    tutor
HAVING
	`m` < 10000
ORDER BY `m` asc;

结果

+----+--------+------------+-----------+-----------+--------+------+
| id | name   | lon        | lat       | service_m | status | m    |
+----+--------+------------+-----------+-----------+--------+------+
|  5 | tutor5 | 116.367180 | 40.009561 |      6000 |      1 | 4948 |
|  7 | tutor7 | 116.329236 | 39.987231 |      8000 |      0 | 7054 |
|  6 | tutor6 | 116.313425 | 39.973078 |     10000 |      1 | 8890 |
+----+--------+------------+-----------+-----------+--------+------+
3 rows in set (0.05 sec)

如何筛选出距离小于10000米且状态为营业中（status==1）的?

SELECT
    *,
    ROUND(
        6378.138 * 2 * ASIN(
            SQRT(
                POW(
                    SIN(
                        (
                            40.0497810000 * PI() / 180 - lat * PI() / 180
                        ) / 2
                    ),
                    2
                ) + COS(40.0497810000 * PI() / 180) * COS(lat * PI() / 180) * POW(
                    SIN(
                        (
                            116.3424590000 * PI() / 180 - lon * PI() / 180
                        ) / 2
                    ),
                    2
                )
            )
        ) * 1000
    ) AS m
FROM
    tutor
WHERE
    `status` = 1
HAVING
	`m` < 10000
ORDER BY `m` asc;

结果：

+----+--------+------------+-----------+-----------+--------+------+
| id | name   | lon        | lat       | service_m | status | m    |
+----+--------+------------+-----------+-----------+--------+------+
|  5 | tutor5 | 116.367180 | 40.009561 |      6000 |      1 | 4948 |
|  6 | tutor6 | 116.313425 | 39.973078 |     10000 |      1 | 8890 |
+----+--------+------------+-----------+-----------+--------+------+
2 rows in set (0.07 sec)

如何查询在服务范围内的（m < service_m）？

SELECT
    *,
    ROUND(
        6378.138 * 2 * ASIN(
            SQRT(
                POW(
                    SIN(
                        (
                            40.0497810000 * PI() / 180 - lat * PI() / 180
                        ) / 2
                    ),
                    2
                ) + COS(40.0497810000 * PI() / 180) * COS(lat * PI() / 180) * POW(
                    SIN(
                        (
                            116.3424590000 * PI() / 180 - lon * PI() / 180
                        ) / 2
                    ),
                    2
                )
            )
        ) * 1000
    ) AS m
FROM
    tutor
WHERE
    `status` = 1
HAVING
	`m` < `service_m`
ORDER BY `m` asc;

结果：

+----+--------+------------+-----------+-----------+--------+------+
| id | name   | lon        | lat       | service_m | status | m    |
+----+--------+------------+-----------+-----------+--------+------+
|  5 | tutor5 | 116.367180 | 40.009561 |      6000 |      1 | 4948 |
|  6 | tutor6 | 116.313425 | 39.973078 |     10000 |      1 | 8890 |
+----+--------+------------+-----------+-----------+--------+------+
2 rows in set (0.10 sec)