2008-10-01 19:18:13 8 Comments

Bearing in mind that I'll be performing calculations on lat / long pairs, what datatype is best suited for use with a MySQL database?

### Related Questions

#### Sponsored Content

#### 28 Answered Questions

### [SOLVED] What is the simplest and most robust way to get the user's current location on Android?

**2010-06-29 21:59:25****emmby****324067**View**814**Score**28**Answer- Tags: android geolocation location latitude-longitude

#### 9 Answered Questions

### [SOLVED] Best data type for storing currency values in a MySQL database

**2009-03-10 01:36:22****Brian Fisher****182476**View**212**Score**9**Answer- Tags: mysql sql sqldatatypes

#### 7 Answered Questions

### [SOLVED] What is the maximum length of latitude and longitude?

**2013-04-12 07:07:05****user2273259****263522**View**321**Score**7**Answer- Tags: database-design latitude-longitude

#### 13 Answered Questions

### [SOLVED] Which MySQL data type to use for storing boolean values

**2008-11-14 10:36:18****Beat****861529**View**1215**Score**13**Answer- Tags: mysql boolean sqldatatypes

#### 5 Answered Questions

### [SOLVED] Which data type for latitude and longitude?

**2011-11-16 11:11:58****user1008404****105591**View**152**Score**5**Answer- Tags: postgresql types latitude-longitude postgis

#### 39 Answered Questions

### [SOLVED] Should I use the datetime or timestamp data type in MySQL?

**2009-01-03 16:14:37****karlipoppins****884289**View**2698**Score**39**Answer- Tags: mysql datetime timestamp sqldatatypes

#### 8 Answered Questions

#### 8 Answered Questions

### [SOLVED] Database/SQL: How to store longitude/latitude data?

**2009-09-02 21:16:47****Timtom****80190**View**69**Score**8**Answer- Tags: sql mysql database performance sqlperformance

#### 1 Answered Questions

### [SOLVED] Clustering Latitude Longitude data in MySQL Database

**2014-08-20 15:32:03****George Melidis****1581**View**1**Score**1**Answer- Tags: mysql database cluster-analysis latitude-longitude

## 21 comments

## @Anderson Ribeiro 2019-11-07 15:30:37

I suggest you use Float datatype for SQL Server.

## @Kirk Strauser 2008-10-01 19:21:04

Use MySQL's spatial extensions with GIS.

## @Codebeef 2008-10-01 19:40:31

Do you have any other links to examples or any other info as to how best get started with them?

## @James Schek 2008-10-02 15:43:45

MYSQL Spatial is a good option, but still has significant limits and caveats (as of 6). Please see my answer below...

## @mkuech 2013-05-14 14:53:37

@James Schek is right. Plus, MySQL does all it's calculations using euclidean geometry, so it doesn't represent a real-world use case for lat/lng.

## @PodTech.io 2017-01-15 05:46:28

FYI; Mysql support spatial index only with *.myisam tables, i.e. the ISAM engine. Link: dev.mysql.com/doc/refman/5.0/en/creating-spatial-indexes.html

## @Jaspal Singh 2017-06-22 15:15:31

Have a look at this article in the end Update part: mysqlserverteam.com/mysql-5-7-and-gis-an-example

## @Sazzad Hissain Khan 2020-03-22 19:15:02

How come this answer got so many vote with single line answer without example!

## @mahfuz 2019-01-22 12:06:30

Latitudes range from -90 to +90 (degrees), so DECIMAL(10, 8) is ok for that

longitudes range from -180 to +180 (degrees) so you need DECIMAL(11, 8).

Note: The first number is the total number of digits stored, and the second is the number after the decimal point.

In short:

`lat DECIMAL(10, 8) NOT NULL, lng DECIMAL(11, 8) NOT NULL`

## @The Godfather 2018-08-13 23:06:32

TL;DRUse FLOAT(8,5) if you're not working in NASA / military and not making aircrafts navi systems.

To answer your question fully, you'd need to consider several things:

