Edward - 8 months ago 57

SQL Question

I'm working with map data, and the

`Latitude/Longitude`

`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)`

Answer

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.