mysql - 在mysql中传入2个值(经度和经度)时如何获取以公里为单位的距离？

Question

我在 mysql 中有一个表调用 BRANCH

===============================
Branch_id latitude longitude
===============================
1         3.109421  101.622913
2         3.101121  101.644913

当我传入当前位置的纬度/经度时，如何从该表中选择公里计算？

示例:

如果我传入当前位置 = 3.122221 101.343913

============================================
Branch_id latitude longitude   distance(km)
============================================
1         3.109421  101.622913   0.4
2         3.101121  101.644913   0.6

编辑(已解决):

SELECT p.title,p.subtitle,p.desc,p.image,p.promotion_id,p.merchant_id,p.date_from,p.date_to,m.merchant_name,p.view,mb.latitude,mb.longitude,
    (6371 * acos (cos( radians(3.158704)) * cos( radians(mb.latitude)) 
        * cos( radians(mb.longitude) - radians(101.713963)) + sin(radians(3.158704)) 
        * sin( radians(mb.latitude)))) AS distance
FROM merchant_branch as mb
left join merchant m on mb.merchant_id = m.merchant_id
left join promotion p on p.merchant_id = m.merchant_id
where p.promotion_id is not null order by distance asc

Answer 1

来自:https://developers.google.com/maps/solutions/store-locator/clothing-store-locator

Here's the SQL statement that finds the closest 20 locations within a radius of 25 miles to the -33, 151 coordinate. It calculates the distance based on the latitude/longitude of that row and the target latitude/longitude, and then asks for only rows where the distance value is less than 25, orders the whole query by distance, and limits it to 20 results. To search by kilometers instead of miles, replace 3959 with 6371.

SELECT id, ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * sin( radians( lat ) ) ) ) AS distance FROM markers HAVING distance < 25 ORDER BY distance LIMIT 0 , 20;