Edward Edward - 2 years ago 178
SQL Question

What MySQL data type should be used for Latitude/Longitude with 8 decimal places?

I'm working with map data, and the

extends to 8 decimal places. For example:

Latitude 40.71727401
Longitude -74.00898606

I saw in the Google document
which uses:

lat FLOAT( 10, 6 ) NOT NULL,
lng FLOAT( 10, 6 ) NOT NULL

however, their decimal places only go to 6.

Should I use
FLOAT(10, 8)
or is there another method to consider for storing this data so it's precise. It will be used with map calculations. Thanks!

Answer Source

DECIMAL is the MySQL data-type for exact arithmetic. Unlike FLOAT its precision is fixed for any size of number, so by using it instead of FLOAT you might avoid precision errors when doing some calculations. If you were just storing and retrieving the numbers without calculation then in practice FLOAT would be safe, although there's no harm in using DECIMAL. With calculations FLOAT is still mostly ok, but to be absolutely sure of 8d.p. precision you should use DECIMAL.

Latitudes range from -90 to +90 (degrees), so DECIMAL(10, 8) is ok for that, but longitudes range from -180 to +180 (degrees) so you need DECIMAL(11, 8). The first number is the total number of digits stored, and the second is the number after the decimal point.

In short: lat DECIMAL(10, 8) NOT NULL, lng DECIMAL(11, 8) NOT NULL

This explains how MySQL works with floating-point data-types.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download