在SQL数据库中存储纬度和经度数据时使用什么数据类型?(复制)

This question already has an answer here:

这个问题已经有了答案:

What is the ideal data type to use when storing latitude / longitudes in a MySQL database? 18 answers
在MySQL数据库中存储纬度/经度时，理想的数据类型是什么?18个答案

When storing latitude or longitude data in an ANSI SQL compliant database, what datatype would be most appropriate? Should float be used, or decimal, or ...?

在符合ANSI SQL的数据库中存储纬度或经度数据时，哪种数据类型最合适?是否应该使用浮点数，或小数，还是…?

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已经添加了一些专门用于处理geo数据的特殊数据类型，但是我对如何在“普通”SQL数据库中存储信息感兴趣。

8 个解决方案

#1

345

Decimal(9,6)

小数(9日,6)

If you're not used to precision and scale parameters, here's a format string visual:

如果你不习惯精确和缩放参数，这里有一个格式字符串视觉:

###.######

# # # # # # # # #

#2

We use float, but any flavor of numeric with 6 decimal places should also work.

我们使用浮点数，但是任何带有6个小数点的数字都可以。

#3

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)，因为它是Geo引用的标准。

But that detail aside I used a User Defined Type in the days before SQL 2008 finally include geo support.

但是在SQL 2008最终包含geo支持之前，我使用了一个用户定义的类型。

#4

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.

在vanilla Oracle中，称为LOCATOR(空间的残缺版本)的特性要求使用数字的数据类型(没有精度)存储坐标数据。当您尝试创建基于函数的索引来支持空间查询时，它将会停止。

#5

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:

您可以轻松地将lat/lon十进制数存储在一个无符号整数字段中，而不是将它们分割成一个整数和十进制的部分，然后使用下面的转换算法将它们分别存储起来:

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.

它需要存储在无符号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扩展时，如果只想保留几百万个lat/lon对，您可以使用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 */

#6

I would use a decimal with the proper precision for your data.

我会用一个精确的十进制来表示你的数据。

#7

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.

如果您需要将整个值作为十进制数，那么请使用具有适当精度和刻度的十进制。我相信浮动是远远超出你的需要的。

If you'll be converting to/from degºmin'sec"fraction notation often, I'd consider storing each value as an integer type (smallint, tinyint, tinyint, smallint?).

如果你从度会转换/º分钟'sec“分数表示法经常,我会考虑将每个值存储为整数类型(短整型,非常小的整数,非常小的整数,smallint ?)。

#8

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服务器- 2008 -空间-数据- types.aspx

#1

345