什么 MySQL 数据类型应该用于具有 8 个小数位的纬度/经度？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12504208/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What MySQL data type should be used for Latitude/Longitude with 8 decimal places?
提问by Edward
I'm working with map data, and the Latitude/Longitudeextends to 8 decimal places. For example:
我正在处理地图数据，并且Latitude/Longitude扩展到小数点后 8 位。例如：
Latitude 40.71727401
Longitude -74.00898606
I saw in the Google documentwhich uses:
我在Google 文档中看到 它使用：
lat FLOAT( 10, 6 ) NOT NULL,
lng FLOAT( 10, 6 ) NOT NULL
however, their decimal places only go to 6.
Should I use FLOAT(10, 8)or is there another method to consider for storing this data so it's precise. It will be used with map calculations. Thanks!
但是，它们的小数位只能到 6。
我是否应该使用FLOAT(10, 8)或是否有另一种方法来考虑存储这些数据，以便它是精确的。它将与地图计算一起使用。谢谢！
回答by gandaliter
DECIMAL is the MySQL data-type for exact arithmetic. Unlike FLOAT its precision is fixed for any size of number, so by using it instead of FLOAT you might avoid precision errors when doing some calculations. If you were just storing and retrieving the numbers without calculation then in practice FLOAT would be safe, although there's no harm in using DECIMAL. With calculations FLOAT is still mostly ok, but to be absolutely sure of 8d.p. precision you should use DECIMAL.
DECIMAL 是用于精确算术的 MySQL 数据类型。与 FLOAT 不同，它的精度对于任何大小的数字都是固定的，因此通过使用它而不是 FLOAT，您可以在进行某些计算时避免精度错误。如果您只是在没有计算的情况下存储和检索数字，那么实际上 FLOAT 将是安全的，尽管使用 DECIMAL 没有坏处。通过计算 FLOAT 仍然基本可以，但要绝对确定 8d.p。精度你应该使用 DECIMAL。
Latitudes range from -90 to +90 (degrees), so DECIMAL(10, 8) is ok for that, but longitudes range from -180 to +180 (degrees) so you need DECIMAL(11, 8). The first number is the total number of digits stored, and the second is the number after the decimal point.
纬度范围从 -90 到 +90（度），因此 DECIMAL(10, 8) 可以，但经度范围从 -180 到 +180（度），因此您需要 DECIMAL(11, 8)。第一个数字是存储的总位数，第二个数字是小数点后的数字。
In short: lat DECIMAL(10, 8) NOT NULL, lng DECIMAL(11, 8) NOT NULL
简而言之： lat DECIMAL(10, 8) NOT NULL, lng DECIMAL(11, 8) NOT NULL
Thisexplains how MySQL works with floating-point data-types.
这解释了 MySQL 如何处理浮点数据类型。
UPDATE:MySQL supports Spatial data typesand Pointis a single-value type which can be used. Example:
更新：MySQL 支持空间数据类型并且Point是可以使用的单值类型。例子：
CREATE TABLE `buildings` (
`coordinate` POINT NOT NULL,
/* Even from v5.7.5 you can define an index for it */
SPATIAL INDEX `SPATIAL` (`coordinate`)
) ENGINE=InnoDB;
/* then for insertion you can */
INSERT INTO `buildings`
(`coordinate`)
VALUES
(POINT(40.71727401 -74.00898606));
回答by K-Gun
Additionally, you will see that floatvalues are rounded.
此外，您将看到float值已四舍五入。
// e.g: given values 41.0473112,29.0077011 float(11,7) | decimal(11,7) --------------------------- 41.0473099 | 41.0473112 29.0077019 | 29.0077011
回答by Jignesh Joisar
回答by O?uzhan KURNU?
You can set your data-type as signed integer. When you storage coordinates to SQL you can set as lat*10000000 and long*10000000. And when you selecting with distance/radius you will divide storage coordinates to 10000000. I was test it with 300K rows, query response time is good. ( 2 x 2.67GHz CPU, 2 GB RAM, MySQL 5.5.49 )
您可以将数据类型设置为有符号整数。当您将坐标存储到 SQL 时，您可以设置为 lat*10000000 和 long*10000000。当您选择距离/半径时，您会将存储坐标划分为 10000000。我用 300K 行对其进行了测试，查询响应时间很好。( 2 x 2.67GHz CPU, 2 GB RAM, MySQL 5.5.49 )
回答by Sam Sabey
Do not use float... It will round your coordinates, resulting in some strange occurrences.
不要使用浮动...它会四舍五入你的坐标，导致一些奇怪的事件。
Use decimal
使用十进制
回答by Bill--
MySQL now has support for spatial data types since this question was asked. So the the current accepted answer is not wrong, but if you're looking for additional functionality like finding all points within a given polygon then use POINT data type.
自从提出这个问题以来，MySQL 现在支持空间数据类型。因此，当前接受的答案并没有错，但是如果您正在寻找其他功能，例如查找给定多边形内的所有点，请使用 POINT 数据类型。
Checkout the Mysql Docs on Geospatial data typesand the spatial analysis functions
回答by dayvee.cz
I believe the best way to store Lat/Lng in MySQL is to have a POINT column (2D datatype) with a SPATIAL index.
我相信在 MySQL 中存储 Lat/Lng 的最佳方法是拥有一个带有 SPATIAL 索引的 POINT 列（2D 数据类型）。
CREATE TABLE `cities` (
`zip` varchar(8) NOT NULL,
`country` varchar (2) GENERATED ALWAYS AS (SUBSTRING(`zip`, 1, 2)) STORED,
`city` varchar(30) NOT NULL,
`centre` point NOT NULL,
PRIMARY KEY (`zip`),
KEY `country` (`country`),
KEY `city` (`city`),
SPATIAL KEY `centre` (`centre`)
) ENGINE=InnoDB;
INSERT INTO `cities` (`zip`, `city`, `centre`) VALUES
('CZ-10000', 'Prague', POINT(50.0755381, 14.4378005));
回答by ToolmakerSteve
Code to use/prove precision of O?uzhan KURNU?'s answer.
使用/证明O?uzhan KURNU? 答案的精确性的代码。
SUMMARY:
Great precision (~1cm) in a small size (4B).
总结：
小尺寸 (4B) 中的高精度 (~1cm)。
Precisionis (very close to) 7 decimal digits for values over range [-180, 180].
That's 7 digits to right of decimal (~1cm), for a total of 9 digits (or 10 digits, if counting the initial "1" of "180") near +-180.
Contrast this with a 4-byte float, which has only ~7 digits total, so ~5 digits to right of decimal near +=180 (~1m).
对于超出范围 [-180, 180] 的值，精度是（非常接近）7 位十进制数字。
那是小数点右边的7 位数字 (~1cm)，在 +-180 附近总共有 9 位数字（或 10 位数字，如果计算“180”的初始“1”）。
将此与 4 字节的 float 进行对比，后者总共只有 ~7 位数字，因此小数点右侧 ~5 位数字接近 +=180 (~1m)。
Methods to use this approach:
使用这种方法的方法：
const double Fixed7Mult = 10000000;
public static int DecimalDegreesToFixed7(double degrees)
{
return RoundToInt(degrees * Fixed7Mult);
}
public static double Fixed7ToDecimalDegrees(int fixed7)
{
return fixed7 / (double)Fixed7Mult;
}
Tests of precision:
精度测试：
/// <summary>
/// This test barely fails in 7th digit to right of decimal point (0.0000001 as delta).
/// Passes with 0.0000002 as delta.
/// </summary>
internal static void TEST2A_LatLongPrecision()
{
//VERY_SLOW_TEST Test2A_ForRange(-180, 360, 0.0000001);
//FAILS Test2A_ForRange(-180, 0.1, 0.0000001);
Test2A_ForRange(-180, 0.1, 0.0000002);
Test2A_ForRange(0, 0.1, 0.0000002);
Test2A_ForRange(179.9, 0.1, 0.0000002);
}
/// <summary>
/// Test for the smallest difference. A: 9.9999994E-08.
/// </summary>
internal static void TEST2B_LatLongPrecision()
{
double minDelta = double.MaxValue;
double vAtMinDelta = 0;
//VERY_SLOW_TEST Test2B_ForRange(-180, 360, ref minDelta, ref vAtMinDelta);
Test2B_ForRange(-180, 0.1, ref minDelta, ref vAtMinDelta);
Test2B_ForRange(0, 0.1, ref minDelta, ref vAtMinDelta);
Test2B_ForRange(179.9, 0.1, ref minDelta, ref vAtMinDelta);
// Fails. Smallest delta is 9.9999994E-08; due to slight rounding error in 7th decimal digit.
//if (minDelta < 0.0000001)
// throw new InvalidProgramException($"Fixed7 has less than 7 decimal digits near {vAtMinDelta}");
// Passes.
if (minDelta < 0.000000099)
throw new InvalidProgramException($"Fixed7 has less than 7 decimal digits near {vAtMinDelta}");
}
Helper methods used by tests:
测试使用的辅助方法：
private static void Test2A_ForRange(double minV, double range, double deltaV)
{
double prevV = 0;
int prevFixed7 = 0;
bool firstTime = true;
double maxV = minV + range;
for (double v = minV; v <= maxV; v += deltaV) {
int fixed7 = DecimalDegreesToFixed7(v);
if (firstTime)
firstTime = false;
else {
// Check for failure to distinguish two values that differ only in 7th decimal digit.
// Fails.
if (fixed7 == prevFixed7)
throw new InvalidProgramException($"Fixed7 doesn't distinguish between {prevV} and {v}");
}
prevV = v;
prevFixed7 = fixed7;
}
}
private static void Test2B_ForRange(double minV, double range, ref double minDelta, ref double vAtMinDelta)
{
int minFixed7 = DecimalDegreesToFixed7(minV);
int maxFixed7 = DecimalDegreesToFixed7(minV + range);
bool firstTime = true;
double prevV = 0; // Initial value is ignored.
for (int fixed7 = minFixed7; fixed7 < maxFixed7; fixed7++) {
double v = Fixed7ToDecimalDegrees(fixed7);
if (firstTime)
firstTime = false;
else {
double delta = Math.Abs(v - prevV);
if (delta < minDelta) {
minDelta = delta;
vAtMinDelta = v;
}
}
prevV = v;
}
}
回答by gilcierweb
Using migrate ruby on rails
在 Rails 上使用 migrate ruby
class CreateNeighborhoods < ActiveRecord::Migration[5.0]
def change
create_table :neighborhoods do |t|
t.string :name
t.decimal :latitude, precision: 15, scale: 13
t.decimal :longitude, precision: 15, scale: 13
t.references :country, foreign_key: true
t.references :state, foreign_key: true
t.references :city, foreign_key: true
t.timestamps
end
end
end
