2

I have longitude and latitude 120,-128.67500305. They are saved in a Database as FLOAT(10,8). How can I send longitude=120 to MySQL database? In here I use Go for API. when I send longitude = 120 to DB then it shows error "Out of range value for column 'longitude'". Can someone help me?

Martin Tournoij
  • 26,737
  • 24
  • 105
  • 146
Pawani Santhara
  • 49
  • 6
  • 5
    A real numeric value never has any trailing zeroes after the decimal point, so what you want is a technical impossibility to begin with. – CBroe Jul 24 '18 at 11:13
  • Unfortunately, this is not possible. The trailing zeros can only exist in string, not in the number. – 31piy Jul 24 '18 at 11:14
  • it is not different data types. In database I can't save 120. because I use FLOAT(10,8).so before send DB i want to convert 120 to 120.00000000 – Pawani Santhara Jul 24 '18 at 11:14
  • 2
    `console.log(120 === 120.0000000000)` // true – baao Jul 24 '18 at 11:14
  • I cannot send 120 to DB as 120. It shows error "Out of range value for column 'longitude' – Pawani Santhara Jul 24 '18 at 11:17
  • 3
    @PawaniSanthara - You're mistaking the number for its representation. The number 120 is just the number 120, which we can write in several ways: 120, 120.0, 120.00, etc. All due respect, you've probably asked the wrong question. I suspect the right question is "How do I store this number in this database's `FLOAT(10,8)` column?" tagging the specific database and the specific API you use to access it, the error message you mentioned in a comment above, and including the code giving you the error. – T.J. Crowder Jul 24 '18 at 11:17
  • yes yes that is the question that I want to ask – Pawani Santhara Jul 24 '18 at 11:19
  • Cool -- you can use the "edit" link under the question to do that, again being sure to tag both the database type and the API you're using to access it, and including your code trying to insert the value. – T.J. Crowder Jul 24 '18 at 11:20
  • 1
    Possible duplicate of [What is the ideal data type to use when storing latitude / longitudes in a MySQL database?](https://stackoverflow.com/questions/159255/what-is-the-ideal-data-type-to-use-when-storing-latitude-longitudes-in-a-mysql) – Martin Jul 24 '18 at 11:54
  • @PawaniSanthara you [absolutely can change column data types](https://stackoverflow.com/questions/1356866/how-do-i-change-the-data-type-for-a-column-in-mysql) in an existing table. – Martin Jul 24 '18 at 12:24
  • is it impact for existing data in the table? – Pawani Santhara Jul 24 '18 at 12:27
  • @PawaniSanthara data will be converted. You should backup the table first, just in case, but the change of column type should make no difference to the data held in the column, in this case. – Martin Jul 24 '18 at 21:26

2 Answers2

4

Do not use the float data type for preserving exact digital values

Float is an inprecise (floating) mathematical storage mechanism. On MySQL for storing specific decimal values you should use DECIMAL number type.

I realise it's confusing as float can in PHP and elsewhere mean a decimal number between integers.

Example:

Latitude DECIMAL(11,8) SIGNED

This will store an 11 digit value with 8 digits after the decimal point. Such as:

-128.67500305

It will never round off this value but will silently slice off any extra digits longer than the set length (11 digits)

References:

Read here about Decimal data type.

Read here a very similar question

Read here the advice from MySQL about how to store Float data.

Also see this question/answer which actually recommends using spatial data types to store Lat/Long values.

Community
  • 1
  • 1
Martin
  • 22,212
  • 11
  • 70
  • 132
2

Notwithstanding the other useful guidance about what data types to use for geo-coordinates, the specific error is right there in your question - "Out of range value".

A FLOAT(10, 8) type in MySQL can only store values in the range ±100.0.

Alnitak
  • 334,560
  • 70
  • 407
  • 495