325

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!

pnuts
  • 58,317
  • 11
  • 87
  • 139
Edward
  • 9,430
  • 19
  • 48
  • 71
  • 5
    Do you really need to store values on the surface of the earth [accurate to 1.1mm](http://en.wikipedia.org/wiki/Decimal_degrees#Accuracy)? If so, then why are you storing values in latlng in the first place? – ovangle Mar 22 '15 at 10:15
  • 3
    possible duplicate of [What is the ideal data type to use when storing latitude / longitudes in a MySQL database?](http://stackoverflow.com/questions/159255/what-is-the-ideal-data-type-to-use-when-storing-latitude-longitudes-in-a-mysql) – Gajus Jun 15 '15 at 19:23
  • 3
    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 Jul 13 '16 at 06:36
  • 14
    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 May 02 '18 at 04:17
  • Also, cross-platform dup https://dba.stackexchange.com/q/107089 – Vega Oct 27 '21 at 09:17
  • `location` POINT NULL DEFAULT NULL COMMENT '坐標', `longitude` DECIMAL(11,8) NULL DEFAULT '0.00000000', `latitude` DECIMAL(10,7) NULL DEFAULT '0.0000000', – kkasp Jun 28 '22 at 03:14
  • "FLOAT(M,D) and DOUBLE(M,D) syntax to specify the number of digits for columns of type FLOAT and DOUBLE (and any synonyms) is a nonstandard MySQL extension. This syntax is deprecated." Note well, this is only related to "[(M,D)]" - non-annotated FLOAT and DOUBLE are Not deprecated. The REAL data type can be FLOAT or DOUBLE, depending on setting - therefore the REAL(M,D) syntax is also affected. This is deprecation, as of 8.0.17, not "removal". Ref https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html#mysql-nutshell-deprecations This does not affect DECIMAL(M,D). – TonyG Mar 14 '23 at 17:11
  • With the accepted answer to use POINT, and relevant to the OP question about data storage and precision : The POINT type is in the Well-Known Binary (WKB) Format, and consumes 21 bytes. The X and Y values in that are each 8 bytes, double-precision. Ref https://dev.mysql.com/doc/refman/8.0/en/gis-data-formats.html – TonyG Mar 14 '23 at 17:31

11 Answers11

697

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));
Lee Goddard
  • 10,680
  • 4
  • 46
  • 63
