Why doesn't this sql query return any results comparing floating point numbers?

Question

I have this in a mysql table:

enter image description here

id and bolag_id are int. lat and lngitude are double.

If I use the the lngitude column, no results are returned:

lngitude Query: SELECT * FROM location_forslag WHERElngitude= 13.8461208

However, if I use the lat column, it does return results:

lat Query: SELECT * FROM location_forslag WHERElat= 58.3902782

What is the problem with the lngitude column?

try `WHERE lngitude BETWEEN 13.8461208 AND 13.8461209`. – Benoit Jan 12 '12 at 17:30 — Benoit, Jan 12 '12 at 17:30
@Benoit works if I change it to 13.8461207 AND 13.8461208 – Lille_skutt Jan 12 '12 at 17:35 — Lille_skutt, Jan 12 '12 at 17:35

score 11 · Accepted Answer · edited May 23 '17 at 11:52

11

It is not generally a good idea to compare floating point numbers with = equals operator.

For your application, you need to consider how close you want the answer to be.

1 degree is about 112km, and 0.00001 degrees is about 1.1 metres (at the equator). Do you really want your application to say "not equal" if two points are different by 0.00000001 degrees = 1mm?

set @EPSLION = 0.00001  /* 1.1 metres at equator */

SELECT * FROM location_forslag 
WHERE `lngitude` >= 13.8461208 -@EPSILON 
AND `lngitude` <= 13.8461208 + @EPSILON

This will return points where lngitude is within @epsilon degrees of the desired value. You should choose a value for epsilon which is appropriate to your application.

edited May 23 '17 at 11:52

Community

1
1

answered Jan 12 '12 at 17:33

Ben

34,935
6
74
113

3

using the SQL BETWEEN keyword is not bad in this case (more readable IMO). – Benoit Jan 12 '12 at 17:37
1

How about using `lngitude` LIKE 13.8461208? Could that be a solution? – Lille_skutt Jan 12 '12 at 17:44
@Lille_skutt: No, you have to take a deliberate decision what accuracy you want, and do your comparisons explicitly. The database doesn't have anything to help you here - unless you have some GIS extensions installed. – Ben Jan 12 '12 at 17:57
@Ben Okey, but I find this pretty weird that you can't compare a number to the exact same number. How much off can the mysql-database be at most? The value I'm sending to it will always be exactly the same as the one stored in it. – Lille_skutt Jan 12 '12 at 18:05
1

@it's not the exact same number. 13.8461208 is probably no dyadic number (no finite representation in base 2) as 1/3 is not a decimal number. When a computer translates what is stored in binary format to a decimal representation for you, and when you provide this 10-based representation back to the computer, letting him translate ni base 2, there can be losses of precision. – Benoit Jan 12 '12 at 18:09
@Benoit I see. But how can I know for sure what that loss of precision can be at most? – Lille_skutt Jan 12 '12 at 18:14

score 4 · Answer 2 · answered Jan 12 '12 at 17:33

4

Floating points are irritating....

 WHERE ABS(lngitude - 13.8461208) < 0.00000005

answered Jan 12 '12 at 17:33

Wrikken

69,272
8
97
136

That does work. So it's just a coincidence that it worked with the other column? Could I store this in a better datatype? – Lille_skutt Jan 12 '12 at 17:37
A `DECIMAL` datatype is exact in recent MySQL versions, see the link. – Wrikken Jan 12 '12 at 17:39
The data type is fine, the issue is that the meaning of "equal" is dependent on the application - in the real world there is no such thing. In a satnav application, equal might be "within 10 metres". For a land registry database "within 0.05 metres" might be good enough. Building a kitchen, it might be "within 5 mm". – Ben Jan 12 '12 at 17:56
@Wrikken: that may prevent indexes from being used. – Ben Jan 12 '12 at 17:56
@Ben: good point. While it's shorter to write my way, your way could use indexes, and to be honest, the query will most likely run more times then the code is written (which is only once, if done properly ;) ) – Wrikken Jan 12 '12 at 19:03

score 0 · Answer 3 · answered Nov 05 '17 at 11:27

Convert float to decimal for compare. I had the same problem and solved like this:

SELECT
    [dbo].[Story].[Longitude],
    [dbo].[Story].[Latitude],
    [dbo].[Story].[Location],
FROM
    [dbo].[Story],
    [dbo].[Places]
WHERE
    convert(decimal, [dbo].[Story].[Latitude]) = convert(decimal,  [dbo].[Places].[Latitude])
    and
    convert(decimal, [dbo].[Story].[Longitude]) = convert(decimal, [dbo].[Places].[Longitude])
    and
    [dbo].[Places].[Id] = @PlacesID 
    and
    [dbo].[Story].IsDraft = 0
ORDER BY
    [dbo].[Story].[Time] desc

Look at the first 3 rows after the WHERE clausule. Hope it helps.

Why doesn't this sql query return any results comparing floating point numbers?

3 Answers3

Linked