Formatdegrees minutes seconds: 40° 26′ 46″ N 79° 58′ 56″ Wdegrees decimal minutes: 40° 26.767′ N 79° 58.933′ Wdecimal degrees 1: 40.446° N 79.982° Wdecimal degrees 2: -32.60875, 21.27812So the first part of the answer would be - you can store the coordinates in the

format your application usesto avoid constant conversions back and forth and make simpler SQL queries.Most probably you use Google Maps or OSM to display your data, and GMaps are using "decimal degrees 2" format. So it will be easier to store coordinates in the same format.

PrecisionThen, you'd like to define precision you need. Of course you can store coordinates like "-32.608697550570334,21.278081997935146", but have you ever cared about millimeters while navigation to the point? If you're not working in NASA and not doing satellites or rockets or planes trajectories, you should be fine with several meters accuracy.

Commonly used format is 5 digits after dots which gives you 50cm accuracy.

Example: there is 1cm distance between X,21.2780818and X,21.2780819. So 7 digits after dot give you 1/2cm precision and 5 digits after dot will give you 1/2 meters precision (because minimal distance between distinct points is 1m, so rounding error cannot be more than half of it). For most civil purposes it should be enough.degrees decimal minutes format (40° 26.767′ N 79° 58.933′ W) gives you exactly the same precision as 5 digits after dot

Space-efficient storageIf you've selected decimal format, then your coordinate is a pair (-32.60875, 21.27812). Obviously, 2 x (1 bit for sign, 2 digits for degrees and 5 digits for exponent) will be enough.

So here I'd like to support

Alix Axelfrom comments saying that Google suggestion to store it in FLOAT(10,6) is really extra, because you don't need 4 digits for main part (since sign is separated and latitude is limited to 90 and longitude is limited to 180). You can easily use FLOAT(8,5) for 1/2m precision or FLOAT(9,6) for 50/2cm precision. Or you can even store lat and long in separated types, because FLOAT(7,5) is enough for lat. See MySQL float types reference. Any of them will be like normal FLOAT and equal to 4 bytes anyway.Usually space is not an issue nowadays, but if you want to really optimize the storage for some reason (Disclaimer: don't do pre-optimization), you may compress lat(no more than 91 000 values + sign) + long(no more than 181 000 values + sign) to 21 bits which is

significantly lessthan 2xFLOAT (8 bytes == 64 bits)## @Mariano Peinador 2016-07-22 00:13:35

No need to go far, according to Google Maps, the best is FLOAT(10,6) for lat and lng.

## @webfacer 2019-10-20 02:57:13

where did you get this information i can´t find it? just in case something changes.

## @turrican_34 2019-10-27 12:55:18

@webfacer, It's in the "Creating a table in MySQL" section here: developers.google.com/maps/documentation/javascript/… e.g

`lat FLOAT( 10, 6 ) NOT NULL,`

`lng FLOAT( 10, 6 ) NOT NULL`

## @turrican_34 2019-10-27 13:29:48

@webfacer, It looks like that

`FLOAT`

syntax is deprecated as of`mysql 8.0.17`

. Mysql now recommends to just use`FLOAT`

without any precision parameters dev.mysql.com/doc/refman/8.0/en/numeric-type-overview.html and dev.mysql.com/doc/refman/5.5/en/floating-point-types.html## @Stormwind 2016-04-26 19:05:23

I am highly surprised by some answers/comments.

Why on earth would anyone be willing to voluntarely "pre-decrease" the precision, and then later on perform calculations on the worse numbers? Sounds ultimately stupid.

If the source has 64-bit precision, certainly it would be dumb to voluntarely fix the scale to eg. 6 decimals, and limit the precision to a maximum of 9 significant digts (which happens with the commonly proposed decimal 9.6 format).

Naturally, one stores the data with the precision that the source material has. The only reason to decrease precision would be limited storage space.

The decimal 9.6-format causes a snap-to-grid phenomen. That should be the very last step, if it is at all to happen.

I wouldn't invite accumulated errors to my nest.

## @Yarin 2016-05-15 17:52:21

