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

Question

As I understand it, SQLite does not have the mathematical functions to correctly implement the Haversine formula in direct SQL. I think this should be possible with an external function, while the implementation is in C.

The goal is to have an SQLite database on the iPhone and be able to sort by distance to the user's current location. I searched, but I cannot find an example of any examples of this. I think the hard parts will get the right function declarations. The end result, which I hope for, is to execute an SQL statement, for example:

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 there is some kind of sample code to start with?

Answer 1

I was just lucky with this post: http://www.thismuchiknow.co.uk/?p=71

Answer 2

This demonstrates the sqlite function, which takes a single string parameter and returns the result of the string.

In your case, you need a function that reads four floats and returns a float, but the principle is the same (you replaced 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);
}