在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数据库一起使用?

19 个解决方案

#1

Use MySQL's spatial extensions with GIS.

在GIS中使用MySQL的空间扩展。

#2

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

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.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的Spatial Extensions是最佳选择,因为您可以使用空间运算符和索引的完整列表。空间索引将允许您非常快速地执行基于距离的计算。请记住,从6.0开始,Spatial Extension仍然不完整。我没有放下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中的距离与第一个几何体的距离相同。这意味着如果您使用十进制度数,则您的距离测量值为十进制度数。这将使你很难从赤道中得到确切的结果。

#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构建的导航数据库执行此操作时,我进行了大量测试并回顾了代码,我使用了DECIMAL(18,12)(实际上是NUMERIC(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 Spatial Extensions是一个很好的选择,因为它们遵循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

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),特别是POINT来存储单个坐标。在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类时,存储坐标的参数顺序必须为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.

基于这篇维基文章http://en.wikipedia.org/wiki/Decimal_degrees#Accuracy,MySQL中的相应数据类型是十进制(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的FLOAT(10,6)。

#10

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

我们在oracle数据库中将纬度/经度X 1,000,000存储为NUMBERS,以避免双打出现错误。

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!

如果您依靠Google显示地图,标记,多边形等等,那么请让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.000000000000001”(长度为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位以上的十进制数字,那么无论如何你都可以将这些数据存储在你的字符串中,以防你以后想要检测一些逃亡或微生物;

• 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(使用Google Maps API,使用Python,Ruby on Rails,PHP,CodeIgniter,Laravel,Yii,Zend Framework等)。

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 VS 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:

虽然它并非对所有操作都是最佳的,但如果您制作地图图块或使用大量标记(点)只使用一个投影(例如墨卡托,如谷歌地图和许多其他滑动地图框架所期望的那样),我找到了什么我称之为“Vast Coordinate System”非常非常方便。基本上,你以某种方式存储x和y像素坐标 - 放大 - 我使用缩放级别23.这有几个好处:

• You do the expensive lat/lng to mercator pixel transformation once instead of every time you handle the point

您只需执行一次昂贵的lat / lng到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.

从记录中获取像素坐标需要一个右移和一个按位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中实现它们是切实可行的,这意味着您可以执行DISTINCT,每个像素位置只返回一条记录,这将减少后端返回的数字记录,这意味着更少的处理前端。

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

PostGIS中的空间函数比MySQL空间函数中的空间函数功能更强大(即不受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 ...所以使用double类型。在大多数情况下,使用float会导致不可预测的舍入值

#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位精度,那么自愿将比例缩小到例如是愚蠢的。 6位小数,并将精度限制为最多9个重要的数字(通常提出的十进制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)

在计算发生的精度中存储从源计算的数据(例如,如果应用程序代码使用双精度数,则将结果存储为双精度数)

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

TL;DR

Use FLOAT(8,5) if you're not working in NASA / military and not making aircrafts navi systems.

如果你不在NASA /军队工作而不是制造飞机导航系统,请使用FLOAT(8,5)。

---

To answer your question fully, you'd need to consider several things:

要完全回答您的问题,您需要考虑以下几点:

Format

• degrees minutes seconds: 40° 26′ 46″ N 79° 58′ 56″ W

度分秒:40°26'46“N 79°58'56”W

• degrees decimal minutes: 40° 26.767′ N 79° 58.933′ W

度小数分钟:40°26.767'N 79°58.933'W

• decimal degrees 1: 40.446° N 79.982° W

十进制度1:40.446°N 79.982°W

• decimal degrees 2: -32.60875, 21.27812

十进制度2:-32.60875,21.27812

• Some other home-made format? Noone forbids you from making your own home-centric coordinates system and store it as heading and distance from your home. This could make sense for some specific problems you're working on.

其他一些自制格式?没有人禁止您制作自己的以家庭为中心的坐标系统,并将其存储为距您家的航向和距离。这对于您正在处理的一些特定问题是有意义的。

So the first part of the answer would be - you can store the coordinates in the format your application uses to avoid constant conversions back and forth and make simpler SQL queries.

因此,答案的第一部分是 - 您可以以应用程序使用的格式存储坐标,以避免来回不断的转换并进行更简单的SQL查询。

Most probably you use Google Maps or OSM to display your data, and GMaps are using "decimal degrees 2" format. So it will be easier to store coordinates in the same format.

很可能您使用谷歌地图或OSM来显示您的数据,而GMaps使用“十进制度数2”格式。因此,以相同的格式存储坐标会更容易。

Precision

Then, you'd like to define precision you need. Of course you can store coordinates like "-32.608697550570334,21.278081997935146", but have you ever cared about millimeters while navigation to the point? If you're not working in NASA and not doing satellites or rockets or planes trajectories, you should be fine with several meters accuracy.

然后,您想要定义所需的精度。当然你可以存储像“-32.608697550570334,21.278081997935146”这样的坐标,但你有没有关心导航到点的毫米?如果你不是在美国国家航空航天局工作而不是做卫星或火箭或飞机轨迹,你应该没有几米精度。

Commonly used format is 5 digits after dots which gives you 50cm accuracy.

常用格式为点后5位数,精度为50cm。

Example: there is 1cm distance between X,21.2780818 and X,21.2780819. So 7 digits after dot give you 1/2cm precision and 5 digits after dot will give you 1/2 meters precision (because minimal distance between distinct points is 1m, so rounding error cannot be more than half of it). For most civil purposes it should be enough.

示例:X,21.2780818和X之间的距离为1厘米,21.2780819。因此,点后7位数字给你1 / 2cm精度,点后5位数将给你1/2米精度(因为不同点之间的最小距离是1米,所以舍入误差不能超过它的一半)。对于大多数民用目的来说应该足够了。

degrees decimal minutes format (40° 26.767′ N 79° 58.933′ W) gives you exactly the same precision as 5 digits after dot

度十进制分钟格式(40°26.767'N 79°58.933'W)为您提供与点后5位数完全相同的精度

Space-efficient storage

If you've selected decimal format, then your coordinate is a pair (-32.60875, 21.27812). Obviously, 2 x (1 bit for sign, 2 digits for degrees and 5 digits for exponent) will be enough.

如果您选择了十进制格式,那么您的坐标是一对(-32.60875,21.27812)。显然,2 x(符号为1位,度为2位,指数为5位)就足够了。

So here I'd like to support Alix Axel from comments saying that Google suggestion to store it in FLOAT(10,6) is really extra, because you don't need 4 digits for main part (since sign is separated and latitude is limited to 90 and longitude is limited to 180). You can easily use FLOAT(8,5) for 1/2m precision or FLOAT(9,6) for 50/2cm precision. Or you can even store lat and long in separated types, because FLOAT(7,5) is enough for lat. See MySQL float types reference. Any of them will be like normal FLOAT and equal to 4 bytes anyway.

所以在这里我想支持Alix Axel的评论说,Google建议将它存储在FLOAT(10,6)中是非常多的,因为主要部分不需要4位数(因为符号是分开的,纬度是有限的到90和经度限制为180)。您可以轻松地将FLOAT(8,5)用于1 / 2m精度或FLOAT(9,6)用于50 / 2cm精度。或者您甚至可以将lat和long存储在分离的类型中,因为FLOAT(7,5)足以用于lat。请参阅MySQL float类型参考。它们中的任何一个都将像普通的FLOAT一样,无论如何都等于4个字节。

Usually space is not an issue nowadays, but if you want to really optimize the storage for some reason (Disclaimer: don't do pre-optimization), you may compress lat(no more than 91 000 values + sign) + long(no more than 181 000 values + sign) to 21 bits which is significantly less than 2xFLOAT (8 bytes == 64 bits)

通常空间现在不是问题,但如果你想出于某种原因真的优化存储(免责声明:不做预优化),你可以压缩lat(不超过91000值+符号)+ long(no超过181 000个值+符号)到21位,明显小于2xFLOAT(8个字节== 64位)

#18

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计算需要精度,因此请使用某种类型的十进制类型,并使精度至少比存储的数字高2,以便执行数学计算。我不知道我的sql数据类型,但在SQL服务器中,人们经常使用float或real而不是decimal,并且遇到麻烦,因为这些是估计的数字而不是真实的数字。因此,只需确保您使用的数据类型是真正的十进制类型而不是浮动十进制类型,您应该没问题。

#19

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.

FLOAT应该为您提供所需的所有精度,并且比将每个坐标存储为字符串等更好地用于比较功能。

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之前,DECIMAL列以精确的精度存储值,因为它们表示为字符串,但DECIMAL值的计算是使用浮点运算完成的。从5.0.3开始,MySQL执行DECIMAL操作,精度为64位十进制数,这可以解决DECIMAL列中最常见的不准确问题