Android 查询以在 SQLite 中基于半径获取记录？

Query to get records based on Radius in SQLite?

提问by Pentium10

I have this query which does work fine in MySQL

我有这个在 MySQL 中运行良好的查询

SELECT ((ACOS(SIN(12.345 * PI() / 180) * SIN(lat * PI() / 180) +
         COS(12.345 * PI() / 180) * COS(lat * PI() / 180) * COS((67.89 - lon) * 
         PI() / 180)) * 180 / PI()) * 60 * 1.1515 * 1.609344) AS distance, poi.* 
FROM poi
WHERE lang='eng' 
HAVING distance<='30'

distance is in Kilometers, the input is lat=12.345and lon=67.89

距离以公里为单位，输入是lat=12.345和lon=67.89

The SQLite is 3, and I can't run custom functions with it as it's on Android. I also don't have acos() etc... as that is not part of the standard SQLite.

SQLite 是 3，我不能像在 Android 上那样用它运行自定义函数。我也没有 acos() 等...因为那不是标准 SQLite 的一部分。

How would be the above query in SQLite?

SQLite 中的上述查询如何？

采纳答案by kennytm

You can create 4 new columns, being sin and cos of latand lon. Since cos(a+b) = cos a cos b - sin a sin b, and other appearances of sin and cos like SIN(12.345 * PI() / 180)can be calculated in the program before running the query, the big "distance" expression reduces to something of the form P * SIN_LAT + Q * COS_LAT + ...that can be handled by SQLite3.

您可以创建4个新列，是罪恶和COSlat和lon。由于cos(a+b) = cos a cos b - sin a sin b, 以及类似 sin 和 cos 的其他外观SIN(12.345 * PI() / 180)可以在运行查询之前在程序中计算，因此大的“距离”表达式P * SIN_LAT + Q * COS_LAT + ...简化为 SQLite3 可以处理的形式。

BTW, see also Sqlite on Android: How to create a sqlite dist db function - to be used in the app for distance calculation using lat, long.

顺便说一句，另请参阅Android 上的 Sqlite：如何创建 sqlite dist db 函数 - 在应用程序中使用 lat, long 进行距离计算。

回答by EricLarch

Here is an implementation in Java for building a location based query on an Android device. The idea comes from KennyTM (see accepted response) and implies the addition of 4 columns in your table to store values of sinus and cosinus of latitude and longitudes.

这是一个 Java 实现，用于在 Android 设备上构建基于位置的查询。这个想法来自 KennyTM（请参阅已接受的回复）并暗示在您的表中添加 4 列来存储纬度和经度的正弦值和余弦值。

Here is the code preparing the data for a "Shop" table at insert time:

这是在插入时为“商店”表准备数据的代码：

public static void injectLocationValues(ContentValues values, double latitude, double longitude) {
    values.put(LocationColumns.LATITUDE, latitude);
    values.put(LocationColumns.LONGITUDE, longitude);
    values.put(LocationColumns.COSLAT, Math.cos(MathUtil.deg2rad(latitude)));
    values.put(LocationColumns.SINLAT, Math.sin(MathUtil.deg2rad(latitude)));
    values.put(LocationColumns.COSLNG, Math.cos(MathUtil.deg2rad(longitude)));
    values.put(LocationColumns.SINLNG, Math.sin(MathUtil.deg2rad(longitude)));
}

public static double deg2rad(double deg) {
    return (deg * Math.PI / 180.0);
}

You can then build your projection using the following function:

然后，您可以使用以下函数构建投影：

/**
 * Build query based on distance using spherical law of cosinus
 * 
 * d = acos(sin(lat1).sin(lat2)+cos(lat1).cos(lat2).cos(long2?long1)).R
 * where R=6371 and latitudes and longitudes expressed in radians
 * 
 * In Sqlite we do not have access to acos() sin() and lat() functions.
 * Knowing that cos(A-B) = cos(A).cos(B) + sin(A).sin(B)
 * We can determine a distance stub as:
 * d = sin(lat1).sin(lat2)+cos(lat1).cos(lat2).(cos(long2).cos(long1)+sin(long2).sin(long1))
 * 
 * First comparison point being fixed, sin(lat1) cos(lat1) sin(long1) and cos(long1)
 * can be replaced by constants.
 * 
 * Location aware table must therefore have the following columns to build the equation:
 * sinlat => sin(radians(lat))
 * coslat => cos(radians(lat))
 * coslng => cos(radians(lng))
 * sinlng => sin(radians(lng))
 *  
 * Function will return a real between -1 and 1 which can be used to order the query.
 * Distance in km is after expressed from R.acos(result) 
 *  
 * @param latitude, latitude of search
 * @param longitude, longitude of search
 * @return selection query to compute the distance
 */
