SQL query to query nearby points of interest based on lat/long - SQLite

Question

Given a database that contains three fields:

Latitude Longitude Proximity

Where Lat and Long are GPS coordinates, and Proximity is (some unit - feet? Seconds? Minutes?)

And given the user's current GPS lat/long...

I want to write a SQL query that will retrieve all rows where the user is within "Proximity" of those rows.

And the trick: This has to work in SQLite, which only supports fairly primitive data types. No cheating and relying on SQL Server (or some other product that provides better geospace functions).

Any suggestions?

Answer 1

Here is a C-based custom function for sqlite [copied from links noted below]. This can be used within an iOS app. It assumes you have columns named latitude and longitude, and calculates the difference between them and any lat/long coordinates you provide. Excellent write-up, works as-is.

#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 ... 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.

EDIT :

Original link http://www.thismuchiknow.co.uk/?p=71 is dead, so as someone mentioned in comment, you can use this link : https://web.archive.org/web/20160808122817/http://www.thismuchiknow.co.uk/?p=71 to get that webpage

Answer 2

The Haversine formula is what you want. Using simple Euclidian distance formula isn't sufficient, because the curvature of the earth affects the distance between two points.

Creating a Store Locator with PHP, MySQL & Google Maps is an article about implementing this solution with MySQL, but SQLite doesn't support trig functions.

Check out the answers in Calculating Great-Circle Distance with SQLite here on Stack Overflow for further tips on extending SQLite functions so you can solve this.

Answer 3

You also know that there is a plugin for SQLite called Spatialite which has all the same functions as PostGIS and SQLServer? I assume you are trying to use SQLLite on the iPhone or in the browser or something. If not they I HIGHLY reccomend spatialite.