Java SQlite 获取最近的位置（带纬度和经度）

SQlite Getting nearest locations (with latitude and longitude)

提问by Jure

I have data with latitude and longitude stored in my SQLite database, and I want to get the nearest locations to the parameters I put in (ex. My current location - lat/lng, etc.).

我的 SQLite 数据库中存储了经纬度数据，我想获取距离我输入的参数最近的位置（例如，我的当前位置 - lat/lng 等）。

I know that this is possible in MySQL, and I've done quite some research that SQLite needs a custom external function for the Haversine formula (calculating distance on a sphere), but I haven't found anything that is written in Java and works.

我知道这在 MySQL 中是可能的，并且我已经做了很多研究，SQLite 需要一个自定义的外部函数来实现 Haversine 公式（计算球体上的距离），但是我还没有找到任何用 Java 编写并且有效的东西.

Also, if I want to add custom functions, I need the org.sqlite.jar (for org.sqlite.Function), and that adds unnecessary size to the app.

另外，如果我想添加自定义函数，我需要org.sqlite.jar (for org.sqlite.Function)，这会增加应用程序不必要的大小。

The other side of this is, I need the Order by function from SQL, because displaying the distance alone isn't that much of a problem - I already did it in my custom SimpleCursorAdapter, but I can't sort the data, because I don't have the distance column in my database. That would mean updating the database every time the location changes and that's a waste of battery and performance. So if someone has any idea on sorting the cursor with a column that's not in the database, I'd be grateful too!

另一方面是，我需要来自 SQL 的 Order by 函数，因为单独显示距离并不是什么大问题 - 我已经在我的自定义 SimpleCursorAdapter 中做到了，但我无法对数据进行排序，因为我我的数据库中没有距离列。这意味着每次位置更改时都要更新数据库，这会浪费电池和性能。因此，如果有人对使用不在数据库中的列对光标进行排序有任何想法，我也将不胜感激！

I know there are tons of Android apps out there that use this function, but can someone please explain the magic.

我知道有大量的 Android 应用程序使用此功能，但有人可以解释一下这个神奇之处。

By the way, I found this alternative: Query to get records based on Radius in SQLite?

顺便说一下，我找到了这个替代方案：Query to get records based on Radius in SQLite?

It's suggesting to make 4 new columns for cos and sin values of lat and lng, but is there any other, not so redundant way?

建议为 lat 和 lng 的 cos 和 sin 值创建 4 个新列，但是还有其他不那么多余的方法吗？

采纳答案by Bobs

1)At first filter your SQLite data with a good approximation and decrease amount of data that you need to evaluate in your java code. Use the following procedure for this purpose:

1)首先用一个很好的近似值过滤你的 SQLite 数据，并减少你需要在你的 java 代码中评估的数据量。为此，请使用以下过程：

To have a deterministic thresholdand more accurate filter on data, It is better to calculate 4 locationsthat are in radiusmeter of the north, west, east and south of your central point in your java codeand then check easily by less than and more than SQL operators (>, <)to determine if your points in database are in that rectangle or not.

为了对数据具有确定性的阈值和更准确的过滤器，最好在 Java 代码中计算中心点的北、西、东和南米处的4 个位置，然后轻松检查小于和大于SQL 运算符 (>, <)来确定您在数据库中的点是否在该矩形中。radius

The method calculateDerivedPosition(...)calculates those points for you (p1, p2, p3, p4 in picture).

该方法calculateDerivedPosition(...)为您计算这些点（图片中的 p1、p2、p3、p4）。

/**
* Calculates the end-point from a given source at a given range (meters)
* and bearing (degrees). This methods uses simple geometry equations to
* calculate the end-point.
* 
* @param point
*           Point of origin
* @param range
*           Range in meters
* @param bearing
*           Bearing in degrees
* @return End-point from the source given the desired range and bearing.
*/
public static PointF calculateDerivedPosition(PointF point,
            double range, double bearing)
    {
        double EarthRadius = 6371000; // m

        double latA = Math.toRadians(point.x);
        double lonA = Math.toRadians(point.y);
        double angularDistance = range / EarthRadius;
        double trueCourse = Math.toRadians(bearing);

        double lat = Math.asin(
                Math.sin(latA) * Math.cos(angularDistance) +
                        Math.cos(latA) * Math.sin(angularDistance)
                        * Math.cos(trueCourse));

        double dlon = Math.atan2(
                Math.sin(trueCourse) * Math.sin(angularDistance)
                        * Math.cos(latA),
                Math.cos(angularDistance) - Math.sin(latA) * Math.sin(lat));

        double lon = ((lonA + dlon + Math.PI) % (Math.PI * 2)) - Math.PI;

        lat = Math.toDegrees(lat);
        lon = Math.toDegrees(lon);

        PointF newPoint = new PointF((float) lat, (float) lon);

        return newPoint;

    }

And now create your query:

现在创建您的查询：

PointF center = new PointF(x, y);
final double mult = 1; // mult = 1.1; is more reliable
PointF p1 = calculateDerivedPosition(center, mult * radius, 0);
PointF p2 = calculateDerivedPosition(center, mult * radius, 90);
PointF p3 = calculateDerivedPosition(center, mult * radius, 180);
PointF p4 = calculateDerivedPosition(center, mult * radius, 270);

strWhere =  " WHERE "
        + COL_X + " > " + String.valueOf(p3.x) + " AND "
        + COL_X + " < " + String.valueOf(p1.x) + " AND "
        + COL_Y + " < " + String.valueOf(p2.y) + " AND "
        + COL_Y + " > " + String.valueOf(p4.y);

COL_Xis the name of the column in the database that stores latitude values and COL_Yis for longitude.

COL_X是数据库中存储纬度值和COL_Y经度的列的名称。

So you have some data that are near your central point with a good approximation.

所以你有一些数据靠近你的中心点，并且有很好的近似值。

2)Now you can loop on these filtered data and determine if they are really near your point (in the circle) or not using the following methods:

2）现在您可以循环使用这些过滤后的数据，并使用以下方法确定它们是否真的靠近您的点（在圆圈中）：

public static boolean pointIsInCircle(PointF pointForCheck, PointF center,
            double radius) {
        if (getDistanceBetweenTwoPoints(pointForCheck, center) <= radius)
            return true;
        else
            return false;
    }

public static double getDistanceBetweenTwoPoints(PointF p1, PointF p2) {
        double R = 6371000; // m
        double dLat = Math.toRadians(p2.x - p1.x);
        double dLon = Math.toRadians(p2.y - p1.y);
        double lat1 = Math.toRadians(p1.x);
        double lat2 = Math.toRadians(p2.x);

        double a = Math.sin(dLat / 2) * Math.sin(dLat / 2) + Math.sin(dLon / 2)
                * Math.sin(dLon / 2) * Math.cos(lat1) * Math.cos(lat2);
        double c = 2 * Math.atan2(Math.sqrt(a), Math.sqrt(1 - a));
        double d = R * c;

        return d;
    }

Enjoy!

享受！

I used and customized this referenceand completed it.

我使用并定制了这个参考并完成了它。

回答by Morrison Chang

Have you considered a Geohashtag/index for your entries to reduce the size of your result set and then apply the appropriate function.

您是否考虑过为条目使用Geohash标签/索引以减少结果集的大小，然后应用适当的函数。

Another stackoverflow question in a similar area: finding-the-closest-point-to-a-given-point

类似领域的另一个 stackoverflow 问题： find-the-closest-point-to-a-given-point

回答by Chris Simpson

I know this has been answered and accepted but thought I'd add my experiences and solution.

我知道这已被回答并接受，但我想我会添加我的经验和解决方案。

Whilst I was happy to do a haversine function on the device to calculate the accurate distance between the user's current position and any particular target location there was a need to sort and limit the query results in order of distance.

虽然我很高兴在设备上做一个半正弦函数来计算用户当前位置和任何特定目标位置之间的准确距离，但需要按距离顺序对查询结果进行排序和限制。

The less than satisfactory solution is to return the lot and sort and filter after the fact but this would result in a second cursor and many unnecessary results being returned and discarded.

不太令人满意的解决方案是在事后返回批次并进行排序和过滤，但这将导致第二个游标和许多不必要的结果被返回和丢弃。

My preferred solution was to pass in a sort order of the squared delta values of the long and lats:

我的首选解决方案是传递 long 和 lats 的平方增量值的排序顺序：

((<lat> - LAT_COLUMN) * (<lat> - LAT_COLUMN) +
 (<lng> - LNG_COLUMN) * (<lng> - LNG_COLUMN))

There's no need to do the full haversine just for a sort order and there's no need to square root the results therefore SQLite can handle the calculation.

不需要为排序顺序做完整的半正弦，也不需要对结果进行平方根，因此 SQLite 可以处理计算。

EDIT:

编辑：

This answer is still receiving love. It works fine in most cases but if you need a little more accuracy, please check out the answer by @Teasel below which adds a "fudge" factor that fixes inaccuracies that increase as the latitude approaches 90.

这个回答还是收到了爱。它在大多数情况下都可以正常工作，但如果您需要更高的准确性，请查看下面@Teasel 的答案，其中添加了一个“软糖”因素，以修复随着纬度接近 90 度数而增加的不准确性。

回答by Teasel

