0

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:

TheMAn
  • 71
  • 1
  • 10

3 Answers3

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.

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • Due to the fact that it is not really possible to represent every possible lat/long value with 16 digits of accuracy correctly, I would say the correct answer is to move towards a integer based data type (so decimal counts since it is a fixed decimal place) if math is attempted then having differing exponents on numbers can have a massive loss of precision, and in addition to all this due to PHP's dynamic typing this could cause issue in unexpected places. Especially because we fudge numbers for display purposes. – Damian Nikodem Feb 26 '15 at 03:35
  • PHP uses 32-bit integers. Degrees * 10000 can be stored in a MEDIUMINT (3 bytes) and give you 2.7 meter (8.8 ft) resolution. INT would give you less than 10 significant digits. I can't imagine why you feel it necessary to store "16 digits of accuracy", nor can I imagine what system will give you that much precision. – Rick James Feb 26 '15 at 05:04
  • PHP integers are platform dependant so on any sane operating system a 64 bit OS means 64 bit signed integers (although the php5.5 documentation states that windows is always considered 32 bit. ) as for that level of in lat/long co-ordinates. I can imagine this resolution being part of a ASTERIX specification. – Damian Nikodem Feb 26 '15 at 05:29
  • Notice how all their values end with a bunch of zeros or 9s or similar indications of displaying more precision than they actually have available. I would suggest that they don't have more than 7 decimal places and that the noise after that is due to sloppiness in their display algorithm. 7 places is tight enough to two marbles. – Rick James Feb 26 '15 at 05:41
  • 1
    My initial thought would be that there is 6 worth of precision and the rest is rounding error when dealing with floating point. – Damian Nikodem Feb 26 '15 at 06:09
  • Well thank you now we discussed several ways to store the values and indeed 30/27 was moreover a test and the accuracy within meters would be enough. Even if I don't understand the inaccuracy discussion about floats & doubles, but therefore I may need to look up that type in detail. However, now I'm not sure what of the three given solutions (four with point) will be the fastest if I need to calculate the distance within MySql over the haversine formula. At least I will create a square range for lat and lng in WHERE to restrict table scan. But witch is fastest int, float, decimal, point? – TheMAn Feb 26 '15 at 12:26
  • None of the above. The haversine formula is not the problem. INT vs FLOAT vs etc is not the problem. Well, changing the datatype can help some: smaller --> more cacheable --> less I/O --> faster. The real problem is that no obvious INDEX makes "find the nearest 10 pizza parlors" fast. If _that_ is your real question, then study http://mysql.rjweb.org/doc.php/latlng . – Rick James Feb 26 '15 at 16:51
0

Your script and PHPMyAdmin wrote on PHP which uses precision to display float numbers, but mysql and php handle numbers as accurate as possible.

Try to change your .ini precision setting: http://php.net/manual/ru/ini.core.php#ini.precision

sectus
  • 15,605
  • 5
  • 55
  • 97
-1

I would not trust any floating point numbers for lat/long storage, you are almost guaranteed to have rounding errors immediately. Floating point numbers are simply not that accurate.

Have you considered a fixed point integer ( 47.2608691999999877 becomes 472608691999999877 by multiplying and dividing by 10000000000000000 when required for display / returning from input (or even simply just performing a string operation for display. )

Numbers of that scale should fit fairly comfortably in the range of a 64 bit integer (hell, you can even get a 17th decimal place and still be ok :P . )

Damian Nikodem
  • 1,324
  • 10
  • 26
  • See my 'answer' as to why I down-graded this. – Rick James Feb 26 '15 at 02:43
  • @RickJamesI just read through your answer, close but no cigar ( but effectively you are confusing the significand with precision ) I would roll it by hand and use integers or with string parsing instead of numerical parsing or even store as strings (just to ensure that floating point conversion dosnt screw it up.) – Damian Nikodem Feb 26 '15 at 03:38