什么MySQL数据类型应该使用8位小数的经度/经度?

I'm working with map data, and the Latitude/Longitude extends to 8 decimal places. For example:

我使用地图数据，纬度/经度扩展到8位小数。例如:

Latitude 40.71727401
Longitude -74.00898606

I saw in the Google document which uses:

我在谷歌文件中看到它使用:

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)还是需要考虑另一种方法来存储这些数据，这样才能更精确。它将用于地图计算。谢谢!

6 个解决方案

#1

405

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数据类型，用于精确的算术。与浮点数不同，它的精度是固定的，所以使用它代替浮点数可以避免在进行某些计算时的精度错误。如果只是存储和检索数字而不进行计算，那么在实践中浮点数是安全的，尽管使用DECIMAL没有什么害处。计算浮点数仍然可以，但绝对可以确定的是8d。你应该使用十进制。

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(度)，所以十进制(10,8)是可以的，但是经度是从-180到+180(度)，所以需要十进制(11,8)。

In short: lat DECIMAL(10, 8) NOT NULL, lng DECIMAL(11, 8) NOT NULL

简而言之:lat DECIMAL(10,8)不是NULL, lng(11.8)不是NULL

This explains how MySQL works with floating-point data-types.

这解释了MySQL如何处理浮点数据类型。

#2

Additionally, you will see that float values rounded.

此外，您将看到浮点值是四舍五入的。

// e.g: given values 41.0473112,29.0077011

float(11,7) | decimal(11,7)
---------------------------
41.0473099  | 41.0473112
29.0077019  | 29.0077011

#3

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。当你选择距离/半径时，你将存储坐标分割为10000。我用300K行测试它，查询响应时间很好。(2 x 2.67GHz CPU, 2 GB RAM, MySQL 5.5.5.49)

#4

The best way in my case was save coordinates as DOUBLE.

在我的例子中，最好的方法是将坐标保存为DOUBLE。

lat DOUBLE NOT NULL,  
lng DOUBLE NOT NULL

It will save the entire value without any rounding.

它将保存整个值，不需要任何舍入。

If you need to round the value, my advice is that this treatment is done at the origin of the data, for example in the UI.

如果需要对值进行四舍五入，我的建议是，这种处理是在数据的起点进行的，例如在UI中。

#5

Using migrate ruby on rails

使用迁移ruby on rails

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

#6

Do not use float... It will round your coordinates, resulting in some strange occurrences.

不使用浮动……它会围绕你的坐标，导致一些奇怪的事件。

Use decimal

使用十进制

#1

405