在 SQL 数据库中存储纬度和经度数据时使用什么数据类型？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1196415/
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 datatype to use when storing latitude and longitude data in SQL databases?
提问by dthrasher
When storing latitude or longitude data in an ANSI SQL compliant database, what datatype would be most appropriate? Should floatbe used, or decimal, or ...?
在符合 ANSI SQL 的数据库中存储纬度或经度数据时，哪种数据类型最合适？应该float使用, or decimal, or ...？
I'm aware that Oracle, MySql, and SQL Server have added some special datatypes specifically for handling geo data, but I'm interested in how you would store the information in a "plain vanilla" SQL database.
我知道 Oracle、MySql 和 SQL Server 已经添加了一些专门用于处理地理数据的特殊数据类型，但我对如何将信息存储在“普通”SQL 数据库中很感兴趣。
回答by dotjoe
Decimal(9,6)
Decimal(9,6)
If you're not used to precision and scale parameters, here's a format string visual:
如果您不习惯精度和缩放参数，这里有一个格式字符串视觉：
###.######
###.######
回答by Keith
We use float, but any flavor of numeric with 6 decimal places should also work.
我们使用浮点数，但任何带有 6 位小数的数字也应该有效。
回答by Glenn Plas
You can easily store a lat/lon decimal number in an unsigned integer field, instead of splitting them up in a integer and decimal part and storing those separately as somewhat suggested here using the following conversion algorithm:
您可以轻松地将纬度/经度十进制数存储在无符号整数字段中，而不是将它们拆分为整数和小数部分，然后按照此处的建议使用以下转换算法将它们分开存储：
as a stored mysql function:
作为存储的 mysql 函数：
CREATE DEFINER=`r`@`l` FUNCTION `PositionSmallToFloat`(s INT)
RETURNS decimal(10,7)
DETERMINISTIC
RETURN if( ((s > 0) && (s >> 31)) , (-(0x7FFFFFFF -
(s & 0x7FFFFFFF))) / 600000, s / 600000)
and back
然后回来
CREATE DEFINER=`r`@`l` FUNCTION `PositionFloatToSmall`(s DECIMAL(10,7))
RETURNS int(10)
DETERMINISTIC
RETURN s * 600000
That needs to be stored in an unsigned int(10), this works in mysql as well as in sqlite which is typeless.
这需要存储在unsigned int(10) 中，这适用于 mysql 以及无类型的 sqlite。
through experience, I find that this works really fast, if all you need to to is store coordinates and retrieve those to do some math with.
通过经验，我发现这真的很快，如果您只需要存储坐标并检索这些坐标来做一些数学运算。
in php those 2 functions look like
在 php 中，这两个函数看起来像
function LatitudeSmallToFloat($LatitudeSmall){
if(($LatitudeSmall>0)&&($LatitudeSmall>>31))
$LatitudeSmall=-(0x7FFFFFFF-($LatitudeSmall&0x7FFFFFFF))-1;
return (float)$LatitudeSmall/(float)600000;
}
and back again:
然后再回来：
function LatitudeFloatToSmall($LatitudeFloat){
$Latitude=round((float)$LatitudeFloat*(float)600000);
if($Latitude<0) $Latitude+=0xFFFFFFFF;
return $Latitude;
}
This has some added advantage as well in term of creating for example memcached unique keys with integers. (ex: to cache a geocode result). Hope this adds value to the discussion.
这在创建例如带有整数的 memcached 唯一键方面也有一些额外的优势。（例如：缓存地理编码结果）。希望这增加了讨论的价值。
Another application could be when you are without GIS extensions and simply want to keep a few million of those lat/lon pairs, you can use partitions on those fields in mysql to benefit from the fact they are integers:
另一个应用程序可能是当您没有 GIS 扩展并且只想保留几百万个这些纬度/经度对时，您可以在 mysql 中的这些字段上使用分区以受益于它们是整数的事实：
Create Table: CREATE TABLE `Locations` (
`lat` int(10) unsigned NOT NULL,
`lon` int(10) unsigned NOT NULL,
`location` text,
PRIMARY KEY (`lat`,`lon`) USING BTREE,
KEY `index_location` (`locationText`(30))
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY KEY ()
PARTITIONS 100 */
回答by Kasper
Well, you asked how to store Latitude/Longitude and my answer is: Don't, you might consider using the WGS 84( in Europe ETRS 89) as it is the standard for Geo references.
好吧，您询问了如何存储纬度/经度，我的回答是：不要，您可能会考虑使用WGS 84（在欧洲ETRS 89 中），因为它是地理参考的标准。
But that detail aside I used a User Defined Type in the days before SQL 2008 finally include geo support.
但撇开这个细节不谈，我在 SQL 2008 最终包含地理支持之前的几天里使用了用户定义类型。
回答by Kasper
In vanilla Oracle, the feature called LOCATOR (a crippled version of Spatial) requires that the coordinate data be stored using the datatype of NUMBER (no precision). When you try to create Function Based Indexes to support spatial queries it'll gag otherwise.
在原版 Oracle 中，称为 LOCATOR（空间的残缺版本）的功能要求使用 NUMBER 数据类型（无精度）存储坐标数据。当您尝试创建基于函数的索引来支持空间查询时，它会以其他方式堵嘴。
回答by syed Ahsan Jaffri
You should take a look at the new Spatial data-types that were introduced in SQL Server 2008. They are specifically designed this kind of task and make indexing and querying the data much easier and more efficient.
您应该看看 SQL Server 2008 中引入的新空间数据类型。它们是专门为此类任务设计的，使索引和查询数据变得更加容易和高效。
http://msdn.microsoft.com/en-us/library/bb933876(v=sql.105).aspx
http://msdn.microsoft.com/en-us/library/bb933876(v=sql.105).aspx
http://blogs.technet.com/andrew/archive/2007/11/26/sql-server-2008-spatial-data-types.aspx
http://blogs.technet.com/andrew/archive/2007/11/26/sql-server-2008-spatial-data-types.aspx
回答by Sam
I would use a decimal with the proper precision for your data.
我会为您的数据使用具有适当精度的小数。
回答by jpj625
I think it depends on the operations you'll be needing to do most frequently.
我认为这取决于您最常需要执行的操作。
If you need the full value as a decimal number, then use decimal with appropriate precision and scale. Float is way beyond your needs, I believe.
如果您需要将完整值作为十进制数，请使用具有适当精度和小数位数的十进制数。我相信 Float 远远超出了您的需求。
If you'll be converting to/from degomin'sec"fraction notation often, I'd consider storing each value as an integer type (smallint, tinyint, tinyint, smallint?).
如果您经常转换为/从 degomin'sec”分数表示法，我会考虑将每个值存储为整数类型（smallint、tinyint、tinyint、smallint？）。
