121

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?
Community
  • 1
  • 1
Citizen
  • 12,430
  • 26
  • 76
  • 117
  • 7
    Accuracy of the coordinates depends on WHERE those coordinates are, because the surface of the planet is not a perfect sphere and distance from the poles is a MAJOR MAJOR factor too. 3 decimals places, on average, is about 120 meters/400 feet, though. 4 decimals would be 12meters/40feet, etc... – Marc B Aug 23 '11 at 21:20
  • 1
    See this question on GIS stackexchange: http://gis.stackexchange.com/questions/8650/how-to-measure-the-accuracy-of-latitude-and-longitude – Flimm Oct 10 '13 at 12:52
  • 1
    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:30

7 Answers7

214

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

klaussner
  • 2,364
  • 3
  • 25
  • 33
Noushad
  • 2,960
  • 1
  • 21
  • 14
  • 4
    If these are at the equator does that mean that these are worst case errors? – Liath Jan 15 '14 at 09:02
  • 6
    Actually, equator is best case. One latitude and one longitude degree are the same size at the equator (69 miles), but one degree of longitude shrinks to zero as it approaches either of the poles. Here's a very nice explanation: http://nationalatlas.gov/articles/mapping/a_latlong.html#four – codingoutloud Jan 23 '14 at 15:31
  • 11
    @codingoutloud Which would make these worst case errors. Or to be pedantic, these are worst case errors for using lat/lon at sea level. At an elevation of 6,378 m, the error increases by 0.1%. – Scott B Mar 13 '14 at 23:08
  • @codingoutload: That link is apparently no longer present :( – Tom Stambaugh Feb 18 '15 at 15:57
  • 1
    @Tom Stambaugh: There's web.archive.org for that: https://web.archive.org/web/20070810120810/http://nationalatlas.gov/articles/mapping/a_latlong.html – Stefan Steiger Jul 28 '16 at 09:03
  • @codingoutloud - The equator is usually the worst, because you need to consider the diagonal distance. For example, how far apart are [1.23, 1.23] and [1.24, 1.24]? It is 41% more than your noshy's table gives. – Rick James May 02 '18 at 04:12
20
+----------------+-------------+
|    Decimals    |  Precision  |
+----------------+-------------+
|    5           |  1m         |
|    4           |  11m        |
|    3           |  111m       |
+----------------+-------------+

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

n611x007
  • 8,952
  • 8
  • 59
  • 102
Gustav
  • 2,902
  • 1
  • 25
  • 31
  • 10
    If you're using SQL Server... It's worth noting that a precision of 1-9 uses 5 bytes. So you may was well use a decimal(9,6) instead of decimal(7,4) and take advantage of the higher accuracy since they both occupy the same amount of space. – Theo Oct 01 '13 at 22:57
  • For latitude, use `(8,6)` (or `(6,4)` to save save a byte (in MySQL). – Rick James May 02 '18 at 04:09
19

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].

Greg
  • 191
  • 1
  • 2
  • 4
    While I agree that 17 digits is too much, I suggest that 6 is too little if the data is going to be post-processed. When doing things like a radius query ("Answer features within a 0.5 mile radius of this point"), errors -- including truncation -- are magnified. If you need 6 decimal digits on the output of such a query, then the *input* should start with significantly more. Our shop tends to use DECIMAL(18,15). Our goal is ensure that the db is not the limiting factor in the accuracy of spatial calculations. – Tom Stambaugh Feb 18 '15 at 15:52
  • 1
    Going beyond 6 decimal places is going beyond the available precision of today's GPS satellites. Post processing will not introduce a significant amount of error. `DECIMAL(18,15)` takes 9 bytes. – Rick James May 02 '18 at 04:07
11

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.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
8

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

Rick James
  • 135,179
  • 13
  • 127
  • 222
4

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.

chaotic3quilibrium
  • 5,661
  • 8
  • 53
  • 86
  • `FLOAT` and `DOUBLE`, _in this context_, does not suffer from some of the issues you describe. – Rick James May 02 '18 at 03:59
  • @RickJames You didn't sufficiently specify "this context". If you mean, strictly in the storage of a value in two DB columns, then perhaps. However, given values don't just sit in DB columns unused, the implicit assumption that there will be (proximity) queries written against these values. And holding this fairly pragmatic assumption then means all of the issues of it being an unreliable approximation continue to hold. – chaotic3quilibrium May 02 '18 at 14:16
  • 1
    If one `FLOAT` value and the 'next' value are so close to each other in value that you can't tell one city (or vehicle or person or flea) from another, then the rounding and representation errors don't matter. Meanwhile, it is almost always folly to compare two `FLOATs` (or `DOUBLEs` or approximate `DECIMALs`) with '='. – Rick James May 02 '18 at 16:49
  • You appear to be missing the point. Any attempted query is going to be implicitly using equals, if not explicitly. And this assumes you aren't going through other layers and languages with the values, strictly staying inside of SQL Server. Here's an official Microsoft response to this for SQL Server: https://blogs.msdn.microsoft.com/qingsongyao/2009/11/14/query-on-float-datatype-may-return-inconsistent-result/ – chaotic3quilibrium May 05 '18 at 21:07
  • I'm sorry, I thought the question was tagged `[mysql]`, not SQL Server. – Rick James May 06 '18 at 03:10
  • The specific example is does not apply to lat/lng -- which won't involve arithmetic with numbers like `300000000000000000000000000000000000`. Float _does_ have problems, but about the only one you are likely to hit is if you use `=` for comparing a pair of latitudes (or longitudes). They _may_ be unequal unexpectedly. – Rick James May 06 '18 at 03:14
  • You're right. I am bouncing between too many different contexts and misapplied the RDBMS technology. That said, almost every software engineer I have met working with spatial data has made the "=" mistake in their SQL queries. And when I highlighted the issues related to floating point numbers, most of them then break all related indexing by then trying to do an in-place bounding box proximity check. This problem is vastly more common than I think you are understanding. And it is eliminated by using the String-like Geohash. Hence, my answer and persistence. – chaotic3quilibrium May 06 '18 at 19:06
3

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

Savage
  • 2,296
  • 2
  • 30
  • 40