Extended unit

Hi,

My question is purely theoretical.

I add use in my time in University some software that use "extended
type".
For each variable, we define the mandatory "classic type" as integer,
float, double array of.
And we define an optional "extended type" as the unit in the MKSA
system (Meter, Kilogram, Second, Ampere) or any other unit we would
have previously define (eg. Currency).

This "extended type" was wonderful, because there was warning/error if
"extend type" does not match in any computation: you can not add apple
to orange.

I would appreciate to have such system in PostgreSQL.

Do you think, it is feasible ? unrealistic ?
Any comment ?

Cordialement,
Jean-Gérard Pailloncy

On Tue, Jan 25, 2005 at 10:40:15AM +0100, Pailloncy Jean-Gerard wrote:
> Hi,
>
> My question is purely theoretical.
>
> I add use in my time in University some software that use "extended
> type".
> For each variable, we define the mandatory "classic type" as integer,
> float, double array of.
> And we define an optional "extended type" as the unit in the MKSA
> system (Meter, Kilogram, Second, Ampere) or any other unit we would
> have previously define (eg. Currency).
>
> This "extended type" was wonderful, because there was warning/error if
> "extend type" does not match in any computation: you can not add apple
> to orange.

I think it's a wonderful idea. You could use a similar mechanism to
implement:

- Currencies (so you can't add dollars to pounds)
- Timezone aware timestamps (so a time in Australia looks differet from
a time in Europe)

Probably much more.

> I would appreciate to have such system in PostgreSQL.
>
> Do you think, it is feasible ? unrealistic ?
> Any comment ?

I think it is definitly feasable. There's been discussion before. I
think the best way syntax-wise would be to extend the type system
generically to have subtypes. For example currency(gbp) and siunit(A).
This would simplify operators. You could create a simple add operator
that checked the subtype and complained if they didn't match. A
multiply operator for siunit might even return the appropriate derived
unit. An advanced add unit for currency might lookup an exchange rate
table.

However, I think this might be a tricky (but very worthwhile) project.
Maybe create a subtypes table with the columns (oid, supertypeid,
subtypename) and use the oid here to identify the subtype in storage.
To be complete it would need to change:

- The parser to idenify the new type definitions
- pg_dump to dump these types
- input/output functions for these types
- handle storage

But with a bit of work it could be a nice project.

Hope this helps,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

# jg(at)rilk(dot)com / 2005-01-25 10:40:15 +0100:
> I add use in my time in University some software that use "extended
> type".
> For each variable, we define the mandatory "classic type" as integer,
> float, double array of.
> And we define an optional "extended type" as the unit in the MKSA
> system (Meter, Kilogram, Second, Ampere) or any other unit we would
> have previously define (eg. Currency).
>
> This "extended type" was wonderful, because there was warning/error if
> "extend type" does not match in any computation: you can not add apple
> to orange.
>
> I would appreciate to have such system in PostgreSQL.

Is CREATE TYPE what you're looking for?
http://www.postgresql.org/docs/8.0/static/sql-createtype.html

--
If you cc me or remove the list(s) completely I'll most likely ignore
your message. see http://www.eyrie.org./~eagle/faqs/questions.html

> Is CREATE TYPE what you're looking for?
> http://www.postgresql.org/docs/8.0/static/sql-createtype.html
No. I'll try to give an exemple of what I want:

I suppose I have the following table
CREATE TABLE experiment (
distance DOUBLE,
time DOUBLE,
speed DOUBLE
);

I can do the following query :
SELECT distance+time+speed FROM experiment;

This is a valid SQL query, but there is no physical meaning.

Now, I suppose I have "extended type". So the table is:
CREATE TABLE experiment (
distance DOUBLE(m1),
time DOUBLE(s1),
speed DOUBLE(m1s-1),
);

distance is of type DOUBLE and of unit METER
time is of type DOUBLE and of unit SECOND
speed is of type DOUBLE and of unit METER/SECOND

SELECT distance+time+speed FROM experiment;
Would throw an error : "Incompatible unit M1, S1, M1S-1"

SELECT distance/time+speed FROM experiment;
would succeed (obviously).

It may be possible to mess with domain/type to achieve a draft.
But I pretty sure that we need extend the type system to achieve it
cleanly.

Cordialement,
Jean-Gérard Pailloncy

Martijn van Oosterhout wrote:
> On Tue, Jan 25, 2005 at 10:40:15AM +0100, Pailloncy Jean-Gerard wrote:
>>This "extended type" was wonderful, because there was warning/error if
>>"extend type" does not match in any computation: you can not add apple
>>to orange.
>
>
> I think it's a wonderful idea. You could use a similar mechanism to
> implement:
>
> - Currencies (so you can't add dollars to pounds)
> - Timezone aware timestamps (so a time in Australia looks differet from
> a time in Europe)
>
> Probably much more.

Indeed, you could even add a way to convert between different types if
they are in the same categories; for example convert between dollars and
euro's or between degrees Celsius and degrees Fahrenheit (that's a
trickier one, the shell command 'units' returns wrong results there).

Of course, it's still not possible to add dollars and degrees
Fahrenheit... (hence the category concept I mentioned)

You could even determine that if you divide a quantity in meters by a
quantity in seconds that you're talking about a speed... I think there
are quite a few people on this planet who would be happy about that.

OTOH, it's probably not that a good idea to add all kinds of complicated
(read: "processor intensive") math to a database engine.

Just my 0.02 Euro.

--
Regards,

Alban Hertroys
MAG Productions

P: +31(0)53 4346874
F: +31(0)53 4346876
E: alban(at)magproductions(dot)nl
W: http://www.magproductions.nl

On Tue, Jan 25, 2005 at 05:17:21PM +0100, Alban Hertroys wrote:
> Martijn van Oosterhout wrote:
> >I think it's a wonderful idea. You could use a similar mechanism to
> >implement:
> >
> >- Currencies (so you can't add dollars to pounds)
> >- Timezone aware timestamps (so a time in Australia looks differet from
> >a time in Europe)
> >
> >Probably much more.
>
> Indeed, you could even add a way to convert between different types if
> they are in the same categories; for example convert between dollars and
> euro's or between degrees Celsius and degrees Fahrenheit (that's a
> trickier one, the shell command 'units' returns wrong results there).

I think it would be fabulous if it could be implemented as a generic
extension to the type system, because I'm sure there are even cooler
uses than what we are thinking of here...

> OTOH, it's probably not that a good idea to add all kinds of complicated
> (read: "processor intensive") math to a database engine.

We're talking here about a database with indexes to speed up
intersection tests for arbitrary polygons, extensions to handle
encryption, full text indexing and even builtin XML support. I think
arguing excessive use of CPU cycles is a bit late :)

