Re: Unit conversion database (was: multiple paramters in aggregate function) - Mailing list pgsql-general

On 18 Aug 2009, at 19:59, Alban Hertroys wrote:

> Hello all,
>
> Inspired by the original discussion on aggregating quantities of
> different units I made a start at a unit conversion database and the
> result is here: http://solfertje.student.utwente.nl/documents/units.sql

I just uploaded an updated version that handles scaling of units
properly for at least the base-units. For example:

development=> select convert_unit(28, '°C', '°F');
       convert_unit
-------------------------
  82.39999999999999999960
(1 row)

development=> select convert_unit(28, 'mg', 'gr');
       convert_unit
------------------------
  0.43210603388236005822
(1 row)

development=> select convert_unit(28, 'lb', 'kg');
     convert_unit
---------------------
  10.4507682048000000
(1 row)

development=> select convert_unit(28, 'kg', 'lb');
     convert_unit
---------------------
  75.0184086601319546
(1 row)

development=> select convert_unit(28, 'dm', 'mm');
      convert_unit
-----------------------
  2800.0000000000000000
(1 row)

> Problem areas are:
> - It doesn't contain every possible conversion yet.
> Some units are probably just plain wrong too. I don't know every
> unit in the list, that's why. I'm especially unfamiliar with
> imperial units and some of the more esoteric units. Corrections and
> additions are welcome.

I added several units from http://en.wikipedia.org/wiki/Conversion_of_units
  (my original source was Binas, an old book I had left over from high-
school days). Imperial units should be more complete now.
Still, if you notice any obvious errors or omissions, let me know.

> - It can't handle unit scaling yet ('mm' to 'm' for example).
> There are some units in there that are scaled by default ('kg' is
> the standard unit for mass and not 'g'), and some units seem to be
> not scalable at all (ever heard of 'mK' - 'milliKelvin'?). This may
> be solved by adding a base_scale column which could be NULL if not
> applicable.

This is working now.

> - Some units are combinations of multiple base-units that would
> require parsing the combined unit to determine how to scale or
> convert parts of it. I haven't found a good way of handling that
> yet, maybe I just shouldn't... I have a feeling that at the very
> least parsing units should only happen if the unit isn't a base-
> unit, which can simply be flagged.

These are flagged now, but that's about it. No conversions for such
units have been entered yet.
One change is that the formatting of the units has changed to
something less likely to give ambiguous results (eg. 'ms-1' has been
changed to 'm.s^-1').

I think this database is fairly usable in its current state. Any more
development on it warrants its own project page somewhere and taking
it off-list, I'll no longer pester you with updates on this ;)

Have a nice day!

Alban Hertroys

--
Screwing up is the correct approach to attaching something to the
ceiling.


!DSPAM:737,4a8c220b10137643883901!