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

ID : 10042

viewed : 36

Tags : mysqltypesfloating-pointmysql

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

vote vote

94

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

80

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

vote vote

78

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  
vote vote

69

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

Use decimal

vote vote

50

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

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

Related QUESTION?