-1

What type of data should I use to store Lat and Lng from Google Map into my database? I'm not sure how many decimal place should I put for length in mysql.

So should I use Decimal, Float etc? and how much should I put for Length? Meanwhile, is it good to put index for Lat and Lng in Mysql?

unkulunkulu
  • 11,576
  • 2
  • 31
  • 49
Soheil
  • 5,054
  • 3
  • 24
  • 43
  • Whether it's "good" to put them in an index will depend on what you are going to be doing... how are you going to access the table? Are you going to be doing distance calculations? etc... – Ben Aug 10 '12 at 14:58
  • Yes, I'll use for distance calculation ... so do i need index ? – Soheil Aug 10 '12 at 15:40
  • @ Soheil Bm: Not necessarily. The need for an index depends on how you formulate your queries. If you say "calculate the distance between location with ID 5 and location with ID 10" then you only need an index on the ID field, if instead you say "find the 10 nearest locations to point lat_A/lon_A" then yes, indexes on the lat and lon fields would be useful. – Marcelo Aug 11 '12 at 08:20

3 Answers3

1

It depends what you are going to be doing with the information, and how precise you need the values to be.

A degree of latitude or longitude is roughly 69 miles. (The actual distance for a degree of latitude varies slightly, the distance for a degree of longitude becomes smaller and smaller the further from the equator.

DECIMAL(9,6) allows for a resolution of about  4 inches
DECIMAL(8,5) allows for a resolution of about 3 1/2 feet
DECIMAL(7,4) allows for a resolution of about 36 feet

(For latitude, you only need two places before the decimal, rather than the three you need for longitude.)

spencer7593
  • 106,611
  • 15
  • 112
  • 140
  • For latitude you need 3 places before the decimal, not 2, because of the minus sign south of the Equator. – Marcelo Aug 11 '12 at 07:18
  • @Marcelo: actually, no. A DECIMAL(M,N) specifies a precision of M digits, independent of the sign. To store two digits before the decimal, either positive or negative, DECIMAL(N+2,N) is sufficient. – spencer7593 Aug 12 '12 at 03:27
  • @spencer7593: see my edit to my reply. Actually we are both right depending on the version. :-) – Marcelo Aug 12 '12 at 04:34
  • @Marcelo: actually, no. It is not necessary to specify a precision of 3 to hold a 2 digit signed decimal number, in any version of MySQL. – spencer7593 Aug 13 '12 at 15:09
1

I use decimal(10,6) for both lat and lon which gives you a resolution better than 1 meter. (Remember to take the minus sign into account).

EDIT:

Quote from the MySQL manual regarding the minus sign on the decimal type:

Standard SQL requires that DECIMAL(5,2) be able to store any value with five digits and two decimals, so values that can be stored in the salary column range from -999.99 to 999.99. MySQL enforces this limit as of MySQL 5.0.3. Before 5.0.3, on the positive end of the range, the column could actually store numbers up to 9999.99. (For positive numbers, MySQL 5.0.2 and earlier used the byte reserved for the sign to extend the upper end of the range.)

Source:

http://dev.mysql.com/doc/refman/5.0/en/fixed-point-types.html

Marcelo
  • 9,387
  • 3
  • 35
  • 40
  • 1
    the "minus sign" is not included in the precision. DECIMAL(10,6) allows for four digits before the decimal point. It is sufficient to store values between -9999.999999 and +9999.999999. For six positions after the decimal point, DECIMAL(9,6) is sufficient for longitude, and DECIMAL(8,6) is sufficient for latitude. – spencer7593 Aug 12 '12 at 03:32
  • @spencer7593: see my edit to the reply. Actually we are both right depending on the version. :-) – Marcelo Aug 12 '12 at 04:32
1

If you're doing this for Google maps and are plotting a polyline (possibly several thousand points) you can store these points in an array, serialize the array, and store it in a BLOB or TEXT field. I haven't done any time trials on this but I suspect it is faster than converting to DEC. Alternatively, if you are passing this to JavaScript, you could store it as a JSON string using JSON_ENCODE and save doing that conversion every time you use the plot.