I think the argument is that if it helps people do their work
"correctly" then it's worth supporting. As long as it doesn't hamper
anybody else.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

Martijn van Oosterhout <kleptog(at)svana(dot)org> writes:
> We're talking here about a database with indexes to speed up
> intersection tests for arbitrary polygons, extensions to handle
> encryption, full text indexing and even builtin XML support.

... none of which require any extensions to the core type system.

AFAICS this could easily be implemented as a user-defined type, along
the lines of

CREATE TYPE measurement AS (value double, units text);

and if you want to constrain a particular column to contain only one
value of units, use CHECK.

The argument that we should extend the type system for this would become
a lot more credible if there were a widely-used extension in existence
for it to prove that there's sufficient demand.

regards, tom lane

On Tue, Jan 25, 2005 at 02:31:40PM -0500, Tom Lane wrote:
> AFAICS this could easily be implemented as a user-defined type, along
> the lines of
>
> CREATE TYPE measurement AS (value double, units text);
>
> and if you want to constrain a particular column to contain only one
> value of units, use CHECK.

I've tried this but I can't work out how to make it work. For composite
types you can't specify input and output functions. It's all record_in
but it's not working for me:

# CREATE TYPE measurement AS (value float, units text);
CREATE TYPE
# select '(5,a)'::measurement;
ERROR: Cannot cast type "unknown" to measurement
# select measurement(5,'a');
ERROR: Function measurement(integer, "unknown") does not exist
Unable to identify a function that satisfies the given argument types
You may need to add explicit typecasts
# select cast( (5,'a') as measurement);
ERROR: parser: parse error at or near "as" at character 22
# select cast( '5' as measurement);
ERROR: Cannot cast type "unknown" to measurement

This is 7.3 though, is it better in later versions? I can't find any
examples anywhere. Composite types don't seems to be used much.
However, it appears you could just update pg_type to change the
input/output functions...

> The argument that we should extend the type system for this would become
> a lot more credible if there were a widely-used extension in existence
> for it to prove that there's sufficient demand.

I guess it's mostly syntactic sugar, but it might normalize the
varchar(n) and timestamp(n) format.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

