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 < 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 statement
mapsIf 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
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?
You can try to mix with the C language.
http://www.cnblogs.com/zeromy… It’s clear in it.