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

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

记住，我将在lat / long对上执行计算，什么数据类型最适合用于MySQL数据库?

18 个解决方案

#1

142

Use MySQL's spatial extensions with GIS.

使用MySQL的空间扩展与GIS。

#2

139

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"

谷歌提供了一个用谷歌映射完成示例“存储定位器”应用程序的PHP/MySQL解决方案。在本例中，它们将lat/lng值存储为“Float”，长度为“10,6”

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

#3

107

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

基本上，这取决于您对位置所需的精度。使用双精度，可以达到3.5纳米的精度。十进制(8,6)/(9,6)降至16cm。浮动是1.7…

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.

希望这个有帮助。

#4

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空间，只是让你知道在你走得太远之前的陷阱。

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

如果你只处理点和距离函数，这没问题。如果需要对多边形、线或缓冲点进行任何计算，空间操作符不会提供精确的结果，除非使用“relate”操作符。在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空间中的距离与你的第一个几何空间是相同的。这意味着如果你使用十进制度，那么你的距离测量是十进制度。这将使你很难得到确切的结果，因为你从赤道。

#5

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构建导航数据库时，我做了大量的测试并查看了代码，我使用了十进制(18,12)(实际上是数字(18,12)，因为它是firebird)。

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 Extensions are a good alternative because they follow The OpenGIS Geometry Model. I didn't use them because I needed to keep my database portable.

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

#6

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.
最精确的选项是DOUBLE。
The most common seen type used is DECIMAL(8,6)/(9,6).
最常见的类型是DECIMAL(8,6)/(9,6)。

As of MySQL 5.7, consider using Spatial Data Types (SDT), specifically POINT for 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)，专门用于存储单个坐标。在5.7之前，SDT不支持索引(表类型为MyISAM时只有5.6个例外)。

Note:

注意:

When using POINT class, the order of the arguments for storing coordinates must be POINT(latitude, longitude).
当使用POINT类时，存储坐标的参数的顺序必须是点(纬度，经度)。
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).
使用SDT的最大好处是您可以访问空间分析函数，例如，计算两个点之间的距离(ST_Distance)并确定一个点是否包含在另一个区域(ST_Contains)。

#7

Based on this wiki article http://en.wikipedia.org/wiki/Decimal_degrees#Accuracy the 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#的准确性，MySQL中适当的数据类型是Decimal(9,6)，用于在单独的字段中存储经度和纬度。

#8

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位是可以的。两者都应该“签署”以允许负值。

#9

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

根据谷歌地图，最好的是lat和lng的浮动(10,6)。

#10

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作为数字存储在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厘米，这就是我们所需要的。许多其他数据库也将lat/long存储到小数点后6位。

#11

In a completely different and simpler perspective:

从一个完全不同和更简单的角度:

if you are relying on Google for showing your maps, markers, polygons, whatever, then let the calculations be done by Google!
如果您依赖谷歌显示地图、标记、多边形等，那么就让谷歌进行计算吧!
you save resources on your server and you simply store the latitude and longitude together as a single string (VARCHAR), E.g.: "-0000.0000001,-0000.000000000000001" (35 length and if a number has more than 7 decimal digits then it gets rounded);
您将资源保存在服务器上，您只需将纬度和经度作为一个字符串(VARCHAR)存储在一起，例如:“-0000.0000001，- 0000.00000000001”(35长度，如果一个数字有超过7位小数，那么它将被四舍五入);
if Google returns more than 7 decimal digits per number, you can get that data stored in your string anyway, just in case you want to detect some flees or microbes in the future;
如果谷歌的每个数字都超过7个十进制数字，那么你就可以把数据存储在你的字符串中，以防将来你想要检测到一些flees或微生物;
you can use their distance matrix or their geometry library for calculating distances or detecting points in certain areas with calls as simple as this: google.maps.geometry.poly.containsLocation(latLng, bermudaTrianglePolygon))
您可以使用它们的距离矩阵或它们的几何库来计算某些区域的距离或检测点，只需这样简单的调用:google.maps.geometry.poly。containsLocation(latLng bermudaTrianglePolygon))
there are plenty of "server-side" APIs you can use (in Python, Ruby on Rails, PHP, CodeIgniter, Laravel, Yii, Zend Framework, etc.) that use Google Maps API.
您可以使用许多“服务器端”API(在Python、Ruby on Rails、PHP、CodeIgniter、Laravel、Yii、Zend Framework等中)使用谷歌Maps API。

This way you don't need to worry about indexing numbers and all the other problems associated with data types that may screw up your coordinates.

这样，您就不需要担心索引号和与数据类型相关的所有其他问题，这些问题可能会使您的坐标出错。

#12

depending on you application, i suggest using FLOAT(9,6)

根据应用程序的不同，我建议使用FLOAT(9,6)

spatial keys will give you more features, but in by production benchmarks the floats are much faster than the spatial keys. (0,01 VS 0,001 in AVG)

空间键会给你更多的特性，但在生产基准测试中，浮动比空间键快得多。(AVG中的0,01和0,001)

#13

While it isn't optimal for all operations, if you are making map tiles or working with large numbers of markers (dots) with only one projection (e.g. Mercator, like Google Maps and many other slippy maps frameworks expect), I have found what I call "Vast Coordinate System" to be really, really handy. Basically, you store x and y pixel coordinates at some way-zoomed-in -- I use zoom level 23. This has several benefits:

