2

I'm doing some compare of prices in same location based on GPS coordinates. So e.g. I've got an item with coordinates:

lat: 45.815005 
lng: 15.978501

I want to search in my MySQL database for each items which are e.g. 500 meters around that place. (it doesn't need to be circle, just 500 meters on X and 500 meters on Y both ways). Lat and lng are stored as a separate columns type float(10,6) in my DB

I am aware of the fact it's not easy to calculate exact lng and lat but I'm fine if I miss few meters each site.

This is pretty complex question but I would be thankful for any advise which will kickoff my start.

Andurit
  • 5,612
  • 14
  • 69
  • 121
  • 1
    What have already tried and/or researched? – PM 77-1 Oct 20 '17 at 17:16
  • How will it not be a circle? I think you are looking for haversine formula. – chris85 Oct 20 '17 at 17:16
  • It could be a square. But then the actual distance is hypotenuse of a 500m triangle and not 500. – Andreas Oct 20 '17 at 17:18
  • If it is a square than you won't have a radius. – chris85 Oct 20 '17 at 17:20
  • Lol. Good one :-) didn't notice the radius. But that is only mentioned in topic as far as I can see. – Andreas Oct 20 '17 at 17:22
  • How many points do you need to check? 5 ways to solve the Question: mysql.rjweb.org/doc.php/find_nearest_in_mysql Computing distance is not the real problem; avoiding doing it a million times is the real answer. Two of the ways do a good job of avoiding a million computations. – Rick James Jan 22 '20 at 21:35

1 Answers1

7

Calculating the distance between two coordinates isn't actually that difficult given the haversine formula.

SELECT 
  -- stuff here
  , ( 6371000 * acos( cos( radians(45.815005) ) * cos( radians( stuff.lat ) ) * cos( radians( stuff.lng ) - radians(15.978501) ) + sin( radians(45.815005) ) * sin(radians(stuff.lat)) ) ) AS distance 
FROM 
  stuff
HAVING 
  distance < 500

Referenced Answer

Necessary changes from the original answer:

  1. The constant offered in the original answer supplied the values for miles or kilometers. I've changed the constant here to work with meters.

  2. The constants have changed to use your coordinates. You might want to adapt the query a little further to make those parameters instead of constants.

  3. The having expression changed a little to reflect your desire for 500 meters. Again, this might be something you want to parameterize.

Jacobm001
  • 4,431
  • 4
  • 30
  • 51