8

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!

Harry
  • 3,031
  • 7
  • 42
  • 67
Amir
  • 199
  • 2
  • 2
  • 7
  • 1
    Possible duplicate: http://stackoverflow.com/questions/1006654/fastest-distance-lookup-given-latitude-longitude – JK. May 12 '12 at 14:36
  • How accurate does the 10km need to be? The correct approach involves a fair amount of math that is very slow to perform in SQL. It may be better to either retrieve a slightly larger set and pass that to the client for the final work, or to use an approximate set. – David Manheim Dec 25 '14 at 00:13

4 Answers4

6

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/

David Manheim
  • 2,553
  • 2
  • 27
  • 42
1

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.

jperelli
  • 6,988
  • 5
  • 50
  • 85
0

Hope this simple query helps

SELECT 
    *
FROM 
    `locator`
WHERE
    SQRT(POW(X(`center`) - 49.843317 , 2) + POW(Y(`center`) - 24.026642, 2)) * 100 < `radius`

details here http://dexxtr.com/post/83498801191/how-to-determine-point-inside-circle-using-mysql

dexxtr
  • 478
  • 1
  • 5
  • 8
  • 1
    This doesn't work. The problem is that the units vary; 1 degree of latitude/longitude is a different number of kilometers depending on the latitude and longitude entered. – David Manheim Dec 25 '14 at 00:10
0

You have to use st_distance_sphere, which will interpret the two points given as geographic points that is lat/lng instead of two points in a two-dimensional coordinate system.

The result is in meters. 

mysql> SET @pt1 = ST_GeomFromText('POINT(0 0)');
mysql> SET @pt2 = ST_GeomFromText('POINT(180 0)');
mysql> SELECT ST_Distance_Sphere(@pt1, @pt2);
+--------------------------------+
| ST_Distance_Sphere(@pt1, @pt2) |
+--------------------------------+
|             20015042.813723423 |

The result is in meters.

Here is the documentation: enter link description here

user3017361
  • 1