MySQL store latitude, longitude with high level precision

Question

I'm currently storing lat/lng values in a database with a 6 decimal place precision. However, some values that I need to store in this database have a 12 decimal precision. My database currently sets lat and long ast

FLOAT (10, 6)

Is it possible to set the storage type so that it can handle either 6 decimal or 12 decimal lat/lng values?

A lat/lon position described by 12 decimal places equates to micrometer precision. I am curious as to what application needs to describe a position on earth to the micrometer level? — TreyA

O. Jones O. Jones · Accepted Answer · 2012-02-11T00:10:47

Use the DOUBLE data type.

You should be able nondestructively to alter your existing table to work this way. For example,

alter table `mytable` 
   change `LAT` `LAT` double NULL , 
   change `LONG` `LONG` double NULL

But be careful not to overstate your data's accuracy. The epsilon of an ordinary float 32-bit floating point lat/long value (the best possible accuracy) is a few centimeters on the ground. The epsilon of a double is a tiny tiny distance. It is almost inconceivable that your lat/long information is that precise. And if it is, it really won't matter unless you're using sophisticated cartographic projections: the typical haversine formula for computing distances assumes that the earth is a perfect sphere. It isn't. The equatorial bulge of the earth is big enough to make the spherical assumption break down for distances more precise than, again, a few centimeters.

Here's Randall Munroe's (XKCD) take on geocoordinate precision:

MySQL store latitude, longitude with high level precision

2 Answers