3

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?

Improve this question
1
  • 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? Commented Feb 12, 2012 at 14:26

2 Answers 2

Reset to default
4

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:

enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Note: when Ollie states the best possible accuracy for a float is a few centimeters, that is only true around the lat/lon point 0,0 (where the prime meridian meets the equator). In the US, it is impossible to describe a position with centimeters accuracy with a float. It is best to use a double for lat/lon.
2

You want to look at using spacial extensions.. Check out this link

MYSQL and using Spatial Extensions

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.