Unit Conversion Table in SQL

Question

I want to implement unit conversion functionality in a database for units of mass, volume, temperature, etc. My experience is in software development, so I naturally tend to create a bunch of user-defined functions for this task. But I have a suspicion that there is a more "SQLish" way to achieve this.

A table-based method such as described here or here will be great, but I see no easy way to extend this approach for units that are not related by a single factor, such as temperature (F-to-C, C-to-F) or atomic mass (kgmol-kg). I have seen multi-step approaches (such as those described here ), but they seem too confusing to be useful.

Am I missing something obvious here, or are functions really the only way to go?

Answer 1

In the end, I decided to stick with UDF. The main motivation for this decision was that the complexity of the table-based approach does not seem justified given the infrequently this task. In addition, some of the functions (for example, ConvertMass() ) depend on subqueries (for example, to determine the atomic weight of a given component). So UDFs looked the most sensible way.

Answer 2

To handle temperature conversions, your conversion table must have a multiplier and an offset. For example, for F → C, the offset will be -32 and a factor of 5/9.

If you know in advance all possible units, then the message based on the table works fine. However, if you need a fully flexible system, for example, meters from 5 * liters to inches ^ 5 * gallons, then you will need a table of base units and a custom function for conversion. This function is likely to use recursive cte to parse the expression of units. All this would be rather complicated, so I hope you have a complete list of units.

Answer 3

It has been a couple of years since this question was asked, but I created a solution that may be of interest for this topic in the future. The following solution is tested on 2012 SQL Server. To reduce the size of the code on the page, I provide only bulk measurements.

 CREATE TABLE [Measurement type] ( [Type ID] INT IDENTITY(1,1) PRIMARY KEY NOT NULL, [Type Name] NVARCHAR(30) NOT NULL ) GO CREATE TABLE [Measurement unit] ( [Unit ID] INT IDENTITY(1,1) PRIMARY KEY NOT NULL, [Type ID] INT REFERENCES [Measurement type]([Type ID]) NOT NULL, [Unit name] NVARCHAR(30) NOT NULL, [Unit symbol] NVARCHAR(10) NOT NULL ) GO /* Use both multiplicand and dividend to reduce rounding errors */ CREATE TABLE [Measurement conversions] ( [Type ID] INT NOT NULL REFERENCES [Measurement type]([Type ID]), [From Unit ID] INT NOT NULL REFERENCES [Measurement unit]([Unit ID]), [To Unit ID] INT NOT NULL REFERENCES [Measurement unit]([Unit ID]), [From Unit Offset] FLOAT NOT NULL DEFAULT(0), [Multiplicand] FLOAT NOT NULL DEFAULT(1), [Dividend] FLOAT NOT NULL DEFAULT(1), [To Unit Offset] FLOAT NOT NULL DEFAULT(0), PRIMARY KEY ([Type ID], [From Unit ID], [To Unit ID]) ) GO SET IDENTITY_INSERT [Measurement type] ON GO INSERT INTO [Measurement type]([Type ID], [Type Name]) VALUES (1, 'Length'), (2, 'Area'), (3, 'Volume'), (4, 'Mass'), (5, 'Temperature') -- ... GO SET IDENTITY_INSERT [Measurement type] OFF GO GO SET IDENTITY_INSERT [Measurement unit] ON GO INSERT INTO [Measurement unit]([Unit ID], [Type ID], [Unit name], [Unit symbol]) VALUES (28, 4, 'Milligram', 'mg'), (29, 4, 'Gram', 'g'), (30, 4, 'Kilogram', 'kg'), (31, 4, 'Tonne', 't'), (32, 4, 'Ounce', 'oz'), (33, 4, 'Pound', 'lb'), (34, 4, 'Stone', 's'), (35, 4, 'hundred weight', 'cwt'), (36, 4, 'UK long ton', 'ton') GO SET IDENTITY_INSERT [Measurement unit] ON GO INSERT INTO [Measurement conversions]([Type ID], [From Unit ID], [To Unit ID], [Multiplicand], [Dividend]) VALUES (4, 28, 29, 1, 1000), (4, 28, 30, 1, 1000000), (4, 28, 31, 1, 1000000000), (4, 28, 32, 1, 28350), (4, 32, 33, 1, 16), (4, 32, 34, 1, 224), (4, 32, 35, 1, 50802345), (4, 32, 36, 1, 35840) GO INSERT INTO [Measurement conversions]([Type ID], [From Unit ID], [To Unit ID], [From Unit Offset], [Multiplicand], [Dividend], [To Unit Offset]) SELECT DISTINCT [Measurement Conversions].[Type ID], [Measurement Conversions].[To Unit ID], [Measurement Conversions].[From Unit ID], -[Measurement Conversions].[To Unit Offset], [Measurement Conversions].[Dividend], [Measurement Conversions].[Multiplicand], -[Measurement Conversions].[From Unit Offset] FROM [Measurement Conversions] -- LEFT JOIN Used to reduce errors on inserting same key twice. LEFT JOIN [Measurement conversions] AS [Existing] ON [Measurement Conversions].[From Unit ID] = [Existing].[To Unit ID] AND [Measurement Conversions].[To Unit ID] = [Existing].[From Unit ID] WHERE [Existing].[Type ID] IS NULL GO 

Then run the following query until it turns to the zero rows, it can complain about the multiple Equal ID if there are several paths leading to different conversion rates between the same values.

 INSERT INTO [Measurement conversions]([Type ID], [From Unit ID], [To Unit ID], [From Unit Offset], [Multiplicand], [Dividend], [To Unit Offset]) SELECT DISTINCT [From].[Type ID], [From].[To Unit ID] AS [From Unit ID], [To].[To Unit ID], -[From].[To Unit Offset] + (([To].[From Unit Offset]) * [From].[Multiplicand] / [From].Dividend) AS [From Unit Offset], [From].[Dividend] * [To].[Multiplicand] AS Multiplicand, [From].[Multiplicand] * [To].[Dividend] AS Dividend, [To].[To Unit Offset] - (([From].[From Unit Offset]) * [To].[Multiplicand] / [To].Dividend) AS [To Unit Offset] FROM [Measurement conversions] AS [From] CROSS JOIN [Measurement conversions] AS [To] -- LEFT JOIN Used to reduce errors on inserting same key twice. LEFT JOIN [Measurement conversions] AS [Existing] ON [From].[To Unit ID] = [Existing].[From Unit ID] AND [To].[To Unit ID] = [Existing].[To Unit ID] WHERE [Existing].[Type ID] IS NULL AND [From].[Type ID] = [To].[Type ID] AND [From].[To Unit ID] <> [To].[To Unit ID] AND [From].[From Unit ID] = [To].[From Unit ID] 

Finally, to remove the factors and dividers that directly cancel each other out:

 UPDATE [Measurement conversions] SET [Multiplicand] = 1, [Dividend] = 1 WHERE Multiplicand = Dividend