Martijn van Oosterhout <kleptog(at)svana(dot)org> writes:
> On Tue, Jan 25, 2005 at 02:31:40PM -0500, Tom Lane wrote:
>> AFAICS this could easily be implemented as a user-defined type, along
>> the lines of
>> CREATE TYPE measurement AS (value double, units text);
>> and if you want to constrain a particular column to contain only one
>> value of units, use CHECK.

> I've tried this but I can't work out how to make it work. For composite
> types you can't specify input and output functions.

No, but as of 8.0 you don't really need them, assuming that you don't
mind some parentheses around your output.

regression=# CREATE TYPE measurement AS (value float, units text);
CREATE TYPE
regression=# select cast( (5,'a') as measurement);
row
-------
(5,a)
(1 row)

Or you can implement it as a scalar type if you really want to define
your own I/O functions.

regards, tom lane

> ... none of which require any extensions to the core type system.
>
> AFAICS this could easily be implemented as a user-defined type, along
> the lines of
>
> CREATE TYPE measurement AS (value double, units text);
>
> and if you want to constrain a particular column to contain only one
> value of units, use CHECK.
>
> The argument that we should extend the type system for this would
> become
> a lot more credible if there were a widely-used extension in existence
> for it to prove that there's sufficient demand.
I have begining to put all the SI unit in a table.
I am writing the function to check the unit in a standard way.
I plan to use the user-defined type proposed by Tom Lane.
The check are done at execution time.

But I object that what I am doing is just a proof of concept and not
the right thing to do.
I do not want for each column and each row to store the value and the
unit.
I do want to put the unit in the definition of the column and the check
on the parser before any execution.

Cordialement,
Jean-Gérard Pailloncy

Pailloncy Jean-Gerard <jg(at)rilk(dot)com> writes:
> I do not want for each column and each row to store the value and the
> unit.

> I do want to put the unit in the definition of the column and the check
> on the parser before any execution.

If you do that, you foreclose the ability to store mixed values in a
single column, in return for what? Saving a couple of bytes per value?
(I suppose that in a serious implementation we'd store the units as some
sort of reference, not as a string.) Compare the implementation of the
NUMERIC type: you *can* constrain a column to have a fixed precision,
but you do not *have* to.

regards, tom lane

Tom Lane wrote:
> Pailloncy Jean-Gerard <jg(at)rilk(dot)com> writes:
>
>>I do not want for each column and each row to store the value and the
>>unit.
>
>
>>I do want to put the unit in the definition of the column and the check
>>on the parser before any execution.
>
>
> If you do that, you foreclose the ability to store mixed values in a
> single column, in return for what? Saving a couple of bytes per value?
> (I suppose that in a serious implementation we'd store the units as some
> sort of reference, not as a string.) Compare the implementation of the
> NUMERIC type: you *can* constrain a column to have a fixed precision,
> but you do not *have* to.

It strikes me that the right level of constraint is the quantity being
represented: length / mass / time / velocity.

Then you could store any of: '1inch', '2m', '3km', '4light-years' in a
"length" column.

I was about to say this is similar to the interval type, but of course
there are issues there with month/year not being a consistent length.

--
Richard Huxton
Archonet Ltd

On Tue, Jan 25, 2005 at 11:41:28PM +0100, Pailloncy Jean-Gerard wrote:
> I have begining to put all the SI unit in a table.
> I am writing the function to check the unit in a standard way.
> I plan to use the user-defined type proposed by Tom Lane.
> The check are done at execution time.
>
> But I object that what I am doing is just a proof of concept and not
> the right thing to do.
> I do not want for each column and each row to store the value and the
> unit.
> I do want to put the unit in the definition of the column and the check
> on the parser before any execution.

Actually, there's no reason to store a string there. Just like for
timezones, I was proposing having a table listing all the timezones
postgresql knows about and using the oid of that row as the timezone
identifier. This only requires four bytes per field, not unreasonable.

So, you have 1=Amps, 2=Volts, 3=Ohms, etc... It's a little more
querying but if you wrote the functions in C I think you could get it
all fairly performant.

Now, how to store the relationships between them to handle
multiplication and division. Probably go back to base types...

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

On Wed, Jan 26, 2005 at 09:06:16AM +0000, Richard Huxton wrote:
> Tom Lane wrote:
> >If you do that, you foreclose the ability to store mixed values in a
> >single column, in return for what? Saving a couple of bytes per value?
> >(I suppose that in a serious implementation we'd store the units as some
> >sort of reference, not as a string.) Compare the implementation of the
> >NUMERIC type: you *can* constrain a column to have a fixed precision,
> >but you do not *have* to.
>
> It strikes me that the right level of constraint is the quantity being
> represented: length / mass / time / velocity.
>
> Then you could store any of: '1inch', '2m', '3km', '4light-years' in a
> "length" column.

