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

The Question :

280 people think this question is useful

I’m working with map data, and the Latitude/Longitude 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!

The Question Comments :
  • Do you really need to store values on the surface of the earth accurate to 1.1mm? If so, then why are you storing values in latlng in the first place?
  • possible duplicate of What is the ideal data type to use when storing latitude / longitudes in a MySQL database?
  • The google doc is WRONG! Do not use the float type – that only has 7 digits of precision. You need at least 9. You do not need 10 – the docs for some strange reason count the minus sign as a digit. Do either: double(9,6) or decimal(9,6).
  • How much precision do you really need? 6 decimal places gives you enough precision to distinguish two people kissing each other. 8 can tell your fingers apart. FLOAT distinguishes two items 1.7m (5.6ft) apart. All of those are ludicrously excessive for “map” applications!

The Answer 1

637 people think this answer is useful

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.

UPDATE: MySQL supports Spatial data types and Point is a single-value type which can be used. Example:

CREATE TABLE `buildings` (
  `coordinate` POINT NOT NULL,
  /* Even from v5.7.5 you can define an index for it */
  SPATIAL INDEX `SPATIAL` (`coordinate`)
) ENGINE=InnoDB;

/* then for insertion you can */
INSERT INTO `buildings` 
(`coordinate`) 
VALUES
(POINT(40.71727401 -74.00898606));

The Answer 2

29 people think this answer is useful

in laravel used decimal column type for migration

$table->decimal('latitude', 10, 8);
$table->decimal('longitude', 11, 8);

for more information see available column type

The Answer 3

16 people think this answer is useful

Additionally, you will see that float values are rounded.

// e.g: given values 41.0473112,29.0077011

float(11,7) | decimal(11,7)
---------------------------
41.0473099  | 41.0473112
29.0077019  | 29.0077011

The Answer 4

8 people think this answer is useful

Do not use float… It will round your coordinates, resulting in some strange occurrences.

Use decimal

The Answer 5

7 people think this answer is useful

You can set your data-type as signed integer. When you storage coordinates to SQL you can set as lat*10000000 and long*10000000. And when you selecting with distance/radius you will divide storage coordinates to 10000000. I was test it with 300K rows, query response time is good. ( 2 x 2.67GHz CPU, 2 GB RAM, MySQL 5.5.49 )

The Answer 6

6 people think this answer is useful

I believe the best way to store Lat/Lng in MySQL is to have a POINT column (2D datatype) with a SPATIAL index.

CREATE TABLE `cities` (
  `zip` varchar(8) NOT NULL,
  `country` varchar (2) GENERATED ALWAYS AS (SUBSTRING(`zip`, 1, 2)) STORED,
  `city` varchar(30) NOT NULL,
  `centre` point NOT NULL,
  PRIMARY KEY (`zip`),
  KEY `country` (`country`),
  KEY `city` (`city`),
  SPATIAL KEY `centre` (`centre`)
) ENGINE=InnoDB;


INSERT INTO `cities` (`zip`, `city`, `centre`) VALUES
('CZ-10000', 'Prague', POINT(50.0755381, 14.4378005));

The Answer 7

5 people think this answer is useful

MySQL now has support for spatial data types since this question was asked. So the the current accepted answer is not wrong, but if you’re looking for additional functionality like finding all points within a given polygon then use POINT data type.

Checkout the Mysql Docs on Geospatial data types and the spatial analysis functions

The Answer 8

-1 people think this answer is useful

Using migrate ruby on rails

class CreateNeighborhoods < ActiveRecord::Migration[5.0]
  def change
    create_table :neighborhoods do |t|
      t.string :name
      t.decimal :latitude, precision: 15, scale: 13
      t.decimal :longitude, precision: 15, scale: 13
      t.references :country, foreign_key: true
      t.references :state, foreign_key: true
      t.references :city, foreign_key: true

      t.timestamps
    end
  end
end

Add a Comment