虽然它不是适合所有操作,如果您正在地图瓦片或处理大量的标记(点)只有一个投影(例如墨卡托,像谷歌地图和许多其他敏捷的地图框架预计),我发现我所说的“巨大的坐标系统”是真的,非常方便。基本上，你以某种方式存储x和y像素坐标——放大——我使用缩放等级23。这有几个好处:

You do the expensive lat/lng to mercator pixel transformation once instead of every time you handle the point
当你处理这个点的时候，你会把昂贵的lat/液化天然气转化为mercator的像素转换。
Getting the tile coordinate from a record given a zoom level takes one right shift.
从给定缩放级别的记录中获取平铺的坐标需要右移一次。
Getting the pixel coordinate from a record takes one right shift and one bitwise AND.
从记录中获取像素坐标需要一个右移和一个位移。
The shifts are so lightweight that it is practical to do them in SQL, which means you can do a DISTINCT to return only one record per pixel location, which will cut down on the number records returned by the backend, which means less processing on the front end.
这种转换是如此的轻量，以至于在SQL中使用它们是很实用的，这意味着您可以在每个像素位置上只返回一条记录，这将减少后端返回的数字记录，这意味着前端处理更少。

I talked about all this in a recent blog post: http://blog.webfoot.com/2013/03/12/optimizing-map-tile-generation/

我在最近的一篇博文中提到了这一切:http://blog.webfoot.com/2013/03/12/optimizing-map-tile-generation/

#14

The spatial functions in PostGIS are much more functional (i.e. not constrained to BBOX operations) than those in the MySQL spatial functions. Check it out: link text

与MySQL空间函数相比，PostGIS中的空间功能更具有功能性(即不局限于BBOX操作)。查看:链接文本

#15

MySQL uses double for all floats ... So use type double. Using float will lead to unpredictable rounded values in most situations

MySQL对所有浮动都使用double…所以利用类型双。在大多数情况下，使用浮动会导致不可预测的圆值

#16

I am highly surprised by some answers/comments.

我对一些回答/评论感到非常惊讶。

Why on earth would anyone be willing to voluntarely "pre-decrease" the precision, and then later on perform calculations on the worse numbers? Sounds ultimately stupid.

为什么地球上有人愿意自愿“预先降低”精度，然后再对更坏的数字进行计算呢?最终听起来很愚蠢。

If the source has 64-bit precision, certainly it would be dumb to voluntarely fix the scale to eg. 6 decimals, and limit the precision to a maximum of 9 significant digts (which happens with the commonly proposed decimal 9.6 format).

如果源程序具有64位精度，那么将其自动固定为eg无疑是愚蠢的。6个小数，并将精度限制为最大9个有效数字(通常采用的是decimal 9.6格式)。

Naturally, one stores the data with the precision that the source material has. The only reason to decrease precision would be limited storage space.

自然地，我们以源材料所具有的精度存储数据。降低精度的唯一原因是有限的存储空间。

Store source figures with original accuracy
存储原始数据的准确性
Store figures calculated from the source in the precision the calculation happens (eg. if the aplication code uses doubles, store the results as doubles)
存储数据从源中计算出，在精度中进行计算。如果aplication代码使用了double，则将结果存储为double)

The decimal 9.6-format causes a snap-to-grid phenomen. That should be the very last step, if it is at all to happen.

十进制9.6格式导致快照到网格表示法。这应该是最后一步，如果它真的发生了。

I wouldn't invite accumulated errors to my nest.

我不会把积累的错误带到我的巢里。

#17

-1

A FLOAT should give you all of the precision you need, and be better for comparison functions than storing each co-ordinate as a string or the like.

浮动应该能够提供您所需的所有精度，并且对于比较函数比将每个坐标存储为字符串之类更好。

If your MySQL version is earlier than 5.0.3, you may need to take heed of certain floating point comparison errors however.

如果MySQL版本早于5.0.3，您可能需要注意某些浮点比较错误。

Prior to MySQL 5.0.3, DECIMAL columns store values with exact precision because they are represented as strings, but calculations on DECIMAL values are done using floating-point operations. As of 5.0.3, MySQL performs DECIMAL operations with a precision of 64 decimal digits, which should solve most common inaccuracy problems when it comes to DECIMAL columns

在MySQL 5.0.3之前，十进制列以精确的精度存储值，因为它们以字符串形式表示，但是十进制值的计算是使用浮点运算完成的。从5.0.3开始，MySQL执行十进制运算，精度为64位小数，这应该可以解决十进制列中最常见的不准确性问题

#18

-1

Lat Long calculations require precision, so use some type of decimal type and make the precision at least 2 higher than the number you will store in order to perform math calculations. I don't know about the my sql datatypes but in SQL server people often use float or real instead of decimal and get into trouble because these are are estimated numbers not real ones. So just make sure the data type you use is a true decimal type and not a floating decimal type and you should be fine.

Lat Long计算需要精确，所以使用某种类型的decimal类型，并使精度至少比您将要存储的数字高2，以便进行数学计算。我不知道我的sql数据类型，但是在sql server中，人们经常使用浮点数或实数而不是十进制数，这会带来麻烦，因为这些是估计的数字而不是实数。所以只要确保你使用的数据类型是真正的十进制类型而不是浮动的十进制类型就可以了。

#1

142