如何比较SQLite查询中的纬度和经度值？

I am trying to compare Latitude and Longitude values in SQLite using greater & less than symbols as i used to do in MySQL. All the values are defined as varchar in Android SQLite. Below is my query:-

我试图在SQLite中比较使用大于和小于符号的纬度和经度值,就像我以前在MySQL中做的那样。所有值都在Android SQLite中定义为varchar。以下是我的查询: -

    double latt = Double.parseDouble(latitude);
    double lonn = Double.parseDouble(longitude);

    Cursor c = db.rawQuery("select jn,zone from tbl_zones where " 
            + latt + " >= CAST(sw_lat as DOUBLE) & " 
            + latt + " <= CAST(ne_lat as DOUBLE) & " 
            + lonn + " >= CAST(sw_lon as DOUBLE) & " 
            + lonn + " <= CAST(ne_lon as DOUBLE)", null);

String myjn = c.getString(c.getColumnIndex("jn"));

Here SQLite is fetching all the 'place' values in database and i am getting outofbox exception.
Any piece of code is highly appreciated.

这里SQLite正在获取数据库中的所有'place'值,我得到outofbox异常。任何一段代码都非常受欢迎。

Query returned all the fields instead of a specific one, here is when i tried it with onlinesqlite This specific latitude & logitude value only falls inside of any one of the rows in database

查询返回所有字段而不是特定字段,这是我用onlinesqlite尝试时这个特定的纬度和逻辑值只落在数据库中的任何一行内

The correct answer from mysql is:-

mysql的正确答案是: -

1 个解决方案

#1

Strings should be between two "" so the definition of your strings is not correct, maybe you mean double.
To solve your problem you need to cast your string to a number using CAST('number' as DOUBLE) and you have to use AND instead of & like you already mention in comment for example :

字符串应该在两个“”之间,所以字符串的定义不正确,也许你的意思是双重。要解决您的问题,您需要使用CAST('number'作为DOUBLE)将字符串转换为数字,并且您必须使用AND而不是像评论中提到的那样,例如:

double lat = 10.017860;
double lon = 76.342782;
Cursor c = db.rawQuery("select place from mytable where " 
        + lat + " >= CAST(sw_lat as DOUBLE) AND " 
        + lat + " <= CAST(ne_lat as DOUBLE) AND " 
        + lon + " >= CAST(sw_lon as DOUBLE) AND " 
        + lon + " <= CAST(ne_lon as DOUBLE)", null);

Note you don't need to use lat and lon between ''

注意你不需要在''之间使用lat和lon

read this

Take a look for this demo :

看看这个演示:

#1

double lat = 10.017860;
double lon = 76.342782;
Cursor c = db.rawQuery("select place from mytable where " 
        + lat + " >= CAST(sw_lat as DOUBLE) AND " 
        + lat + " <= CAST(ne_lat as DOUBLE) AND " 
        + lon + " >= CAST(sw_lon as DOUBLE) AND " 
        + lon + " <= CAST(ne_lon as DOUBLE)", null);