Is it possible to implement the Haversine formula in Objective-C and call it from SQLite?

Question

As I understand, SQLite doesn't have the math functions to properly implement the Haversine formula in straight SQL. I'm thinking this should be possible using an external function, with the implementation being in C.

The goal is to have a SQLite database in an iPhone, and to be able to sort by the distance to the user's current location. I've searched, but I can't find an example of any examples of this being done. I think the difficult parts would be getting the function declarations correct. The end result I'm hoping for, is to be able to execute a SQL statement like:

SELECT * FROM LOCATION loc ORDER BY distance(loc.lat, loc.long, ?, ?)

I have a C Haversine formula. The function definition is as follows:

float distance( float nLat1, float nLon1, float nLat2, float nLon2 );

Does anyone know if this is possible and/or have some example code to start from?

Answer 1

This demonstrates a sqlite function that takes in one string parameter and returns a string result.

In your case you would need a function that reads four floats and returns a float but the principle is the same (you would replace *sqlite3_value_text* with *sqlite3_value_double* and *sqlite3_result_text* with *sqlite3_result_double*):

#include <stdlib.h>
#include <sqlite3.h>
#include <stdio.h>


void haver(sqlite3_context* ctx,int cnt,sqlite3_value** val)
{
    printf("In SQLite haver implementation, called for value: %s\n", sqlite3_value_text(*val));

    char * resultOfCall = "Result of function call"; //this would call the distance function
    sqlite3_result_text(ctx, resultOfCall, strlen(resultOfCall), NULL);
}
int cback (void* udata,int ncol,char** value,char** colname)
{
    int i=0;
    for(;i<ncol;i++)
    printf("Result column: %s value: %s   \n", colname[i], value[i]);
    return 0;
}
int main()
{

    sqlite3 * handle;
    int res = sqlite3_open("./test.sql", &handle);

    res = sqlite3_create_function(handle, "haver", 1, SQLITE_UTF8, NULL, &haver, NULL, NULL);

    char * errmsg = NULL;   
    res = sqlite3_exec(handle, "select haver(w) from t", &cback, NULL, &errmsg);
    printf("sqlite3_exec result: %d %s\n", res, errmsg != NULL ? errmsg : "No error");

    sqlite3_close(handle);
}

Answer 2

I just had good luck with this post: http://www.thismuchiknow.co.uk/?p=71