直接SQLite中计算两点之间的距离 [英] Calculate distance between two points directly in SQLite

问题描述

在我的Web / MySQL的应用程序，我有这样的事情来获得两点之间的距离：

In my web/MySQL application I have something like this to get distance between two points:

6371 * acos(cos(radians(-19.83996)) * cos(radians(lat)) * cos(radians(-43.94910) - radians(lng)) + sin(radians(-19.83996)) * sin(radians(lat)))

不过，我在SQLite的这些数学函数测试（ACOS，COS，弧度，罪）不存在。有没有为我直接计算在数据库中的距离相当于什么？

But I tested in SQLite and these mathematical functions (acos, cos, radians, sin) do not exist. Is there something equivalent for me to calculate the distance directly in the database?

不过，我有一个使用这种方法计算一个iPhone应用程序。作品完美，但现在我需要在Android应用程序在数据库中执行此相同的搜索。

However, I have an iPhone application that uses this method to calculate. Works perfectly, but now I need to perform this same search in the database in an Android application.

在此先感谢。

更新

我有9000点来计算距离，并​​获得一个给定的点5附近的位置。

I have 9000 points to calculate distance and obtain the 5 nearby locations of a given point.

推荐答案

下面是我会做什么：

把你给点。测量它周围的（也就是被细化为任意值）〜2公里宽阔的广场，并采取价值为东/西/北/南边界。

Take your given point. Measure a (that is an arbitrary value to be refined) ~2km wide square around it and take the values for the east/west/north/south bounds.

请为这个广场中的元素的查询。这个人是很容易，你只需要

Make a query for elements inside this square. This one is easy, you just have to

select * from points where lat between ? and ? and lon between ? and ?

想想你的结果。没有足够的结果（小于5，很明显，但我要说的两倍，可以肯定的），重试有较大的半径。过多（比如，超过100个），再次尝试以较小的半径。

Count your result. Not enough result (less than 5, obviously, but I would say twice that to be sure), retry with a larger radius. Too much (say, more than 100), try again with a smaller radius.

一旦你有足够的，加载它们，请确保您所需要的所有5种元素不仅在XKM宽阔的广场，而且在XKM半径圈（以避免不被previous检测到潜在的更紧密元素近似​​值）。

Once you have enough, load them, make sure all 5 elements you need are not only in the Xkm wide square, but also in the Xkm radius circle (to avoid having a potential closer element not detected by the previous approximation).

仅对如果给定的点是比较接近的，你正在寻找。

Valid only if your given point is relatively close to those you are searching.

测量平坦地球的局部逼近。接近你的时候，你可以考虑纬度，经度和距离之间的线性关系。这可让您的要求进行排序通过简单的微积分。 （乘法和加法）。同样，为了SQLite的请求后做出正确的计算选择多一点分。

Measure a local approximation of a flat earth. Close to your point, you can consider a linear relation between lat, lon, and distance. That allows you to make a request sorted by a simple calculus. (multiplication and addition). Again, select a little more points in order to make the proper calculation after the SQLite request.

这篇关于直接SQLite中计算两点之间的距离的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！