当我从php更新每个查询时，MySql和float或double裁剪我的号码

Question

I like to store Latitudes and Longitudes in a very precise way into my MySql Database with InnoDB. However, float did not offer enough internal decimal places so I switched to double. Wondering myself a little but MySql accepted double with a size up to 30 so I used double(30,27) because only 3 regular places are needed and the rest must be behind the comma.

Well in MySql that worked so far and on the other side I receive floats over json_decode and when I echo them the have up to 18 or 19 places after the comma. So even here everything as expected.

But when I build a update query to fill the empty double fields (double 30/27) it just fills up all digits with zero exempted the first 9 digits. Or sometimesbreaks the rule start form the 7. digit with a line of 9s.

For example when I update 47.2608691999999877 in mysql – no matter if per script or per PhpMyAdmin, after klicking the save button 47.260869199999990000000000000 appears in the table where 47.260869199999987700000000000 should appear or

11.396251100000000633 as update into the table gives me a 11.396251100000000000000000000

So it looks like it ignores the possible places starting from the 7. or sometimes fills it with 9s but in most cases there are just zeros.

May could anybody grant me a tip to solve this problem please?
Remember I also get the Problem with PHPMyAdmin but is made of PHP. Now I am not sure if it is a MySQL or PHP Problem.

Thank you

So for example I MYSql I can store this easily over for example PHPMyAdmin:

Answer 1

Notice how the DOUBLE(30, 27) made sense for 16-17 significant digits, then went haywire?

FLOAT has 24 bits of precision. That is enough for about 7 significant digits. In particular, for latitude and longitude, that is precise enough to get within 1.7 meters (5.6 feet). For most lat/lng applications, that is sufficient. So, it does not really matter that there is a roundoff error when inserting (converting from decimal to binary) and another error when reading (converting back to decimal).

DOUBLE has 53 bits of precision, about 16 significant digits -- 3.5 nanometers north-south or east-west!

DOUBLE(30, 27) -- When INSERTing, it say (1) round to 27 decimal places, (2) round to the 53 bits of precision. When reading, it reverses the steps, leading to the strange messes you have.

I have never seen a valid need for using (M,N) on FLOAT or DOUBLE. Don't do it.

For DECIMAL(M,N), you are storing exactly what will fit to N decimal places. For lat/lng purposes, consider DECIMAL(6,4) for latitude and (7,4) for longitude -- 16 meters (52 feet). Or, for more precision, (8,6) and (9,6) -- 16cm (6 inches).

Note further that a FLOAT takes 4 bytes (always), DOUBLE: 8, DECIMAL(6,4): 3, (7,4): 4, (8,6): 4, (9,6): 5. If you have billions of lat/lng entries, the bytes of storage adds up.