Because most GPS tools and applications are only accurate to 6 decimal places. Pointless to store data to a greater precision than what tools can measure gis.stackexchange.com/questions/8650/…

## @Stormwind 2016-05-15 21:44:41

@Yarin Yes indeed, but you talk about measurements and GPS, which are not mentioned in the question. Most certainly there exist more accurate figures. But lets consider GPS; say a source data set of 64-bit floats, that already contains an inaccuracy. 6 decimals means snapping a latitude to closest ca 11 centimeters. Hence, by only storing the data (with 6 decimals) now, you open up for a potential

22 cminaccuracy (if originally 11 cm too). Voluntarely, probably to do 64-bit calculation on that, before maybe storing a 3rd time - now 33 cm inaccuracy window, +-16 cm. Sounds dumb, imho.## @Stormwind 2018-05-07 12:24:48

@Rick James I'd likely store it as 64-bit, ie. 0.3333333333333333. We talk geodata, right? "1/3" rarely appears in nature where things are normally measured, with a reasonable precision.

## @Gajus 2015-06-15 19:41:11

Depends on the precision that you require.

From: http://mysql.rjweb.org/doc.php/latlng

To summarise:

`DOUBLE`

.`DECIMAL(8,6)/(9,6)`

.As of MySQL 5.7, consider using Spatial Data Types (SDT), specifically

`POINT`

for storing a single coordinate. Prior to 5.7, SDT does not support indexes (with exception of 5.6 when table type is MyISAM).Note:

`POINT`

class, the order of the arguments for storing coordinates must be`POINT(latitude, longitude)`

.`ST_Distance`

) and determining whether one point is contained within another area (`ST_Contains`

).## @Armfoot 2015-11-26 11:51:08

You copy pasted part of a previous answer and "summarise" with something the guy that created that table

did not recommend: «How to PARTITION? Well, MySQL is very picky. So FLOAT/DOUBLE are out. DECIMAL is out. So, we are stuck with some kludge. Essentially, we need to convert Lat/Lng to some size of INT and use PARTITION BY RANGE.» AND «FLOAT has 24 significant bits; DOUBLE has 53. (They don't work with PARTITIONing but are included for completeness.Often people use DOUBLE without realizing how much an overkill it is, and how much space it takes.)» Just leave the SDT part you wrote.## @Gajus 2015-11-26 12:00:53

@Armfoot If you look at the time of the edits, it is the other answer that copied from me. Not that it matters: I am seeing Stack Overflow more of a "notes for the future me".

## @Armfoot 2015-11-26 12:07:15

No he didn't copy from you, he just pasted the table like you did from the link he referenced on 2014 (your post is from 2015). Btw, I think you misspelled "Special" when you linked

SpatialData Types. This part you wrote is actually useful for people who want to start using them, if you add some more examples like`CREATE TABLE geom (g GEOMETRY NOT NULL, SPATIAL INDEX(g)) ENGINE=MyISAM;`

and the warning about SDT limitations, as James mentioned, perhaps your answer will be more concise and precise in helping other people as well...## @Rick James 2018-05-06 03:46:08

@Gajus - I'm honored that two of you found my document! (No, I don't know how big a flea is, but I felt it would get someone's attention.)

## @AndreyP 2019-03-01 16:42:59

When using POINT class, the order of the arguments for storing coordinates must be POINT(longitude/X, latitude/Y).

## @Rick James 2019-05-02 22:01:10

@AndreyP - It appears that

`POINT()`

stores X and Y in`DOUBLE`

. This is further substantiated by the size of a`POINT`

. Down with fleas!## @Armfoot 2015-11-26 14:47:06

In a completely different and simpler perspective:

`VARCHAR`

), E.g.: "-0000.0000001,-0000.000000000000001" (35 length and if a number has more than 7 decimal digits then it gets rounded);`google.maps.geometry.poly.containsLocation(latLng, bermudaTrianglePolygon))`

This way you don't need to worry about indexing numbers and all the other problems associated with data types that may screw up your coordinates.

## @Yarin 2016-05-15 17:42:41

