SQL Select Radius Search based on Latitude Longitude

Question

I got 2 tables:

Events:
 - id
 - name
 - place

Places:
 - id
 - name
 - lat
 - lng

I would like to retrieve all events that in 10KM radius (based on the place lat & lng) from the current lat and lng. How can I do that?

Thanks!

Answer 1

If you are willing to use an extension, the geospatial extension, in MySQL 5.6 and on, is intended to address exactly this type of question. You will need to build a spatial index on your places table:

ALTER TABLE places ADD SPATIAL INDEX lat, lng

select name from places
order by st_distance(point(@lng, @lat), point(lng, lat))
limit 10

The actual finding of actual distances is a bit computation heavy. The following post lays out some of the methods you might want to try: http://www.plumislandmedia.net/mysql/using-mysqls-geospatial-extension-location-finder/

For even more detail, look at http://www.percona.com/blog/2013/10/21/using-the-new-spatial-functions-in-mysql-5-6-for-geo-enabled-applications/

Answer 2

Maybe this helps you http://www.scribd.com/doc/2569355/Geo-Distance-Search-with-MySQL

Basically the problem is that lat/lng are spherical coordinates (lat and lng are angles), and you want to make a search using a linear distance over the spherical surface. Then, you are trying to compare two different things, I mean, angles with kilometers. To do it, you must make a coordinate transformation and then compare.