Calculating Distance Between Latitude and Longitude Values in SQLite Database

Question

DISCLAIMER This is a hometask. Please, don't take it serious or think about this problem for more than 2 seconds. It's just not worth it. I'm sorry...

Hello! I'm working on an Android project where I need to calculate the distance between latitude and longitude values stored in an SQLite Database. I suspect there might be an issue with my calculation. I'd like to retrieve these locations and calculate the distances between them.

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;

}

I've been trying to calculate the distances using the Haversine formula. Here's a simplified version of my code:

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);

}

The issue is that my code doesn't return the expected results. I suspect the problem might be related to the Haversine formula, but I'm not entirely sure. Can anyone help me identify the problem or suggest improvements to calculate the distances correctly?

Answer 1

I believe it is almost correct, just that the haversine formula assumes the latitude and longitude values are in radians.

So, just convert your latitudes and longitudes to radians before using them in the formula.

You can modify your distance method like this:

private double distance(double lat1, double lon1, double lat2, double lon2) {
   lat1 = deg2rad(lat1);
   lon1 = deg2rad(lon1);
   lat2 = deg2rad(lat2);
   lon2 = deg2rad(lon2);
   // rest of the code to be filled accordingly
}

Now, the input values are in radians, which is necessary for the haversine formula to work correctly.

Please try it out.

Answer 2

Alright, so I set-up a small env to test and as suspected, the problem is not in the formula itself, but the measuring system. This dist = dist * 60 * 1.1515; will return the distance in Miles, so if you're expecting KMs, of course it will be off. To convert from miles you need to multiply by 1.609344.

Try this:

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

    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;

    if (kmConv)
        dist *= 1.609344;

    return (dist);
}

I tested some values versus Distance Calculator and it gave me (almost) the same result (apart from some rounding, of course).

Also, you have this

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

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

Shouldn't they be ...

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

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

... since those are the column names? Please double check both cases I mentioned (wrong conversion or wrong column names).

Minor note: I know you said this should not be taken seriously, so a discussion regarding the use of a DB or what type of DB you picked is not relevant. However, I will stress that you need to avoid this type of coding: Cursor cursor = db.rawQuery("select latitude,longitude from " + Table_Name_Location, null); . While this is homework, in any normal app, this style (concatenating SQL statements) is a major security issue as it allows SQL injection. The "best" attack vector is when using this type of concatenation in login forms in apps or sites. Just something to keep in mind for the future :)