MySQL 我应该如何准确存储纬度和经度？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7167604/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How accurately should I store latitude and longitude?
提问by Citizen
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?
似乎普遍的共识是使用 Decimal(9,6) 是要走的路。我的问题是，我真的需要这个有多准确？
For instance, Google's API returns a result like:
例如，Google 的 API 返回如下结果：
"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.
在 -122.0841430 中，我需要多少位数字？我已经阅读了几本指南，但我无法从它们中获得足够的意义来弄清楚这一点。
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?
在我的问题中更准确地说：如果我想在准确位置的 50 英尺内准确，我需要存储多少个小数点？
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?
有这么简单吗？
- List item
- x00 = 6000 miles
- xx0 = 600 miles
- xxx = 60 miles
- xxx.x = 6 miles
- xxx.xx = .6 miles
- etc?
- 项目清单
- x00 = 6000 英里
- xx0 = 600 英里
- xxx = 60 英里
- xxx.x = 6 英里
- xxx.xx = .6 英里
- 等等？
回答by Noushad
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
回答by Gustav
+----------------+-------------+
| Decimals | Precision |
+----------------+-------------+
| 5 | 1m |
| 4 | 11m |
| 3 | 111m |
+----------------+-------------+
If you want 50ft (15m) precision go for 4 digits.
So decimal(9,6)
如果您想要 50 英尺（15 米）的精度，请选择 4 位数字。所以decimal(9,6)
回答by Greg
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.
我设计数据库并且研究这个问题已经有一段时间了。我们使用带有 Oracle 后端的现成应用程序，其中数据字段被定义为允许 17 位小数。荒谬的！那是千分之一英寸。世界上没有任何 GPS 仪器能如此准确。所以让我们把小数点后 17 位放在一边，实际处理。政府保证他们的系统在“最坏情况下”伪距准确度为 7.8 米，置信度为 95%”，但接着说实际的 FAA（使用他们的高质量仪器）显示 GPS 读数通常对一米以内。
So you have to ask yourself two questions: 1) What is the source of your values? 2) What will the data be used for?
所以你必须问自己两个问题：1）你的价值观的来源是什么？2）数据将用于什么？
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).
手机不是特别准确，Google/MapQuest 读数可能只有小数点后 4 或 5 位。高质量的 GPS 仪器可能会让您获得 6（在美国境内）。但捕获更多的是打字和存储空间的浪费。此外，如果对这些值进行了任何搜索，那么用户很高兴知道 6 将是他/她应该查找的最多值（显然，输入的任何搜索值应首先四舍五入到与正在搜索的数据值相同的精度） ）。
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.
此外，如果您要做的只是在 Google 地图中查看某个位置或将其放入 GPS 以到达那里，那么四五个就足够了。
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].
作为一个好的数据库设计，我会接受来自用户的可能多于五个十进制数字的值，然后四舍五入并只捕获五个以保持一致性[如果你的仪器很好并且你的最终用途保证它，可能是六个]。
回答by Bill Karwin
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 degreeis 110.574km for latitude and 111.320km for longitude.
每个纬度之间的距离因地球的形状而异，并且每个经度之间的距离随着您靠近两极而变小。那么让我们来谈谈赤道，其中每一度之间的距离为纬度110.574公里，经度为111.320公里。
50ft is 0.01524km, so:
50 英尺是 0.01524 公里，所以：
- 0.01524 / 110.574 = 1/7255 of a degree of latitude
- 0.01524 / 111.320 = 1/7304 of a degree of longitude
- 0.01524 / 110.574 = 纬度的 1/7255
- 0.01524 / 111.320 = 经度的 1/7304
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.
DECIMAL(7,4)应该足以满足您的需求。
回答by Rick James
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
-- http://mysql.rjweb.org/doc.php/latlng#representation_choices
回答by chaotic3quilibrium
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.
相反，使用Geohash。该视频在 5 分钟内介绍并直观地解释了 Geohash。Geohash 是迄今为止以一致方式编码/解码经度/纬度信息的最佳方式。通过从不将经度/纬度的近似浮点值“序列化”到数据库列中，而是使用 Geohash，您将获得与字符串值相同的理想往返一致性保证。该网站非常适合帮助您使用 Geohash。
回答by Savage
If you click locations on Google Maps, you get latitude and longitude with 7 decimal places
如果您点击 Google 地图上的位置，您将获得带有 7 个小数位的纬度和经度
