MySQL 对于 GPS 坐标，我应该在我的数据库中存储多少有效数字？

How many significant digits should I store in my database for a GPS coordinate?

提问by teddyk

I have in my MySQL database both longitude and latitude coordinates (GPS data).

我的 MySQL 数据库中有经度和纬度坐标（GPS 数据）。

It's currently stored as:

它目前存储为：

column     type
------------------------
geolat     decimal(10,6)
geolng     decimal(10,6)

Question:Do I really need a data type as large as decimal(10,6)to properly store coordinate data?

问题：我真的需要大decimal(10,6)到能正确存储坐标数据的数据类型吗？

Since I have a combined index on the longitude and latitude, this index size is huge. If I can make it smaller without compromising anything, that would be great.

由于我在经度和纬度上有一个组合索引，所以这个索引大小很大。如果我能在不影响任何东西的情况下使它更小，那就太好了。

采纳答案by Cade Roux

WGS84datum are usually given as coordinates in a fully decimal notation, usually with 5 decimal places, so for latitude (-90 to +90) you could use decimal(7, 5) (-90.00000 to 90.00000), for longitude you could use decimal(8, 5) (-180.00000 to 180.00000).

WGS84数据通常以完全十进制表示法作为坐标给出，通常有 5 个小数位，因此对于纬度（-90 到 +90），您可以使用小数（7, 5）（-90.00000 到 90.00000），对于经度，您可以使用十进制（8, 5）（-180.00000 到 180.00000）。

.00001 gives an precision of around a meter at the equator

.00001在赤道给出大约一米的精度

