没有三角函数的SQL距离查询 [英] SQL Distance Query without Trigonometry

问题描述

我有一个SQLite数据库，它不支持trig函数。我想按照距离排列一系列经纬度相对于第二个经纬度的经纬度。我熟悉用于按距离排序lat，lng对的标准haversine距离公式。

在这种情况下，我不特别关心精确度，我的点之间有很大的距离，所以我不介意将曲线视为直线行。

我的问题是，这种查询有一个公认的公式吗？记住没有三角函数！

解决方案

如果你的观点在彼此的合理距离内（即不跨越半个世界，在日期线上），你可以对经度和纬度之间的差异进行修正（因为经度的程度比较短，除了在赤道），然后就好像地球是平的一样计算距离。

由于您只需要对值进行排序，您甚至不必使用平方根，只需添加差异的平方。

示例，其中 @lat 和 @lng 是您当前的位置，<$ c $

  select * 
 from Points 
 order by（lat  -  @lat）*（lat  -  @lat）+（（lng  -  @ng）* 2）*（（lng  -  @ng）* 2）
  

您可以计算特定纬度的差异修正为 1 / cos（lat） p>

---

  pow（lat-lat2，2）这个公式也适用于日期线： + pow（2 * min（abs（lon-lon2），360  -  abs（lon-lon2）），2）
  

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.

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)

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

---

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屋！