Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2004
    Posts
    124

    Unanswered: 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-

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Pat Phelan
    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

    Thanks Pat - you've firmed up my understanding.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you guys are good

    if i write a query like
    Code:
    select x * 3.141592654 ...
    what datatype is that, DECIMAL or FLOAT?

    and wouldn't it be better to use
    Code:
    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...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by r937
    you guys are good

    if i write a query like
    Code:
    select x * 3.141592654 ...
    what datatype is that, DECIMAL or FLOAT?

    and wouldn't it be better to use
    Code:
    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

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by r937
    oh my god, pat, can't you tell when someone is kidding
    You have to watch for those smilies... Sometimes they sneak in at the end!

    It still wouldn't change the data type.

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •