SQlite获取最近的位置(带有纬度和经度)

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(用于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中做过了，但是我不能对数据进行排序，因为我的数据库中没有distance列。这意味着每次位置改变时都要更新数据库，这是对电池和性能的浪费。因此，如果有人想用不在数据库中的列对游标进行排序，我也很感激!

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?

顺便说一下，我找到了另一种方法:查询获取基于SQLite中的Radius的记录?

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个新的列，但有没有其他不那么多余的方法呢?

7 个解决方案

#1

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 threshold and more accurate filter on data, It is better to calculate 4 locations that are in radius meter of the north, west, east and south of your central point in your java code and then check easily by less than and more than SQL operators (>, <) to determine if your points in database are in that rectangle or not.

有一个确定的阈值和更精确的过滤数据,最好是4的位置半径计算计的北方,西方,东部和南部的中心点在java代码中,然后检查容易被不到超过SQL操作符(>、<)来确定你点数据库是否在矩形。

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 reliablePointF 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_X is the name of the column in the database that stores latitude values and COL_Y is 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 reference and completed it.

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

#2

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网格引用)时才会有效。如果在lat/lng中使用度数(如WGS84)，则上述方法仅适用于赤道。在其他纬度，您需要减少经度对排序顺序的影响。(想象你离北极很近……纬度的度数和任何地方的度数一样，但经度的度数可能只有几英尺。这将意味着排序顺序不正确)。

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

如果你不在赤道，根据你当前的纬度，预先计算出“fubridge -factor”:

<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_COLUMN)*( <液化天然气> - LNG_COLUMN)* <软糖> )

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

这仍然只是一个近似值，但比第一个要好得多，所以排序不准确的情况会更少。

#3

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.

虽然我很乐意在设备上执行haversine函数来计算用户当前位置与任何特定目标位置之间的精确距离，但是需要根据距离对查询结果进行排序和限制。

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.

不太满意的解决方案是返回lot和sort和filter，但这会导致第二个游标和许多不必要的结果被返回和丢弃。

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.

没有必要做完全的haversine只是为了排序，没有必要对结果进行平方根，因此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度而增加的不准确性。

#4

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

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

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

另一个类似领域的*问题:查找最近的点到给定的点

#5

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

为了尽可能提高性能，我建议用以下的顺序逐句改进@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^2 as 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列在数据库。填充它，将您的实体插入到数据库中。这在提取大量数据时稍微提高了性能。

#6

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()函数，这可以避免跳过其他答案中的所有限制。

#7

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!

希望会有帮助!