Menu
Use DECIMAL(8,6) for latitude (90 to -90 degrees) and DECIMAL(9,6) for longitude (180 to -180 degrees). 6 decimal places is fine for most applications.
Longitude and latitude coordinates are stored with 15 decimal digits right of the decimal points.
In MySQL, the DECIMAL and NUMERIC data types store exact, fixed-point values. In MySQL, NUMERIC is implemented as a DECIMAL, so a NUMERIC and DECIMAL are the same data type. This data type is used when it is important to preserve the exact precision, such as storing money data.
Decimal degrees (DD) express latitude and longitude geographic coordinates as decimal fractions and are used in many Geographic Information Systems (GIS), web mapping applications such as Google Maps, and GPS devices. Decimal degrees are an alternative to using degrees, minutes, and seconds (DMS).
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));
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
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
Do not use float... It will round your coordinates, resulting in some strange occurrences.
Use decimal
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));
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With