在 MySQL 数据库中存储纬度/经度时使用的理想数据类型是什么？

What is the ideal data type to use when storing latitude / longitude in a MySQL database?

提问by Codebeef

Bearing in mind that I'll be performing calculations on lat / long pairs, what datatype is best suited for use with a MySQL database?

请记住，我将在经纬度对上执行计算，哪种数据类型最适合与 MySQL 数据库一起使用？

采纳答案by Kirk Strauser

Use MySQL's spatial extensionswith GIS.

将 MySQL 的空间扩展与 GIS 结合使用。

回答by Ted Avery

Google provides a start to finish PHP/MySQL solution for an example "Store Locator" application with Google Maps. In this example, they store the lat/lng values as "Float" with a length of "10,6"

Google 为带有 Google 地图的示例“商店定位器”应用程序提供了一个从头到尾的 PHP/MySQL 解决方案。在这个例子中，他们将 lat/lng 值存储为“Float”，长度为“10,6”

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

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

回答by Simon

Basically it depends on the precision you need for your locations. Using DOUBLE you'll have a 3.5nm precision. DECIMAL(8,6)/(9,6) goes down to 16cm. FLOAT is 1.7m...

基本上，这取决于您所在位置所需的精度。使用 DOUBLE，您将获得 3.5nm 的精度。DECIMAL(8,6)/(9,6) 下降到 16cm。FLOAT 是 1.7m...

This very interesting table has a more complete list: http://mysql.rjweb.org/doc.php/latlng:

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

Hope this helps.

希望这可以帮助。

回答by James Schek

MySQL's Spatial Extensions are the best option because you have the full list of spatial operators and indices at your disposal. A spatial index will allow you to perform distance-based calculations very quickly. Please keep in mind that as of 6.0, the Spatial Extension is still incomplete. I am not putting down MySQL Spatial, only letting you know of the pitfalls before you get too far along on this.

MySQL 的空间扩展是最好的选择，因为您可以使用完整的空间运算符和索引列表。空间索引将允许您非常快速地执行基于距离的计算。请记住，从 6.0 开始，空间扩展仍然不完整。我不是在贬低 MySQL Spatial，只是在你深入了解它之前让你知道其中的陷阱。

If you are dealing strictly with points and only the DISTANCE function, this is fine. If you need to do any calculations with Polygons, Lines, or Buffered-Points, the spatial operators do not provide exact results unless you use the "relate" operator. See the warning at the top of 21.5.6. Relationships such as contains, within, or intersects are using the MBR, not the exact geometry shape (i.e. an Ellipse is treated like a Rectangle).

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

Also, the distances in MySQL Spatial are in the same units as your first geometry. This means if you're using Decimal Degrees, then your distance measurements are in Decimal Degrees. This will make it very difficult to get exact results as you get furthur from the equator.

此外，MySQL Spatial 中的距离与您的第一个几何体的单位相同。这意味着如果您使用十进制度数，那么您的距离测量值以十进制度数为单位。当您远离赤道时，这将很难获得准确的结果。

回答by Richard Harrison

When I did this for a navigation database built from ARINC424 I did a fair amount of testing and looking back at the code, I used a DECIMAL(18,12) (Actually a NUMERIC(18,12) because it was firebird).

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

Floats and doubles aren't as precise and may result in rounding errors which may be a very bad thing. I can't remember if I found any real data that had problems - but I'm fairly certain that the inability to store accurately in a float or a double could cause problems

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

The point is that when using degrees or radians we know the range of the values - and the fractional part needs the most digits.

关键是当使用度数或弧度时，我们知道值的范围 - 小数部分需要最多的数字。

The MySQL Spatial Extensionsare a good alternative because they follow The OpenGIS Geometry Model. I didn't use them because I needed to keep my database portable.

在MySQL中的空间扩展是一个很好的选择，因为他们遵循的开放GIS几何模型。我没有使用它们，因为我需要保持我的数据库可移植。

回答by Gajus

Depends on the precision that you require.

取决于您需要的精度。

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

From: http://mysql.rjweb.org/doc.php/latlng

来自：http: //mysql.rjweb.org/doc.php/latlng

To summarise:

总结一下：

• The most precise available option is DOUBLE.
• The most common seen type used is DECIMAL(8,6)/(9,6).

• 最精确的可用选项是DOUBLE。
• 最常见的类型是DECIMAL(8,6)/(9,6).

As of MySQL 5.7, consider using Spatial Data Types(SDT), specifically POINTfor storing a single coordinate. Prior to 5.7, SDT does not support indexes (with exception of 5.6 when table type is MyISAM).

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

Note:

笔记：

• When using POINTclass, the order of the arguments for storing coordinates must be POINT(latitude, longitude).
• There is a special syntax for creating a spatial index.
• The biggest benefit of using SDT is that you have access to Spatial Analyses Functions, e.g. calculating distance between two points (ST_Distance) and determining whether one point is contained within another area (ST_Contains).

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

回答by saeed khalafinejad

Based on this wiki article http://en.wikipedia.org/wiki/Decimal_degrees#Accuracythe appropriate data type in MySQL is Decimal(9,6) for storing the longitude and latitude in separate fields.

基于这篇 wiki 文章 http://en.wikipedia.org/wiki/Decimal_degrees#Accuracy，MySQL中适当的数据类型是 Decimal(9,6)，用于将经度和纬度存储在不同的字段中。

回答by Alex Holsgrove

Use DECIMAL(8,6)for latitude (90 to -90 degrees) and DECIMAL(9,6)for longitude (180 to -180 degrees). 6 decimal places is fine for most applications. Both should be "signed" to allow for negative values.

使用DECIMAL(8,6)纬度（90至-90度）和DECIMAL(9,6)经度（180〜-180度）。6 位小数适用于大多数应用程序。两者都应该“签名”以允许负值。

回答by Mariano Peinador

No need to go far, according to Google Maps, the best is FLOAT(10,6) for lat and lng.

不需要走多远，根据谷歌地图，最好的是 FLOAT(10,6) for lat 和 lng。

回答by Mariano Peinador

We store latitude/longitude X 1,000,000 in our oracle database as NUMBERS to avoid round off errors with doubles.

我们将纬度/经度 X 1,000,000 作为 NUMBERS 存储在我们的 oracle 数据库中，以避免双精度舍入错误。

Given that latitude/longitude to the 6th decimal place was 10 cm accuracy that was all we needed. Many other databases also store lat/long to the 6th decimal place.

鉴于到小数点后 6 位的纬度/经度精度为 10 厘米，这就是我们所需要的。许多其他数据库也将纬度/经度存储到小数点后第 6 位。