Database Forum / General DB Topics / DB Theory / July 2005
Storing units in the database
|
|
Thread rating:  |
Mikito Harakiri - 07 Jul 2005 22:48 GMT This discussion is triggerd by Jon Heggland's message about dimensional units supported by user-defined type.
Mikito Harakiri wrote:
> vc wrote: >> My impression is that you've got no clue as to what you are talking >> about The impression, of course, can be reversed by trying to present >> your thoughts in a coherent manner.
> The > best I can suggest is googling "units group:sci.math". First few hits > are pretty good ones. Perhaps, in this context that writing would have > more sence to you. Actually, I have trouble finding the relevant reference.
The key idea is that you treat the expression with unit symbols, for example
20 feet
formally. That is "Dimensionless number 20 multiplied by variable 'feet', whatever it might be". Now the conversion
feet = 0.3 meters
is an equation that can also be considered formally. We can substitute variables and get
20 feet = 20*0.3 meters
etc.
Now, when applied to the database, how should we store 20 feet? User defined types provide us some (quite unsophisticated) solution. But do we really have to program a class every time we want to do trivial conversions?
Marshall Spight - 08 Jul 2005 04:09 GMT > The key idea is that you treat the expression with unit symbols, for > example [quoted text clipped - 3 lines] > we really have to program a class every time we want to do trivial > conversions? I believe that C++, for example, has everything you need to be able to do this sort of thing in the type system. I am not aware, however, of any body of people finding it to be worth the effort.
Marshall
Misha Dorman - 12 Jul 2005 21:47 GMT Marshall Spight wrote:
> I believe that C++, for example, has everything you need to > be able to do this sort of thing in the type system. See e.g. http://www.awprofessional.com/articles/article.asp?p=375705&seqNum=1, which describes a framework for enforcing dimensional correctness; extension to handle automatic units declaration and conversion is not too hard (the hardest part is probably that a naive implementation will have lots of rounding error -- probably want to implement it using rationals rather than floating point).
> I am not aware, however, of any body of people finding it to be > worth the effort. The Ariane-5 software designers (and budget holders) might have been glad of it...
Misha
Mikito Harakiri - 12 Jul 2005 22:00 GMT > Marshall Spight wrote: > > I believe that C++, for example, has everything you need to [quoted text clipped - 7 lines] > have lots of rounding error -- probably want to implement it using > rationals rather than floating point). Now the question to ask is do those templates, metafunctions and lambdas really simpler than, say
20 * kg * 30 * m / c^2
To quote somebody (commented my own work!-): "Computer buffs really excel at complicating simple things".
Misha Dorman - 21 Jul 2005 20:47 GMT >>http://www.awprofessional.com/articles/article.asp?p=375705&seqNum=1, > Now the question to ask is do those templates, metafunctions and > lambdas really simpler than, say > > 20 * kg * 30 * m / c^2 Its quite simple[1] to set up a system so that expression is checked for dimensional/unit correctness and type safety (statically) using the TMP approach. Basically, just make the constructors-from-basic-types of the dimensioned type be protected, define constant static members of a friend "units" class named "kg", "m", "c" etc. (with the relevant values), and define a bunch of overloaded friend operators for *, /, +, - etc. taking mixtures of quantities and scalars to allow expressions like 20 * kg.
Then you can write something like:
quantity<float,mass> m = 10 * units::kg; quantity<float,accel> g = 9.81 * units::m / (units::sec*units::sec) quantity<float,force> f = m*a; //Note units are "implicit"
but not, say:
quantity<float,force> g = 9.81; //ERROR -- no units (attempt to use protected ctor)
quantity<float,force> f = m/a; //ERROR -- incorrect dimensions
Misha
[1] In C++ terms, that is -- at best that generally means "horribly complex to write, but at least it can be tested easily and used painlessly".
Mikito Harakiri - 12 Jul 2005 22:34 GMT > > The key idea is that you treat the expression with unit symbols, for > > example [quoted text clipped - 8 lines] > not aware, however, of any body of people finding it to be > worth the effort. Again, type system has little to offer.
Adding
5 * lbs + 5 * kg
is perfectly legal, although depends upon "lbs" defintion. If
lbs = 0.4 * kg
then we simply substitute. Then we apply distributive law. How about adding velocity to distance? Why not:
5 * m / c^2 + 20 * m
We can apply a distributive law again
(5 / c^2 + 20) * m
but we fail to isolate unit symbols in a separate factor.
Marshall Spight - 13 Jul 2005 04:10 GMT > > I believe that C++, for example, has everything you need to > > be able to do this sort of thing in the type system. I am [quoted text clipped - 21 lines] > > but we fail to isolate unit symbols in a separate factor. The C++ type system is powerful enough to
1) define a type lbs 2) define a type kg 3) define implicit conversions from one to the other 4) define a type m 5) define a type m^2 6) define an operation * on m that yields m^2 7) etc for ever.
lots of work defining types, though.
Marshall
Drago Ganic - 17 Jul 2005 16:37 GMT Marshall,
you said: .... 4) define a type m 5) define a type m^2 6) define an operation * on m that yields m^2 ...
Of course this is doable. But the question is: are types "m^2", "m^3" a good and practical approach ? I don't think so. There are algebraically relationships between them. How would you relate the types "m", "m^2", "m^3"? How would we handle Kilo, Mega etc ?
I believe, as Mikito, this approach is not practical. It's not done in physics that way. There are no types in physics. Instead we use algebra on "symbols". There are no symbols in mainstream programming languages and database systems and that's not good.
Greeting from Croatia, Drago Ganic
>> > I believe that C++, for example, has everything you need to >> > be able to do this sort of thing in the type system. I am [quoted text clipped - 35 lines] > > Marshall mAsterdam - 17 Jul 2005 17:21 GMT > Marshall wrote: >> 4) define a type m [quoted text clipped - 8 lines] > I believe, as Mikito, this approach is not practical. It's not done in > physics that way. There are no types in physics. When physicists start checking dimensions and units, to my programmer eyes it looks very much like type checking - but better.
> Instead we use algebra on > "symbols". There are no symbols in mainstream programming languages and > database systems and that's not good. Heh. 'mainstream' almost tautologices this statement. BASIC stood for Beginners Allpurpose *Symbolic* Instruction Code. Both Prolog and LISP have strong symbolic underpinnings IMHO.
Neo - 08 Jul 2005 04:10 GMT > 20 feet = 20*0.3 meters > Now, when applied to the database, how should we store 20 feet? User > defined types provide us some (quite unsophisticated) solution. But > do we really have to program a class every time we want to do trivial > conversions? Below script models 20 ft, 20 sec, conversion from ft to meter, etc in xrdb. Application code (OO or not) should perform conversion based on data in db.
// Create type distance, time, qty and unit // and make them items of main directory. // Note: "inst" is short for "instance". // Note: "it" refers to last new thing created by "*" in CREATE stmt. (CREATE type inst *distance & dir item it) (CREATE type inst *time & dir item it) (CREATE type inst *qty & dir item it) (CREATE type inst *unit & dir item it)
// Create 20 ft. (CREATE distance inst * & it qty +20 & it unit +feet)
// Create 40 ft. // Note: db auto normalized unit "feet" (CREATE distance inst * & it qty +40 & it unit +feet)
// Create 20 sec. // Note: db auto normalized qty "20". (CREATE time inst * & it qty +20 & it unit +second)
// Create abbreviation for feet and second. (CREATE type inst *abbr) (CREATE feet abbr +ft) (CREATE second abbr +sec)
// Find all things whose qty is 20. // Returns 20ft and 20sec. (SELECT * qty 20)
// Find all things whose qty is abbreviated as "ft". // Returns 20ft and 40ft. (SELECT * unit (* abbr ft))
// Create verb height to relate distance as height of things. (CREATE verb inst *height)
// Create person John's height is 20 ft. (CREATE type inst *person & dir item it) (CREATE person inst *john & it height (SELECT * qty 20 & * unit feet))
// Create tree1's height is 20 ft. (CREATE type inst *tree & dir item it) (CREATE tree inst *tree1 & it height (SELECT * qty 20 & * unit feet))
// Find all things that are 20 ft in height. // Returns john and tree1. (SELECT * height (SELECT * qty 20 & * unit feet))
// Create conversion rate from feet to meter // and vise versa. (CREATE type inst *"conversion rate") (CREATE unit inst *meter) (CREATE meter abbr +m) (CREATE feet to meter "conversion rate" +0.3) (CREATE meter to feet "conversion rate" +3.3)
// Find conversion rate from feet to meter. // Returns 0.3 (SELECT feet to meter "conversion rate" *)
Jonathan Leffler - 08 Jul 2005 05:25 GMT > This discussion is triggerd by Jon Heggland's message about dimensional > units supported by user-defined type. [quoted text clipped - 13 lines] > > Actually, I have trouble finding the relevant reference. You have to do that search at http://groups.google.com/; doing it at http://www.google.com/ is not so helpful (it drops the term 'group:sci.math' from the search and comes up with a number of books).
> The key idea is that you treat the expression with unit symbols, for > example [quoted text clipped - 10 lines] > > 20 feet = 20*0.3 meters 0.3048, but who's counting.
> Now, when applied to the database, how should we store 20 feet? User > defined types provide us some (quite unsophisticated) solution. But do > we really have to program a class every time we want to do trivial > conversions? C J Date does discuss this. No; but (in his terminology) you do define a different POSSREP for each set of units within a type. Thus, you can define a type for speeds (velocities have direction as well as magnitude), and you define a different possrep for each set of units you wish to work with - kilometres per hour, miles per hour, metres per second and furlongs per fortnight. The data stored in the DBMS in a separately defined way - hidden from the user. When you want a value in furlongs per fortnight, you simply choose the appropriate possrep when specifying the value. Further, if the type designer has done their stuff properly, you can multiply your value which you think is measured in furlongs per fortnight by a duration in microseconds and end up with the distance travelled in metres. Yes, someone has to think about which possreps are meaningful and provide some basic functionality, but the rest pretty much falls into place.
About the only issue that I've not seen dealt with is the ability or requirement to retrieve the original value as specified by the user, rather than obtaining the value in some specific set of units. That is, I might have (typically legal) reasons for needing to know that the value was originally specified as 23.45 furlongs per fortnight rather than, say, 0.01280 feet per second (23.45 furlongs per fortnight * 220 yards per furlong * 3 feet per yard / (14 days per fortnight * 24 * 60 * 60 seconds per day)). There's an easy way to get any the output in any particular supported possrep - but not to determine the original possrep that was used.
 Signature Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2005.01 -- http://dbi.perl.org/
|
|
|