0

I have table User that contains amoung others collumns, latitude and longitude (Double). I have another table called Locations that has the same structure (latitude and longitude) (Also double type).

How can I query all the locations that are arround the user on a surface of 10 km in circle?(or a circle with diameter of 10 km).

I've seen that mysql has some Point data type but I never used it so it's very hard to understand.

For example, random coordinates: User (latitude=23.102131, longitude=-21.1231241)

Thank you!

AndreiTiberiu
  • 55
  • 2
  • 6

2 Answers2

0

MySQL has spatial query capabilities. I suggest you read about them.

Assaf Lavie
  • 73,079
  • 34
  • 148
  • 203
0

It looks rather like a popular question on Stack Overflow. For example, you can consult answers to this question: Fastest Way to Find Distance Between Two Lat/Long Points and also answers to questions linked to that question.

Here is basic MySQL query solving your concrete problem using Haversine formula:

SELECT loc.id
FROM `User` u, Locations loc
WHERE u.id = @user_id
  AND (6371 * acos(cos(radians(u.latitude)) * cos(radians(loc.latitude )) * cos(radians(loc.longitude ) - radians(u.longitude)) + sin(radians(u.latitude)) * sin(radians(loc.latitude )))) <= @circle_diameter / 2;

It doesn't use any optimizations, so it may be slow on a large data.

Community
  • 1
  • 1
dened
  • 4,253
  • 18
  • 34
  • I've seen a couple of questions that are asking for similar answears, but I am looking for something with an exact answear. For example, if I have in table user a latitude and a longitude how do I manage to get all locations that are near? exactly like this – AndreiTiberiu Apr 13 '14 at 15:42
  • OK, I have made my answer more specific. – dened Apr 13 '14 at 16:50