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!
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