Ofcourse, only one of those is in SI units :) Just like the interval
type, all this could be handled by the parser. Define some costant
conversions, after all a light-year is about 9.5e15 metres.

The question is, if you put one inch in, do you expect to get one inch
out?

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

>> It strikes me that the right level of constraint is the quantity being
>> represented: length / mass / time / velocity.
>>
>> Then you could store any of: '1inch', '2m', '3km', '4light-years' in a
>> "length" column.
>
> Ofcourse, only one of those is in SI units :) Just like the interval
> type, all this could be handled by the parser. Define some costant
> conversions, after all a light-year is about 9.5e15 metres.
>
> The question is, if you put one inch in, do you expect to get one inch
> out?
I'm glad that some understand that I want to have strong type checking
and not multiform filed (with special compound type).

I want that whater is the unit in, the data is coherent.
And the output will be format with convert function.

If I have a column with "speed DOUBLE(m1s-1)"
I want to be able to put in in any unit format.
If I want special output, I would have a function
doubleunit_to_char(speed,'si') that will output "3 m/s" and
doubleunit_to_char(speed,'si','fr') that will output "3 mètre/seconde"
and
doubleunit_to_char(speed,'si','en') that will output "3 meter/second"
and
doubleunit_to_char(speed,'british','en') that will output "xxx
yard/day" ;-)

I am in the process of writing such function to convert from test to an
internal format.
But the problem is that all the work I am doing is about coupound type
(double, unit)
the unit type is an integer with for table for the definition of unit,
convertion and translation in human form.

Cordialement,
Jean-Gérard Pailloncy

On Jan 26, 2005, at 20:06, Pailloncy Jean-Gerard wrote:

>>> It strikes me that the right level of constraint is the quantity
>>> being
>>> represented: length / mass / time / velocity.
>>>
>>> Then you could store any of: '1inch', '2m', '3km', '4light-years' in
>>> a
>>> "length" column.
>>
>> Ofcourse, only one of those is in SI units :) Just like the interval
>> type, all this could be handled by the parser. Define some costant
>> conversions, after all a light-year is about 9.5e15 metres.
>>
>> The question is, if you put one inch in, do you expect to get one inch
>> out?
> I'm glad that some understand that I want to have strong type checking
> and not multiform filed (with special compound type).
>
> I want that whater is the unit in, the data is coherent.
> And the output will be format with convert function.

<snip />

> I am in the process of writing such function to convert from test to
> an internal format.
> But the problem is that all the work I am doing is about coupound type
> (double, unit)
> the unit type is an integer with for table for the definition of unit,
> convertion and translation in human form.

This reminds me of Date and Darwen's possible representations. You
might be interested in some of their writings, in particular "The Third
Manifesto", though they have some papers online as well. Most of what
they discuss is at a more theoretical level rather than implementation,
but it provides some food for thought. Here's a couple of sites. The
second is also includes writings by Fabien Pascal.

http://www.thethirdmanifesto.com/
http://www.dbdebunk.com/index.html

Neat stuff, in my opinion.

Michael Glaesemann
grzm myrealbox com

On Wed, Jan 26, 2005 at 12:06:15PM +0100, Pailloncy Jean-Gerard wrote:
> If I have a column with "speed DOUBLE(m1s-1)"
> I want to be able to put in in any unit format.
> If I want special output, I would have a function
> doubleunit_to_char(speed,'si') that will output "3 m/s" and
> doubleunit_to_char(speed,'si','fr') that will output "3 mètre/seconde"
> and
> doubleunit_to_char(speed,'si','en') that will output "3 meter/second"
> and
> doubleunit_to_char(speed,'british','en') that will output "xxx
> yard/day" ;-)

Sound very cool. Don't forget en_AU should return "metre" and en_US
should return "meter". Not to mention "rods/hogshead" :)

> I am in the process of writing such function to convert from test to an
> internal format.
> But the problem is that all the work I am doing is about coupound type
> (double, unit)
> the unit type is an integer with for table for the definition of unit,
> convertion and translation in human form.

I just thought though, it's going to be impossible to list all possible
unit types in a table. There are essentially infinite of them. For
example, the gravitational constant is m3 kg-1 s-2, Plancks constant is
m2 kg s-1, none of which appear in the standard list of derived units.

