What is the ideal data type to use when storing la

Question

Bearing in mind that I'll be performing calculations on lat / long pairs, what datatype is best suited for use with a MySQL database?

Answer 1

Use MySQL's spatial extensions with GIS.

Answer 2

I am highly surprised by some answers/comments.

Why on earth would anyone be willing to voluntarely "pre-decrease" the precision, and then later on perform calculations on the worse numbers? Sounds ultimately stupid.

If the source has 64-bit precision, certainly it would be dumb to voluntarely fix the scale to eg. 6 decimals, and limit the precision to a maximum of 9 significant digts (which happens with the commonly proposed decimal 9.6 format).

Naturally, one stores the data with the precision that the source material has. The only reason to decrease precision would be limited storage space.

• Store source figures with original accuracy
• Store figures calculated from the source in the precision the calculation happens (eg. if the aplication code uses doubles, store the results as doubles)

The decimal 9.6-format causes a snap-to-grid phenomen. That should be the very last step, if it is at all to happen.

I wouldn't invite accumulated errors to my nest.

Answer 3

Google provides a start to finish PHP/MySQL solution for an example "Store Locator" application with Google Maps. In this example, they store the lat/lng values as "Float" with a length of "10,6"

http://code.google.com/apis/maps/articles/phpsqlsearch.html

Answer 4

MySQL uses double for all floats ... So use type double. Using float will lead to unpredictable rounded values in most situations

Answer 5

No need to go far, according to Google Maps, the best is FLOAT(10,6) for lat and lng.

Answer 6

We store latitude/longitude X 1,000,000 in our oracle database as NUMBERS to avoid round off errors with doubles.

Given that latitude/longitude to the 6th decimal place was 10 cm accuracy that was all we needed. Many other databases also store lat/long to the 6th decimal place.