CODEWITHSUNDEEP
mysqldouble
Lille_skutt
Lille_skutt

Reputation: 139

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

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?

Upvotes: 10

Views: 18443

Answers (3)

Wasyster
Wasyster

Reputation: 2535

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.

Upvotes: 0

Ben
Ben

Reputation: 35613

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.

Upvotes: 12

Wrikken
Wrikken

Reputation: 70460

Floating points are irritating....

 WHERE ABS(lngitude - 13.8461208) < 0.00000005

Upvotes: 4

Related Questions