can i MySQL select where row's co-ords are < x miles from target coords? (mysql proximity search)

Question

There's a few questions like this on Stack Overflow but because i can't find any quite similar enough for me to follow.

I'm looking to select rows from db which are within x miles of a target.

example table columns:

| city_name(varchar) | lat(decimal) | lon(decimal) |

query effect i'm looking for (either using php or mysql)

$target_lat = ;
$target_lon = ;
"SELECT table.city_name WHERE (table.lat,table.lon) LESS THAN 20 MILES FROM ($target_lat,$target_lon")

obviously i don't expect query to take this structure! (i've never used Pi etc in mysql...)

Answer 1

you will find many code snippets in google searching for "mysql radius search"

they are a little bit too long for copying here.

Edit: http://spinczyk.net/blog/2009/10/04/radius-search-with-google-maps-and-mysql/

Answer 2

It sounds like you're looking for a geo-spatial package for mySql. There's a function listed here that allows for distance checking between two lat/long points. Just remember that a regular distance calculation won't work with lat/long because of the Earth's curvature. =)

http://forge.mysql.com/wiki/GIS_Functions

Answer 3

The Pythagorean theorem states that the square of the length of the hypotenuse of a right triangle is equal to the sum of the squares of the other two sides.

C^2 = A^2 + B^2
C = SQRT(A^2 + B^2)

In this case, the lengths of the other sides are determined by the difference of your lat and long values. Over short distances this will give you a pretty good approximation. However, this still doesn't give you miles, which is a much more difficult conversion since there is not a fixed relationship between lat/long and miles. Instead, the distance between longitudes varies as a function of latitude.

Answer 4

I found this which is brilliant and really easy to understand

http://code.google.com/apis/maps/articles/phpsqlsearch.html