iphone - iOS - 使用 "<"运算符的 Sqlite3 查询不起作用

Question

我有 sqlite3 数据库，其中包含具有纬度和经度值的位置。我想使用 SELECT 语句获取特定半径内的位置。

我尝试按照此处所述实现:http://www.thismuchiknow.co.uk/?p=71如果我的查询如下所示，一切正常:

"SELECT pk FROM location WHERE distance(latitude, longitude, '%d', '%d') > 100"

但是如果我使用“<”运算符而不是“>”，则语句无法执行，我会得到 sqlite3_errmsg(database) 的“未知错误”。我真的想不通为什么使用另一个运算符会导致错误。有人知道吗？

我的代码:

    NSMutableArray *resultsArray = [[NSMutableArray alloc] init];
    self.results = resultsArray;
    [resultsArray release];

    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentsDirectory = [paths objectAtIndex:0];
    NSString *path = [documentsDirectory stringByAppendingPathComponent:@"Locations.sqlite"];

    if (sqlite3_open([path UTF8String], &database) == SQLITE_OK) {

        sqlite3_create_function(database, "distance", 4, SQLITE_UTF8, NULL, &distanceFunc, NULL, NULL);


        double vLongitude;
        double vLatitude;
        vLongitude = 8.683;
        vLatitude = 50.117;

        sql = [[NSString stringWithFormat:@"SELECT pk FROM location WHERE distance(latitude, longitude, '%d', '%d') < 50", vLatitude, vLongitude]cStringUsingEncoding:NSUTF8StringEncoding];
        sqlite3_stmt *statement;

        if(sqlite3_prepare_v2(database, sql, -1, &statement, NULL) == SQLITE_OK){

                while(sqlite3_step(statement) == SQLITE_ROW) {
                    //the second parameter indicates the column index to the result set
                    int primary_key = sqlite3_column_int(statement, 0);
                    Location *loc = [[Location alloc] initWithPrimaryKey:primary_key database:database];
                    [results addObject:loc];
                    [loc release];
                }
            }
        printf( "could not prepare statemnt: %s\n", sqlite3_errmsg(database) );
        sqlite3_finalize(statement);
    }

    else {
        sqlite3_close(database);
        NSAssert1(0, @"Failed to open database with message '%s'.",  sqlite3_errmsg(database));
    }
    searchResultsController.results = self.results;

和上面链接的距离函数:

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

Answer 1

“SELECT pk FROM location WHERE distance(latitude, longitude, '%d', '%d') < 50”纬度和经度是双重类型你应该做 %f 而不是 %d

关于SQL错误，尝试做:

SELECT pk, distance(latitude, longitude, '%f', '%f') AS distance_km FROM location WHERE disntace_km < 50