Article From:https://segmentfault.com/q/1010000011107479
Question:

I’m currently calculating distances in the database and sorting them by distances, which can be especially large when some merchants don’t enter latitude and longitude. How can we make a judgement to solve this problem?

The code is as follows:

``````SELECT
substring_index(maps, ",", 1) AS lng,
substring_index(maps, "," ,- 1) AS lat,
ROUND(
6378.138 * 2 * ASIN(
SQRT(
POW(
SIN(
(
32.640351 * PI() / 180 - substring_index(maps, ",", - 1) * PI() / 180
) / 2
),
2
) + COS(32.640351 * PI() / 180) * COS(
substring_index(maps, ",", - 1) * PI() / 180
) * POW(
SIN(
(
117.013281 * PI() / 180 - substring_index(maps, ",", 1) * PI() / 180
) / 2
),
2
)
)
) * 1000
) AS distance
FROM
list
ORDER BY
distance ASC``````

The code is like above, if I use it`HAVING distance < 1000000`It does solve the problem I’ve described, but the problem also arises that the merchant doesn’t show, and my idea is, how to judge in a MySQL query statement`maps`If this field is empty, it does not show or calculate the distance, so it is best to become text hint.`Unknown distance`。How can I do it? Thank you! ~

It has been solved.
case when maps then
Calculation formula
else ‘Unknown distance ‘end distance
Yes, but because of strings, sorting can be sorted in numerical order.

if（Judge, ‘right’, ‘wrong’)?
case when Distance > 0 then ‘**’ when Distance is null then ‘No location’ else ‘unknown’ end distance?