Unfortunatly, the only bolution I can think of is to encode the units
as numbers in a bitfield. There's only seven base units anyway so if
you allocate 4 bits for each you can fit it all in 32 bits. If you
wanted more units you could cut the usage of mol and cd to two bits
since they don't really need powers from +7 to -8.

You still need the table to do lookups for input and output but it is a
little more flexible.

Any chance of seeing the code, I'd love to play with it...
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

Hi all,

I wonder if it makes sense to implement the units as separate data types
? Cause that's what they are really.
So "amper" would be a data type which aliases one of the numeric data
types (depending on what precision range you need), but does not allow
to be added with anything else than "amper". Any other interaction with
other units (read data types) would be achieved by defining the needed
operators on the respective data types (read units).

And all the "unit" stuff could be added as an extension.

Is this a workable solution ?

Cheers,
Csaba.

On Wed, 2005-01-26 at 12:06, Pailloncy Jean-Gerard wrote:
> >> It strikes me that the right level of constraint is the quantity being
> >> represented: length / mass / time / velocity.
> >>
> >> Then you could store any of: '1inch', '2m', '3km', '4light-years' in a
> >> "length" column.
> >
> > Ofcourse, only one of those is in SI units :) Just like the interval
> > type, all this could be handled by the parser. Define some costant
> > conversions, after all a light-year is about 9.5e15 metres.
> >
> > The question is, if you put one inch in, do you expect to get one inch
> > out?
> I'm glad that some understand that I want to have strong type checking
> and not multiform filed (with special compound type).
>
> I want that whater is the unit in, the data is coherent.
> And the output will be format with convert function.
>
> If I have a column with "speed DOUBLE(m1s-1)"
> I want to be able to put in in any unit format.
> If I want special output, I would have a function
> doubleunit_to_char(speed,'si') that will output "3 m/s" and
> doubleunit_to_char(speed,'si','fr') that will output "3 mètre/seconde"
> and
> doubleunit_to_char(speed,'si','en') that will output "3 meter/second"
> and
> doubleunit_to_char(speed,'british','en') that will output "xxx
> yard/day" ;-)
>
> I am in the process of writing such function to convert from test to an
> internal format.
> But the problem is that all the work I am doing is about coupound type
> (double, unit)
> the unit type is an integer with for table for the definition of unit,
> convertion and translation in human form.
>
> Cordialement,
> Jean-Gérard Pailloncy
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

That's not necessarily a constant; there is evidence to suggest that
the speed of light is slowing down over time. If that is indeed the
case, then as light travels more slowly, a light year will become
shorter.

http://www.ldolphin.org/speedo.html
http://www.ldolphin.org/cdkalan.html
http://www.answersingenesis.org/docs2002/0809_cdk_davies.asp

On Jan 26, 2005, at 5:34 AM, Martijn van Oosterhout wrote:
> Define some costant
> conversions, after all a light-year is about 9.5e15 metres.

- -----------------------------------------------------------
Frank D. Engel, Jr. <fde101(at)fjrhome(dot)net>

$ ln -s /usr/share/kjvbible /usr/manual
$ true | cat /usr/manual | grep "John 3:16"
John 3:16 For God so loved the world, that he gave his only begotten
Son, that whosoever believeth in him should not perish, but have
everlasting life.
$
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (Darwin)

iD8DBQFB957r7aqtWrR9cZoRAsCHAJ4ukBZtXZ4yhNvS8Im9Bx6AV8oHBQCfdlWa
CdDrwfaHS8SJsMjphYyMZ7s=
=Rjfk
-----END PGP SIGNATURE-----

___________________________________________________________
$0 Web Hosting with up to 120MB web space, 1000 MB Transfer
10 Personalized POP and Web E-mail Accounts, and much more.
Signup at www.doteasy.com

On Wed, 2005-01-26 at 08:45 -0500, Frank D. Engel, Jr. wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> That's not necessarily a constant; there is evidence to suggest that
> the speed of light is slowing down over time. If that is indeed the
> case, then as light travels more slowly, a light year will become
> shorter.
>
> http://www.ldolphin.org/speedo.html
> http://www.ldolphin.org/cdkalan.html
> http://www.answersingenesis.org/docs2002/0809_cdk_davies.asp

I don't intend to start any off-topic threads, but the evidence that you
sighted seems to suggest that the speed of light is slowing down, but it
doesn't explain how it can only be 6,000 years old when scientists are
able to observe the light of stellar objects more than 6,000 light years
away. This would be evidence that would contradict the information that
you have referenced... as it would mean that the age of our universe is
several billions of years old.

