Deleting database rows based on current location

Question

I store objects with a position (lat/long) in a normal SQLite database in my Android applicaton. The application receives messages (UDP packets) with new objects which are stored in the this DB.

To avoid the explosion of the DB at one point and to keep it light, I would like to delete all objects which are further away than a specific radius permanently. The DB is controlled via a DB-Adapter class implementing a DB Helper.

As I do not want to use distance approximations, I would like to use the haversine formula. Now I have the following questions:

What is the best way to implement this task in a lightweight intelligent solution?
I thought about using an AsyncTask to do the job. Is this recommended?

look at this answer http://stackoverflow.com/a/12997900/779408 — Bob, Oct 22 '12 at 12:18

score 2 · Accepted Answer · edited May 23 '17 at 11:55

This is the method that I have been using to get a sort clause for objects with GPS data stored in a database:

public String getOrderString(double latitude, double longitude) {
    double fudge = Math.pow(Math.cos(Math.toRadians(latitude)), 2);

    return "((" + latitude + " - " + KEY_LATITUDE + ") * (" + latitude
            + " - " + KEY_LATITUDE + ") + (" + longitude + " - "
            + KEY_LONGITUDE + ") * (" + longitude + " - " + KEY_LONGITUDE
            + ") * " + fudge + ") ASC";
}

'borrowed' from here. My understanding of the fudge factor is that it takes into account coordinates that are not near the equator. This equation has worked well for me so far and is fairly quick.

Now, I think you can utilize the same equation, by doing something like:

public String getWhereString(double latitude, double longitude, double threshold) {
    double fudge = Math.pow(Math.cos(Math.toRadians(latitude)), 2);

    return "((" + latitude + " - " + KEY_LATITUDE + ") * (" + latitude
            + " - " + KEY_LATITUDE + ") + (" + longitude + " - "
            + KEY_LONGITUDE + ") * (" + longitude + " - " + KEY_LONGITUDE
            + ") * " + fudge + ") < " + threshold;
}

Then your delete method would be like:

public int removeBuildingEntry(double latitude, double longitude, double threshold) {
    String where = getWhereString(double latitude, double longitude, double threshold)

    return db.delete(TABLE, where, null);
}

The only issue is that I do not know the units of the result of the equation, and therefore what you should pass in as a threshold. In my situation I don't care about those as I only want the order, but in your case that might make a difference. You could either play around with different values or attempt to calculate it if you need a more exact number.

Thank you very much for the code and explanations! This is really helpful! I will try it out and report! — Palund, Oct 25 '11 at 17:43
Ok, I implemented it this way and it works quite well. For the units I multiply both products with 111.2 km which is approx. the width of one degree at the equator. With the fudge factor it works, too. The approximation is ok. Thanks again! — Palund, Oct 29 '11 at 12:58

Deleting database rows based on current location

1 Answers1