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

hi, 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.

Answer 3

Hi ,

Your solution is not the best . Maybe in your app is working great , but what if you have 100.000 locations ?

The problem is that SQLITE does not support SIN , SQRT or other important functions in order to select in a limit of 5 the closest shops . Something like : select "distance" FROM table order by distance asc limit 5. I can do this formula in MySQL , Oracle without any problems , but not in SQLITE .

Your solution is not very great if you will have a large number of locations . In this case SQLITE kind of sucks and it is very annoying . Anyway , if somebody founds a solution for selecting the directly from the table in SQLITE , i will appreciate it .

Thanks in advance