MySql使用Float数据类型来存储地理坐标 [英] MySql using Float Data Type To Store Geographic Coordinates

问题描述

我有一张存储经度和纬度坐标（谷歌地图）的表格，我将列定义为浮动，但是当我尝试插入一个值-61.45859899999999和10.28289时，它们被四舍五入 - 61.46和10.30。我如何修改列来保存数据。

我正在使用 mysql toad 。下是该表的代码：

  CREATE TABLE`tblGeoCodes`（
`recNo` int（11）NOT NULL AUTO_INCREMENT，
`longLocation` float（30,2）DEFAULT NULL，
`latLocation` float（30,2）DEFAULT NULL 
  

解决方案

您的实现有两个问题。

都是四舍五入到2位数的精度是你明确定义为2。

另外，FLOAT在MySQL中是一个不精确的数据类型。

要解决这两个问题，您应该使用DECIMAL数据类型，并使用适当的精度和比例。 例如，类似这样的内容：

  CREATE TABLE`tblGeoCodes`（
`recNo` int（11）NOT NULL AUTO_INCREMENT主键
`longLocation` decimal（18,14）DEFAULT NULL，
`latLocation` decimal（18,14）DEFAULT NULL 
）; 
  

示例：

  mysql> CREATE TABLE`tblGeoCodes`（
  - >`recNo` int（11）NOT NULL AUTO_INCREMENT主键，
  - >`longLocation` decimal（18,14）DEFAULT NULL，
  - >`latLocation` decimal（18,14）DEFAULT NULL 
  - >）; 
查询OK，0行受影响（0.02秒）
 
 mysql> 
 mysql>插入到tblGeoCodes（longLocation，latLocation）值（-61.45859899999999,10.28289）; 
查询OK，1行受影响（0.00秒）
 
 mysql> 
 mysql>从tblGeoCodes中选择*; 
 + ------- + -------------------- + ---------------- --- + 
 | recNo | longLocation | latLocation | 
 + ------- + -------------------- + ---------------- --- + 
 | 1 | -61.45859899999999 | 10.28289000000000 | 
 + ------- + -------------------- + ---------------- --- + 
 1行（0.00秒）
  

I have a table that stores longitude and latitude coordinates (google maps) i have the columns defined as float however when i try to insert a value -61.45859899999999 and 10.28289 they are being rounded off to -61.46 and 10.30. How can i modify the columns to hold the data as is.

I am using mysql toad. Under is the code for the table:

CREATE TABLE `tblGeoCodes` (
  `recNo` int(11) NOT NULL AUTO_INCREMENT,
  `longLocation` float(30,2) DEFAULT NULL,
  `latLocation` float(30,2) DEFAULT NULL 

解决方案

There are two problems with your implementation.

The reason the values are both being rounded to 2 digits of precision is that you explicitly defined the scale as 2.

Also, FLOAT is an imprecise data type in MySQL.

To solve both problems you should use the DECIMAL data type with an appropriate precision and scale.

For example, something like this:

CREATE TABLE `tblGeoCodes` (
  `recNo` int(11) NOT NULL AUTO_INCREMENT primary key,
  `longLocation` decimal(18,14) DEFAULT NULL,
  `latLocation` decimal(18,14) DEFAULT NULL
); 

Example:

mysql> CREATE TABLE `tblGeoCodes` (
    ->   `recNo` int(11) NOT NULL AUTO_INCREMENT primary key,
    ->   `longLocation` decimal(18,14) DEFAULT NULL,
    ->   `latLocation` decimal(18,14) DEFAULT NULL
    -> ); 
Query OK, 0 rows affected (0.02 sec)

mysql> 
mysql> insert into tblGeoCodes (longLocation,latLocation) values(-61.45859899999999 , 10.28289);
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> select * from tblGeoCodes;
+-------+--------------------+-------------------+
| recNo | longLocation       | latLocation       |
+-------+--------------------+-------------------+
|     1 | -61.45859899999999 | 10.28289000000000 |
+-------+--------------------+-------------------+
1 row in set (0.00 sec)

这篇关于MySql使用Float数据类型来存储地理坐标的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！