0

For the purposes of my application, i am using google maps so that: A user will mark at the map the place where he stays (it is stored to the db). Then when he views the map again, it should show other users who stay near to him.

The coordinates are stored at a db field of users named eg coords, at this form: 22.515578,33.265897 (latitude,longitude)

What should be the query so that to find all marks which are +/- 1 (or any other distance) from him?? (Eg if he is at 22.515578,33.265897 then it should search for others which are eg at 21.515578,32.265897 or 23.515578,34.265897)

Thanks in advance!

Man_E
  • 71
  • 2
  • 9
  • Interesting question. Some Googling turned up this answer which seems pretty good. http://www.scribd.com/doc/2569355/Geo-Distance-Search-with-MySQL – alexn Feb 12 '11 at 16:00
  • i recommend separating lat and long into their own columns, makes things easier. – Galen Feb 12 '11 at 19:45

2 Answers2

1

This can help you.

For example here let given latitude and longitude is 32, -122 and distance is less then 25 miles.

SELECT id, ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * sin( radians( lat ) ) ) ) AS distance 
FROM geocodeTable HAVING distance < 25 ORDER BY distance LIMIT 0 , 20;
Gaurav
  • 28,447
  • 8
  • 50
  • 80
0

Don't reinvent the wheel.If you are using mysql try the spatial extensions and the spatial functions between geometries(i.e. distance).if you try to do this manually than you must go another way.Cheers

Community
  • 1
  • 1
Argiropoulos Stavros
  • 9,436
  • 11
  • 61
  • 79
  • S'euxaristw.. :) exw vrei vevaia mia prosorini lisi alli auti dn sximatizei kuklo gurw apo emena(an kai auto thelw) alla tetragwno.. – Man_E Mar 16 '11 at 21:42