0

I must query some objects that are close to a city. They must be sorted according to the user's location.

So I decided to write a SQL query which uses two times the Pythagoras formula.

It seems to get the good objects (close to the city), but it fails in sorting them correctly according to the user's location.

Do you know if I correctly use SQL or if it's the formula which goes wrong?

Data

In the following query...

  • distance stores the distance between the objects and the city.

  • distance_2 stores the distance between the objects and the user's location.

  • lat and lon are the coordinates of the objects. The query is indeed executed in the table of the objects.

User's location

  • 47.643310
  • -2.805655

City's location

  • 48.51666700

  • -2.78333300

Found objects' location

Closest-to-the-user object

  • 47,6593484

  • -2,7546085

Farest-to-the-user object

  • 48,4962385

  • -2,7642788

Query

SELECT *, 

SQRT((48.51666700 - lat) * (48.51666700 - lat) + ((-2.78333300 - lon) * COS(RADIANS(lat))) * ((-2.78333300 - lon) * COS(RADIANS(lat)))) * 111.325 AS distance,

SQRT((47.64331000 - lat) * (47.64331000 - lat) + ((-2.80565500 - lon) * COS(RADIANS(lat))) * ((-2.80565500 - lon) * COS(RADIANS(lat)))) * 111.325 AS distance_2

FROM restaurants WHERE 1=1 AND visible = 1 HAVING distance < 200 ORDER BY distance_2 ASC

Order of showing

The farest object is displayed before the closest, which isn't normal (see next part).

JarsOfJam-Scheduler
  • 2,809
  • 3
  • 31
  • 70
  • I'm expecting the Pythagorean formula to have squares in it. – Gordon Linoff Jan 22 '19 at 16:55
  • Shouldn't the distance formula be like `sqrt((48.51666700 - lat) * (48.51666700 - lat) + (-2.78333300 - lon) * (-2.78333300 - lon)) as distance` ? Cosinus has nothing to do with it, unless you were using polar coordinates or something like that. – The Impaler Jan 22 '19 at 16:55
  • 1
    Isn't it the Harversine formula? – JarsOfJam-Scheduler Jan 22 '19 at 16:56
  • Plus: normally you should have the same problem than me if you execute my query with the data I provided – JarsOfJam-Scheduler Jan 22 '19 at 16:58
  • The distance west-east depends on the latitude (bigger distance at the equator, approaches 0 to the poles) so this part is fine at least. What I don't get is that distances for your closest object are bigger and I don't see why you still call that closest. At the very least, I would say the sorting is fine in the db – FXD Jan 22 '19 at 16:59
  • So based on the distances you had given, this is not an SQL sorting issue. The calculations are wrong and I cannot find out where it is coming from. You need to check each part of your formulae separately to see if they return what you expect. Editing your question to hide details from the people willing to help you will lead you nowhere ... – FXD Jan 22 '19 at 18:00

1 Answers1

1

For greater accuracy (distances 20 miles/32 kilometers), I'd suggest looking at using a Great Circle distance calculation. We live on a globe! There's a nice example at the link below from Stack Overflow. This is the Haversine formula JarsofJam alluded to. The author started with PHP but asked how to convert it to pure MySQL. I believe you could, with little trouble, adapt the accepted answer to your needs.

MySql Great Circle distance calculation

Do take note the proper conversion to use for the Earth's radius depending on whether you're working in kilometers or miles.

markaaronky
  • 1,231
  • 12
  • 29