No good. OP said he'd be performing calculations on the lat/lng pairs - your answers preclude that

## @Armfoot 2016-05-22 00:42:18

@Yarin This is a popular question where a few (or a lot) of people just need an answer on how to store the coordinates according to their own needs (a great deal of them may just use Google maps). Your downvote suggests that this answer may not help them... By storing the coordinates in a string they will know exactly the original values that were provided to them (e.g.: by Google) which will do help them later if they decide to evolve their own app and perform calculations on them. At that time, they'll still have the original raw data just because they didn't mess it up with conversions.

## @Simon 2014-08-04 13:46:49

Basically it depends on the precision you need for your locations. Using DOUBLE you'll have a 3.5nm precision. DECIMAL(8,6)/(9,6) goes down to 16cm. FLOAT is 1.7m...

This very interesting table has a more complete list: http://mysql.rjweb.org/doc.php/latlng :

Hope this helps.

## @Sam_Butler 2017-06-25 22:30:43

I need to write a constructive, detailed commentary focused on the contents of the posts, so I will say that while observing the accuracy table as provided from Rick James' website, I was mildly amused at the resolution description "fleas on a dog" and felt it worthy of kudos. Technically speaking, this was a helpful depiction that assisted me in deciding what datatype to use when storing coordinates for measuring the distance between two addresses, which, @Simon, I'd like to thank you for sharing.

## @ToolmakerSteve 2020-03-31 20:51:53

FWIW, that link's use of "SMALLINT scaled" is horrendously inefficient. Oguzhan's answer is great way to store long/lat with

7digits after decimal point, in a 4-byte signed int. Great precision (~1cm) in a small size (4B).## @Alex Holsgrove 2015-01-13 16:30:51

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. Both should be "signed" to allow for negative values.## @Kondybas 2017-07-28 22:21:01

`DECIMAL`

type is intended for financial calculations where no`floor/ceil`

is accepted. Plain`FLOAT`

significantly outperforms`DECIMAL`

.## @Rick James 2018-05-06 03:49:36

@Kondybas - Since the main cost in a database is fetching rows, the performance difference between float and decimal should not be a concern.

## @saeed khalafinejad 2013-09-28 11:30:00

Based on this wiki article http://en.wikipedia.org/wiki/Decimal_degrees#Accuracy the appropriate data type in MySQL is Decimal(9,6) for storing the longitude and latitude in separate fields.

## @Kaitlin Duck Sherwood 2013-04-03 08:05:56

While it isn't optimal for all operations, if you are making map tiles or working with large numbers of markers (dots) with only one projection (e.g. Mercator, like Google Maps and many other slippy maps frameworks expect), I have found what I call "Vast Coordinate System" to be really, really handy. Basically, you store x and y pixel coordinates at some way-zoomed-in -- I use zoom level 23. This has several benefits:

I talked about all this in a recent blog post: http://blog.webfoot.com/2013/03/12/optimizing-map-tile-generation/

## @mlinuxgada 2012-12-19 11:14:15

MySQL uses double for all floats ... So use type double. Using float will lead to unpredictable rounded values in most situations

## @Rick James 2018-05-06 03:54:48

MySQL

performs operations in. MySQL lets you`DOUBLE`

storedata as either a 4-byte`FLOAT`

or an 8-byte`DOUBLE`

. So, there is likely to be a loss of precision when storing an expression into a`FLOAT`

column.## @Richard Harrison 2008-10-01 19:37:02

When I did this for a navigation database built from ARINC424 I did a fair amount of testing and looking back at the code, I used a DECIMAL(18,12) (Actually a NUMERIC(18,12) because it was firebird).

Floats and doubles aren't as precise and may result in rounding errors which may be a very bad thing. I can't remember if I found any real data that had problems - but I'm fairly certain that the inability to store accurately in a float or a double could cause problems

The point is that when using degrees or radians we know the range of the values - and the fractional part needs the most digits.

The MySQL Spatial Extensions are a good alternative because they follow The OpenGIS Geometry Model. I didn't use them because I needed to keep my database portable.

