Rails和MySQL上纬度和经度的最佳列类型

I'm wondering what's the best column type to store latitude/longitude on MySQL + Rails.

我想知道在MySQL + Rails上存储纬度/经度的最佳列类型是什么。

Precision must be enough to store every bit of lat/lng degrees obtained from mobile devices and/or geocoders.

精度必须足以存储从移动设备和/或地理编码器获得的每一位lat / lng度。

Storage requirement should be minimal for the best query performance.

存储要求应该是最小的,以获得最佳查询性能。

From Google's official document:

来自谷歌的官方文件:

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

With the current zoom capabilities of Google Maps, you should only need 6 digits of precision after the decimal. To keep the storage space required for your table at a minimum, you can specify that the lat and lng attributes are floats of size (10,6). That will let the fields store 6 digits after the decimal, plus up to 4 digits before the decimal, e.g. -123.456789 degrees.

使用Google地图的当前缩放功能,您应该只需要小数点后的6位精度。要将表所需的存储空间保持在最小值,可以指定lat和lng属性是大小为浮点数(10,6)。这将使字段存储小数点后的6位数,加上小数点前最多4位数,例如-123.456789度。

So, actually FLOAT(10,6) is recommended by Google.

所以,实际上谷歌推荐使用FLOAT(10,6)。

However, with Rails 3, there seems no easy way to define FLOAT column type with precision after the decimal point. For instance, you could write a migration with raw SQL as follows:

但是,使用Rails 3,似乎没有简单的方法来在小数点后精确定义FLOAT列类型。例如,您可以使用原始SQL编写迁移,如下所示:

def self.up
  execute <<-SQL
  ALTER TABLE places
    ADD `lat` FLOAT(10,6),
    ADD `lng` FLOAT(10,6)
  SQL
  add_index :places, [ :lat, :lng ]
end

But the schema.rb as a consequence will look like this:

但schema.rb的结果如下:

t.float "lat", :limit => 10
t.float "lng", :limit => 10

which is missing the precision for the fractional part.

这缺少了小数部分的精度。

Here I can see several options:

在这里,我可以看到几个选项:

Use FLOAT(10,6) for optimal production performance, and don't dump schema (e.g. rake db:test:load) on development.

使用FLOAT(10,6)获得最佳生产性能,并且不要在开发时转储模式(例如rake db:test:load)。

Use DECIMAL(10,6), which is supported by Rails, but it takes 6 bytes, 1.5 times larger than FLOAT (see: http://dev.mysql.com/doc/refman/5.1/en/storage-requirements.html). Maybe this would be a good compromise?

使用Rails支持的DECIMAL(10,6),但它需要6个字节,比FLOAT大1.5倍(参见:http://dev.mysql.com/doc/refman/5.1/en/storage-requirements。 HTML)。也许这将是一个很好的妥协?

Use DOUBLE, which is much roomier than Google's requirement, and takes 8 bytes, 2 times larger than FLOAT. It's simple as well.

使用比Google要求更宽敞的DOUBLE,占用8个字节,比FLOAT大2倍。它也很简单。

What's your recommendation?

你的建议是什么?

2 个解决方案

#1

KISS (i.e. submit to the framework), it's not a lot of bytes. It'll only matter more if you're using particular indexes.

KISS(即提交到框架),它不是很多字节。如果你使用特定的索引,那就更重要了。

#2

Depending on the type of query you want to do on your data, you might want to look into the geometry type:

根据您要对数据执行的查询类型,您可能需要查看几何类型:

http://dev.mysql.com/doc/refman/5.1/en/creating-a-spatially-enabled-mysql-database.html

I'm not familiar with RoR, but this line, in particular:

我不熟悉RoR,但这一行特别是:

add_index :places, [ :lat, :lng ]

... seems to indicate that you're planning to run geospatial queries. If so, the index will be useless for all intents and purposes, because a btree index won't help for nearest-neighbor point searches.

...似乎表明您计划运行地理空间查询。如果是这样,索引对于所有意图和目的都是无用的,因为btree索引对最近邻点搜索没有帮助。

#1