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

Question

I have this in the mysql table:

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

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

lngitude Request: SELECT * FROM location_forslag WHERE lngitude = 13.8461208

However, if I use the lat column, it returns the results:

lat Query: SELECT * FROM location_forslag WHERE lat = 58.3902782

What is the problem with lngitude column?

Answer 1

As a rule, it's nice to compare floating point numbers with the = equals operator.

• Is it correct to compare two rounded floating point numbers using the == operator?

• Work with precision floating point problems

For your application, you need to think about how close you want an answer.

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

 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 the points where lngitude is within @epsilon degrees of the desired value. You must select a value for epsilon that matches your application.

Answer 2

Floating dots annoy ....

  WHERE ABS(lngitude - 13.8461208) < 0.00000005 

Answer 3

Convert float to decimal for comparison. I had the same problem and was 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 lines after the WHERE clause. Hope this helps.