CODEWITHSUNDEEP
mysqlprecisiondistancelatitude-longitude
JarsOfJam-Scheduler
JarsOfJam-Scheduler

Reputation: 3149

How to get more precision for a computed distance in MySQL, without using geometric types?

I must compute the distance between an object (a city) and each of the several entries from a MySQL table I have (some restaurants). This city and the restaurants are located in a same country.

The computed distance is used in order to show all the restaurants which are close to this city ; the threshold distance is arbitrary. Moreover, this is a ranked list: the closest restaurants are shown first, and the farest are shown at end-of-list. My problem is about this ranking.

What I've done for now

So I made some researches and I succeeded in computing this distance.

    $special_select_distance = "DEGREES(ACOS(COS(RADIANS(" . $oneVilles->__get('latitude')[app::getLang()] . ")) * COS(RADIANS(lat)) * COS(RADIANS(lon) - RADIANS(" . $oneVilles->__get('longitude')[app::getLang()] . ")) + SIN(RADIANS(" . $oneVilles->__get('latitude')[app::getLang()] . ")) * SIN(RADIANS(lat))))";

    $restaurants = $restaurantsDAO->getAll(null, ['distance DESC'] , null, 'HAVING distance < 1.9' , null , '*, ' . $special_select_distance . " AS distance");

... where:

  1. ['distance DESC'] stands for the ranking by distance

  2. 'HAVING distance < 1.9' stands for the arbitrary threshold

  3. '*, ' . $special_select_distance . " AS distance" is the selector

  4. $oneVilles->__get('latitude')[app::getLang()] and $oneVilles->__get('longitude')[app::getLang()] are the city's coordinates lat and lon

  5. lat and lon are the restaurant's coordinates (automatically taken into the table we are iterating on, i.e.: restaurants table, since we use the restaurants DAO)

Question

Actual and unexpected result

For each of the restaurants that are quite close between themselves, the computed distance with the city remains the same.

Example: assume that restaurants A and B are quite close. Then, the distance between A and the city is the same than B and the city, it's my actual and unexpected result.

This is not what I want. Indeed, in reality one of these restaurants is closest to the city than the other. I think there isn't enough precision in MySQL.

Expected result

Expected result: to make the restaurants ranking according to the distance to the city working. In other words, to get a more precise computed distance.

Example: assume that restaurants A and B are quite close. Then, the distance between A and the city is shorter than B and the city, it's my expected result.

Examples of computed distances

  1. Between a restaurant and the city (the restaurant being far from the city): 1.933156948976873

  2. Between a restaurant A and the city (A being close to the city): 1.6054631070094885

  3. Between a restaurant B and the city (B being close to A): 1.6054631070094885

Distances in points 2. and 3. are the same and it's not normal. I would want to have more digits, in order to be able to rank my restaurants more efficiently.

Constraints

Upvotes: 1

Views: 147

Answers (1)

Rick James
Rick James

Reputation: 142258

For long distances, use the Haversine formula for accuracy. For short distances, Pythagoras is twice as fast.

16 significant digits (data type DOUBLE) is ludicrous. You don't need to distinguish two different fleas on your dog.

With Pythagoras, be sure to divide the longitude by the cosine of the latitude -- One degree of longitude near Helsinki is half as far as one degree at the equator.

Some more details here: http://mysql.rjweb.org/doc.php/latlng

If 1.6054631070094885 is a latitude diff, then think about it this way: If you and I are at the same longitude, but our latitudes are 1.605463 and 1.605464, then, well, I don't know you well enough to be that close.

It is not practical to compare two floating point values without having a fudge factor:

If abs(a-b) < 0.00001, then treat them as equal.

More

I recommend FLOAT for lat, lng, and distance since you are talking about restaurants. If you are not talking about more than, say, 100 miles or km, then this expression is sufficiently precise:

SQRT(  ($lat - lat) *
       ($lat - lat) +
      (($lng - lng) * COS(RADIANS(lat))) *
      (($lng - lng) * COS(RADIANS(lat))) )  * $factor

Where...

  • lat and lng are names of FLOAT columns in the table, in units of degrees.
  • $lat and $lng are values of the location you are starting from, also in degrees. (PHP uses $; other languages use other conventions.)
  • $factor is 69.172 for miles or 111.325 for kilometers.
  • I would not display the result with more than perhaps 1 decimal place. (Don't display "12.345678 miles"; "12.3 miles" is good enough.)

A comparison of Pythagoras and GCD:

             Pyt        GCD
To Rennes:  93.9407    93.6542
To Vannes:  95.6244    95.6241

Upvotes: 2

Related Questions