SQL Server SELECT Statement to exactly match latitude and longitude values held in a float data type

Question

I have a table with latitude and longitude stored as a float. I am needing a SELECT statement to match each latitude and longitude directly as I will be 'looking up' and inserting a postcode against each record. The table looks like the below

+-----------+-----------+------------------+--------------------+
|   Name    | LocatorID |     Latitude     |     Longitude      |
+-----------+-----------+------------------+--------------------+
| THE ALLEY |         1 | 52.2007179260254 | 0.39888408780098   |
| THE ALLEY |         2 | 52.5201377868652 | -2.13742804527283  |
| THE ALLEY |         3 | 51.0303649902344 | -1.90383625030518  |
| THE ALLEY |         4 | 50.9091453552246 | -0.537165105342865 |
+-----------+-----------+------------------+--------------------+

I would have thought the select statement would be something like:

SELECT TOP 1000 [Name]
      ,[LocatorID]
      ,[Latitude]
      ,[Longitude]
  FROM [UKStreetsAndPlaces].[dbo].[OS_Locator]
  WHERE [Latitude] = 52.2007179260254
  AND [Longitude] = 0.39888408780098

However this returns NO results and I see many articles suggesting this approach is not appropriate.

The thing is, as you can see, I really do need an accurate match to ensure I insert the right postcode against the right record.

This is a typical precision problem that is inherent to the way that SQL Server stores values of type `float` internally. I would suggest storing `Latitude` and `Longitude` as `decimal`. — Giorgos Betsos, May 26 '15 at 16:46
If you need accuracy stop using float. It is an approximate datatype and not all values can accurately be stored. You should instead use NUMERIC. — Sean Lange, May 26 '15 at 16:46
Unlike forum sites, we don't use "Thanks", or "Any help appreciated", or signatures on [so]. See "[Should 'Hi', 'thanks,' taglines, and salutations be removed from posts?](http://meta.stackexchange.com/questions/2950/should-hi-thanks-taglines-and-salutations-be-removed-from-posts). — John Saunders, May 26 '15 at 16:48
I recommend reading the previous question: [http://stackoverflow.com/questions/1056323/difference-between-numeric-float-and-decimal-in-sql-server] — SQLHound, May 26 '15 at 16:53
OK @JohnSaunders. I'm just not used to being so curt - especially when people are helping - it feels alien not to thank anyone in advance, but fair enough. — ChrisCurrie, May 26 '15 at 17:11
So, what if you thank people in advance for help, and nobody helps? — John Saunders, May 26 '15 at 17:11
@GiorgosBetsos. If you post an answer suggesting I should store the data as decimal I can accept this as the Answer. This was the solution that worked best for me in this case. — ChrisCurrie, May 26 '15 at 17:45
@Chris Glad I was able to help. I have posted my comment as an answer. — Giorgos Betsos, May 26 '15 at 17:55

score 1 · Answer 1 · answered May 26 '15 at 16:51

1

have you tried to case your lat/long as a decimal and do the compare?

SELECT TOP 1000 [Name]
    ,[LocatorID]
    ,[Latitude]
    ,[Longitude]
FROM [UKStreetsAndPlaces].[dbo].[OS_Locator]
WHERE CONVERT(DECIMAL(28,14), [Latitude]) = 52.2007179260254
AND CONVERT(DECIMAL(28,14), [Longitude]) = 0.39888408780098

answered May 26 '15 at 16:51

XenoPuTtSs

1,254
1
11
31

Interesting... I did just try this but my latitude and longitude values all have different precisions and I noticed the CONVERT had to be exact (i.e. CONVERT(DECIMAL(9,6),Longitude) = 0.3988841 would not work but CONVERT(DECIMAL(8,7),Longitude) = 0.3988841 would). I found that converting the entire table fields from a float type to decimal type with a precision of 6,9 gave me the consistency and accuracy I need. – ChrisCurrie May 26 '15 at 17:41
so then, you could just change the 28,14 to 6,9. would that work? – XenoPuTtSs May 26 '15 at 18:47
It worked on values that were exactly 9,6 (e.g. 152.454384) but not if the precision was different (e.g. 8,6 such as 52.454384). – ChrisCurrie May 26 '15 at 18:51

score 0 · Answer 2 · edited May 26 '15 at 16:52

FLOAT is an approximate datatype and it could couse the difference. Try to do it in that way for exmaple:

    CREATE TABLE #TMP
    (
     id int,
     lat decimal(20,15),
     long decimal(20,15),
    )

    INSERT INTO #TMP (id, lat, long) VALUES (1, 52.2007179260254, 0.39888408780098)
                                          , (2, 52.5201377868652, -2.13742804527283)
                                          , (3, 51.0303649902344, -1.90383625030518)
                                          , (4, 50.9091453552246, -0.537165105342865)


    select * from #TMP WHERE lat = 52.2007179260254
      AND long = 0.39888408780098

score 0 · Accepted Answer · answered May 26 '15 at 17:55

0

This is a typical precision problem that is inherent to the way that SQL Server stores values of type FLOAT internally.

According to MSDN FLOAT and REAL are:

Approximate-number data types for use with floating point numeric data. Floating point data is approximate; therefore, not all values in the data type range can be represented exactly.

I would suggest storing Latitude and Longitude as DECIMAL.

answered May 26 '15 at 17:55

Giorgos Betsos

71,379
9
63
98

1

This is the solution that worked best for me. I followed a commonly recommended precision of Decimal(9,6) for storing the latitude and longitude points. – ChrisCurrie May 26 '15 at 18:59

SQL Server SELECT Statement to exactly match latitude and longitude values held in a float data type

3 Answers3