6

I have two fields for storing geolocation data, defined as doubles in my MySQL database:

`address_geo_latitude` float(10,6) NOT NULL,
`address_geo_longitude` float(10,6) NOT NULL

And I'm using Yii2's double validator over values passed by user:

public function rules()
{
    return [
        [['address_geo_latitude', 'address_geo_longitude'], 'double', 'min'=>0, 'max'=>360]
    ];
}

(though my tests seems to be proving, that this issue has nothing to do with Yii2 validators)

During tests I've observed strange (?) changes of values, i.e.:

  • 359.90 becomes 359.899994 (0,000006 difference),
  • 359.80 becomes 359.799988 (0,000012 difference),
  • 311.11 becomes 311.109985 (0,000015 difference),
  • 255.55 becomes 255.550003 (-0,000003 difference),
  • 205.205 becomes 205.205002 (-0,000002 difference),
  • 105.105 becomes 105.105003 (-0,000003 difference).

but:

  • 359.899994 remains 359.899994,
  • 311.109985 remains 311.109985,
  • 311 remains 311,
  • 255 remains 255,
  • 200 remains 200,
  • 75.75 remains 75.75,
  • 11.11 remains 11.11.

What am I missing? I can't see any pattern or logic behind these.

Is this, because I have an incorrect MySQL's field declaration for this kind of data? If yes, then what is the correct one? Few different answers:

suggests, that using float(10,6) is the best option, if not using MySQL's spatial extensions.

My tests seems to be proving, that this issue has nothing to do with Yii2 validators, because value remains correct until re-read from database:

print_r(Yii::$app->request->post()); //Correct!
print_r($lab->address_geo_latitude); //Correct!

if ($lab->load(Yii::$app->request->post(), 'Lab') && $lab->save()) {
    print_r($lab->address_geo_latitude); //Correct!

    $lab2 = $this->findModel($lab->id);
    print_r($lab2->address_geo_latitude); //<-- HERE! Incorrect!
}

My question is on contrary to this one. My numbers gains, not looses, accuracy! And only for certain numbers, not always.

Community
  • 1
  • 1
trejder
  • 17,148
  • 27
  • 124
  • 216
  • are values correct in *mysql* after you save them again ? – Tony Jun 29 '15 at 12:06
  • @Tony Sorry, I don't understand your question, because I never said anything about second save, only about second read (re-read). Yes, immediately after `$model->save()` value saved as `$model->address_geo_latitude = 311.11` turns out to actually store / have `311.109985` value in `address_geo_latitude` column. – trejder Jun 30 '15 at 06:16

1 Answers1

3

This happens not because of Yii but because of how floating-point values are stored on binary systems.

As you can read in the MySQL documentation "Problems with Floating-Point Values":

Floating-point numbers sometimes cause confusion because they are approximate and not stored as exact values. A floating-point value as written in an SQL statement may not be the same as the value represented internally.

Here you can find the great explanation for this problem with examples. As you can see numbers can get a bit bigger, smaller or not changed at all but you always have to remember that this is just an approximation.

For gelocation data you can use simple DECIMAL type to make sure values are stored unchanged in database or use Spacial Data type optimized to store and query data that represents objects defined in a geometric space.

Community
  • 1
  • 1
Bizley
  • 17,392
  • 5
  • 49
  • 59
  • As I wrote in my question (did I?) rounding or approximation is known to me, but it has nothing to do with this issue. Rounding is always toward nearest number, both in programming and math. It is about loosing precision, not gaining it. You can't call changing `105.105` into `105.105003` a _rounding_ because that is not a rounding at all and that is both an absurd and mathematically incorrect operation. – trejder Jun 30 '15 at 06:02
  • 1
    @trejder an earlier version of mysql documentation mentioned in the answer https://dev.mysql.com/doc/refman/5.0/en/problems-with-float.html has a good example of how different floating point numbers can be represented when you add more decimal places, so try to use *decimal* instead of *float*, and compare the results – Tony Jun 30 '15 at 06:41
  • @Bizley, can you reedit your answer, including most of these comments here and maybe linking [this answer](http://stackoverflow.com/a/163084/1469208). You have actually solved the problem! :> Changing `float(10,6)` to `decimal(9,6)` make a miracle and no more value changing appears. I will be glad to accept your answer, once you reedit it. – trejder Jun 30 '15 at 07:21
  • @trejder Edited as requested, let me know if something is missing – Bizley Jul 01 '15 at 13:04