Chris's answer is really useful (thanks!), but will only work if you are using rectilinear coordinates (eg UTM or OS grid references). If using degrees for lat/lng (eg WGS84) then the above only works at the equator. At other latitudes, you need to decrease the impact of longitude on the sort order. (Imagine you're close to the north pole... a degree of latitude is still the same as it is anywhere, but a degree of longitude may only be a few feet. This will mean that the sort order is incorrect).

Chris 的回答非常有用（谢谢！），但只有在您使用直线坐标（例如 UTM 或 OS 网格参考）时才有效。如果对纬度/经度使用度数（例如 WGS84），则上述仅适用于赤道。在其他纬度，您需要减少经度对排序顺序的影响。（假设您靠近北极...纬度的度数仍然与任何地方相同，但经度的度数可能只有几英尺。这意味着排序顺序不正确）。

If you are not at the equator, pre-calculate the fudge-factor, based on your current latitude:

如果您不在赤道，请根据您当前的纬度预先计算软糖因子：

<fudge> = Math.pow(Math.cos(Math.toRadians(<lat>)),2);

Then order by:

然后按以下顺序订购：

((<lat> - LAT_COLUMN) * (<lat> - LAT_COLUMN) + (<lng> - LNG_COLUMN) * (<lng> - LNG_COLUMN) * <fudge>)

((<lat> - LAT_COLUMN) * (<lat> - LAT_COLUMN) + (<lng> - LNG_COLUMN) * (<lng> - LNG_COLUMN) * <fudge>)

It's still only an approximation, but much better than the first one, so sort order inaccuracies will be much rarer.

它仍然只是一个近似值，但比第一个好得多，因此排序顺序不准确的情况会少得多。

回答by NickG

Have a look at this post:

看看这个帖子：

Distance function for sqlite

sqlite的距离函数

It seems to allow you to add a custom Distance() function to SQLite which might allow you to avoid jumping through all the hoops in the other answers.

它似乎允许您向 SQLite 添加自定义 Distance() 函数，这可能使您避免跳过其他答案中的所有圈套。

回答by Scott Helme

Try something like this:

尝试这样的事情：

    //locations to calculate difference with 
    Location me   = new Location(""); 
    Location dest = new Location(""); 

    //set lat and long of comparison obj 
    me.setLatitude(_mLat); 
    me.setLongitude(_mLong); 

    //init to circumference of the Earth 
    float smallest = 40008000.0f; //m 

    //var to hold id of db element we want 
    Integer id = 0; 

    //step through results 
    while(_myCursor.moveToNext()){ 

        //set lat and long of destination obj 
        dest.setLatitude(_myCursor.getFloat(_myCursor.getColumnIndexOrThrow(DataBaseHelper._FIELD_LATITUDE))); 
        dest.setLongitude(_myCursor.getFloat(_myCursor.getColumnIndexOrThrow(DataBaseHelper._FIELD_LONGITUDE))); 

        //grab distance between me and the destination 
        float dist = me.distanceTo(dest); 

        //if this is the smallest dist so far 
        if(dist < smallest){ 
            //store it 
            smallest = dist; 

            //grab it's id 
            id = _myCursor.getInt(_myCursor.getColumnIndexOrThrow(DataBaseHelper._FIELD_ID)); 
        } 
    } 

After this, id contains the item you want from the database so you can fetch it:

在此之后， id 包含您想要从数据库中获取的项目，以便您可以获取它：

    //now we have traversed all the data, fetch the id of the closest event to us 
    _myCursor = _myDBHelper.fetchID(id); 
    _myCursor.moveToFirst(); 

    //get lat and long of nearest location to user, used to push out to map view 
    _mLatNearest  = _myCursor.getFloat(_myCursor.getColumnIndexOrThrow(DataBaseHelper._FIELD_LATITUDE)); 
    _mLongNearest = _myCursor.getFloat(_myCursor.getColumnIndexOrThrow(DataBaseHelper._FIELD_LONGITUDE)); 

Hope that helps!

希望有帮助！

回答by Sergey Metlov

In order to increase performance as much as possible I suggest improve @Chris Simpson's idea with the following ORDER BYclause:

为了尽可能提高性能，我建议使用以下ORDER BY条款改进@Chris Simpson 的想法：

ORDER BY (<L> - <A> * LAT_COL - <B> * LON_COL + LAT_LON_SQ_SUM)

In this case you should pass the following values from code:

在这种情况下，您应该从代码中传递以下值：

<L> = center_lat^2 + center_lon^2
<A> = 2 * center_lat
<B> = 2 * center_lon

And you should also store LAT_LON_SQ_SUM = LAT_COL^2 + LON_COL^2as additional column in database. Populate it inserting your entities into database. This slightly improves performance while extracting large amount of data.

并且您还应该将其LAT_LON_SQ_SUM = LAT_COL^2 + LON_COL^2作为附加列存储在数据库中。填充它，将您的实体插入数据库。这在提取大量数据的同时略微提高了性能。