mysql - How accurately should I store latitude and longitude?


Translate

I was reading this question here:

What datatype to use when storing latitude and longitude data in SQL databases?

And it seems the general consensus is that using Decimal(9,6) is the way to go. The question for me is, how accurate do I really need this?

For instance, Google's API returns a result like:

"lat": 37.4219720,
"lng": -122.0841430

Out of -122.0841430, how many digits do I need? I've read several guides but I can't make enough sense out of them to figure this out.

To be more precise in my question: If I want to be accurate within 50 feet of the exact location, how many decimal points do I need to store?

Perhaps a better question would actually be a non-programming question, but it would be: how much more accurate does each decimal point give you?

Is it this simple?

  1. List item
  2. x00 = 6000 miles
  3. xx0 = 600 miles
  4. xxx = 60 miles
  5. xxx.x = 6 miles
  6. xxx.xx = .6 miles
  7. etc?

All Answers
  • Translate

    Accuracy versus decimal places at the equator

    decimal  degrees    distance
    places
    -------------------------------  
    0        1.0        111 km
    1        0.1        11.1 km
    2        0.01       1.11 km
    3        0.001      111 m
    4        0.0001     11.1 m
    5        0.00001    1.11 m
    6        0.000001   0.111 m
    7        0.0000001  1.11 cm
    8        0.00000001 1.11 mm
    

    ref : https://en.wikipedia.org/wiki/Decimal_degrees#Precision


  • Translate
    +----------------+-------------+
    |    Decimals    |  Precision  |
    +----------------+-------------+
    |    5           |  1m         |
    |    4           |  11m        |
    |    3           |  111m       |
    +----------------+-------------+
    

    If you want 50ft (15m) precision go for 4 digits. So decimal(9,6)


  • Translate

    I design databases and have been studying this question for a while. We use an off-the shelf application with an Oracle backend where the data fields were defined to allow 17 decimal places. Ridiculous! That's in the thousandths of an inch. No GPS instrument in the world is that accurate. So let's put aside 17 decimal places and deal with practical. The Government guarantees their system is good to "a "worst case" pseudorange accuracy of 7.8 meters at a 95% confidence level" but then goes on to say actual FAA (using their high quality instruments) has shown GPS readings to usually be good to within a meter.

    So you have to ask yourself two questions: 1) What is the source of your values? 2) What will the data be used for?

    Cell phones are not particularly accurate, and Google/MapQuest readings are probably only good to 4 or 5 decimals. A high quality GPS instrument might get you 6 (within the United States). But capturing more than that is a waste of typing and storage space. Furthermore, if any searches are done on the values, it's nice for a user to know that 6 would be the most he/she should look for (obviously any search value entered should first be rounded to the same accuracy as the data value being searched).

    Furthermore, if all you're going to do is view a location in Google Maps or put it in a GPS to get there, four or five is plenty.

    I have to laugh at people around here entering all those digits. And where exactly are they taking that measurement? Front door knob? Mailbox out front? Center of building? Top of cell tower? AND... is everyone consistently taking it at the same place?

    As a good database design, I would accept values from a user for maybe a few more than five decimal digits, then round and capture only five for consistency [maybe six if your instruments are good and your end use warrants it].


  • Translate

    The distance between each degree of latitude varies because of the shape of the earth and distance between each degree of longitude gets smaller as you get closer to the poles. So let's talk about the equator, where the distance between each degree is 110.574km for latitude and 111.320km for longitude.

    50ft is 0.01524km, so:

    • 0.01524 / 110.574 = 1/7255 of a degree of latitude
    • 0.01524 / 111.320 = 1/7304 of a degree of longitude

    You need four digits of scale, enough to go down to ten-thousandths of a degree, with a total of seven digits of precision.

    DECIMAL(7,4) should be plenty for your needs.


  • Translate

    Don't store floating point values. While you might assume they are accurate, they are not. They are an approximation. And it turns out different languages have different methods of "parsing" the floating point information. And different databases have different methods of implementing the value approximations.

    Instead, use a Geohash. This video introduces and visually explains the Geohash in under 5 minutes. The Geohash is BY FAR the superior way to encode/decode longitude/latitude information in a consistent way. By never "serializing" the approximated floating point values of a longitude/latitude into database columns and instead, using a Geohash, you will get the same desirable round trip consistency guarantees you get with String values. This website is great for helping you play with a Geohash.


  • Translate

    Taking into account the various parts of a sphere and a diagonal distance, here is a table of the precisions available:

       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
    

    -- http://mysql.rjweb.org/doc.php/latlng#representation_choices


  • Translate

    If you click locations on Google Maps, you get latitude and longitude with 7 decimal places