Indexing Floats

I was looking for a few opinions from some DBA's with experience in this.
I'm setting up a table with over a million records in it. Two of the columns
are latitude and longitude. I'm looking for fast, read-only lookup against
queries based on those columns.
I have been told not to index on floats because it's not efficient, but I
have existing tables that store latitude and longitude as floats and they
work well, although the number of records involved is significantly lower.
A typical lat/long coordinate uses 8 to 9 places, up to three before the
decimal, up to six after, and are signed.
If I need fast lookups, should I be using decimals instead?
--
Regards,
Fred Chateau
http://hotelmotelnow.comOne other thing...
I don't know if it makes any difference, but I'm not querying for one
record, but rather a range of values. In other words, all records between
one set of coordinates and a second set of coordinates.
--
Regards,
Fred Chateau
http://hotelmotelnow.com
"Fred Chateau" <webmaster@.hotelmotelnow.com> wrote in message
news:u5sM3fWrHHA.3636@.TK2MSFTNGP06.phx.gbl...
>I was looking for a few opinions from some DBA's with experience in this.
> I'm setting up a table with over a million records in it. Two of the
> columns are latitude and longitude. I'm looking for fast, read-only lookup
> against queries based on those columns.
> I have been told not to index on floats because it's not efficient, but I
> have existing tables that store latitude and longitude as floats and they
> work well, although the number of records involved is significantly lower.
> A typical lat/long coordinate uses 8 to 9 places, up to three before the
> decimal, up to six after, and are signed.
> If I need fast lookups, should I be using decimals instead?
> --
> Regards,
> Fred Chateau
> http://hotelmotelnow.com
>|||On Jun 13, 9:07 am, "Fred Chateau" <webmas...@.hotelmotelnow.com>
wrote:
> I was looking for a few opinions from some DBA's with experience in this.
> I'm setting up a table with over a million records in it. Two of the columns
> are latitude and longitude. I'm looking for fast, read-only lookup against
> queries based on those columns.
> I have been told not to index on floats because it's not efficient, but I
> have existing tables that store latitude and longitude as floats and they
> work well, although the number of records involved is significantly lower.
> A typical lat/long coordinate uses 8 to 9 places, up to three before the
> decimal, up to six after, and are signed.
> If I need fast lookups, should I be using decimals instead?
> --
> Regards,
> Fred Chateauhttp://hotelmotelnow.com
Floats are fine and I use them all the time instead of decimal,
numeric etc although not for indexing just to store values.|||I don't think there is any problem with indexing float data types. Perhaps
you are confusing this with using a float(s) as a primary key. The PK issue
is that real and float are approximate data types so not all decimal values
can be stored.
For example:
DECLARE @.MyFloat float
SET @.MyFloat = 1.15
SELECT @.MyFloat
returns 1.1499999999999999 instead of 1.15. However, some GUIs like SQL
Server Management Studio will round the value for display and show the value
as 1.15.
Hope this helps.
Dan Guzman
SQL Server MVP
"Fred Chateau" <webmaster@.hotelmotelnow.com> wrote in message
news:u5sM3fWrHHA.3636@.TK2MSFTNGP06.phx.gbl...
>I was looking for a few opinions from some DBA's with experience in this.
> I'm setting up a table with over a million records in it. Two of the
> columns are latitude and longitude. I'm looking for fast, read-only lookup
> against queries based on those columns.
> I have been told not to index on floats because it's not efficient, but I
> have existing tables that store latitude and longitude as floats and they
> work well, although the number of records involved is significantly lower.
> A typical lat/long coordinate uses 8 to 9 places, up to three before the
> decimal, up to six after, and are signed.
> If I need fast lookups, should I be using decimals instead?
> --
> Regards,
> Fred Chateau
> http://hotelmotelnow.com
>|||> Floats are fine and I use them all the time instead of decimal,
> numeric etc although not for indexing just to store values.
Float is an approximate data type and cannot accurately store all decimal
values. I suggest you reconsider this approach except in cases where
accuracy is not needed. See the Books Online and my response to Fred for
more information.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"SB" <othellomy@.yahoo.com> wrote in message
news:1181706330.040993.149140@.d30g2000prg.googlegroups.com...
> On Jun 13, 9:07 am, "Fred Chateau" <webmas...@.hotelmotelnow.com>
> wrote:
>> I was looking for a few opinions from some DBA's with experience in this.
>> I'm setting up a table with over a million records in it. Two of the
>> columns
>> are latitude and longitude. I'm looking for fast, read-only lookup
>> against
>> queries based on those columns.
>> I have been told not to index on floats because it's not efficient, but I
>> have existing tables that store latitude and longitude as floats and they
>> work well, although the number of records involved is significantly
>> lower.
>> A typical lat/long coordinate uses 8 to 9 places, up to three before the
>> decimal, up to six after, and are signed.
>> If I need fast lookups, should I be using decimals instead?
>> --
>> Regards,
>> Fred Chateauhttp://hotelmotelnow.com
> Floats are fine and I use them all the time instead of decimal,
> numeric etc although not for indexing just to store values.
>|||On Tue, 12 Jun 2007 22:07:23 -0500, Fred Chateau wrote:
>I'm setting up a table with over a million records in it. Two of the columns
>are latitude and longitude. I'm looking for fast, read-only lookup against
>queries based on those columns.
Hi Fred,
Though your question has been answered, you might be interested in
reading "Expert SQL Server 2005 Development", written by Adam Machanic
with small contributions from Lara Rubbelke and myself. The chapter I
contributed is entirely devoted to working with spatial data, and
finding locations in a table that uses latitude and longitude to store
the locations is a major part of that chapter.
After reading that chapter, check my blog (see signature for the URL)
for a correction to an annoying error I was alerted to just AFTER the
deadline for corrections.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||On Jun 13, 6:41 pm, "Dan Guzman" <guzma...@.nospam-
online.sbcglobal.net> wrote:
> > Floats are fine and I use them all the time instead of decimal,
> > numeric etc although not for indexing just to store values.
> Float is an approximate data type and cannot accurately store all decimal
> values. I suggest you reconsider this approach except in cases where
> accuracy is not needed. See the Books Online and my response to Fred for
> more information.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "SB" <othell...@.yahoo.com> wrote in message
> news:1181706330.040993.149140@.d30g2000prg.googlegroups.com...
>
> > On Jun 13, 9:07 am, "Fred Chateau" <webmas...@.hotelmotelnow.com>
> > wrote:
> >> I was looking for a few opinions from some DBA's with experience in this.
> >> I'm setting up a table with over a million records in it. Two of the
> >> columns
> >> are latitude and longitude. I'm looking for fast, read-only lookup
> >> against
> >> queries based on those columns.
> >> I have been told not to index on floats because it's not efficient, but I
> >> have existing tables that store latitude and longitude as floats and they
> >> work well, although the number of records involved is significantly
> >> lower.
> >> A typical lat/long coordinate uses 8 to 9 places, up to three before the
> >> decimal, up to six after, and are signed.
> >> If I need fast lookups, should I be using decimals instead?
> >> --
> >> Regards,
> >> Fred Chateauhttp://hotelmotelnow.com
> > Floats are fine and I use them all the time instead of decimal,
> > numeric etc although not for indexing just to store values.- Hide quoted text -
> - Show quoted text -
When I sum hundreds of thousands I can sacrifice precision of ONE
penny although I do not understand why SQL Server can not maintain
exact precision like numbers with larger precision. The original
poster said that he would search within a range of co-ordinates and
therefore exact match to the 1/100th of decimal may not be important.
Thanks.|||> When I sum hundreds of thousands I can sacrifice precision of ONE
> penny although I do not understand why SQL Server can not maintain
> exact precision like numbers with larger precision. The original
> poster said that he would search within a range of co-ordinates and
> therefore exact match to the 1/100th of decimal may not be important.
I see no problem with using float as long as you are aware of the
implications. I just wanted to caution that using floats "all the time" is
definitely not a Best Practice. It should be used only as a conscious
decision when accuracy is not important. In most cases, one should never
use float for currency values. Auditors can get particularly annoyed when
those pennies drop on the floor.
> although I do not understand why SQL Server can not maintain
> exact precision like numbers with larger precision.
This is not a SQL Server issue but one inherent with the IEEE standard. See
http://en.wikipedia.org/wiki/IEEE_754.
Hope this helps.
Dan Guzman
SQL Server MVP
"SB" <othellomy@.yahoo.com> wrote in message
news:1181822618.110812.193080@.x35g2000prf.googlegroups.com...
> On Jun 13, 6:41 pm, "Dan Guzman" <guzma...@.nospam-
> online.sbcglobal.net> wrote:
>> > Floats are fine and I use them all the time instead of decimal,
>> > numeric etc although not for indexing just to store values.
>> Float is an approximate data type and cannot accurately store all decimal
>> values. I suggest you reconsider this approach except in cases where
>> accuracy is not needed. See the Books Online and my response to Fred for
>> more information.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "SB" <othell...@.yahoo.com> wrote in message
>> news:1181706330.040993.149140@.d30g2000prg.googlegroups.com...
>>
>> > On Jun 13, 9:07 am, "Fred Chateau" <webmas...@.hotelmotelnow.com>
>> > wrote:
>> >> I was looking for a few opinions from some DBA's with experience in
>> >> this.
>> >> I'm setting up a table with over a million records in it. Two of the
>> >> columns
>> >> are latitude and longitude. I'm looking for fast, read-only lookup
>> >> against
>> >> queries based on those columns.
>> >> I have been told not to index on floats because it's not efficient,
>> >> but I
>> >> have existing tables that store latitude and longitude as floats and
>> >> they
>> >> work well, although the number of records involved is significantly
>> >> lower.
>> >> A typical lat/long coordinate uses 8 to 9 places, up to three before
>> >> the
>> >> decimal, up to six after, and are signed.
>> >> If I need fast lookups, should I be using decimals instead?
>> >> --
>> >> Regards,
>> >> Fred Chateauhttp://hotelmotelnow.com
>> > Floats are fine and I use them all the time instead of decimal,
>> > numeric etc although not for indexing just to store values.- Hide
>> > quoted text -
>> - Show quoted text -
> When I sum hundreds of thousands I can sacrifice precision of ONE
> penny although I do not understand why SQL Server can not maintain
> exact precision like numbers with larger precision. The original
> poster said that he would search within a range of co-ordinates and
> therefore exact match to the 1/100th of decimal may not be important.
> Thanks.
>