SQLite的获取最近的位置（经纬度） [英] SQlite Getting nearest locations (with latitude and longitude)
问题描述
我有经度和纬度保存在我的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.).
我知道这可能是在MySQL中，我已经做了很长一段的研究，SQLite的需要自定义外部函数的半正矢公式（球体上的计算距离），但我还没有发现，是用什么Java和作品。
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.
另外，如果我想添加自定义的功能，我需要的 org.sqlite 的.jar（对于 org.sqlite.Function ），并且增加了不必要的大小的应用程序。
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.
这样做的另一面是，我需要整理从SQL函数，因为单独显示的距离不是太大的问题 - 我已经做到了我的自定义SimpleCursorAdapter，但我不能对数据进行排序，因为我没有在我的数据库中的距离栏。这将意味着，每次更新数据库的位置变化，这是一个废电池和性能。因此，如果有人对排序光标列，这不是在数据库中的任何想法，我会感激不尽呢！
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!
我知道有吨的Android应用程序，在那里，使用此功能，但可有人请解释的神奇。
I know there are tons of Android apps out there that use this function, but can someone please explain the magic.
顺便说一句，我发现这个替代方案：查询来获取SQLite中基于RADIUS记录
By the way, I found this alternative: Query to get records based on Radius in SQLite?
它的提示，使4个新列COS和土地增值税和液化天然气罪恶值，但还有其他的，没有那么多余的办法吗？
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?
推荐答案
1）首先筛选的SQLite的数据有较好的近似，减少你需要在你的java $评估数据量C $℃。使用下面的过程用于此目的：
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:
要具有确定性的阈值和更准确的数据过滤器，这是更好地计算出的 4的位置是在半径米的北，西，在你的java code东部的中心点和南部的的，然后按比SQL运算符（>，＆LT不足，更容易检查;）以确定是否在数据库中的点是在矩形或不
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.
方法 calculateDerivedPosition（...）计算这些点对你（P1，P2，P3，P4的图片）。
The method calculateDerivedPosition(...) calculates those points for you (p1, p2, p3, p4 in picture).
/**
* 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_X 是在数据库中的列存储纬度值和 COL_Y 是经度的名字。
COL_X is the name of the column in database that stores latitude values and COL_Y is for longitude.
所以，你有一些数据是靠近你的中心点，具有良好的近似。
So you have some data that are near your central point with a good approximation.
2）现在你可以循环对这些过滤数据，并确定它们是否真的靠近你点（圆），或者不使用以下方法：
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:
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;
}
享受！
我曾经和定制这个参考并完成它。
I used and customized this reference and completed it.
这篇关于SQLite的获取最近的位置（经纬度）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
