CODEWITHSUNDEEP
mysqlsql
Tal Levi
Tal Levi

Reputation: 331

SQL correct definition of floating points

I am measuring some values over a geo-location I need to create a table that consists of:

date 
longitude
latitude
value

The date column is the date of the measure, The value is just an int value of the measure, the longitude and latitude are a coordinates - they are floating points columns with 3 digits before the point and 5 after (i.e. *.*)

I'm wondering how to define my table, I try to use:

CREATE TABLE `obs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date` date DEFAULT NULL,
  `lon` decimal(5,5) DEFAULT NULL,
  `lat` decimal(5,5) DEFAULT NULL,
  `val` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Here is a link to sqlFiddle

But When I'm trying to run 

INSERT INTO `obs` VALUES (null,'2014/06/07','34.000','31.342',1)

I am getting the following error:

Out of range value for column 'lon' at row 1:

Can anyone explain me what's wrong?

Upvotes: 0

Views: 31

Answers (1)

juergen d
juergen d

Reputation: 204766

decimal(5,5) means

5 decimal places in TOTAL
5 decimal places after the point

That would make all numbers invalid having a decimal place before the point.

You probably want 

decimal(10,5)

Upvotes: 3

Related Questions