数据库中查询，两个经纬度之间的距离

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.

转载于:https://www.cnblogs.com/mgbert/p/4146671.html