mysql - select records with near distance from each other

Question

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

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)

where

[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.

The formula works well for short distances.

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.

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.

Thanks for helping.

Felippe Duarte Felippe Duarte · Accepted Answer · 2017-04-27T20:33:37

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 is a constant for kilometers. 3959 is a constant for miles.

This topic have more answers: MySQL Great Circle Distance (Haversine formula)

mysql - select records with near distance from each other

1 Answers