Project: I have a MySQL database of alumni home address locations stored as lng/lat. I want to be able to identify groups of 15 members or more of alumni that live within a 15 mile radius of each other.
Given a lng/lat, how do I select the other lng/lat locations that are within a 15 mile radius? I've installed on Linux the PHP geonames package from PEAR. It isn't clear to me if I can use this package for aid in this function. It contains radius routines, but they appear to be for returning information such as postal codes within a lng/lat radius. Thanks!
You'll need to look into using the Haversine Formula. You'll find plenty of online examples showing you how to implement the formula in both PHP and SQL.
Google has a nice example for MySQL:
SELECT id, ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * sin( radians( lat ) ) ) ) AS distance
FROM markers HAVING distance < 25 ORDER BY distance LIMIT 0 , 20;
Wayne's answer is interesting, but I wonder whether over 15 miles the spherical component can be ignored, using trig instead? Pseudocode:
For every (x1, y1) row:
Loop through every (x2, y2):
Distance = SQRT ( ( x2 - x1 ) ^ 2 + ( y2 - y1 ) ^ 2 )
If (Distance <= 15)
// ...
End If
End
End
Or a SQL version (not sure I've got powers correct, but you get the idea):
SELECT
SQRT ( ( a2.x - a1.x ) ^ 2 + ( a2.y - a1.y ) ^ 2 ) AS distance
FROM alumni a1, alumni a2
WHERE a1.id != a2.id
HAVING distance <= 15;
Thinking about it, the 15 miles will have to be converted from lat/long.