This question already has an answer here:

- MySQL Great Circle Distance (Haversine formula) 9 answers

I don't have all that much SQL experience and honestly don't have a clue where to go from here, most people that ask a question like this are using MS Server or something, and can use a bunch of parameters/values that I can't. I'm using MySQL.

Here's the code in Java I use to create a random location within X miles.

`public static GeoPosition randomLocation(GeoPosition location, double radius) { Random random = new Random(); // Convert radius from miles to meters double meters = radius * 1609.34; // Convert radius from meters to degrees double radiusInDegrees = meters / 111300f; double u = random.nextDouble(); double v = random.nextDouble(); double w = radiusInDegrees * Math.sqrt(u); double t = 2 * Math.PI * v; double x = w * Math.cos(t); double y = w * Math.sin(t); // Adjust the x-coordinate for the shrinking of the east-west distances double new_x = x / Math.cos(location.latitude()); double foundLongitude = new_x + location.longitude(); double foundLatitude = y + location.latitude(); return new GeoPosition(foundLongitude, foundLatitude); } `

These GeoPositions (Groups of longitude/latitude) are stored in the database. However now I need to figure out how to get all of the rows in the database within a radius of X miles.

When writing this out in Java, the distanceBetween method looked like this:

`public static double distanceBetween(GeoPosition a, GeoPosition b) { double longDif = a.longitude() - b.longitude(); double distance = Math.sin(deg2rad(a.latitude())) * Math.sin(deg2rad(b.latitude())) + Math.cos(deg2rad(a.latitude())) * Math.cos(deg2rad(b.latitude())) * Math.cos(deg2rad(longDif)); distance = Math.acos(distance); distance = rad2deg(distance); distance = distance * 60 * 1.1515; // Convert to meters distance = distance * 0.8684; // Convert to miles. return distance; } private static double rad2deg(double rad) { return (rad * 180.0 / Math.PI); } private static double deg2rad(double deg) { return (deg * Math.PI / 180.0); } `

and I just had to loop through a collection to find all of the positions, where `distanceBetween`

was `true`

. However this seems like more work on the client that needs to be done.

Is there a proper way to handle returning all results within X miles of a latitude/longitude from the database? because returning every result from the server is going to become a bandwidth killer really fast.

Question marked as PHP as well, because that's what I'm going to be using to poll the database.

-------------Problems Reply------------

In your place I simply moved the distance calculation to the mysql side. Although your code is complex, the Haversine formula with it can be calculated, is not. Here you can see also mysql code examples for the calculation, it doesn't even need a stored function.

Unfortunately, this simple formula is already too complex to be indexable from mysql. This solution will solve your bandwidth killing problem, but maybe it will overload your mysql. It will depend on your query rate and the number of your positions.

In case of a mysql overload I suggest to ask this in a new question.

There is a stackexchange site for geographic information systems here.