Sqlite 中的 acos 函数

问题描述

我有一个关于 SQLite 查询的问题.我需要像这样执行一个 sqlite 查询:

I have a question about SQLite queries.I need to execute a sqlite query like this:

SELECT
    id, (
      6371 * acos (
      cos ( radians(78.3232) )
      * cos( radians( lat ) )
      * cos( radians( lng ) - radians(65.3234) )
      + sin ( radians(78.3232) )
      * sin( radians( lat ) )
    )
) AS distance
FROM markers
HAVING distance < 30
ORDER BY distance
LIMIT 0 , 20;

但是我得到一个错误，因为 Sqlite 不支持像 acos 这样的函数.有没有办法在 Sqlite 中执行我的查询?我需要在 iOS 上使用它.欢迎提出任何实际建议.

But I obtain an error because Sqlite not support functions like acos.Is there a way to execute my query in Sqlite?I need to use this on iOS.Any practical suggestion is welcome.

推荐答案

您可以在 SQLite 中实现自己的功能.例如，为 acos 编写一个 SQLite C 函数包装器:

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.

然后调用 sqlite3_create_function 将这个 C 函数映射到一个 SQL 表达式:

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);
}

然后您将在 SQLite 中定义一个 distance 函数，如下所示:

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);
}

所以，你打开数据库，为这个distance函数调用sqlite3_create_function，然后你就可以编写使用这个新的distance函数的SQL:

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);

那个distance函数在你关闭数据库之前都是有效的，所以每次打开数据库时一定要调用sqlite3_create_function.

That distance function is valid until you close the database, so make sure to call sqlite3_create_function every time you open the database.

这篇关于Sqlite 中的 acos 函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！