数据库/SQL:如何存储经度/纬度数据? [英] Database/SQL: How to store longitude/latitude data?

问题描述

性能问题...

我有一个具有地理位置数据(经度和纬度)的房屋数据库.

I have a database of houses that have geolocation data (longitude & latitude).

我想做的是找到使用InnoDB数据库引擎将位置数据存储在MySQL(v5.0.24a)中的最佳方法，这样我就可以在返回所有原始记录的地方执行很多查询在x1和x2 latitude之间以及y1和y2 longitude之间.

What I want to do is find the best way to store the locational data in my MySQL (v5.0.24a) using InnoDB database-engine so that I can perform a lot of queries where I'm returning all the home records that are between x1 and x2 latitude and y1 and y2 longitude.

现在，我的数据库架构是

Right now, my database schema is

---------------------
Homes   
---------------------
geolat - Float (10,6)
geolng - Float (10,6)
---------------------

我的查询是:

SELECT ... 
WHERE geolat BETWEEN x1 AND x2
AND geolng BETWEEN y1 AND y2

• 这就是我上面所描述的最佳存储方式 使用Float(10,6)在MySQL中查找经纬度数据并分离出经度/纬度?如果没有，那是什么?数据类型有浮点数，小数甚至空间.
• 这是执行广告的最佳方法吗? 从性能角度看SQL?如果没有，那是什么?
• 是否使用其他MySQL 数据库引擎有意义吗?
• Is what I described above the best way to store the latitude and longitude data in MySQL using Float (10,6) and separating out the longitude/latitude? If not, what is? There exist Float, Decimal and even Spatial as a data type.
• Is this the best way to perform the SQL from a performance standpoint? If not, what is?
• Does using a different MySQL database-engine make sense?

更新:仍未答复

我在下面有3个不同的答案.一个人说要使用Float.一个人说要使用INT.一个人说要使用Spatial.

I have 3 different answers below. One person say to use Float. One person says to use INT. One person says to use Spatial.

因此，我使用了MySQL的"EXPLAIN"语句来衡量SQL的执行速度.如果对经度和纬度数据类型使用INT或FLOAT，则似乎在SQL执行(结果集获取)方面绝对没有区别.

So I used MySQL "EXPLAIN" statement to measure the SQL execution speed. It appears that absolutely no difference in SQL execution (result set fetching) exist if using INT or FLOAT for the longitude and latitude data type..

看来，使用"BETWEEN"语句比使用">"或"<" SQL语句快得多.使用"BETWEEN"的速度比使用">"和"<"语句快将近三倍.

It also appears that using the "BETWEEN" statement is SIGNIFICANTLY faster than using the ">" or "<" SQL statements. It's nearly 3x faster to use "BETWEEN" than to use the ">" and "<" statement.

话虽如此，我仍然不确定使用Spatial对性能的影响，因为我不清楚我的MySQL版本(v5.0.24)是否支持它，以及我如何启用它如果支持的话.

With that being said, I still am unceratin on what the performance impact would be if using Spatial since it's unclear to me if it's supported with my version of MySQL running (v5.0.24) ... as well as how I enable it if supported.

任何帮助都会得到很大的帮助

推荐答案

float(10,6)很好.

float(10,6) is just fine.

任何其他复杂的存储方案都需要进出更多的转换，并且浮点数学运算速度很快.

Any other convoluted storage schemes will require more translation in and out, and floating-point math is plenty fast.

这篇关于数据库/SQL:如何存储经度/纬度数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！