Php – Problem Storing Latitude and Longitude values in MySQL database

floating pointMySQLPHPtypes

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.

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

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

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 ;

Best Answer

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.

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.)

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

Related Topic