0

Possible Duplicate:
What is the ideal data type to use when storing latitude / longitudes in a MySQL database?

I have GPS coordinates and some service provider's converted map coordinates to save into dababase. I don't know which type of data is wise for me to save, varchar, float, double? Also easy to use.

I should be doing queries based on these data like finding locations 'greater than' or 'less than' certain distance.

"double" seemed ok. But when I looked at the value it saved in db. It looks just not nice. It added many zero to the end because I have to set a decimal and length which is just a guess based on the data I have. There is a risk that it might change. It turned out that I cannot change the decimals after there are data in it. I just scewed all the existing data if I did.

I don't know which way is efficient and right.

Does anyone have experience about this?

Community
  • 1
  • 1
Hao
  • 6,291
  • 9
  • 39
  • 88
  • You have mysql as the db. But what Front end are you using? So you can use a datatype that can easily be converted/parsered from mysql to your front end language :) Choose accordingly. Otherwise you will only know that you had no parsers after saving the data as xyz type.. but most unlikely though. – bonCodigo Nov 27 '12 at 15:36

2 Answers2

4

Use float instead of double, unless you're a civil engineer planning a three-acre parking lot or a bridge. In that case you already know about whether you're using a Lambert or Universal Transverse Mercator projection for your mapping work, and which datum you're basing your elevations on. If that jargon may as well be in ancient Sanksrit to you, you only need float, not double.

GPS coordinates and the conventional haversine algorithm used to apply them to stuff like store finders simply don't have more precision than float provides. The reasons for this are a complex, but they have to do with the fact that Earth isn't quite a perfect sphere.

By the way, -xxx.yyyyy display precision is just fine for MySQL. 

SELECT FORMAT(latitude, 5) AS latitude

does what you need to strip lots of trailing zeros off your display.

O. Jones
  • 103,626
  • 17
  • 118
  • 172
0

I use MySQL’s DECIMAL data type, which also handles negative values in coordinates.

Martin Bean
  • 38,379
  • 25
  • 128
  • 201