Laravel float variable changing value when adding to database

Question

Theory

I have quite a comprehensive coordinates list (Latitude and Longitude to 7 decimal places), which I want to continually add to. In order to stop data duplication, for each coordinate that's trying to be be inputted, I want to check the database to see if the latitude and longitude exist, on the same row.

Check to see if coordinates exist

 $coordinate = DB::table('coordinates')
            ->where('lat', '=', $geocode->getLatitude())
            ->where('lng', '=', $geocode->getLongitude())
            ->count();

Migration

Schema::create('coordinates', function(Blueprint $table)
        {
            $table->increments('id');
            $table->float('lat', 10, 7);
            $table->float('lng', 10, 7);
            $table->timestamps();
        });

When the user's address is converted, I have noticed that when dd() the latitude variable, it comes out as:

float(53.7960957) from the code dd($geocode->getLatitude());

When I try to add it afterwards to the database, by removing the dd(), the actual decimal that's added into the database is 53.7960968 - A completely different location when we're talking about coordinates!

Why is the decimal changing from echoing on my screen, to adding to the database?

Do I need to convert it to a float before adding? How can I resolve this?

Sam · Accepted Answer · 2014-06-03T21:06:40.947

Update:

I haven't used MySQL in a while, so I did some more research. It looks like the decimal type has the ability to be precise for up to 65 digits. Since you only need 7 digits passed the decimal and 3 before the decimal, you should have no problem creating a schema with decimal(10,7).

SQL Fiddle

Assuming you are using MySQL, there is not much you can do. According to the documentation, float and double types are approximate representations:

The FLOAT and DOUBLE types represent approximate numeric data values. MySQL uses four bytes for single-precision values and eight bytes for double-precision values.

Also check out this SQL Fiddle, you'll see that MySQL 5.5.32 will represent a float of 53.7960957 as 53.796100616455 and a double of 53.7960957 as 53.7960957. You may want to update your schema to use doubles instead, for slightly more precision.

You can also specify the precision of a float with nonstandard syntax (so it is not recommended, for portability to other SQL standards) by using float(m,d) where m is the total digits and d is the number of digits after the decimal. For instance, if you want to allow latitude/longitude values (-180 to 180) with up to 7 digits of precision after the decimal..you should be able to create your table with float(10,7). But, as you'll see, this still wasn't as precise as double was.

MySQL's reference on [Problems with Floating-Point Values](http://dev.mysql.com/doc/refman/5.0/en/problems-with-float.html) is worth a read as well. — Sam, Jun 03 '14 at 20:55
@Svengali see my final update. Turns out `decimal` can be precise for up to 65 digits, and you only need 10. — Sam, Jun 03 '14 at 21:07

score 2 · Answer 2 · answered Jun 03 '14 at 21:01

2

as the forewriter say's ... use DOUBLE instad of FLOAT ...

you could also use 1E6 notation so it would be an Integer to save is MySQL.

53.7960957 * 1E6 = 537960957
537960957 / 1E6 = 53.7960957

this is the way I am using to save geocoordinates

answered Jun 03 '14 at 21:01

EselDompteur

139
5

Laravel float variable changing value when adding to database

2 Answers2