Distance between Long Lat coord using SQLITE

Question

I've got an sqlite db with long and lat of shops and I want to find out the closest 5 shops.

So the following code works fine.

    if(sqlite3_prepare_v2(db, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK) {


    while (sqlite3_step(compiledStatement) == SQLITE_ROW) {

        NSString *branchStr = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 0)];
        NSNumber *fLat = [NSNumber numberWithFloat:(float)sqlite3_column_double(compiledStatement, 1)];
        NSNumber *fLong = [NSNumber numberWithFloat:(float)sqlite3_column_double(compiledStatement, 2)];

        NSLog(@"Address %@, Lat = %@, Long = %@", branchStr, fLat, fLong);
        CLLocation *location1 = [[CLLocation alloc] initWithLatitude:currentLocation.coordinate.latitude longitude:currentLocation.coordinate.longitude];
        CLLocation *location2 = [[CLLocation alloc] initWithLatitude:[fLat floatValue] longitude:[fLong floatValue]];

        NSLog(@"Distance i meters: %f", [location1 getDistanceFrom:location2]);
        [location1 release];
        [location2 release];
    }       
}

I know the distance from where I am to each shop. My question is.

1. Is it better to put the distance back into the sqlite row, I have the row when I step thru the database. How do I do that? Do I use the UPDATE statement? Does someone have a piece of code to help me.

2. I can read the sqlite into an array and then sort the array. Do you recommend this over the above approach? Is this more efficient?

Finally, if someone has a better way to get the closest 5 shops, love to hear it.

Answer 1

The fastest way to find nearby locations in SQL is to use the Haversine formula in an SQL query. Do a Google search for sqlite and Haversine and you'll find an implementation.

Here's one I've used before:

http://www.thismuchiknow.co.uk/?p=71

Answer 2

you have to run through every element only once so the complexity is fairly good. you need a kind of fixed size Deque-container, a kind of linked list or a fixed size array, fixed by the size of 5, the number of shops you want to get from every point. add always the shop with the most minimal distance to the Deque. after running through your db you are have the nearest 5 shops in your deque.