java 按最近的纬度和经度坐标使用 SQLITE 进行排序

Ordering with SQLITE by nearest latitude & longitude coordinates

提问by NullPointerException

I must obtain a SQLite SQL Sentence for ordering by nearest latitude and longitude coordinates, given a initial location.

给定初始位置，我必须获得一个 SQLite SQL Sentence，以便按最近的纬度和经度坐标进行排序。

THis is a example sentence for my table in the sqlite database:

这是我在 sqlite 数据库中的表的例句：

SELECT id, name, lat, lng FROM items

EXAMPLE RESULT: 1, Museu, 41375310.0, 2175970.0

I must achieve this with SQLite, and with that table. I can't use another techniques because this is for a existen SQlite database that i can't change.

我必须用 SQLite 和那个表来实现这一点。我无法使用其他技术，因为这是针对我无法更改的现有 SQlite 数据库。

Exists a way to achieve this with Android and SQlite? I checked a lot of stackoverflow posts and i didn't find the way to achieve that

有没有办法用 Android 和 SQlite 实现这一点？我检查了很多stackoverflow帖子，但没有找到实现这一目标的方法

Thanks

谢谢

回答by Darkwater

SELECT * AS distance FROM items ORDER BY ABS(location_lat - lat) + ABS(location_lng - lng) ASC

This should roughly sort the items on distance in MySQL, and shouldwork in SQLite.
If you need to sort them preciser, you could try using the Pythagorean theorem (a^2 + b^2 = c^2) to get the exact distance.

这应该对 MySQL 中的距离项目进行粗略排序，并且应该在 SQLite 中工作。
如果您需要更精确地对它们进行排序，您可以尝试使用勾股定理 (a^2 + b^2 = c^2) 来获得精确的距离。

回答by chris

The answer from Darkwater is nearly correct. To be correct, you need to use the square of the differences. As the square function is not available on SqLite, you need to multiply the differences by themselves. No need to calculate the square roots.

Darkwater的答案几乎是正确的。为了正确，您需要使用差异的平方。由于 Square 函数在 SqlLite 上不可用，您需要自己乘以差异。无需计算平方根。

SELECT * AS distance FROM items ORDER BY ((location_lat-lat)*(location_lat-lat)) + ((location_lng - lng)*(location_lng - lng)) ASC

回答by Jesús Tomás

The solution proposed by Chris:

克里斯提出的解决方案：

SELECT * AS distance FROM items ORDER BY ((location_lat-lat)*(location_lat-lat)) + ((location_lng - lng)*(location_lng - lng)) ASC

SELECT * AS distance FROM items ORDER BY ((location_lat-lat)*(location_lat-lat)) + ((location_lng - lng)*(location_lng - lng)) ASC

is correct when we are close to the equator. For it to work correctly in other latitudes, I propose:

当我们靠近赤道时是正确的。为了让它在其他纬度正常工作，我建议：

SELECT * AS distance FROM items ORDER BY ((location_lat-lat)*(location_lat-lat)) + ((location_lng - lng)*(location_lng - lng)*cos_lat_2) ASC

SELECT * AS distance FROM items ORDER BY ((location_lat-lat)*(location_lat-lat)) + ((location_lng - lng)*(location_lng - lng)* cos_lat_2) ASC

where we must precompute:

我们必须预先计算：

cos_lat_2 = cos(location_lat * PI / 180) ^ 2

THE PROBLEM:

问题：

If we are in the equator and we move one degree in longitude (east or west), we do so on a circumference of 40,000 km, representing a distance of 40.000 / 360. If we move one degree in latitude (north or south), we do so on a circle that crosses both poles, which also involves a distance of 40.000 / 360 (considering that the earth is a sphere).

如果我们在赤道，经度（东或西）移动 1 度，我们在 40,000 公里的圆周上移动，代表 40.000 / 360 的距离。如果我们在纬度（北或南）移动 1 度，我们在一个跨越两极的圆上这样做，这也涉及 40.000 / 360 的距离（考虑到地球是一个球体）。