I would be more than happy to discuss this off-list with you as I don't
think the postgresql list is the place to debate what is constant or
not..at least not in terms of mythology and mans creation of metaphors
which we overuse to try to explain what all of this is...when we really
haven't the foggiest idea. ;-)

Cheers,

Robby

--
/***************************************
* Robby Russell | Owner.Developer.Geek
* PLANET ARGON | www.planetargon.com
* Portland, OR | robby(at)planetargon(dot)com
* 503.351.4730 | blog.planetargon.com
* PHP/PostgreSQL Hosting & Development
* --- Now hosting PostgreSQL 8.0! ---
****************************************/

Martijn van Oosterhout <kleptog(at)svana(dot)org> writes:

> Now, how to store the relationships between them to handle
> multiplication and division. Probably go back to base types...

I've thought about this myself quite a bit. I decided that trying to implement
multiplication and division is a bad idea.

What you really want is just a type that guarantees that you can add and
subtract two dimensional values as long as they're in the same units.
(1km + 2km = 3km)

You should also be able to divide two dimensional values of the same type and
get a plain dimensionless floating point number. (2km / 1km = 2.0)

Also you should be able to multiple or divide a dimensional value with a
dimensionless value. (2km * 2.0 = 4km)

All of the above doesn't require recognizing what the meaning of the
dimensions are at all. As long as they're all linear they can be completely
opaque strings. That keeps the system quite simple.

It also keeps it most flexible in that a user can use *any* unit whatsoever.
Not just units from some approved list.

If you wanted to implement multiplying two dimensioned values or converting
from one unit to another then you would suddenly have to know the meaning of
every unit.

You could implement the sementic-free approach where the system doesn't know
anything about the actual meanings of units and then implement a function that
allows you to call out to the traditional units program to convert.

So you could have units_conv(dim_val, 'km') to call units and request to
convert whatever is in the dim_val column into km. And maybe a
units_expr(dim_val||'*'||dim_val2, 'km^2').

I would suggest this because it doesn't try to reimplement all of units and it
isolates the complex parts that limit the flexibility of the system in one
area. Someone who isn't trying to use postgres as a units substitute doesn't
have to deal with it.

--
greg

If you allow multiplication and division, you'd need to store not only
one type, but an expression like m.s^-2, etc. You'll end up with something
with Maple. Isn't there some free open source algebraic computation
toolkit with equations and units somewhere ?

> I wonder if it makes sense to implement the units as separate data types
> ? Cause that's what they are really.
> So "amper" would be a data type which aliases one of the numeric data
> types (depending on what precision range you need), but does not allow
> to be added with anything else than "amper". Any other interaction with
> other units (read data types) would be achieved by defining the needed
> operators on the respective data types (read units).

You'd have to create a postgres datatype for every variation on m, m/s,
m/s², etc... which would be kinda unworkable... I think it's better to
have one datatype (number with unit) and have the operators raise an
exception when trying to add incompatible units ?

As for the encoding, why not just use a (float, text) with the text as a
parseable representation of the unit, which could as well be the SI unit
(like m/s) which would be a lot more flexible than bitfields. Problem is I
think it'll always be variable length. Maybe there is enough space in an
int64 to fit it all ? Maybe with huffman coding ? Is it really important
to save a few bytes ? I don't think so.
For table columns, the type would be saved in the column definition so
you'd just have a float anyway.
I think it's an exciting project !

> If you allow multiplication and division, you'd need to store not
> only one type, but an expression like m.s^-2, etc. You'll end up with
> something with Maple. Isn't there some free open source algebraic
> computation toolkit with equations and units somewhere ?
Yes and no.
I am in the (slow) process to have a small library that do a parsing of
this string.
I want to have, + - * / square power function.
We do not need a full algebraic parser.

I restrict for my test the pattern of the type in human reading form as
m.s-1.
A2.m.s-1.rad-2.

no space, separator is point, no power sign, no plus sign for power.

The main trouble is to be clean the power should be in "fraction"
space. and not in "double precision" space. That is just more fun to
add ;-)

Cordialement,
Jean-Gérard Pailloncy

>> ision range you need), but does not allow
>> to be added with anything else than "amper". Any other interaction
>> with
>> other units (read data types) would be achieved by defining the needed
>> operators on the respective data types (read units).
>
> You'd have to create a postgres datatype for every variation on m,
> m/s, m/s², etc... which would be kinda unworkable... I think it's
> better to have one datatype (number with unit) and have the operators
> raise an exception when trying to add incompatible units ?
No doable.
After you will need the factorial number of operator between all the
combinatory of couple of unit.

