Hi all,
I've been racking my brains over this for a few hours, with no joy.

Basically, I have a MySQL database that stores a series of latitude/longitude coordinates in two fields (lat and long). I need to be able to search the database and retrieve records that are within a given distance of a lat/long pair that is specified at search using PHP.

I have managed to retrieve everything that is within a set distance east/west and north/south, the only problem is I can only figure out how to get data in a square (ie lat + distance, long + distance), which means that the corners are about 41% too far, but I cant think of any way to get only the data that is within the circle.

I hope that I have managed to explain it well enough, and any help would be really appreciated,
Chris

Since you have restricted your return set to pints that are bounded by a square, I guess that you need to add one more condition to your existing conditions restricting the set of points to lie within the circle that is bounded by the square.

Use the formula a*a + b*b <= r*r, where a is the latitudinal difference, b is the longitudinal difference and r is the given distance.

1 members found this post helpful.

Excellent, thanks graemef. Is it possible to use this in a MySQL SELECT statement, or can it only be done on the returned set?

You should be able to squeeze it into the Select statement, just as you had with the other parts of the Where clause.

Cool, I'll try that. Thanks again.

I would compute the r*r once and feed it into the query rather than recomputing it for every member of the set that is being examined (I rather doubt the SELECT statement will optimize to handle this).

Also, be aware that the accuracy of this approach will vary considerably with latitude because your lat-long "square" isn't a square, it is a trapezoid. The longitudinal distances between whole degree measurements vary from about 69.5 miles (114.4 KM) at the equator to zero at either pole, while the latitude distance is constant at about 69.5 miles. At a latitude of 45 degrees, the longitudinal distance will be about 49.1 miles (80.9 KM)