The DECIMAL/NUMERIC data type is a fixed precision scaled integerand both positive and negative parts of the range are always available - they do not affect the precision or scale (there is obviously storage required for it, but you don't get a choice about that for DECIMAL)

该DECIMAL / NUMERIC数据类型为固定精度缩放整数和范围的正面和负面的部分总是可用的-它们不影响精度或者比例（显然有它所需的存储，但你没有得到一个选择关于 DECIMAL）

回答by Steve Elmer

I've always worked with six digits after the decimal. I used to do GIS work under a military contract and this was sufficient.

我一直使用小数点后六位数字。我曾经根据军事合同从事 GIS 工作，这就足够了。

回答by Mark Canlas

Keep in mind that it's easier to reduce data than it is to increase data. Usually, increasing data accuracy isn't even possible short of remeasuring. And remeasuring comes at a cost. Knowing nothing else of your situation or the industry, I would say capture as much data/specificity as possible.

请记住，减少数据比增加数据更容易。通常，如果没有重新测量，甚至不可能提高数据准确性。重新测量是有代价的。对您的情况或行业一无所知，我会说尽可能多地获取数据/特异性。

The data that you actually use can be culled from this set. If you end up needing a higher degree of specificity, you can always recalculate without remeasuring.

你实际使用的数据可以从这个集合中剔除。如果您最终需要更高程度的特异性，您可以随时重新计算而无需重新测量。

Also, I'm not sure that indexing raw data is the best thing to do since it isn't a discrete set of elements. Creating a table of less accurate/smaller data points would make the indexes much smaller.

另外，我不确定索引原始数据是否是最好的做法，因为它不是一组离散的元素。创建一个包含不太准确/较小数据点的表会使索引小得多。

回答by maycca

It depends on the accuracy of the survey you are conducting. In general:

这取决于您进行的调查的准确性。一般来说：

• The 6 decimalplaces represent accuracy for ~ 10 cm
• The 7 decimal places for ~ 1 cm
• The 13 decimal places - accuracy for an atom structure...

• 的6个小数位表示的精度为〜10厘米
• 小数点后 7 位为 ~ 1 厘米
• 小数点后 13 位 - 原子结构的准确性...

Therefore, unless very precise studies i.e. for tectonic plates movements, the use of 6 digits should be enough.

因此，除非对构造板块运动进行非常精确的研究，否则使用 6 位数字就足够了。

---

---

Here is the copy of the full answer from whuber(GIS stack overflow):

以下是来自whuber（GIS 堆栈溢出）的完整答案的副本：

Accuracyis the tendency of your measurements to agree with the true values. Precisionis the degree to which your measurements pin down an actual value. The question is about an interplay of accuracy and precision.

准确度是您的测量值与真实值一致的趋势。精度是您的测量确定实际值的程度。问题是关于准确性和精确度的相互作用。

As a general principle, you don't need much more precisionin recording your measurements than there is accuracy built into them. Using too much precision can mislead people into believing the accuracy is greater than it really is.

作为一般原则，您在记录测量值时不需要比内置的精度更高的精度。使用过多的精确度会误导人们相信精确度高于实际值。

Generally, when you degrade precision--that is, use fewer decimal places--you can lose some accuracy. But how much? It's good to know that the meter was originally defined (by the French, around the time of their revolution when they were throwing out the old systems and zealously replacing them by new ones) so that ten million of them would take you from the equator to a pole. That's 90 degrees, so one degree of latitude covers about 10^7/90 = 111,111 meters. ("About," because the meter's length has changed a little bit in the meantime. But that doesn't matter.) Furthermore, a degree of longitude (east-west) is about the same or less in length than a degree of latitude, because the circles of latitude shrink down to the earth's axis as we move from the equator towards either pole. Therefore, it's always safe to figure that the sixth decimal place in one decimal degree has 111,111/10^6 = about 1/9 meter = about 4 inches of precision.

通常，当您降低精度（即，使用较少的小数位）时，您可能会失去一些准确性。但是多少钱？很高兴知道仪表最初是由法国人定义的（大约在他们革命的时候，当时他们正在抛弃旧系统并用新系统热情地替换它们），以便其中一千万会带你从赤道到一根杆子。那是 90 度，所以一个纬度覆盖大约 10^7/90 = 111,111 米。（“大约”，因为米的长度在此期间发生了一些变化。但这并不重要。）此外，经度（东西向）的长度与纬度的长度大致相同或更少，因为当我们从赤道向任一极移动时，纬度圈缩小到地轴。因此，它'

Accordingly, if your accuracy needs are, say, give or take 10 meters, than 1/9 meter is nothing: you lose essentially no accuracy by using six decimal places. If your accuracy need is sub-centimeter, then you need at least seven and probably eight decimal places, but more will do you little good.

因此，如果您的精度需求是，比如说，给或取 10 米，那么 1/9 米就不算什么：使用六位小数基本上不会损失精度。如果您的精度要求是亚厘米，那么您至少需要七个小数位，可能还有八个小数位，但更多的数字对您没什么好处。

Thirteen decimal placeswill pin down the location to 111,111/10^13 = about 1 angstrom, around half the thickness of a small atom.

十三个小数位将确定位置为 111,111/10^13 = 大约 1 埃，大约是小原子厚度的一半。

Using these ideas we can construct a table of what each digit in a decimal degree signifies:

使用这些想法，我们可以构建一个表格，其中包含十进制度中每个数字的含义：

• The signtells us whether we are north or south, east or west on the globe.
• A nonzero hundreds digittells us we're using longitude, not latitude!
• The tens digitgives a position to about 1,000 kilometers. It gives us useful information about what continent or ocean we are on.
• The units digit(one decimal degree) gives a position up to 111 kilometers (60 nautical miles, about 69 miles). It can tell us roughly what large state or country we are in.
• The first decimalplace is worth up to 11.1 km: it can distinguish the position of one large city from a neighboring large city.
• The second decimalplace is worth up to 1.1 km: it can separate one village from the next.
• The third decimal place is worth up to 110 m: it can identify a large agricultural field or institutional campus.
• The fourth decimalplace is worth up to 11 m: it can identify a parcel of land. It is comparable to the typical accuracy of an uncorrected GPS unit with no interference.
• The fifth decimalplace is worth up to 1.1 m: it distinguish trees from each other. Accuracy to this level with commercial GPS units can only be achieved with differential correction.
• The sixth decimalplace is worth up to 0.11 m: you can use this for laying out structures in detail, for designing landscapes, building roads. It should be more than good enough for tracking movements of glaciers and rivers. This can be achieved by taking painstaking measures with GPS, such as differentially corrected GPS.
• The seventh decimalplace is worth up to 11 mm: this is good for much surveying and is near the limit of what GPS-based techniques can achieve.
• The eighth decimalplace is worth up to 1.1 mm: this is good for charting motions of tectonic plates and movements of volcanoes. Permanent, corrected, constantly-running GPS base stations might be able to achieve this level of accuracy.
• The ninth decimalplace is worth up to 110 microns: we are getting into the range of microscopy. For almost any conceivable application with earth positions, this is overkill and will be more precise than the accuracy of any surveying device.
• Ten or more decimalplaces indicates a computer or calculator was used and that no attention was paid to the fact that the extra decimals are useless. Be careful, because unless you are the one reading these numbers off the device, this can indicate low quality processing!

• 这个标志告诉我们我们在地球上是北还是南，东还是西。
• 一个非零的百位数字告诉我们我们使用的是经度，而不是纬度！
• 在十位数给予约1000公里的位置。它为我们提供了关于我们所在的大陆或海洋的有用信息。
• 在个位（一个小数度）给出了一个位置最多至111公里（60海里，约69英里）。它可以粗略地告诉我们我们所处的大州或国家。
• 小数点后第一位的值高达11.1公里：它可以区分一个大城市和邻近大城市的位置。
• 在第二个小地方是价值高达1.1公里它能一个村从下分开。
• 小数点后第三位值高达 110 m：可以标识大型农田或机构校园。
• 在第四个小地方是价值高达11米：它可以识别的一块土地。它与没有干扰的未校正 GPS 单元的典型精度相当。
• 所述第五小数地方价值高达1.1米：它彼此区分的树木。商业 GPS 单元达到这个水平的精度只能通过差分校正来实现。
• 在第六小数地方是价值高达0.11 M：你可以用这个布局结构细节，设计景观，修建道路。它应该足以跟踪冰川和河流的运动。这可以通过对 GPS 采取艰苦的措施来实现，例如差分校正 GPS。
• 小数点后第7 位最多为 11 毫米：这有利于进行大量测量，并且接近基于 GPS 的技术所能达到的极限。
• 在第八小数地方价值高达至1.1mm：这是良好的图表板块和火山的运动的运动。永久的、经过校正的、持续运行的 GPS 基站可能能够达到这种精度水平。
• 小数点后第 9 位值高达 110 微米：我们正在进入显微镜的范围。对于几乎任何可以想到的地球位置应用，这都是矫枉过正，而且比任何测量设备的精度都更精确。
• 十位或更多小数位表示使用了计算机或计算器，并且没有注意额外的小数位无用的事实。小心，因为除非您是从设备上读取这些数字的人，否则这可能表明处理质量低！

回答by jk.

if this is for real estate do you really have so many houses that 2 bytes saved per row is going to be that noticeable? I'd keep as much precision as possible unless there was a good reason not to.

如果这是为了房地产，你真的有这么多房子，每行节省 2 个字节会那么明显吗？除非有充分的理由不这样做，否则我会尽可能保持精确。

回答by Martin Beckett

The regular GGA sentance in a NMEA Lat/lon output is only 3decimal places roughly 10m resolution at the equator. Some brands add a custom extra digit to give 1m.

NMEA 纬度/经度输出中的常规 GGA 句子在赤道上只有大约 10m 分辨率的 3decimal 位置。一些品牌添加自定义的额外数字以给出 1m。

4 digit deg.mm mm/1000 is also common.

4 位 deg.mm mm/1000 也很常见。

If you are using high end precision RTK-GPS you might need more places to get mm precision

如果您使用的是高端精度 RTK-GPS，您可能需要更多地方来获得毫米精度

回答by miko

You can also try storing (and/or working with) your coordinates in different units. One project I worked on, all of our coordinates were in milliarcseconds stored as longs (maybe ints, it's been a couple years). This was done partially for speed and for storage space (this was an embedded system). But the same logic could apply here.

您还可以尝试以不同的单位存储（和/或使用）您的坐标。我参与的一个项目，我们所有的坐标都以毫秒为单位存储为 long（可能是 int，已经有几年了）。这部分是为了速度和存储空间（这是一个嵌入式系统）。但同样的逻辑在这里也适用。

回答by Simon B.

If all coordinates are in a specific area, fix some central point (i.e. average current points and round to get a number you can say out loud) and then store coordinates as relative to this point. That way you can probably skip the first 2-4 most significant digits which makes for big savings. But remember to handle this data only via a Class or VIEW that gives back true WGS84 coords.

如果所有坐标都在一个特定区域内，固定一些中心点（即平均当前点和圆形以获得一个你可以大声说出的数字），然后存储相对于该点的坐标。这样你就可以跳过前 2-4 位最重要的数字，这样可以节省很多钱。但是请记住仅通过返回真实 WGS84 坐标的类或视图来处理这些数据。

回答by Sampson

That depends on how precise you want your locatability to be. Obviously the larger the more precise, and the smaller the more broad your results will be. I'd suggest keeping your values larger, as it isn't really much data anyway.

这取决于您希望可定位性的精确程度。显然，越大越精确，而越小，您的结果就越广泛。我建议保持您的值更大，因为无论如何它都不是很多数据。