> As for the encoding, why not just use a (float, text) with the text
> as a parseable
I am doing test with this.
But constraint is done at execution time.
Space is larger.
Operation of two of these is slower than the native one (float).
I want to have it at parsing level to speed the error detection as
writing code.

> representation of the unit, which could as well be the SI unit (like
> m/s) which would be a lot more flexible than bitfields. Problem is I
> think it'll always be variable length. Maybe there is enough space in
> an int64 to fit it all ? Maybe with huffman coding ? Is it really
> important to save a few bytes ? I don't think so.
(float, text) versus (float)
with text is cd3.A2.sr.m-1.s-2.rad-3.kg-4
with an experiment of few milions of results that have few dozen of
parameter.
just to be sure that be not add at parser time meter with second.
I think this work-less, except for a proof of concept.

Cordialement,
Jean-Gérard Pailloncy

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Yep:

http://www-fourier.ujf-grenoble.fr/~parisse/giac.html

Try things like:

convert(4_acre, _in^2)

(I'm in TI-89 mode, if that makes any difference I don't know).

On Jan 26, 2005, at 6:57 PM, PFC wrote:

> Isn't there some free open source algebraic computation toolkit with
> equations and units somewhere ?

- -----------------------------------------------------------
Frank D. Engel, Jr. <fde101(at)fjrhome(dot)net>

$ ln -s /usr/share/kjvbible /usr/manual
$ true | cat /usr/manual | grep "John 3:16"
John 3:16 For God so loved the world, that he gave his only begotten
Son, that whosoever believeth in him should not perish, but have
everlasting life.
$
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (Darwin)

iD8DBQFB+QaP7aqtWrR9cZoRAom/AJ43HzYhGi8EYyrABIePRz6ArkDe8wCfdLct
7/lDjssEvky3UKm3t+KbcVw=
=+jYC
-----END PGP SIGNATURE-----

___________________________________________________________
$0 Web Hosting with up to 120MB web space, 1000 MB Transfer
10 Personalized POP and Web E-mail Accounts, and much more.
Signup at www.doteasy.com

Frank D. Engel, Jr. wrote [01/27/05 9:19 AM]:
> On Jan 26, 2005, at 6:57 PM, PFC wrote:
>
>> Isn't there some free open source algebraic computation toolkit with
>> equations and units somewhere ?

A very simple but effective system that I have used quite a lot for
"computation with units" is a Python module called unum ("Unit numbers"):

http://home.tiscali.be/be052320/Unum.html

I suppose what makes this (marginally) "on-topic" is that you could use
it within pgpythonu, but I haven't tried that myself. In particular,
you'd have to serialize the unum objects to store them.
--
________________
harrison(at)tbc(dot)net

> Frank D. Engel, Jr. wrote [01/27/05 9:19 AM]:
> > On Jan 26, 2005, at 6:57 PM, PFC wrote:
> >
> >> Isn't there some free open source algebraic computation toolkit with
> >> equations and units somewhere ?

You mean like the traditional units program available on virtually all Unix
machines?

$ units
2084 units, 71 prefixes, 32 nonlinear units

You have: 1 lightyear/fortnight
You want: m/s
* 7.8213711e+09
/ 1.2785482e-10

That's the program I suggested writing a function to hand this work off to
(presumably in the form of a dynamic library). Keep the postgres code agnostic
about the semantics of the units. As long as you stick to linear units then
Postgres can treat them as opaque strings.

--
greg

Hi,

Some word from my test work.

I try first to just have a parser that can manage a string of unit.
like 'm s-1 kg2.5 A3.5/7.2'
The parser accept :
- any letters for the unit
- a + or - or nothing before the power
- a fractional power
- the unit separator is space ;-/
The system normalize the fraction of the power.

select ('1 m'::text + '1 m'::text)::text
'2 m'
select ('1 m'::text - '1 m'::text)::text
'0 m'
select ('1 m'::text / 2)::text
'0.5 m'
select ('1 m'::text * 2)::text
'2 m'
select ('1 m'::text + 2)::text
'NULL'
select ('1 m'::text * '3 s'::text)::text
'3 s m'
select ('1 m'::text / '3 s'::text)::text
'0.333333333333333 s-1 m'
select ('1 m'::text + '3 s'::text)::text
'NULL'
select (1 / '3 s'::text)::text
'0.333333333333333 s-1'
select ('1 m s2 m s kg'::text::doubleunit)::text
'1 s3 m2 kg'
select ('4.5 m s2 m-3.2/7.1 s'::text::doubleunit)::text
'4.5 s3 m39/71'

