不使用三角函数的 SQL 距离查询 [英] SQL Distance Query without Trigonometry

问题描述

我有一个不支持三角函数的 SQLite 数据库.与第二个 lat,lng 对相比，我想按距离对表中的一组 lat,lng 对进行排序.我熟悉按距离对 lat、lng 对进行排序的标准半正弦距离公式.

I have an SQLite database, which does not support trig functions. I would like to sort a set of lat,lng pairs in my table by distance as compared to a second lat,lng pair. I'm familiar with the standard haversine distance formula for sorting lat,lng pairs by distance.

在这种情况下，我并不特别在意精度，我的点之间的距离很远，所以我不介意通过将曲线视为直线来四舍五入距离.

In this case I don't care particularly for precision, my points are separated by large distances, so I don't mind rounding off the distances by treating curves as straight lines.

我的问题，这种查询是否有一个普遍接受的公式?记住不要使用三角函数！

My question, is there a generally accepted formula for this kind of query? Remember no trig functions!

推荐答案

如果您的点之间的距离在合理的范围内(即不跨越半个世界，也不跨越日期线)，您可以对纬度和经度之间的差(因为经度更短，除了赤道)，然后就像地球是平的一样计算距离.

If your points are within reasonable distance of each other (i.e. not across half the world, and not across the date line), you can make a correction for the difference between latitude and longitude (as a longitude degree is shorter, except at the Equator), and then just calculate the distance as if the earth was flat.

由于您只想对值进行排序，因此您甚至不必使用平方根，只需将差异的平方相加即可.

As you just want to sort the values, you don't even have to use the square root, you can just add the squares of the differences.

例如，@lat 和 @lng 是你当前的位置，2 是差值修正:

Example, where @lat and @lng is your current position, and 2 is the difference correction:

select *
from Points
order by (lat - @lat) * (lat - @lat) + ((lng - @lng) * 2) * ((lng - @lng) * 2)

您可以将特定纬度的差异校正计算为 1/cos(lat).

You can calculate the difference correction for a specific latitude as 1 / cos(lat).

Cees Timmerman 提出了这个公式，它也适用于日期变更线:

Cees Timmerman came up with this formula which also works across the date line:

pow(lat-lat2, 2) + pow(2 * min(abs(lon-lon2), 360 - abs(lon-lon2)), 2)

这篇关于不使用三角函数的 SQL 距离查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！