数据库中查询,两个经纬度之间的距离
You can implement your own functions in SQLite. For example, write a SQLite C function wrapper for acos
:
void sqlite_acos(sqlite3_context *context, int argc, sqlite3_value **argv)
{
int dataType = sqlite3_value_numeric_type(argv[0]);
if (dataType == SQLITE_INTEGER || dataType == SQLITE_FLOAT) {
double value = sqlite3_value_double(argv[0]);
sqlite3_result_double(context, acos(value));
} else {
sqlite3_result_null(context);
}
}
You can repeat this process for each of these trigonometric functions you need.
Then call sqlite3_create_function
to map this C function to a SQL expression:
- (BOOL)createFunctions:(sqlite3 *)db
{
int rc;
if ((rc = sqlite3_create_function(db, "acos", 1, SQLITE_ANY, NULL, sqlite_acos, NULL, NULL)) != SQLITE_OK) {
NSLog(@"%s: sqlite3_create_function acos error: %s (%d)", __FUNCTION__, sqlite3_errmsg(db), rc);
}
// repeat this for all of the other functions you define
return rc;
}
Frankly, rather than writing these individual trigonometric functions, I'd write one that does the higher-level distance calculation. That would be a little more efficient. That might look something like:
double radians(double degrees)
{
return degrees * M_PI / 180.0;
}
void sqlite_distance(sqlite3_context *context, int argc, sqlite3_value **argv)
{
double values[4];
// get the double values for the four arguments
for (int i = 0; i < 4; i++) {
int dataType = sqlite3_value_numeric_type(argv[i]);
if (dataType == SQLITE_INTEGER || dataType == SQLITE_FLOAT) {
values[i] = sqlite3_value_double(argv[i]);
} else {
sqlite3_result_null(context);
return;
}
}
// let's give those values meaningful variable names
double lat = radians(values[0]);
double lng = radians(values[1]);
double lat2 = radians(values[2]);
double lng2 = radians(values[3]);
// calculate the distance
double result = 6371.0 * acos(cos(lat2) * cos(lat) * cos(lng - lng2) + sin(lat2) * sin(lat));
sqlite3_result_double(context, result);
}
And you'd then define a distance
function in SQLite, like so:
int rc;
if ((rc = sqlite3_create_function(db, "distance", 4, SQLITE_ANY, NULL, sqlite_distance, NULL, NULL)) != SQLITE_OK) {
NSLog(@"%s: sqlite3_create_function distance error: %s (%d)", __FUNCTION__, sqlite3_errmsg(db), rc);
}
So, you open the database, call sqlite3_create_function
for this distance
function, and then you can write SQL that uses this new distance
function:
const char *sql = "SELECT "
"id, distance(lat, lng, 65.3234, 78.3232) AS distance "
"FROM markers "
"WHERE distance < 30 "
"ORDER BY distance";
if (sqlite3_prepare_v2(db, sql, -1, &statement, NULL) != SQLITE_OK)
NSLog(@"prepare failed: %s", sqlite3_errmsg(db));
while (sqlite3_step(statement) == SQLITE_ROW) {
sqlite3_int64 rowid = sqlite3_column_int64(statement, 0);
double distance = sqlite3_column_double(statement, 1);
NSLog(@"%lld %f", rowid, distance);
}
sqlite3_finalize(statement);
That distance
function is valid until you close the database, so make sure to call sqlite3_create_function
every time you open the database.