public static String buildDistanceQuery(double latitude, double longitude) {
    final double coslat = Math.cos(MathUtil.deg2rad(latitude));
    final double sinlat = Math.sin(MathUtil.deg2rad(latitude));
    final double coslng = Math.cos(MathUtil.deg2rad(longitude));
    final double sinlng = Math.sin(MathUtil.deg2rad(longitude));
    //@formatter:off
    return "(" + coslat + "*" + LocationColumns.COSLAT
            + "*(" + LocationColumns.COSLNG + "*" + coslng
            + "+" + LocationColumns.SINLNG + "*" + sinlng
            + ")+" + sinlat + "*" + LocationColumns.SINLAT 
            + ")";
    //@formatter:on
}

It will inject a response column with the distance on which you need to apply the following formula to convert in kilometers:

它将注入一个响应列，其中包含您需要应用以下公式以公里为单位转换的距离：

public static double convertPartialDistanceToKm(double result) {
    return Math.acos(result) * 6371;
}

If you want to order your query using the partial distance, you need to order DESC and not ASC.

如果要使用部分距离对查询进行排序，则需要排序 DESC 而不是 ASC。

回答by sclaeys

Had the same issue while working on sqlite3 for ios, after playing a little with the formula here is a way to do it without using function from the sql side (pseudo-code):

在为 ios 使用 sqlite3 时遇到了同样的问题，在玩了一点公式之后，这里有一种方法可以在不使用 sql 端的函数（伪代码）的情况下做到这一点：

1. Pre-calculate these value for each item you store in the database (and store them):

   cos_lat = cos(lat * PI / 180)
   sin_lat = sin(lat * PI / 180)
   cos_lng = cos(lng * PI / 180)
   sin_lng = sin(lng * PI / 180)
   

2. Pre-calculate these value at the search time (for a given position cur_lat,cur_lng)

   CUR_cos_lat = cos(cur_lat * PI / 180)
   CUR_sin_lat = sin(cur_lat * PI / 180)
   CUR_cos_lng = cos(cur_lng * PI / 180)
   CUR_sin_lng = sin(cur_lng * PI / 180)
   cos_allowed_distance = cos(2.0 / 6371) # This is 2km
   

3. Your SQL query will look like this (replace CUR_* by the values you just calculated)

   SELECT * FROM position WHERE CUR_sin_lat * sin_lat + CUR_cos_lat * cos_lat * (cos_lng* CUR_cos_lng + sin_lng * CUR_sin_lng) > cos_allowed_distance;

1. 为您存储在数据库中的每个项目预先计算这些值（并存储它们）：

   cos_lat = cos(lat * PI / 180)
   sin_lat = sin(lat * PI / 180)
   cos_lng = cos(lng * PI / 180)
   sin_lng = sin(lng * PI / 180)
   

2. 在搜索时预先计算这些值（对于给定位置 cur_lat,cur_lng）

   CUR_cos_lat = cos(cur_lat * PI / 180)
   CUR_sin_lat = sin(cur_lat * PI / 180)
   CUR_cos_lng = cos(cur_lng * PI / 180)
   CUR_sin_lng = sin(cur_lng * PI / 180)
   cos_allowed_distance = cos(2.0 / 6371) # This is 2km
   

3. 您的 SQL 查询将如下所示（用您刚刚计算的值替换 CUR_*）

   SELECT * FROM position WHERE CUR_sin_lat * sin_lat + CUR_cos_lat * cos_lat * (cos_lng* CUR_cos_lng + sin_lng * CUR_sin_lng) > cos_allowed_distance;