## @aexl 2016-11-08 22:03:11

Thank you, this was helpful. Feels weird reading all these questions and answers from 2008 realising it was already 8 years ago.

## @Rick James 2018-05-06 03:42:28

@TheSexiestManinJamaica - Before IEEE 754-1985, computer floating-point hardware was chaotic. There was even on machine where

`a*b`

was not equal`b*a`

(for some values). There were many examples somewhat like:`2+2 = 3.9999`

. The standard cleaned up a lot of mess, and was 'rapidly' adopted by virtually every piece of hardware and software. So, this discussion has been valid, not just since 2008, but for a third of a century.## @Ted Avery 2011-05-13 15:18:40

Google provides a start to finish PHP/MySQL solution for an example "Store Locator" application with Google Maps. In this example, they store the lat/lng values as "Float" with a length of "10,6"

http://code.google.com/apis/maps/articles/phpsqlsearch.html

## @Alix Axel 2013-05-09 09:54:57

Google clearly doesn't understand how the FLOAT specification works:

`FLOAT(10,6)`

leaves 4 digits for the integer part of the coordinate. And no, the sign doesn't count - that comes from the (un)signed attribute.## @Hrvoje Golcic 2014-01-15 20:05:48

But if you need to store as integral part values from [0, 180] should be more then enough, right?

## @1.44mb 2014-02-19 10:58:58

@AlixAxel I think Google knows what it is doing. Because it states that: "

With the current zoom capabilities of Google Maps, you should only need 6 digits of precision after the decimal. That will let the fields store 6 digits after the decimal, plus up to 4 digits before the decimal, e.g.". If unsigned is checked the pattern will be-123.456789degrees.1234,567890. So no problems.## @Andrew Ellis 2014-05-27 21:57:40

@AlixAxel He is counting off the the numbers in the sequence; not using an actual coordinate...

## @FooBar 2014-11-11 12:07:42

Using datatype

`Double`

for Laravel## @Ben Davison 2017-01-04 17:34:18

I know I am late to the party (2 years) but I believe @1.44mb to be correct.

## @Ben Davison 2017-01-04 17:40:51

According to MySql:

"MySQL permits a nonstandard syntax: FLOAT(M,D) ... Here, (M,D) means than values can be stored with up to M digits in total, of which D digits may be after the decimal point. For example, a column defined as FLOAT(7,4) will look like -999.9999 when displayed."Using this example you would only needFLOAT(9,6)to store the coord -179.999999.## @phil294 2017-01-28 16:56:28

Using

`float`

instead of`double`

leads to inaccuries of around 1-2 meters. Just tested it myself, it blew up my entire application. Switching to double now.## @Rick James 2018-05-06 03:28:05

In MySQL, the 'sign' does not take a digit. So

`(10,6)`

is overkill on the integer side. (It happens not to matter, since 4 digits takes 2 bytes, and so does 3 digits.)## @Rick James 2018-05-06 03:35:59

@BenDavison - Since

`FLOAT`

has only about 7 'significant' digits, so`-179.999999`

gets mangled exactly`-180.`

`-179.99999`

turns into about`-179.99998474`

because there are only 24 bits of precision in IEEE-754's Float.## @Torben Brodt 2010-07-23 12:37:42

depending on you application, i suggest using FLOAT(9,6)

spatial keys will give you more features, but in by production benchmarks the floats are much faster than the spatial keys. (0,01 VS 0,001 in AVG)

## @NameNotFoundException 2017-09-29 07:10:00

Can you please provide your test result with details here ?

## @Dylan 2008-12-29 18:31:31

The spatial functions in PostGIS are much more functional (i.e. not constrained to BBOX operations) than those in the MySQL spatial functions. Check it out: link text

## @HLGEM 2008-10-01 19:23:07

Lat Long calculations require precision, so use some type of decimal type and make the precision at least 2 higher than the number you will store in order to perform math calculations. I don't know about the my sql datatypes but in SQL server people often use float or real instead of decimal and get into trouble because these are are estimated numbers not real ones. So just make sure the data type you use is a true decimal type and not a floating decimal type and you should be fine.

