问题在MySQL数据库中存储纬度和经度值

I want to store the values of latitude and longitude fetched from Google Maps GeoCoding API in a MySQL database. The values are in float format.

我想将从Google Maps GeoCoding API获取的纬度和经度值存储在MySQL数据库中。值为浮点格式。

12.9274529

12.9274529

77.5905970

77.5905970

And when I want to store it in database (which is datatype float) it rounds up float and store it in following format:

当我想将它存储在数据库（数据类型为float）时，它会向上舍入浮动并以下列格式存储它：

12.9275

12.9275

77.5906

77.5906

Am I using the wrong datatype? If yes then what datatype should I be using to store latitude and longitude values?

我使用了错误的数据类型吗？如果是，那么我应该使用什么数据类型来存储纬度和经度值？

Update :

更新：

here is the CREATE TABLE as requestted by Allin

这是Allin要求的CREATE TABLE

CREATE TABLE `properties` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(100) NOT NULL,
  `description` text,
  `latitude` float DEFAULT NULL,
  `longitude` float DEFAULT NULL,
  `landmark` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `serial` (`serial`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

7 个解决方案

#1

You need to use decimal if you don't want the numbers to be approximated.

如果您不希望数字近似，则需要使用小数。

Fixed-Point (Exact-Value) Types

定点（精确值）类型

The DECIMAL and NUMERIC types store exact numeric data values. These types are used when it is important to preserve exact precision, for example with monetary data.

DECIMAL和NUMERIC类型存储精确的数字数据值。在保持精确精度很重要时使用这些类型，例如使用货币数据。

And now the "here you go" answer:

而现在“你走了”回答：

Use DECIMAL(10,7). Where 10 is the total number of digits in the number and 7 is the number of digits after the .. (This means that before the dot will be 3 digits.)

使用DECIMAL（10,7）。其中10是数字中的总位数，7是...之后的位数。（这意味着在点之前将是3位数。）

Adjust these numbers as needed. Also please take a look at the manual entry I linked earlier in the answer.

根据需要调整这些数字。另请参阅我之前在答案中链接的手册条目。

#2

MySQL has special types for GIS applications.

MySQL有适用于GIS应用程序的特殊类型。

Use the point type and see:

使用点类型并查看：

http://dev.mysql.com/doc/refman/5.0/en/spatial-extensions.html

For a general discussion see: http://dev.mysql.com/tech-resources/articles/4.1/gis-with-mysql.html

有关一般性讨论，请参阅：http：//dev.mysql.com/tech-resources/articles/4.1/gis-with-mysql.html

Some guys made a special UDF for computing distances between points on a sphere (i.e. earth)
See: http://www.lenzg.net/archives/220-New-UDF-for-MySQL-5.1-provides-GIS-functions-distance_sphere-and-distance_spheroid.html

有些人为计算球体上的点之间的距离（即地球）制作了一个特殊的UDF，请参阅：http：//www.lenzg.net/archives/220-New-UDF-for-MySQL-5.1-provides-GIS-functions- distance_sphere-和distance_spheroid.html

Here's a howto: http://howto-use-mysql-spatial-ext.blogspot.com/2007/11/using-circular-area-selection.html

这是一个方法：http：//howto-use-mysql-spatial-ext.blogspot.com/2007/11/using-circular-area-selection.html

#3

use double

使用双

float lacks the necessary precision to save that number of digits after the decimal point. double, although not always guaranteed to have 7 decimal places for all numbers, will have where there are not more than 8 digits on the left of the decimal so should suit your needs.

float缺少必要的精度来保存小数点后的位数。 double，虽然并不总是保证所有数字都有7位小数，但是小数点左边的数字不超过8位，所以应该符合你的需要。

#4

The optimal setup in my experience is DOUBLE(11,8), keep in mind that lat/lng could be > 99

根据我的经验，最佳设置是DOUBLE（11,8），请记住lat / lng可能> 99

#5

Alter your table so it's a double precision float instead of a single precision float:

改变你的表，所以它是一个双精度浮点数，而不是一个精度浮点数：

alter table properties modify latitude double, modify longitude double;

#6

Decimal (10,8) is more than enough. Some GPS devices provide more accurate position.

十进制（10,8）绰绰有余。一些GPS设备提供更准确的位置。

#7

Use Double

使用双

CREATE TABLE `properties` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(100) NOT NULL,
  `description` text,
  `latitude` Double DEFAULT NULL,
  `longitude` Double DEFAULT NULL,
  `landmark` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `serial` (`serial`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

#1