gandaliter
  • 9,863
  • 1
  • 16
  • 23
  • How does the DECIMAL type avoid “precision errors” when dividing by three or taking a cosine? – Eric Postpischil Sep 20 '12 at 00:11
  • 14
    Perhaps my answer misused the word exact, as DECIMAL is still only as accurate as the precision you give it. My point was that it *is* that accurate. Of course some calculations expand error. If I have a DECMIAL x then sin(x^100) is going to be way off. But if (using DECIMAL (10, 8) or FLOAT (10, 8)) I calculate 0.3 / 3 then DECIMAL gives 0.100000000000 (correct), and float gives 0.100000003974 (correct to 8dp, but would be wrong if multiplied). I understand the main difference is in how the numbers are stored. DECIMAL stores the decimal digits, where FLOAT stores the binary approximation. – gandaliter Sep 20 '12 at 15:03
  • 1
    By the doubt of precision, I'm going to DOUBLE. – Ratata Tata Mar 31 '14 at 20:27
  • 3
    8 decimal places is 1.1mm (less than 1/16 of inch) precision. Why would you ever need that for latitude and longitude? – vartec Aug 26 '15 at 17:29
  • 1
    Facebook seems to use up to 12 decimals for lat and 13 for lng. vartec wrote that 8 decimals is equal to 1.1mm; what about 7 and 6 ? (I'm not good at maths). I'm using double for now but would like to check if I could gain in distance calculations by changing type. Thank you. – Alain Zelink Feb 26 '16 at 12:50
  • 4
    The answers to this question (http://gis.stackexchange.com/questions/8650/how-to-measure-the-accuracy-of-latitude-and-longitude) give information about the precision that you get with different numbers of decimal places of latitude and longitude. – gandaliter Mar 01 '16 at 14:06
  • @gandaliter - That stackexchange Q&A is interesting, but it fails to measure diagonals. There is corrected [_here_](http://mysql.rjweb.org/doc.php/latlng#representation_choices) . – Rick James May 02 '18 at 04:22
  • 1
    @gandaliter does the minus sign (-90/-180) counts in the DECIMAL length? – Wayne Li Aug 06 '18 at 04:59
  • Not certain, but I’d be pretty surprised if the minus sign counted as a digit – gandaliter Aug 07 '18 at 08:47
  • BTW, [Oğuzhan KURNUÇ's answer shows an alternative way to get exact values](https://stackoverflow.com/a/37127245/199364). The benefit of that approach is that a precise geo value (~1cm precision) is stored in 4B instead of 5B. For a DB that might be a minor difference; but computers are more efficient, in both storage and performance, when values fit in 4B. The *downside* is the need to document how you are using the value, and to convert it in your application to some other format for calculation purposes; `DECIMAL` (used by gandaliter) is indeed a more natural data type for this purpose. – ToolmakerSteve Mar 31 '20 at 22:13
  • @AlainZelink - important to clarify whether the number of digits being discussed includes the digits to the left of the decimal point. (And sometimes, one counts the sign, though technically that is a character count not a digit count.) "(..,8)" refers to 8 digits after the decimal point. For most purposes, `DECIMAL(10,7)`, ~1 cm precision, is an abundance of digits. That could hold `+-179.1234567`. If only need accuracy to ~1 meter, that could be `DECIMAL(8,5)` or `+-179.12345`. OTOH, to be safe, one usually stores 1 or 2 digits more than you really need. In case of round-off errors. – ToolmakerSteve Mar 31 '20 at 22:25
  • 1
    Just a small typo I guess. Need comma between lat long args in POINT function in your INSERT statement – Sameer Joshi Apr 04 '20 at 10:45
  • 1
    @vartec Client may require the missile to hit the target precisely. – TheLegendaryCopyCoder Jul 02 '20 at 13:11
  • @TheLegendaryCopyCoder That notification puzzled me for a while! – gandaliter Jul 02 '20 at 13:20
  • this is an incomplete answer. Just because you can, doesn't mean you should. So WHY use point instead of floats? – Toskan Jun 29 '21 at 21:06
  • What srid should be used for POINT (coordinates in lat , lon) ?? – Mubasher Jul 11 '23 at 09:03
66

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

Jignesh Joisar
  • 13,720
  • 5
  • 57
  • 57
25

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
Kerem
  • 11,377
  • 5
  • 59
  • 58
15

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

Use decimal

Sam Sabey
  • 169
  • 1
  • 3
10

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));
ΔO 'delta zero'
  • 3,506
  • 1
  • 19
  • 31
  • can you explain why? how do you run selects? – Toskan Jun 29 '21 at 21:07
  • @Toskan, because MySQL's [POINT](https://dev.mysql.com/doc/refman/8.0/en/gis-class-point.html) is as a geometry datatype that represents a single location in coordinate space, and can be used for a spacial index. – ΔO 'delta zero' Jul 05 '21 at 13:24
  • @Toskan, you can simply `SELECT centre ...` for coordinates, or you can `SELECT ST_X(centre), ST_Y(centre) ...` for latitude and longitude respectively. See [point property functions](https://dev.mysql.com/doc/refman/8.0/en/gis-point-property-functions.html) – ΔO 'delta zero' Jul 05 '21 at 13:26
  • why is it better than floats I mean. You can doesn't mean you should. I know it exists, I know you can do it. But you can jump off of high buildings too. Doesn't mean you should. Doing `select * from mytable where ST_X(Center) = 7.51234;` sounds like there could be some problem. – Toskan Jul 05 '21 at 21:15
  • 1
    @Toskan, the whole purpose of this is having a 2d data, i.e. coordinates, in a single column and with a single R-tree index. Then you can easily sort by distance on a sphere, filter entries within a polygon etc etc. – ΔO 'delta zero' Jul 07 '21 at 01:26
  • If you need to use separate indexes for lat and long, or a B-tree index for filtering exact matches, you can achieve this with generated columns while maintaining a single point of entry. – ΔO 'delta zero' Jul 07 '21 at 01:27
8

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

Bill--
  • 140
  • 3
  • 6
7

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 )

Oğuzhan KURNUÇ
  • 229
  • 3
  • 6
  • Which is faster? Doing this or using float or decimal? – Dinidiniz Mar 16 '18 at 15:09
  • 1
    @Dinidiniz - The speed difference is very small. Fetching rows overwhelms the timing of any database action. – Rick James May 02 '18 at 04:13
  • 1
    Why 10000000? What happens if it contains more than 6 digits after decimal value? Or will it always return 6 decimal points. – Mahbub Morshed Oct 29 '18 at 09:39
  • @MahbubMorshed - you mean **7** digits - there are 7 zero digits shown. But yes, this technique is always storing exactly 7 digits, no more. (If using 4-byte integer, can't increase the multiplier beyond 7 digits because longitude value can be as large as 180, and must avoid overflowing signed integer maximum.) This is 2 digits more precise than storing in single-precision float, which only has about 5 digits-to-right-of-decimal-point at large longitude values. (179.99998 and 179.99997 may store as same float value; 179.99996 is safely far from 179.99998).) – ToolmakerSteve Mar 31 '20 at 20:19
6

Accuracy of 6 decimal places is about 16cm, which means 2 object have the same lat and lng if they are less than 16cm far from each other.

Also, in mariadb/mysql, using float/double is not ideal if we have huge data for indexing, and Point datatype is overhead for data size. It'd better to use decimal or convert lat long to INT.

It's good option to use decimal with 6 decimal places as we can ignore convert, and we have only 16cm inaccuracy, longitude is between -180 to 180, thus require 1 digit more than latitude, which is between -90 to 90 degree:

Lat DECIMAL(8,6)
Lng DECIMAL(9,6)

We can extend to 8 decimal places:

Lat DECIMAL(10,8)
Lng DECIMAL(11,8)

MySQL reference

Mariadb reference

Vengleab SO
  • 716
  • 4
  • 11
  • 1
    Cross-referencing with https://stackoverflow.com/questions/159255/what-is-the-ideal-data-type-to-use-when-storing-latitude-longitude-in-a-mysql/25120203#25120203 for more info on this precision – ᴍᴇʜᴏᴠ Jan 28 '22 at 18:38
2
CREATE TABLE your_table_name (
   latitude  REAL,
   longitude  REAL
)

REAL is a synonym for FLOAT.

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

CREATE TABLE your_table_name (
   latitude  REAL CHECK(latitude IS NULL OR (latitude >= -90 AND latitude <= 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

TonyG
  • 1,432
  • 12
  • 31
Kevin KOUOMEU
  • 159
  • 1
  • 1
  • 9
-3

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
gilcierweb
  • 2,598
  • 1
  • 16
  • 15
  • Won't this limit longitudes to -99..99? This excludes much of the Pacific! – Rick James May 02 '18 at 04:26
  • That is an example should not be taken as absolute truth. You can use another DECIMAL decimal precision (20, 18) and so on ... If you need to save geographic and spatial data you can use the postgis database for this purpose. The MySQL Spatial Extensions are a good alternative because they follow The OpenGIS Geometry Model. I did not use them because I needed to keep my database portable. https://postgis.net/ – gilcierweb May 03 '18 at 17:56
  • 1
    `(20,18)` also tops out at +/-99. – Rick James May 03 '18 at 18:18
  • That is an example should not be taken as absolute truth. You can use another DECIMAL decimal precision (20, 18) and so on ... If you need to save geographic and spatial data you can use the postgis database for this purpose. The MySQL Spatial Extensions are a good alternative because they follow The OpenGIS Geometry Model. I did not use them because I needed to keep my database portable. postgis.net – gilcierweb May 03 '18 at 20:52
  • Dude this is just an example, you can use the precision you want, if decimal is not helping you use postgis a database made just for geographic and spatial data – gilcierweb May 03 '18 at 20:56
  • How big is your coordinates? Because these decimal type precisions work for the whole world and nowhere on the planet is left! Why do not they work for you? – gilcierweb May 03 '18 at 21:32
  • 1
    Bankok, for example, is at lat=13.754, lng=100.501. But that longitude cannot be represented in (15,13) or (20,18). For longitude, `DECIMAL(m,n)` needs to have `m` be at least 3 more than `n`. Honolulu is at lng=-157.858. For cities, I claim that `n`=2` is probably sufficient. If two adjacent cities are at least 1.0 mile (1.6km) apart, lat and/or lng will be different. – Rick James May 03 '18 at 21:47
  • Usually in the majority of the database only used lat DECIMAL(10, 8) lng DECIMAL(11, 8)answers well, if that precision is not attending you can count the highest latitude and longitude and put the decimal precision according to your count. You can also use postgis that solves all problems related to geographic and spatial data, most prefectures around the world use postgis to write the data. 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). – gilcierweb May 03 '18 at 21:49
  • I found an extreme case (in the US). Roselle and Roselle Park, New Jersey are much less than a mile apart. Their lat&lng agree to 2 decimal places, and almost agree to 3 places. – Rick James May 03 '18 at 22:10
  • To clarify, if I understand correctly, the fix for what Rick James points out, is that `longitude` must have scale **3** less than the precision, not **2** less, allowing longitude values greater than +-99. E.g. `DECIMAL(10, 7)` or `DECIMAL(11, 8)` but **not** `DECIMAL(10,8)`. Or `DECIMAL(15,12)` instead of `DECIMAL(15,13)`. I know you mention `DECIMAL(11,8)` in a comment above, but that is a bit buried; just stating here the general solution. – ToolmakerSteve Mar 31 '20 at 21:54
-8

You should simply use varchar (20)

Cody Gray - on strike
  • 239,200
  • 50
  • 490
  • 574
Pirogrammer
  • 9
  • 1