php 在 MySQL 数据库中存储纬度和经度值的问题
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6188197/
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
Problem Storing Latitude and Longitude values in MySQL database
提问by Ibrahim Azhar Armar
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
77.5905970
12.9274529
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:
当我想将它存储在数据库中（这是数据类型浮点数）时，它会将浮点数四舍五入并以以下格式存储：
12.9275
77.5906
12.9275
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 ;
回答by Alin Purcaru
You need to use decimalif 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 10is the total number of digits in the number and 7is the number of digits after the .. (This means that before the dot will be 3digits.)
使用DECIMAL(10,7). 其中10是数字中的总7位数， 是..后的位数。（这意味着在点之前是3数字。）
Adjust these numbers as needed. Also pleasetake a look at the manual entry I linked earlier in the answer.
根据需要调整这些数字。另请查看我之前在答案中链接的手册条目。
回答by Johan
MySQL has special types for GIS applications.
MySQL 具有用于 GIS 应用程序的特殊类型。
Use the pointtype and see:
使用point类型并查看：
http://dev.mysql.com/doc/refman/5.0/en/spatial-extensions.html
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-and-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
回答by Billy Moon
use double
用 double
floatlacks 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 位，因此应该适合您的需要。
回答by peerless
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
回答by Adonias Vasquez
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 ;
回答by Fredrik
Decimal (10,8) is more than enough. Some GPS devices provide more accurate position.
十进制 (10,8) 绰绰有余。一些 GPS 设备提供更准确的位置。
回答by Denis de Bernardy
Alter your table so it's a double precisionfloat instead of a single precision float:
更改您的表，使其成为双精度浮点数而不是单精度浮点数：
alter table properties modify latitude double, modify longitude double;
