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

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

This is not a complete implementation, I just thought I'd show you
what I got so far and hope you have some ideas about a few problems
I'm facing.

What it can do is convert a bunch of units to and fro, including to
the same unit, using a conversion factor and a pair of offsets to
adjust for zero-point differences (°C to °F or K for example).
By default it installs itself in a schema named 'units'. At the end of
the script is a query that converts '23' (which happened to be the
temperature here while I was testing) from every known unit to every
other known unit. That's meant as a test, but it also makes verifying
correctness fairly easy.

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.

- 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.

- 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.

The latter two issues seem to require a unit parser, which seems a bit
heavy-weight. Or I should just drop all the combined units and only
deal with base-units. Suggestions or even code are welcome.

On 18 Aug 2009, at 13:22, Alban Hertroys wrote:

> In this case however we have far better tools, namely a computer
> with a database. It's easy to create a table with units and their
> conversion factor to a standard unit. If you go a bit further you'd
> create a few tables linking units and how to convert them to each
> other, which also solves the case where you're not dealing with just
> distances (the volumes you mention above, for example).
>
> Once you have that, it's easy to write a few (immutable!) functions:
> - convert_to(quantity, unit), which converts a quantity in a given
> unit to a standard unit, and
> - convert_from(quantity, unit), which converts a quantity in your
> standard unit to the given unit.
>
> Then you simply write your query as:
> SELECT convert_from(SUM(convert_to(quantity, unit)), 'inch') FROM
> table;
>
> If you're going for the more complicated approach that can directly
> convert any unit to any other (provided the record that links them
> exists) the query gets even simpler. You only need one conversion
> function in that case:
> - convert_unit(quantity, from_unit, to_unit)
> and your query would become:
> SELECT SUM(convert_unit(quantity, unit, 'inch')) FROM table;

Alban Hertroys

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


!DSPAM:737,4a8aec0910131445318212!