12

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?

starblue
  • 55,348
  • 14
  • 97
  • 151
Don Jones
  • 9,367
  • 8
  • 39
  • 49
  • Cant you use a simple SQL to calculate the distance of two points and then see if this is less that the distance? Simple Euclydian geometry? – Thomas Jul 02 '10 at 20:37
  • 2
    @Thomas: The *Euclidean* distance between two points is along a straight line *through* the earth. That might not be what you want. – dan04 Jul 03 '10 at 02:43
  • 2
    @dan04 the circumference of the Earth is such a larger magnitude than any search radius that it is negligible. 100 miles to 99.88 miles, that is about 630 feet, less than a football field. – Gallonallen Sep 27 '13 at 05:24

3 Answers3

7

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

StayOnTarget
  • 11,743
  • 10
  • 52
  • 81
Don Jones
  • 9,367
  • 8
  • 39
  • 49
  • 1
    There's always the [wayback machine](https://web.archive.org/web/20160808122817/http://www.thismuchiknow.co.uk/?p=71) – mvandillen Mar 10 '17 at 16:01
3

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.

Community
  • 1
  • 1
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
2

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.

TheSteve0
  • 3,530
  • 1
  • 19
  • 25