java - sql计算离哪里的距离问题。

Question

手机端传个经纬度 想要查询到指定商家的距离

sql：String sql = "SELECT c.pubid,c.couponname couponName,c.type,c.couponfee / 100 couponfee,c.pic1_path picPath,count(c.fetch_num) AS num,g.category_name categoryName,"; 
        sql += " IF(ISNULL(r.minfee) || LENGTH(trim(r.minfee))<1,r.maxfee,r.minfee) fee,sc.id shopCartId,";
        sql += " ifnull((SELECT min(round(6378.138 * 2 * asin(sqrt(pow(sin(("+positionNS+" * pi() / 180 - s.positionNS * pi() / 180) / 2),2) + cos("+positionNS+" * pi() / 180) * cos(s.positionNS * pi() / 180) ";
        sql += " * pow(sin(("+positionWE+"* pi() / 180 - s.positionWE * pi() / 180) / 2),2))),2)) AS distance";
        sql += " FROM market s,saas m WHERE s.saas = m.id AND s.saas = ru.saas ) ,0) distance";
        sql += " FROM couponpublish c,hh_coupons_category g,ruledescribe r,actionrule ru, shop_cart sc";
        sql += " where c.category_id = g.id and c.getruleid=r.ruleid    and r.actionfile=ru.actionfile    and sc.pub_id = c.pubid    and sc.user_id = '"+userId+"'";
        sql += " GROUP BY c.pubid ORDER BY sc.create_time DESC";
        
         List<Map> list=super.findListbySqlReturnMap(sql);

s.positionWE positionNS是经纬度，想问问怎么个思路写的，这是别人写的给我研究研究。

Answer 1

透過經緯度計算距離是有公式的，這個查詢就是把公式套上去寫出來的。

計算公式可以參考：
http://www.cnblogs.com/ycsfwhh/archive/2010/12/20/1911232.html