在MySQL数据库中存储纬度/经度时，使用哪种数据类型最理想？

Question

在MySQL数据库中存储纬度/经度时，使用哪种数据类型最理想？

mysqldatabase-designmapsmappinglatitude-longitude

494

考虑到我将对纬度/经度对执行计算，哪种数据类型最适合与MySQL数据库一起使用？

- Mr. Matt

1

我发现这个链接非常有用：http://howto-use-mysql-spatial-ext.blogspot.com/2007/11/using-circular-area-selection.html。虽然它可能有点老，但它包含了完整的解释和示例。 - madc

续：当将数字作为外部来源的未更改且第一次的源材料存储时，保留6位小数可能是可以的。但是，如果对其进行一次计算，然后再次存储它，通过强制使用特定的小数格式来删除部分精度是“愚蠢的”。仅在服务器内执行计算可能会有所不同（服务器可能正在使用比双精度更好的数字表示），而在应用程序计算中使用比双精度更糟糕的数字表示当然会降低存储精度的需求。 - Stormwind

1

最后：对于纬度和经度，第六位小数点差距只有约11厘米。每次读取（触摸）、计算并再次存储时，如果使用6位小数，将会产生新的差距（累积误差）。如果所有误差恰好朝着同一个方向发生，那么就会出现大误差。如果在其上进行临时乘法（例如，放大、然后减去和缩小），误差可能会变得更大。没有充分的理由不要弃用精度！ - Stormwind

（在MySQL中，至少）DECIMAL(9.6)被 精确地 存储。正常的读写操作不会损坏值。 - Rick James

@ToolmakerSteve - 我承认我很多疑。对我来说，“可能”几乎等同于“不可避免”。例如：缓冲区溢出从未被设计，但我们已经看到了它们40年，因为它们是可能的。但请注意：原始问题说“执行计算”，这是一个非常笼统的陈述。我们不知道它的含义。对我来说，可能会有多个数据库读写操作。如果它在60％的写入时向西（在11厘米网格中），并且在40％的写入时向东...（无论CPU有多精确），而您这样做1000次...为什么要邀请这种情况呢？没有关系，你的说法也是正确的-这取决于情况！ :-) - Stormwind

显示剩余11条评论

22个回答

173

使用MySQL的空间扩展功能与GIS一起使用。

- Just Some Guy

8

MYSQL空间查询是一个不错的选择，但仍有显著的限制和注意事项（截至6版本）。请查看我下面的答案... - James Schek

1

@James Schek是正确的。此外，MySQL使用欧几里得几何进行所有计算，因此它不代表经纬度的真实世界应用情况。 - mkuech

1

FYI；Mysql仅支持使用*.myisam表格，即ISAM引擎的空间索引。链接：http://dev.mysql.com/doc/refman/5.0/en/creating-spatial-indexes.html - PodTech.io

请查看此文章的最后更新部分：http://mysqlserverteam.com/mysql-5-7-and-gis-an-example/ - Jaspal Singh

1

这个回答怎么可能只有一行没有示例就得到了这么多的投票！ - Sazzad Hissain Khan

160

谷歌提供了一个针对“门店定位器”应用程序的PHP/MySQL全流程解决方案，其中包括Google Maps。在这个例子中，他们将lat/lng值存储为“10,6”长度的“Float”类型。

http://code.google.com/apis/maps/articles/phpsqlsearch.html

- Ted Avery

11

谷歌显然不理解FLOAT规范的工作原理：“FLOAT(10,6)”留下4个数字作为坐标整数部分。是的，符号不算在内——这来自（无）符号属性。 - Alix Axel

3

但如果您需要存储来自[0,180]范围内的整数值，那么应该超过足够了，对吗？ - Hrvoje Golcic

43

我认为Google知道自己在做什么。因为它声明：“使用谷歌地图的当前缩放功能，您只需要保留小数点后6位精度。这将使字段可以存储小数点前最多4位，小数点后6位，例如-123.456789度。”如果选中无符号，则模式将是1234.567890。所以没有问题。 - tozlu

18

他正在数列中依次点数，而不是使用实际的坐标。 - Andrew Ellis

10

在 Laravel 中使用数据类型 Double。 - FooBar

