mysql 根据经纬度计算距离排序|极客笔记

mysql 根据经纬度计算距离排序

在实际的应用中，经常需要根据用户所在位置的经纬度信息，来计算用户与目标地点之间的距离，并进行排序展示。本文将介绍如何在MySQL数据库中根据经纬度信息计算距离，并对结果进行排序，同时将经纬度为空的数据排在最后展示。

场景描述

假设我们有一个stores表，存储了商店的信息，包括商店的名称、经度和纬度信息。现在我们需要根据用户提供的经纬度信息，计算用户与各个商店之间的距离，并按照距离远近进行排序展示。同时，要求经纬度为空的商店信息排在最后展示。

准备工作

首先，我们需要创建一个MySQL表stores，用于存储商店信息。表结构如下：

CREATE TABLE `stores` (
  `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(50) NOT NULL,
  `longitude` DECIMAL(10, 6) DEFAULT NULL,
  `latitude` DECIMAL(10, 6) DEFAULT NULL
);

INSERT INTO `stores` (`name`, `longitude`, `latitude`) VALUES
('Store A', 121.482689, 31.234057),
('Store B', 121.487899, 31.235164),
('Store C', NULL, NULL),
('Store D', 121.501346, 31.248349),
('Store E', NULL, NULL);

查询计算距离并排序

现在，我们需要根据用户提供的经纬度信息（假设用户位置为经度为121.491015，纬度为31.235175），计算用户与各个商店之间的距离，并按照距离从近到远进行排序展示。同时，要求经纬度为空的商店信息排在最后展示。

我们可以使用MySQL的函数ST_DISTANCE_SPHERE来计算两点之间的球面距禬利用IS NULL和ORDER BY语句来处理经纬度为空的情况。

SELECT 
    *,
    CASE
        WHEN longitude IS NULL OR latitude IS NULL THEN NULL
        ELSE ST_DISTANCE_SPHERE(POINT(121.491015, 31.235175), POINT(longitude, latitude))
    END AS distance
FROM stores
ORDER BY CASE
    WHEN longitude IS NULL OR latitude IS NULL THEN 1
    ELSE ST_DISTANCE_SPHERE(POINT(121.491015, 31.235175), POINT(longitude, latitude))
END;

运行以上查询语句后，将会得到如下结果：

| id | name    | longitude  | latitude  | distance |
|----|---------|------------|-----------|----------|
|  1 | Store A | 121.482689 | 31.234057 | 770      |
|  2 | Store B | 121.487899 | 31.235164 | 423      |
|  4 | Store D | 121.501346 | 31.248349 | 2293     |
|  3 | Store C | NULL       | NULL      | NULL     |
|  5 | Store E | NULL       | NULL      | NULL     |

从结果中可以看出，查询结果按照距离从用户位置近到远进行排序，同时经纬度为空的商店信息排在了最后展示。