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 itHAVING distance < 1000000It 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 statementmapsIf 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! ~

Answer 0:

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.

Answer 1:

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

Answer 2:

http://jiangbai333.github.io/…

Answer 3:

You can try to mix with the C language.

Answer 4:

http://www.cnblogs.com/zeromy… It’s clear in it.

Similar Posts:

Leave a Reply

Your email address will not be published. Required fields are marked *