## @Javier 2008-10-01 19:26:04

both float and decimal types have their place. as a rule of thumb, floats mean physical variables, and decimals are for countable entities (mostly money). i don't see why you'd prefer decimal for lat/long

## @Dragoljub Ćurčić 2009-05-21 16:46:24

I also think float is fine for lat/long. At least on SQL Server (4bytes, 7 digits).

## @HLGEM 2009-05-21 17:26:09

Float is not exact it is estimated, lake of exactness in a lat long is fatal! It could point you to a completely differnt spot on the globe.

## @Spidey 2012-04-16 21:12:12

The maximum error of float datatypes is low enough that this shouldn't be a problem. I mean, you have to be aware of error multiplication/accumulation with both implementations anyway.

## @Rick James 2018-05-06 03:59:14

@HLGEM - Rounding to some number of

decimalplaces also lands you in a different spot on the globe. The question is whether that different spot is so close that it does not matter.## @user18443 2008-10-19 01:45:25

We store latitude/longitude X 1,000,000 in our oracle database as NUMBERS to avoid round off errors with doubles.

Given that latitude/longitude to the 6th decimal place was 10 cm accuracy that was all we needed. Many other databases also store lat/long to the 6th decimal place.

## @Kaitlin Duck Sherwood 2016-04-22 03:43:34

Multiplying by some large number (like a million) is great if you have a lot of data because integer operations (e.g. indexed retrieval) are much much faster than floats.

## @ToolmakerSteve 2019-04-07 15:32:09

@KaitlinDuckSherwood - bits are bits - I'm not aware of any reason a 32-bit float would be slower for retrieval (indexed or otherwise) than a 32-bit integer. Even floating math these days is fast enough to be a non-issue. Nevertheless, I agree with the comment to use implied multiplier with an integer: it maximizes the precision you get out of 32 bits. A bit of future-proofing as technology improves.

## @James Schek 2008-10-02 15:41:53

MySQL's Spatial Extensions are the best option because you have the full list of spatial operators and indices at your disposal. A spatial index will allow you to perform distance-based calculations very quickly. Please keep in mind that as of 6.0, the Spatial Extension is still incomplete. I am not putting down MySQL Spatial, only letting you know of the pitfalls before you get too far along on this.

If you are dealing strictly with points and only the DISTANCE function, this is fine. If you need to do any calculations with Polygons, Lines, or Buffered-Points, the spatial operators do not provide exact results unless you use the "relate" operator. See the warning at the top of 21.5.6. Relationships such as contains, within, or intersects are using the MBR, not the exact geometry shape (i.e. an Ellipse is treated like a Rectangle).

Also, the distances in MySQL Spatial are in the same units as your first geometry. This means if you're using Decimal Degrees, then your distance measurements are in Decimal Degrees. This will make it very difficult to get exact results as you get furthur from the equator.

## @O. Jones 2012-02-11 00:17:48

Restating: MySQL Spatial Extensions aren't suitable for calculating great circle distances between points on the surface of the earth represented by lat/long. Their distance functions, etc, are only useful on cartesian, planar, coordinates.

## @ConroyP 2008-10-01 19:21:22

A

`FLOAT`

should give you all of the precision you need, and be better for comparison functions than storing each co-ordinate as a string or the like.If your MySQL version is earlier than 5.0.3, you may need to take heed of certain floating point comparison errors however.

## @Kirk Strauser 2008-10-01 19:23:49

You need a real latitude/longitude coordinate datatype for easy math. Imagine the convenience of something like the equivalent of "select * from stores where distance(stores.location, mylocation) < 5 miles"

## @ConroyP 2008-10-01 19:33:47

Hadn't heard of the spatial extensions before, that does sound very convenient alright, having previously worked on an inherited app that does quite a bit of geo-related calculations, must check it out.

## @Rick James 2018-05-06 04:01:38

@ConroyP - No. That quote is pointing out that

`DECIMAL`

had (before 5.0.3) certain errors due to the use of floating implementation.