显示剩余4条评论

77

MySQL的空间扩展是最好的选择，因为您可以使用完整的空间操作符和索引列表。空间索引将允许您快速执行基于距离的计算。请记住，截至6.0版本，空间扩展仍然不完整。我不是贬低MySQL空间，只是在您深入研究之前告诉您存在的缺陷。

如果您只处理点并且仅需要DISTANCE函数，则这很好。如果您需要对多边形、线或缓冲点进行任何计算，则除非使用“relate”操作符，否则空间操作符将无法提供精确的结果。请参见21.5.6的顶部警告。例如，包含、包含在内或相交等关系正在使用MBR，而不是准确的几何形状（即椭圆被视为矩形）。

此外，在MySQL空间中，距离与您的第一个几何图形使用相同的单位。这意味着，如果您使用十进制度数，则距离测量值也以十进制度数表示。随着您远离赤道，这将使获得精确结果变得非常困难。

- James Schek

30

重新表述：MySQL的空间扩展不适用于计算由纬度/经度表示的地球表面上两点之间的大圆距离。它们的距离函数等只适用于笛卡尔平面坐标系，而非球面坐标系。 - O. Jones

3

以上高评价的注释似乎已经过时了几年。自从mysql 5.7以来，就有了ST_Distance_Sphere来精确实现这一功能。 - Ecuador

76

当我为基于ARINC424构建的导航数据库执行此操作时，我进行了大量测试。回顾代码时，我使用了DECIMAL（18,12）（实际上是NUMERIC（18,12），因为它是Firebird）。

浮点数和双精度浮点数不够精确，可能会导致舍入误差，这可能是非常糟糕的事情。我不记得是否发现了任何有问题的真实数据 - 但我相当确定，在浮点数或双精度浮点数中无法准确存储会导致问题。

关键是，在使用度或弧度时，我们知道值的范围 - 分数部分需要最多的数字。 MySQL空间扩展是一个很好的选择，因为它们遵循OpenGIS几何模型。我没有使用它们，因为我需要保持我的数据库可移植性。

- Richard Harrison

5

谢谢，这很有帮助。读所有这些来自2008年的问题和回答，意识到那已经是8年前了，感觉有点奇怪。 - ᴍᴇʜᴏᴠ

3

在IEEE 754-1985之前，计算机浮点数硬件是混乱的。甚至有一种机器，其中a*b不等于b*a（对于某些值）。还有许多类似的例子：2+2=3.9999。该标准清理了很多混乱，并且被几乎所有硬件和软件“迅速”采用。因此，这个讨论不仅在2008年以后有效，而且已经持续了三分之一个世纪。 - Rick James

47

取决于您所需的精度。

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

总结：

最精确的选择是 DOUBLE。
最常见的类型是使用 DECIMAL(8,6)/(9,6)。

从 MySQL 5.7 开始，考虑使用空间数据类型（SDT），特别是 POINT 存储单个坐标。在5.7之前，SDT不支持索引（5.6表类型为MyISAM除外）。

注意：

使用POINT类时，存储坐标的参数顺序必须是POINT(latitude, longitude)。
有一种特殊的语法用于创建空间索引。
使用SDT的最大好处是您可以访问空间分析函数，例如计算两点之间的距离(ST_Distance)和确定一个点是否包含在另一个区域内(ST_Contains)。

- Gajus

4

你复制粘贴了之前回答的部分并用了一些那个创建表格的人不建议的东西来"总结": "如何进行分区？嗯，MySQL非常挑剔。所以浮点数/双精度浮点数不行。DECIMAL也不行。所以，我们只能使用一些替代方法。基本上，我们需要将Lat/Lng转换为某个大小的整数，并使用PARTITION BY RANGE。" 以及"FLOAT有24个有效位；DOUBLE有53个有效位。（它们不能用于PARTITIONing，但为了完整性而包括在内。通常人们使用DOUBLE而没有意识到它有多么过度和占用多少空间。）"请保留你写的SDT部分。 - Armfoot

1

@Armfoot 如果你看编辑的时间，是另一个回答从我这里复制的。虽然这并不重要：我认为 Stack Overflow 更像是“未来自己的笔记”。 - Gajus

2

