Distance function for sqlite

Posted on by

As part of an iPhone SDK project, I have an sqlite database with a table full of geographic locations, each stored as a latitude and longitude value in degrees. I wanted to be able to perform an SQL SELECT on this table and ORDER BY each row’s distance from an arbitrary point. I’ve achieved this by defining a custom sqlite function. This article contains the code for the function, together with instructions on using it.

Here’s the function, together with a convenience macro to convert from degrees to radians. This function is based on an online distance calculator I found which makes use of the spherical law of cosines.


#define DEG2RAD(degrees) (degrees * 0.01745327) // degrees * pi over 180

static void distanceFunc(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
sqlite3_result_double(context, acos(sin(lat1rad) * sin(lat2rad) + cos(lat1rad) * cos(lat2rad) * cos(DEG2RAD(lon2) - DEG2RAD(lon1))) * 6378.1);
}

This defines an SQL function distance(Latitude1, Longitude1, Latitude2, Longitude2), which returns the distance (in kilometres) between two points.

To use this function, add the code above to your Xcode project, and then add this line immediately after you call sqlite3_open:


sqlite3_create_function(sqliteDatabasePtr, "distance", 4, SQLITE_UTF8, NULL, &distanceFunc, NULL, NULL);

…where sqliteDatabasePtr is the database pointer returned by your call to sqlite3_open.

Assuming you have a table called Locations, with columns called Latitude and Longitude (both of type double) containing values in degrees, you can then use this function in your SQL like this:


SELECT * FROM Locations ORDER BY distance(Latitude, Longitude, 51.503357, -0.1199)

This example orders the locations in your database based on how far away they are from the London Eye, which is at 51.503357, -0.1199.

47 thoughts on “Distance function for sqlite”

  4. Thx for you’re code, it works well !
    I have a question, I use you’re query with two parameters so it’s like that :

    “SELECT * FROM spot_en ORDER BY distance(SPOT_LAT, SPOT_LON, ?, ?) LIMIT 0,20″

    but when I use the sqlite3_bind_double(selectstmt, 1, lat);

    it returns a 21 error, that it says that I misuse the library, do you know if it come from you’re function or it’s a mistake of my code.

    Thanks

    Reply ↓

  6. Dave, I am having a little difficulty deploying your code. I am getting a few errors I can not figure out. I am trying to use it in the iPhone SDK.

    Reply ↓

  8. I’m having trouble integrating this code into my project. I am new to Objective-C and C but I assume this is C code. What are the details to properly integrate it? I put the function code into a .c file and also tried creating a header file. (Is a header necessary? How exactly should it look?) I #import the header file in the .m file I will be using it and follow your sqlite3_create_function instructions.

    I’ve reduced it to one warning and one error.
    The warning “‘distanceFunc’ used but never defined” is in my header file which looks like this…
    #import
    static void distanceFunc(sqlite3_context *context, int argc, sqlite3_value **argv);

    The error is simply “gcc-4.0 failed with exit code 1″

    What is my lack of C skills blinding me to!?

    Reply ↓

  10. Dave, I do have one issue. I got the code to work, but I want to substitute a variable where you are plugging the coordinates for the London Eye in your select statement. My select statement looks like this:
    [NSString stringWithFormat:@"select pk, station, dial, city, state from radiostations ORDER BY distance(Latitude, Longitude, %@, %@)",presentLat, presentLng];

    Tommy

    Does this syntax look correct?

    Reply ↓

  15. This is f’n awesome! THANKS! I had something that seemed like overkill. THis works better. Place a limit on it to return a max number, and I’m good to go.

    Reply ↓

  21. Is there any way to adapt this to javascript? I’m using phonegap to create an application, currently I have an sqlite database full of lats and lons and I want to be able to pull all of the rows from the db that are within say a 25mi radius (optionally converting to km). i have found no documentation on how to create a custom function for sqlite in javascript.

    Reply ↓

  22. just to pitch in.. if you are using fmdb…

    sqlite3_create_function([db sqliteHandle], “distance”, 4, SQLITE_UTF8, NULL, &distanceFunc, NULL, NULL);

    see FMDatabase.m

    regards,
    rupert

    Reply ↓

  23. Pingback: Calculate POI distance from current location in wp7 « Bazinga!

  30. Unfortunately, no matter where I put this function into my viewController.m file, I get this error: “expected ‘)’ before ‘*’ token”. This is even after putting it above the @ implementation line.

    Can anyone provide a few details on how to actually include this function into your Objective-C viewController.m file?

    Reply ↓

  31. I’d be interested to know about performance with this. How many seconds per 1,000 location records does it take for an iPhone 3, 3GS and 4 to return results?

    We have a database of 1,400 locations taking 4 seconds on iPhone 3GS and 9 seconds on iPhone 4. We’re looking to increase the data set to 14,000 locations, but that’ll take an unacceptible 90 seconds on 3GS.

    Also, would love to see a JavaScript version of the custom SQL function for those developing HTML5 web apps with SQLite storage and PhoneGap apps.

    Reply ↓

  32. Hi Dave,

    I’m a total newbie to the iphone SDK. I have some knowledge of php/mysql, and javascript/html, that’s about it. Do you mind elaborating on where to put the code you offered in xcode. Can it literally go anywhere? what kind of file type? Do you have a source code file I could use?

    If not, no worries, it was worth a shot.

    Reply ↓

  33. Pingback: Usando Funções Matemáticas no Sqlite3 « WORKAROUNDS

  37. Thanks for the code-snippet, awesome :) though since Apple encourages using the core-data rather than direct database access, I wonder if you can do the same with core-data interface.

    Reply ↓

  40. I have successfully got this to sort my results… now I am trying to also display the kilometre distance in my uitableview.

    But I cannot seem to work out how to extract that form the function.

    thanks for the great code snippet

    Reply ↓

    • The function returns the distance, so you can make it field in your SELECT clause. For example:

      SELECT *, distance(Latitude, Longitude, lat, long) AS Distance FROM table ORDER BY Distance

      I have done this with the app I am currently working on. Hope this helps.

    • This code is working great for the iPhone with the SDK implemented through Titanium! THANK YOU!!!
      I have NOT been able to get it into SQLite for Android. Any clues on how to do the same distance calculation for Android?

  43. Thank you so much!

    I started to think that searching based on distance was nog possible with SQLite, but you definitely proved me wrong… :-)

    Reply ↓

  44. please help, i am facing an issue..

    i added the code to my sqlite plugin .m file in xcode and added the sqlite.createfunc statement just after the sqlite open statement in the same .m file.

    now, when i query using order by distance…., error is coming, if i remove this order by clause, it give result.

    please help

    Reply ↓

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>