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

300
votes

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!

9
mysqltypesfloating-point
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? – ovangle
possible duplicate of What is the ideal data type to use when storing latitude / longitudes in a MySQL database? – Gajus
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). – Ariel
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! – Rick James

9 Answers

661
votes

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));
39
votes

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

21
votes

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
8
votes

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

Use decimal

8
votes

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));
7
votes

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

6
votes

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 )

1
votes
CREATE TABLE your_table_name (
   lattitude  REAL,
   longitude  REAL
)

also consider adding further verifications to your lat, long declaration:

CREATE TABLE your_table_name (
   lattitude  REAL CHECK(lattitude IS NULL OR (lattitude >= -90 AND lattitude <= 90)),
   longitude  REAL CHECK(longitude IS NULL OR (longitude >= -180 AND longitude <= 180))
)

explanation : https://dev.mysql.com/doc/refman/5.7/en/floating-point-types.html

-3
votes

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