mysql-选择彼此之间相距较近的记录 [英] mysql - select records with near distance from each other
问题描述
我有一个包含位置的mysql表,例如:
I have a mysql table containing locations, for example:
ID latitude longitude value
1 11.11111 22.22222 1
2 33.33333 44.44444 2
3 11.11112 22.22223 5
我想选择彼此靠近的记录(在上面的示例中,第1行和第3行),以便可以将它们作为一个记录插入到新表中.说近就说100米.我希望新表是:
I want to select records which are located near to each other (in the above example, rows 1 and 3), so that I can insert them in a new table as one record. By saying near, let's say 100 meters. I would like the new table to be:
ID latitude longitude value
1 11.11111 22.22222 3
2 33.33333 44.44444 2
如您所见,我想保留第一条记录的坐标,并在值"列中插入两条记录的平均值.
As you can see, I would like to keep the coordinates of the first record and insert an average of the two records in column 'value'.
我用于距离的公式如下:
The formula I use for distance is the following:
R*SQRT(((lon2*c-lon1*c)*cos(0.5*(lat2*c+lat1*c)))^2 + (lat2*c-lat1*c)^2)
其中
[lat1, lon1] the coordinates of the first location,
[lat2, lon2] the coordinates of the second location,
[R] the average radius of Earth in meters,
[c] a number to convert degrees to radians.
该公式适用于短距离.
所以,我的问题不是lat,lon到距离的转换,而是我的SQL.我知道如何选择与特定纬度,经度坐标之间最大距离为100米的记录,但是我不知道如何选择彼此之间最大距离的记录.
So, my problem is not the conversion of lat,lon to distances but my SQL. I know how to select records that have a maximum distance of 100 meters from specific lat,lon coordinates but I dont know how to select records with a maximum distance from each other.
我做到这一点的一种方法(它确实有效)是通过在PHP中逐个循环记录,然后对每个记录进行循环,通过SQL查询选择附近的记录.但是通过这种方式,我循环执行了一个SQL查询,据我所知,这是一种不好的做法,尤其是当记录要成千上万的时候.
One way I did it -and it works- is by looping the records one by one in PHP and for each one, making an SQL query to select records that are near. But this way, I do an SQL query in a loop and as far as I know, this is a bad practice, especially if the records are gonna be thousands.
我希望我很清楚.如果没有,我很乐意为您提供更多信息.
I hope I was clear. If not, I would be glad to give you additional information.
感谢您的帮助.
推荐答案
下面是一条SQL,用于获取该范围内的所有位置:
Here is a SQL to get all places within the range:
SELECT
ID,
latitude,
longitude,
(6371 * acos (cos( radians(origin.latitude)) * cos( radians( destination.latitude ))
* cos( radians(destination.longitude) - radians(origin.longitude)) + sin(radians(origin.latitude))
* sin( radians(destination.latitude)))) AS distance
FROM myTable as destination, myTable as origin
WHERE destination.id = myId
HAVING distance < 100 --some value in kilometers
6371是千米的常数.3959是常数.
6371 is a constant for kilometers. 3959 is a constant for miles.
本主题有更多答案: MySQL大圆距离(Haversine公式)
这篇关于mysql-选择彼此之间相距较近的记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!