不，他没有从你那里复制，他只是像你一样从他在2014年引用的链接中粘贴了表格（你的帖子是从2015年的）。顺便说一句，我认为你在链接“Spatial Data Types”时拼错了“Special”。你写的这部分对于想要开始使用它们的人来说实际上是有用的，如果你添加一些更多的例子，比如CREATE TABLE geom (g GEOMETRY NOT NULL, SPATIAL INDEX(g)) ENGINE=MyISAM;以及关于SDT限制的警告，就像James提到的那样，也许你的答案将更加简明扼要，帮助其他人。 - Armfoot

@Gajus - 我很荣幸你们两个人发现了我的文档！（不，我不知道跳蚤有多大，但我觉得这会引起某人的注意。） - Rick James

1

使用POINT类时，存储坐标的参数顺序必须为POINT（经度/X，纬度/Y）。 - AndreyP

@AndreyP - 看起来 POINT() 将 X 和 Y 存储在 DOUBLE 中。这一点可以通过 POINT 的大小进一步证实。与跳蚤说再见！ - Rick James

37

根据这篇维基百科文章http://en.wikipedia.org/wiki/Decimal_degrees#Accuracy，在MySQL中分别使用Decimal（9,6）数据类型来存储经度和纬度是合适的。

- saeed khalafinejad

29

使用 DECIMAL(8,6) 存储纬度（90 到 -90 度）和 DECIMAL(9,6) 存储经度（180 到 -180 度），大多数应用程序六位小数精度足够。两者应该是“有符号的”以支持负值。

- Alexander Holsgrove

DECIMAL 类型用于金融计算，不接受 floor/ceil。普通的 FLOAT 明显比 DECIMAL 更高效。 - Kondybas

4

由于数据库中最主要的成本在于提取行，因此浮点数和十进制数之间的性能差异不应成为一个问题。 - Rick James

22

无需走得太远，根据Google地图，最佳的经纬度精度为FLOAT(10,6)。

- Mariano Peinador

你从哪里得到这个信息的？我找不到它。以防万一有什么变化。 - webfacer

4

@webfacer，在这里的“在MySQL中创建表格”部分中可以找到：https://developers.google.com/maps/documentation/javascript/mysql-to-maps，例如 lat FLOAT( 10, 6 ) NOT NULL, 和 lng FLOAT( 10, 6 ) NOT NULL。 - turrican_34

4

@webfacer，看起来MYSQL 8.0.17已经将FLOAT语法废弃。现在Mysql建议只使用无精度参数的FLOAT https://dev.mysql.com/doc/refman/8.0/en/numeric-type-overview.html和https://dev.mysql.com/doc/refman/5.5/en/floating-point-types.html - turrican_34

而且，MySQL一直忽略了可选地修饰FLOAT和DOUBLE声明中的括号中的数字。 - O. Jones

@webfacer，该链接不再具有该部分... - burf

确认[M,D]扩展选项已从8.0.17开始弃用：https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html#mysql-nutshell-deprecations - TonyG

8

为避免使用双精度数时出现的四舍五入误差，我们将纬度/经度乘以1,000,000后存储为数字类型在我们的Oracle数据库中。

因为纬度/经度到小数点后第6位已经具有10厘米的准确度，这已经足够我们的需求。许多其他数据库也将纬度/经度存储到小数点后第6位。

- user18443

2

如果你有大量的数据，那么乘以一些大数（比如一百万）是非常好的，因为整数运算（例如索引检索）比浮点数快得多。 - Kaitlin Duck Sherwood

@KaitlinDuckSherwood - 位是位 - 我不知道为什么32位浮点数在检索（索引或其他方式）方面会比32位整数慢。即使是浮点数运算，现在也足够快，不会成为问题。尽管如此，我同意使用隐含乘数与整数的评论：它最大化了您从32位中获得的精度。这是一种随着技术进步而具有未来性的方法。 - ToolmakerSteve

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Simon · Accepted Answer

基本上这取决于您对位置精度的需求。使用DOUBLE，您将具有3.5纳米的精度。DECIMAL(8,6)/(9,6)可降至16厘米。FLOAT为1.7m...

这个非常有趣的表格有一个更完整的列表: 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