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

this question has answer here:

• mysql great circle distance (haversine formula) 8 answers

i don't have sql experience , don't have clue go here, people ask question using ms server or something, , can use bunch of parameters/values can't. i'm using mysql.

here's code in java use create random location within x miles.

public static geoposition randomlocation(geoposition location, double radius) {         random random = new random();          // convert radius miles meters         double meters = radius * 1609.34;          // convert radius meters 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 x-coordinate shrinking of 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) stored in database. need figure out how of rows in database within radius of x miles.

when writing out in java, distancebetween method looked 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 meters         distance = distance * 0.8684; // convert 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 had loop through collection find of positions, distancebetween true. seems more work on client needs done.

is there proper way handle returning results within x miles of latitude/longitude database? because returning every result server going become bandwidth killer fast.

question marked php well, because that's i'm going using poll database.

in place moved distance calculation mysql side. although code complex, haversine formula can calculated, not. here can see mysql code examples calculation, doesn't need stored function.

unfortunately, simple formula complex indexable mysql. solution solve bandwidth killing problem, maybe overload mysql. depend on query rate , number of positions.

in case of mysql overload suggest ask in new question.

there stackexchange site geographic information systems here.