iOS: sqlite3 runtime function always returning 0 (where double is expected)

Question

I have implemented the algorithm provided by the brilliant Dave Addey to calculate the distance between two coordinates using the "spherical law of cosines" formula (this is the original link). This is how I intend to use the function call in a query:

NSString *SQLQuery= [NSString stringWithFormat: @"SELECT distance(lat, lon, '%f', '%f') as distance, * FROM table WHERE distance < %f", coord.latitude, coord.longitude, distance/1000.0];

The problem is that I can correctly filter the results in a radius of 1km, but when I try to access the column "distance" it always returns 0. I am using the FMDatabase, but direct calls to sqlite3 ([resultset doubleForColumnIndex: 0]) doesn't fix the issue.

Here is the code used to declare the function (I tried with direct calls to sqlite3 too):

    [database makeFunctionNamed:@"distance"
               maximumArguments:4
                      withBlock: ^( sqlite3_context *context, int argc, sqlite3_value **argv ) {
                          // check that we have four arguments (lat1, lon1, lat2, lon2)
                          assert(argc == 4);
                          // check that all four arguments are non-null
                          if (sqlite3_value_type(argv[0]) == SQLITE_NULL || sqlite3_value_type(argv[1]) == SQLITE_NULL || sqlite3_value_type(argv[2]) == SQLITE_NULL || sqlite3_value_type(argv[3]) == SQLITE_NULL) {
                              sqlite3_result_null(context);
                              return;
                          }
                          // get the four argument values
                          double lat1 = sqlite3_value_double(argv[0]);
                          double lon1 = sqlite3_value_double(argv[1]);
                          double lat2 = sqlite3_value_double(argv[2]);
                          double lon2 = sqlite3_value_double(argv[3]);
                          // convert lat1 and lat2 into radians now, to avoid doing it twice below
                          double lat1rad = DEG2RAD(lat1);
                          double lat2rad = DEG2RAD(lat2);
                          // apply the spherical law of cosines to our latitudes and longitudes, and set the result appropriately
                          // 6378.1 is the approximate radius of the earth in kilometres
                          double distance = acos(sin(lat1rad) * sin(lat2rad) + cos(lat1rad) * cos(lat2rad) * cos(DEG2RAD(lon2) - DEG2RAD(lon1))) * 6378.1;
                          sqlite3_result_double(context, distance);
                      }];

Any clues?

Answer 1

There is an error in how the SQL Query was built, it should be:

@"SELECT * FROM (SELECT distance(lat, lon, %f, %f) as distance, * FROM table) WHERE distance < %f ORDER BY distance DESC"

(See the comments to the question for details)