But, if we are in the south of England, with a latitude of 50°, and we move one degree in longitude (east or west) we do it on the 50th parallel, which has a smaller circumference than the equator. The distance is perimeter_parallel_50 / 360. Calculating this perimeter is simple: perimeter_parallel_50 = cos (50) * 2 * PI * EARHT_RADIUS = 0.64 * 40,000 km. This reduction in distance is not seen if we move one degree south or north. The circumference by which we move still has a perimeter of 40,000 Km.

但是，如果我们在英格兰南部，纬度为 50°，并且我们在经度（东或西）上移动 1 度，我们会在 50 度纬线上进行，它的周长比赤道小。距离是perimeter_parallel_50 / 360。计算这个周长很简单：perimeter_parallel_50 = cos (50) * 2 * PI * EARHT_RADIUS = 0.64 * 40,000 km。如果我们向南或向北移动 1 度，则不会看到这种距离的减少。我们移动的圆周仍然有 40,000 公里的周长。

SOLUTION:

解决方案：

Since location_lat is a value known beforehand, we can pre-calculate the value of cos(location_lat) so that it can be used as a scaling factor so the displacements in longitude and latitude are equivalent. Moreover, we pre square it to avoid having to multiply it twice.

由于 location_lat 是一个事先已知的值，我们可以预先计算 cos(location_lat) 的值，以便将其用作比例因子，从而使经度和纬度的位移相等。此外，我们预先对它进行平方以避免必须将其乘以两次。

NOTE:

笔记：

This is still an approximation, and it will give wrong results when moving big distances, especially near the poles and when crossing the 180th meridian.

这仍然是一个近似值，在大距离移动时会给出错误的结果，尤其是在极地附近和穿过 180 度子午线时。

回答by Paul D'Ambra

If you're able to load the records; convert them to Locations and then use the distanceTo functionI'd recommend that but...

如果您能够加载记录；将它们转换为 Locations 然后使用 distanceTo 函数我建议但是......

You can approximate the distance between two points using plain SQL and the various approaches are laid out clearly here. The further apart the points you are comparing become then your values can become increasingly incorrect if you use a simple calculation

您可以使用简单的 SQL 来估算两点之间的距离，这里清楚地列出了各种方法。您比较的点越远，如果您使用简单的计算，您的值就会变得越来越不正确

If you're calculating these things yourself and your locations can be anywhere then you might need to be aware of values wrapping around if you're comparing locations around the international date line.

如果您自己计算这些东西并且您的位置可以在任何地方，那么如果您比较国际日期变更线附近的位置，您可能需要注意环绕的值。

回答by DanielFo

If you know, that 1 degree of latitude is about 111111 meters and 1 degree of longitude is 111111*cos(latitude) meters, then you can easily get all places inside a specific square.

如果您知道 1 度纬度大约是 111111 米，1 度经度是 111111*cos(纬度) 米，那么您可以轻松获取特定正方形内的所有位置。

SELECT * FROM items WHERE latitude BETWEEN %f AND %f AND longitude BETWEEN %f AND %f

This query is very fast, even with millions of rows. But don't forget to create an index for latitude and longitude:

即使有数百万行，这个查询也非常快。但不要忘记为纬度和经度创建索引：

CREATE INDEX position ON items (latitude, longitude)

I use this in Objective-C to obtain all places of interest that are within 3km around the current location:

我在 Objective-C 中使用它来获取当前位置周围 3 公里范围内的所有景点：

double latDist = 1.0 / 111111.0 * 3.0;
double lonDist = 1.0 / ABS(111111.0*cos(location.coordinate.latitude)) * 3.0;

FMResultSet *results = [database executeQueryWithFormat:@"SELECT * FROM items WHERE latitude BETWEEN %f AND %f AND longitude BETWEEN %f AND %f", location.coordinate.latitude - latDist, location.coordinate.latitude + latDist, location.coordinate.longitude - lonDist, location.coordinate.longitude + lonDist];

You can now calculate the exact distance and sort your results...

您现在可以计算确切的距离并对结果进行排序...