java - Android Studio : SQLite Database Query for getting closest location from coordinates

Question

所以我有一个露营地数据库，其中包含名称、纬度和经度列。我正在尝试使用游标对象执行查询，但我似乎无法让它工作。

我有:

 Campsite getClosestCampsite() {
    SQLiteDatabase db = this.getReadableDatabase();
    String query = "SELECT COL_NAME, COL_LATITUDE, COL_LONGITUDE, ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * sin( radians( lat ) ) ) ) AS distance FROM TABLE_CAMPSITES HAVING distance < 25 ORDER BY distance LIMIT 0 , 1;";
    Cursor cursor = db.rawQuery(query, null);

    Campsite campsite = new Campsite();
    campsite.setName(cursor.getString(1));
    campsite.setLatitude(cursor.getDouble(6));
    campsite.setLongitude(cursor.getDouble(7));
    cursor.close();
    return campsite;
}

所以我只是想返回 1 个露营地，也就是最近的露营地，这样我就可以将名称和坐标传递给谷歌地图功能。我输入的查询是否正确，可以根据纬度和经度获取最近的位置？

IDE 强调了 HAVING 并给了我预期的 , , , AS GROUP 或分号

表结构:

@Override
public void onCreate(SQLiteDatabase db) {
    // CREATE TABLE
    String CREATE_CAMPSITES_TABLE = "CREATE TABLE " + TABLE_CAMPSITES + "("
            + COL_ID + " INTEGER PRIMARY KEY," + COL_NAME + " TEXT,"
            + COL_CITY + " TEXT," + COL_FEATURE + " TEXT," + COL_FAVORITE + " TEXT," + COL_RATING + " INTEGER," + COL_LATITUDE + " REAL," + COL_LONGITUDE + " REAL" + ")";
    db.execSQL(CREATE_CAMPSITES_TABLE);

Answer 1

HAVING 需要 GROUP BY 子句，根据以下突出显示的部分:-

SQL As Understood By SQLite- SELECT

你想使用WHERE而不是have，所以:-

String query = "SELECT COL_NAME, COL_LATITUDE, COL_LONGITUDE, ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * sin( radians( lat ) ) ) ) AS distance FROM TABLE_CAMPSITES WHERE distance < 25 ORDER BY distance LIMIT 0 , 1;"

然后您将收到错误 CURSOR INDEX OUT OF BOUNDS，因为在生成游标后，它位于第一行 (-1) 之前。您需要移动到光标中的一行(如果存在)，使用类似:-

Campsite campsite = new Campsite();
if (cursor.moveToFirst() {
    campsite.setName(cursor.getString(1));
    campsite.setLatitude(cursor.getDouble(6));
    campsite.setLongitude(cursor.getDouble(7));
}
cursor.close();
return campsite;

• 请注意，如果没有选择任何行，则返回的 Campsite 对象将按照构造时的情况进行(您应该检查这一点)。