What is the ideal data type to use when storing latitude longitude in a MySQL database

Question

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

User · Answer

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

User · Answer

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    You do the expensive lat lng to mercator pixel transformation once instead of every time you handle the point Getting the tile coordinate from a record given a zoom level takes one right shift  Getting the pixel coordinate from a record takes one right shift and one bitwise AND  The shifts are so lightweight that it is practical to do them in SQL  which means you can do a DISTINCT to return only one record per pixel location  which will cut down on the number records returned by the backend  which means less processing on the front end     I talked about all this in a recent blog post     http   blog webfoot com 2013 03 12 optimizing-map-tile-generation

User · Answer

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

User · Answer

TL DR  Use 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   Format   degrees minutes seconds  40   26  46  N 79   58  56  W degrees decimal minutes  40   26 767  N 79   58 933  W decimal degrees 1  40 446   N 79 982   W decimal degrees 2  -32 60875  21 27812 Some other home-made format  Noone forbids you from making your own home-centric coordinates system and store it as heading and distance from your home  This could make sense for some specific problems you re working on    So the first part of the answer would be - you can store the coordinates in the format your application uses to 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   Precision  Then  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 2780818 and 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 storage  If 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 Axel from 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 less than 2xFLOAT  8 bytes    64 bits

User · Answer

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      Prior to MySQL 5 0 3  DECIMAL columns store values with exact precision because they are represented as strings  but calculations on DECIMAL values are done using floating-point operations  As of 5 0 3  MySQL performs DECIMAL operations with a precision of 64 decimal digits  which should solve most common inaccuracy problems when it comes to DECIMAL columns

User · Answer

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

User · Answer

I suggest you use Float datatype for SQL Server

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

The ideal datatype for storing Lat Long values is decimal 9 6  This is at approximately 10cm precision  whilst only using 5 bytes of storage  e g  CAST 123 456789 as decimal 9 6

User · Answer

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

User · Answer

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

User · Answer

Depends on the precision that you require   Datatype           Bytes       resolution ------------------ -----  -------------------------------- Deg 100  SMALLINT      4  1570 m    1 0 mi  Cities DECIMAL 4 2   5 2      5  1570 m    1 0 mi  Cities SMALLINT scaled        4   682 m    0 4 mi  Cities Deg 10000  MEDIUMINT   6    16 m     52 ft  Houses Businesses DECIMAL 6 4   7 4      7    16 m     52 ft  Houses Businesses MEDIUMINT scaled       6   2 7 m    8 8 ft FLOAT                  8   1 7 m    5 6 ft DECIMAL 8 6   9 6      9    16cm    1 2 ft  Friends in a mall Deg 10000000  INT      8    16mm    5 8 in  Marbles DOUBLE                16   3 5nm            Fleas on a dog   From  http   mysql rjweb org doc php latlng  To summarise    The most precise available option is DOUBLE  The most common seen type used is 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    When using POINT class  the order of the arguments for storing coordinates must be POINT latitude  longitude   There is a special syntax for creating a spatial index  The biggest benefit of using SDT is that you have access to Spatial Analyses Functions  e g  calculating distance between two points  ST Distance  and determining whether one point is contained within another area  ST Contains

User · Answer

Use MySQL s spatial extensions with GIS

User · Answer

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    Datatype               Bytes            Resolution  Deg 100  SMALLINT      4      1570 m    1 0 mi  Cities DECIMAL 4 2   5 2      5      1570 m    1 0 mi  Cities SMALLINT scaled        4       682 m    0 4 mi  Cities Deg 10000  MEDIUMINT   6        16 m     52 ft  Houses Businesses DECIMAL 6 4   7 4      7        16 m     52 ft  Houses Businesses MEDIUMINT scaled       6       2 7 m    8 8 ft FLOAT                  8       1 7 m    5 6 ft DECIMAL 8 6   9 6      9        16cm    1 2 ft  Friends in a mall Deg 10000000  INT      8        16mm    5 8 in  Marbles DOUBLE                16       3 5nm            Fleas on a dog   Hope this helps

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

In a completely different and simpler perspective    if you are relying on Google for showing your maps  markers  polygons  whatever  then let the calculations be done by Google  you save resources on your server and you simply store the latitude and longitude together as a single string  VARCHAR   E g    -0000 0000001 -0000 000000000000001   35 length and if a number has more than 7 decimal digits then it gets rounded   if Google returns more than 7 decimal digits per number  you can get that data stored in your string anyway  just in case you want to detect some flees or microbes in the future  you can use their distance matrix or their geometry library for calculating distances or detecting points in certain areas with calls as simple as this  google maps geometry poly containsLocation latLng  bermudaTrianglePolygon   there are plenty of  server-side  APIs you can use  in Python  Ruby on Rails  PHP  CodeIgniter  Laravel  Yii  Zend Framework  etc   that use Google Maps API    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

User · Answer

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

[mysql] What is the ideal data type to use when storing latitude / longitude in a MySQL database?

Examples related to mysql

Examples related to database-design

Examples related to maps

Examples related to mapping

Examples related to latitude-longitude