Problem Storing Latitude and Longitude values in MySQL database

Question

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 ;

@jezmck i would have simply used varchar and it should have worked, but since the latitude and longitude are float values it does not make sense to use whichever datatype i want. what i want to know is the correct datatype for storing this type of values. — Ibrahim Azhar Armar, May 31 '11 at 13:21
@Ibrahim Azhar Armar Can you do a `SHOW CREATE TABLE` or something and tell use what the data type is? — Alin Purcaru, May 31 '11 at 13:21
@Ibrahim Azhar Armar I just wanted to make sure you actually used the `FLOAT` data type. — Alin Purcaru, May 31 '11 at 13:27
@Ibrahim: you're currently using a single precision float (6-digit precision), rather than a double precision float (15-digit precision). — Denis de Bernardy, May 31 '11 at 13:31
@Denis how do i use a 15-digit precision float, i have updated my post with `CREATE TABLE` please have a look. — Ibrahim Azhar Armar, May 31 '11 at 13:34
possible duplicate of [What is the ideal data type to use when storing latitude / longitudes in a MySQL database?](http://stackoverflow.com/questions/159255/what-is-the-ideal-data-type-to-use-when-storing-latitude-longitudes-in-a-mysql) — Gajus, Jun 15 '15 at 19:25

Alin Purcaru · Accepted Answer · 2011-05-31T13:48:47.223

40

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.

edited May 31 '11 at 13:48

answered May 31 '11 at 13:23

Alin Purcaru

43,655
12
77
90

after testing and playing around with float, double and decimal, i felt relevant to use DECIMAL. thank you :) – Ibrahim Azhar Armar May 31 '11 at 14:31
1

My answer was a double, got more up-votes (+6 - and -3 votes) - people have mixed feelings about it. I think it all comes down to priorities, but probably makes little difference in this circumstance, both are perfectly fit for task. Interesting topic though, good chance for discussion on the issue. – Billy Moon May 31 '11 at 19:52

Johan · Answer 2 · 2011-12-12T12:36:14.857

8

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

edited Dec 12 '11 at 12:36

answered May 31 '11 at 14:03

Johan

74,508
24
191
319

http://howto-use-mysql-spatial-ext.blogspot.com/2007/11/using-circular-area-selection.html – Johan May 31 '11 at 14:34

Billy Moon · Answer 3 · 2011-05-31T13:45:12.673

7

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.

edited May 31 '11 at 13:45

answered May 31 '11 at 13:20

Billy Moon

57,113
24
136
237

2

-1 `Double` is pretty much the same thing as `Float`, only with more precision. – Alin Purcaru May 31 '11 at 13:24
Double works fine... that's what I use for coordinates in my DB anyway. – Brad May 31 '11 at 13:26
1

@Alin: based on the question, the OP incorrectly reporting that his table is using the `float` type -- the 6-number precision vs up to 15 for `float` suggests he's currently using a `real`. – Denis de Bernardy May 31 '11 at 13:28
@Brad ...erm. VARCHAR would also work. Would you use that as well? Also CHAR, BLOB, storing the two parts of the number in different columns, etc. – Alin Purcaru May 31 '11 at 13:28
@Denis That's why I asked for a `SHOW CREATE TABLE`. – Alin Purcaru May 31 '11 at 13:29
2

@Alin, of course not, but I see no problem with using double. Can you elaborate? – Brad May 31 '11 at 13:30
@Brad Hmm, after thinking a bit about this I would say that the answer depends on what he wants to use the coordinates for. Double and Float are better if he wants to use them in mathematical operations, but if his only goal is to store them it would be better to use a data type that doesn't loose precision, like Decimal. From what he states in the question he wants to keep them unaltered and wants to know what is the best data type for this. That would be Decimal. – Alin Purcaru May 31 '11 at 13:34
@Alin i have updated my post please have a look, and as for the usage of this values i plan to use it with Google Maps to show the location on the map using this co-ordinates. – Ibrahim Azhar Armar May 31 '11 at 13:37
1

If the co-ordinates given are vectors that wrap around the world several hundred times, then double would lose precision as the number left of the decimal would start reducing the amount of space available after the decimal, but for vectors that don't wrap around the world too many times it should not lose precision, and should be a fairly format for mathematical processing – Billy Moon May 31 '11 at 13:42

peerless · Answer 4 · 2012-11-22T07:58:27.173

2

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

edited Nov 22 '12 at 07:58

answered May 15 '12 at 09:21

peerless

558
3
11

score 0 · Answer 5 · answered Sep 13 '14 at 21:44

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 ;

Fredrik · Answer 6 · 2011-05-31T13:57:30.047

0

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

edited May 31 '11 at 13:57

answered May 31 '11 at 13:25

Fredrik

1,282
12
15

2

No GPS can provide accuracy that high. Just because the calculated position provides extra digits doesn't mean they aren't just noise. Beyond 6 digits is an extremely accurate fix. – Brad May 31 '11 at 13:29
Decimal number have fixed precision. – VGE May 31 '11 at 13:31
1

`DECIMAL (2,8)` is not a valid declaration. You probably meant `DECIMAL (10,8)`. – Alin Purcaru May 31 '11 at 13:46

score 0 · Answer 7 · answered May 31 '11 at 13:38

0

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;

answered May 31 '11 at 13:38

Denis de Bernardy

75,850
13
131
154

Problem Storing Latitude and Longitude values in MySQL database

7 Answers7

Linked