Suitable mysql datatype to store Latitude and Longitude in separate fields

11
votes

I have stored Latitude and Longitude in separate fields. I am getting Lat & Lng coordinates from google and also used them to find nearby places using some formula. Now sometimes latitude and longitude value reach upto 15 decimal places like this --> 15.123456789000001. what would be the best datatype to store these values? Right now i am using Double to store Latitude and Longitude coordinates.

Thanks in Advance

3
mysqltypes
possible duplicate of What is the ideal data type to use when storing latitude / longitudes in a MySQL database? – Gajus

3 Answers

24
votes

99% of the time, any floating-point data type of your choice should be sufficient.

Yes, I know they can't always precisely and accurately represent values out to 15 decimal places, but that doesn't matter. Lat/long to 15 decimal places is specifying a position with a (claimed) accuracy on the order of the width of a couple hydrogen atoms. You only need six decimal places to get accuracy to within a meter, which is sufficient for most cases. Very few should need more than centimeter-level accuracy (8 places) and 10 or even 100 meter accuracy will be good enough for many applications.

And, of course, that's all assuming that the original data source is that accurate in the first place. My phone's GPS rarely claims accuracy better than within 10m (and even then, half the time its map doesn't show my actual location within the 10m circle where it claims I should be), so storing a position to greater precision would be pointless and misleading. Granted, you may be using a more accurate data source than the cheap GPS receiver integrated into a cellphone, but, even so, it's unlikely to actually be accurate all the way down to a molecular scale.

4
votes

I've used DECIMAL data type.

CREATE TABLE IF NOT EXISTS `example` (
  `lat` decimal(18,15) DEFAULT NULL,
  `long` decimal(18,15) DEFAULT NULL 
);