data type question

Would float be the best choice for a data type using latitude and longitude values...i.e. " 33.606379" " -86.50249"
Thanks,
-D-The FLOAT datatype is a logical choice for representing Latitude and Longitude measurements, as long as your database sees them as "measurements" instead of exact values. If you need to treat them as counts instead of measures, you'll probably want to use NUMERIC instead of FLOAT.

-PatP|||Hi Dman100

I hope this is valuable to the thread and not a hijack. I am interested in appropriate use of approximate floating point numbers (well - a bit).

I have pretty well eliminated float and real datatypes from all my databases. This is because the majority of the applications I support and produce require absolute values. As far as I can tell - if the application of a numeric field requires some sort of mathematical manipulation (especially to a high degree of precision) and the number concerned is absolute then float and real are poor choices for the field data type. The exception would be a numbers that cannot be absolutely represented by a fixed number of digits (one third, pi etc).

I am not mathematically trained to the sort of standard many of the SQl gurus are. I am not certain the above is correct. I am happy to be corrected. I am happy to be told that the above is entirely irrelevent to the question in hand. I am happy to be told to bog off. I am, in fact, happy.|||It is good to be happy!

You hit the nail pretty much on the head, saying the same thing that I said in a bit more roundabout way.

Numbers can be looked at two different ways by most computer languages, and SQL can deal with them either way.

One way to think about a number is as a count... It is exact, repeatable, and can be "proved" in some way. The data types INT, BIGINT, DECIMAL, and NUMERIC are well suited for counts.

The other way to think about numbers is as a measurement... A measurement can be quite exact (to N decimal places), but it can't be "proved" like a count can. The data types REAL and FLOAT are well suited for measurements.

Values that can be derived from computation (such as speeds, accelerations, and many forms of location) are inherantly measurements. While they can be quite precise, there isn't a way to derive them from a count (other than to use one or more counts to mathematically derive the measurement). Speed as such is relative, so there isn't a direct way to count it... The best you can do is measure or count the distance traveled and the time used to compute the speed. While you might be able to count units of distance and time, there is no way to count units of speed (contrary to the belief of my neighbors in college).

The problem is that sometimes you need to deal with people that don't understand the difference between a count and a measurement. They think you should be able to store a measurement to N digits, and always have the exact same value come back. This isn't unreasonable from their perspective, and it makes perfect sense to them... They see nothing silly about the assertion that 3.141592654 is the value of pi, because to them that is a true statement.

I'm going to cut my blither short here... I've probably blabbered far more than anyone wanted to read already. The short answer boils down to REAL and FLOAT are for measurements. Most people prefer to think in counts, so most databases use INT or NUMERIC.

-PatP|||You hit the nail pretty much on the head, saying the same thing that I said in a bit more roundabout way.That pretty much sums up my entire career to date :D

Thanks Pat - you've firmed up my understanding.|||you guys are good

if i write a query likeselect x * 3.141592654 ...what datatype is that, DECIMAL or FLOAT?

and wouldn't it be better to use select x * ( select value from constants where name='pi' ) ...to allow you to define the value of pi in one spot, so that all queries could use it, so that, you know, in case the value ever changes, you wouldn't have a ton o' queries to change...|||you guys are good

if i write a query likeselect x * 3.141592654 ...what datatype is that, DECIMAL or FLOAT?

and wouldn't it be better to use select x * ( select value from constants where name='pi' ) ...to allow you to define the value of pi in one spot, so that all queries could use it, so that, you know, in case the value ever changes, you wouldn't have a ton o' queries to change...While I've heard that there is one state that has changed the value of pi to meet biblical requirements, I don't see how that would justify creating a table of constants to cope with that kind of problem. There are too many variables that I couldn't predict to make that practical. Even if we considered creating such a table as an option, it wouldn't help with the data type, only the value being used.

Its a good idea Rudy, and one that I wouldn't expect from you, but I just don't see it as practical in this particular case. ;)

-PatP|||oh my god, pat, can't you tell when someone is kidding

"in case the value of pi ever changes" -- you thought i was serious??

that's hilarious

:)|||oh my god, pat, can't you tell when someone is kiddingYou have to watch for those smilies... Sometimes they sneak in at the end!

It still wouldn't change the data type.

-PatP