CODEWITHSUNDEEP
mysqlgoogle-mapsgeolocation
devdar
devdar

Reputation: 5654

MySql using Float Data Type To Store Geographic Coordinates

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

Upvotes: 5

Views: 10254

Answers (4)

Alix Axel
Alix Axel

Reputation: 154553

Bare in mind that FLOAT(10,6) makes no sense, this reserves 4 digits for the integer part.

Personally, I prefer DOUBLE(9,6) but you might use FLOAT instead to save the 4 extra bytes.

Upvotes: 0

Ike Walker
Ike Walker

Reputation: 65547

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)

Upvotes: 13

guessimtoolate
guessimtoolate

Reputation: 8642

I'd suggest converting to MYISAM to take advantage of GSI. It is better suited for that purpose and should perform better.

However, as already suggested, increasing precision of float fields might be a simpler solution. Since you're tagged the question as google-maps releted I'd also recommend this tutorial at Google's dev site.

Upvotes: 0

david strachan
david strachan

Reputation: 7228

The ,2 in float(30,2) represents 2 decimal places. I use float(10,6) as this is adequate to hold coordinates

Upvotes: 5

Related Questions