Problem Storing Latitude and Longitude values in MySQL database

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 ;

Contents hide

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

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

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.

Method 2

MySQL has special types for GIS applications.

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

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

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

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

Method 4

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

Method 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;

Method 6

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

Method 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 ;

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating