根据用户经纬度，sql计算距离并排序

根据经纬度计算距离

public class DistanceUtil {

    // 地球平均半径
    private static final double EARTH_RADIUS = 6378137;

    // 把经纬度转为度（°）
    private static double rad(double d) {
        return d * Math.PI / 180.0;
    }

    /**
     * 根据两点间经纬度坐标（double值），计算两点间距离，单位：千米
     * @author ershuai
     * @param lng1
     * @param lat1
     * @param lng2
     * @param lat2
     * @return
     */
    public static double getDistance(double lng1, double lat1, double lng2, double lat2) {
        double radLat1 = rad(lat1);
        double radLat2 = rad(lat2);
        double a = radLat1 - radLat2;
        double b = rad(lng1) - rad(lng2);
        double s = 2 * Math.asin(Math.sqrt(Math.pow(Math.sin(a / 2), 2) + Math.cos(radLat1) * Math.cos(radLat2) * Math.pow(Math.sin(b / 2), 2)));
        s = s * EARTH_RADIUS;
        s = s / 1000;
        DecimalFormat df = new DecimalFormat("#.00");
        s = Double.parseDouble(df.format(s));
        return s;
    }

    public static void main(String[] args) {
        double distance1 = getDistance(104.046115, 30.6030110000, 104.074666, 30.611842);
        System.out.println("Distance is: " + distance1 + " km");
    }
}

sql   查询

 

SELECT
    es_name,
    es_lon,
    es_lat,
    ROUND(
        6378.138 * 2 * ASIN(
            SQRT(
                POW(
                    SIN(
                        (
                            30.611842 * PI() / 180 - es_lat * PI() / 180
                        ) / 2
                    ),
                    2
                ) + COS(30.611842 * PI() / 180) * COS(es_lat * PI() / 180) * POW(
                    SIN(
                        (
                            104.074666 * PI() / 180 - es_lon * PI() / 180
                        ) / 2
                    ),
                    2
                )
            )
        ) * 1000
    ) AS distance_um
FROM
    c_ershuai
ORDER BY
    distance_um ASC