Android项目中SQLite经纬度距离计算问题排查

免责声明 #

这是一项家庭作业，请不要认真对待，也不要为这个问题思考超过2秒，不值得为此花费时间，抱歉...

问题场景 #

开发Android项目时，需计算SQLite数据库中存储的经纬度点之间的距离，但当前代码返回结果不符合预期，怀疑计算过程存在问题。

获取数据库经纬度的代码 #

public ArrayList<Double> getPoints(){

    ArrayList<Double> location = new ArrayList<>();

    SQLiteDatabase db = this.getReadableDatabase();

    Cursor cursor = db.rawQuery("select latitude,longitude from " + Table_Name_Location, null);

    if(cursor.getCount() > 0){

        while(cursor.moveToNext()) {

            Double latitude = cursor.getDouble(cursor.getColumnIndex("Lat"));

            Double longitude = cursor.getDouble(cursor.getColumnIndex("Longi"));

            location.add(latitude);

            location.add(longitude);

        }

    }

    cursor.close();

    return location;

}

距离计算代码（Haversine公式实现） #

private double distance(double lat1, double lon1, double lat2, double lon2) {

    double theta = lon1 - lon2;

    double dist = Math.sin(deg2rad(lat1)) * Math.sin(deg2rad(lat2))

                + Math.cos(deg2rad(lat1)) * Math.cos(deg2rad(lat2)) * Math.cos(deg2rad(theta));

    dist = Math.acos(dist);

    dist = rad2deg(dist);

    dist = dist * 60 * 1.1515;

    return (dist);

}

private double deg2rad(double deg) {

    return (deg * Math.PI / 180.0);

}

private double rad2deg(double rad) {

    return (rad * 180.0 / Math.PI);

}

问题排查与修复方案 #

1. 数据库字段名不匹配（核心错误） #

SQL查询语句中指定的字段是latitude和longitude，但获取列索引时用的是"Lat"和"Longi"，这会导致获取到错误的数值（甚至抛出异常）。必须保持字段名一致：

• 要么修改查询语句为select Lat, Longi from " + Table_Name_Location
• 要么将getColumnIndex的参数改为"latitude"和"longitude"

2. Haversine公式的精度与单位问题 #

当前实现使用acos计算，当两点距离极近时，可能因浮点数精度问题返回NaN。更稳定的半正矢公式实现如下，同时可直接返回公里单位：

private double distance(double lat1, double lon1, double lat2, double lon2) {
    final int EARTH_RADIUS_KM = 6371; // 地球平均半径（公里）
    double latDiff = deg2rad(lat2 - lat1);
    double lonDiff = deg2rad(lon2 - lon1);
    
    double a = Math.sin(latDiff / 2) * Math.sin(latDiff / 2)
            + Math.cos(deg2rad(lat1)) * Math.cos(deg2rad(lat2))
            * Math.sin(lonDiff / 2) * Math.sin(lonDiff / 2);
    double c = 2 * Math.atan2(Math.sqrt(a), Math.sqrt(1 - a));
    
    double distanceKm = EARTH_RADIUS_KM * c;
    // 如需转换为英里，乘以0.621371即可
    return distanceKm;
}

3. 经纬度存储结构优化 #

当前用ArrayList<Double>存储，每个点的经纬度是连续两个元素，处理时极易出错。建议自定义实体类：

public class LocationPoint {
    private double lat;
    private double lon;

    public LocationPoint(double lat, double lon) {
        this.lat = lat;
        this.lon = lon;
    }

    public double getLat() { return lat; }
    public double getLon() { return lon; }
}

修改getPoints方法返回ArrayList<LocationPoint>，后续计算距离时逻辑更清晰：

public ArrayList<LocationPoint> getPoints(){
    ArrayList<LocationPoint> locations = new ArrayList<>();
    SQLiteDatabase db = this.getReadableDatabase();
    Cursor cursor = db.rawQuery("select latitude, longitude from " + Table_Name_Location, null);
    
    if(cursor.moveToFirst()){
        do {
            double lat = cursor.getDouble(cursor.getColumnIndex("latitude"));
            double lon = cursor.getDouble(cursor.getColumnIndex("longitude"));
            locations.add(new LocationPoint(lat, lon));
        } while(cursor.moveToNext());
    }
    
    cursor.close();
    return locations;
}

内容的提问来源于stack exchange，提问作者jango123