note: this is more of a note to myself than anything
To do latitude/longitude radius from a point in mysql, first you need to have a table with latitude/longitude pairs for items to look up. You can find various databases online with geocoded data.

Now lets assume we are on a spot near Union Square in New York.
its coded location is: Latitude: 40.7383040 and Longitude: -73.99319

We assume the following distances in relation to our earth’s radius (R)
6378137 meters, 6378.137 km, 3963.191 miles, 3441.596 nautical miles
We will use these in our computation for distance from point if we want to use miles, kilo’s or meters from our starting point, if you really wanted to get crazy then 6378137 meters = 20925646.3 feet so you could literally search for something within several hundred feet of yourself.

We use these units in the following SQL to determine how we want to determine our distance from point of origin, so using R = 3963.191 to give us our distance back in miles. For this example we want to see what is 1.5 miles from this point in our database.

note:PI = 3.141592653589793, mysql’s pi() function returns 3.141593 so if you need finer grain granularity then use the above constant, latitude and longitude are the names of fields in my database in the query below


select asciiname,latitude,longitude, acos(SIN( PI()* 40.7383040 /180 )*SIN( PI()*latitude/180 )
)+(cos(PI()* 40.7383040 /180)*COS( PI()*latitude/180) *COS(PI()*longitude/180-PI()* -73.99319 /180)
)* 3963.191 AS distance
FROM allcountries
WHERE 1=1
AND 3963.191 * ACOS( (SIN(PI()* 40.7383040 /180)*SIN(PI() * latitude/180)) +
(COS(PI()* 40.7383040 /180)*cos(PI()*latitude/180)*COS(PI() * longitude/180-PI()* -73.99319 /180))
) < = 1.5
ORDER BY 3963.191 * ACOS(
(SIN(PI()* 40.7383040 /180)*SIN(PI()*latitude/180)) +
(COS(PI()* 40.7383040 /180)*cos(PI()*latitude/180)*COS(PI() * longitude/180-PI()* -73.99319 /180))
)

This can be used over the results of a sub-query as well, so if you have a huge dataset you can search for a squared area by just adding and subtracting from both longitude and latitude taking that result set and running this query over it. The query give back results from closest to furthest.

In tests over 6.2M records on my machine here, this took 21.3 seconds to complete without subquery first and 0.05 seconds after subquery over the 6.2M records. latitude and longitude being indexed.

[ spread the love ]