Searching a MySQL database for values within radius (lat/long)

Question

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.

If you do it on the client side seems like all rows need to be passed down, why not go the other way and calculate the rows on the server and just pass those down? — DCR, Apr 25 '15 at 21:35
@DCR - "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." — Hobbyist, Apr 25 '15 at 22:33
The question to find nearest points in a database to a lat/long coordinate comes up quite often on Stack Overflow - the solution is to do it in a MySQL query. [Try this search](http://stackoverflow.com/search?q=mysql+haversine). — halfer, Apr 25 '15 at 22:51
I see you have taken the assistance rendered on this post and asked a new question - great! However, bear in mind that help in the comments, or an identified duplicate, all represents volunteer time taken to help you. If you could take the time to respond to that help in comments, it is generally appreciated - we are a community here, after all. I've downvoted, with apologies. — halfer, Apr 26 '15 at 08:17
@halfer - I'm sorry, I was under the impression that marking the answer as duplicate locked the question. My apologies. — Hobbyist, Apr 26 '15 at 14:48
That's okay, no problem. Comments are actually welcome on on-hold questions - for example a comment can explain to a poster how a closed question can be improved, and this sometimes results in people voting to reopen it. — halfer, Apr 26 '15 at 17:13

score 1 · Accepted Answer · edited May 23 '17 at 12:06

1

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.

edited May 23 '17 at 12:06

Community

1
1

answered Apr 25 '15 at 21:48

peterh

11,875
18
85
108

My problem is calculating the radius in which to search for the coordinates. If I could calculate the radius and get the 4 corners (long/lat) then I could easily write a BETWEEN query, but that's what I'm having problems with. – Hobbyist Apr 25 '15 at 22:35
@Christian.tucker No, it is not your problem. Your problem is: "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." This is I wrote this answer for. – peterh Apr 25 '15 at 22:38
Peter, you can do nearest coordinate discovery using the Haversine formula within a lat/long system (see the link I've put in the question comments). However your answer is sometimes uses as a prior optimisation step, as it is much cheaper than the trig functions. – halfer Apr 25 '15 at 22:55
@Christian.tucker Well, seems you have right. I am working on my answer. – peterh Apr 25 '15 at 23:09
@halfer Thank you very much your info about the formula! But I think, his problem is more complex, practically he wants a fast and simple prefilter in the mysql angular data, which he could later refine in the client app. – peterh Apr 25 '15 at 23:16
@Christian.tucker What now? Is it already okay? – peterh Apr 25 '15 at 23:30
1

@peterh - Thank you for the update, it has helped a lot. – Hobbyist Apr 26 '15 at 14:49

Searching a MySQL database for values within radius (lat/long)

1 Answers1