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:
I saw in the Google document
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 :
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.
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`)
/* then for insertion you can */
INSERT INTO `buildings`
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.
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`)
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]
create_table :neighborhoods do |t|
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