0

I need to do some database housekeeping.

I have a database table which consists of locations (containing latitude and longitude amongst other stuff). It's being used to generate Geofences (if you were interested).

As part of housekeeping, I want to remove location records from the database that are 'close to' other records (i.e. if 2 records in the table are within 250 metres of each other, I will delete the first one created and leave the second one).

My algorithm for achieving this is to loop through each record (I'll call this the root record) and for each root record loop through every other record in the table (I'll call this the check record), calculate the distance between the two locations, and if <250, delete the root record.

[This is pseudocode]

while (rootLoop.moveToNext) {
    while (checkLoop.moveToNext) {
        distance = getDistanceBetween(rootLocation, checkLocation);
        if (distance < 250) delete(rootRecord);
    }
}

If I've got a database table of a few hundred locations, this might take a bit of processing effort (I'll obviously run it in a background service).

So my question is - is there a more efficient way of achieving what I want than these nested loops?

If there is then I can't think of it but I thought I'd throw it open to the floor. How creative are you?

Simon Hutton
  • 1,677
  • 3
  • 22
  • 35
  • Does your implementation have the [R-tree module](http://www.sqlite.org/rtree.html)? – CL. Feb 22 '17 at 12:37
  • Ooooooooooooh!! Now THAT looks interesting. Not something I knew anything about but I shall do some reading. Thank you :) – Simon Hutton Feb 22 '17 at 12:47
  • So you escaped being a duplicate of [Getting nearest locations (with latitude and longitude)](http://stackoverflow.com/questions/3695224/android-sqlite-getting-nearest-locations-with-latitude-and-longitude) … – CL. Feb 22 '17 at 12:50
  • Awww bugger it :) There's always a duplicate if you look hard enough (and I did!). Thanks again for the link. I have a few things to think about (primarily not writing close locations to the database in the first place!) – Simon Hutton Feb 22 '17 at 12:58
  • ... and to answer your first comment, I'm developing for Android and it looks like in order to use the R*Tree module, I'll have to build my own sqlite implementation. Which I'd prefer not to do. I think I'm going to go with some of the stuff in the 'duplicate' link and not write close locations to the database at the outset. Appreciate your help. – Simon Hutton Feb 22 '17 at 13:22

0 Answers0