So with this system you can add with unit checking any value.
But there is no conversion.
So I need to enter all unit in a coherent system.
I have begin to add a translation table for unit.
The first table will to manage affix only (kilo, mega, giga, micro,
milli, etc.)

The may problem is because is done at execution time, it is 50 times
slower that standard calculus.

Note: I use too much ::text because I mix som CAST definition ;-(

I really want it at parsing time....

Note 2: I think version 8.0 has a better support for function returning
composite type. It would be easier to build the system, when I'll
upgrade...

Cordialement,
Jean-Gérard Pailloncy

De: jg(at)rilk(dot)com
Objet: Rép : [GENERAL] Extended unit
Date: 28 janvier 2005 18:18:18 GMT+01:00
À: jg(at)rilk(dot)com

I have done a new version with
- a new conversion function
- possibility to enter value as fraction
- addition of the operator + - * / = <> != > < >= <=
- SQL only code

Some results :
calcul | result
--------------------------------------+------------------------
1 m | 1 m
1/4 m kg-1/2 m1/2 s-3 | 0.25 s-3 m3/2 kg-1/2
1/2 m2 + 1/4 m2 | 0.75 m2
1.5/2 m2 - 1/2.5 m2 | 0.35 m2
1 m + 1 s | null
1 m s-1 * 1 s3/2 | 1 s1/2 m
1 / 1 s | 1 s-1
2 m ^ 3 | 8 m3
1 m = 1 m | T
1 m = 3 m | F
1 m = 1 s | null
unit_conv(3/2 C J2.5 m-1/2.5 kg m-1) | 150 s-6 A m18/5 kg7/2

Exemple of the command:
select ('1.5/2 m2'::text::du - '1/2.5 m2'::text::du)::text
0.35 m2

The code is 600 lines.
I do not know if it is too big to be send on the mailing list.
If someone want to test it, contact me in private.

Note 1: I add a trouble in CAST where a quoted string is recognize as
"unknown' and not as "text". For this reason I force the type to
"texte" before anything else.
Note 2: There is some trouble with the set of SI compatible units.
Example: Pa may peta-are or Pascal. I supress the less important unit
to keep uniquenness of the unit symbol.

Cordialement,
Jean-Gérard Pailloncy

Greg Stark wrote:
> You mean like the traditional units program available on virtually all Unix
> machines?
>
> $ units
> 2084 units, 71 prefixes, 32 nonlinear units
>
> You have: 1 lightyear/fortnight
> You want: m/s
> * 7.8213711e+09
> / 1.2785482e-10
>
> That's the program I suggested writing a function to hand this work off to
> (presumably in the form of a dynamic library). Keep the postgres code agnostic
> about the semantics of the units. As long as you stick to linear units then
> Postgres can treat them as opaque strings.

Except that it also says:

$ units
510 units, 54 prefixes
You have: 1 K
You want: degC
* 1
/ 1

Which is incorrect, of course. Same for degrees Fahrenheit. The poor
command can't do baseline offsets.

--
Alban Hertroys
MAG Productions

T: +31(0)53 4346874
F: +31(0)53 4346876
E: alban(at)magproductions(dot)nl
W: http://www.magproductions.nl

On Mon, Jan 31, 2005 at 11:57:41AM +0100, Alban Hertroys wrote:
> Greg Stark wrote:
> >That's the program I suggested writing a function to hand this work off to
> >(presumably in the form of a dynamic library). Keep the postgres code
> >agnostic
> >about the semantics of the units. As long as you stick to linear units then
> >Postgres can treat them as opaque strings.
>
> Except that it also says:
>
> $ units
> 510 units, 54 prefixes
> You have: 1 K
> You want: degC
> * 1
> / 1
>
> Which is incorrect, of course. Same for degrees Fahrenheit. The poor
> command can't do baseline offsets.

Well, yes and no. As units, saying the temperature has risen by 34
Kelvin is the same as saying it has risen by 34 Celcius and is also the
same as 34*5/9 Fahrenheit. So if you're just dealing with unit
conversion it is the correct answer. The units "m^2 K" and "m^2 C" are
the same unit.

However, when people are talking about absolute temperatures you have a
problem as 1 absolute degK <> 1 absolute degC. The question is what is
the right thing for this library to do.

Hope this helps,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

RTFM.

You have: tempK(1)
You want: tempC
-272.15

--
greg