Strange (?) value change when saving double-type data (geolocation) into database

Question

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:

• Database/SQL: How to store longitude/latitude data?
• What datatype to use when storing latitude and longitude data in SQL databases?
• What is the ideal data type to use when storing latitude / longitudes in a MySQL database?

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.}

Answer 1

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.