Nimble Coder

Bilinear Interpolation in SQL Server

The past several days I have been wrapped in relatively complex calculations in SQL Server. The process involves gas composition data from a time-series historian that is weighted by flow-rate and averaged. Then the composition is used for several lookup tables for volume correction based on the gas mixture and temperature. The lookup tables are similar to the following structure:

The algorithm tries to find the closest x and y values to the supplied parameters. If any of the parameters match the lookup table, then standard linear interpolation can be used or if both parameters match then no computation is necessary. The bilinear interpolation is used for the x and y parameters and then between the intermediate x and y interpolated values.

The SQL lookup table is defined as follows and I created a quick script to generate INSERT statements from the Excel spreadsheet source:

CREATE TABLE [dbo].[K1_Lookup](
     X          float           NOT NULL        -- Molecular mass of mixture (Xi * Mi)
    ,Y          float           NOT NULL        -- Temperature, degC
    ,Z          float           NOT NULL        -- Volume correction factor, m^3/kmol
)

The algorithm to find the lookup values is crude in SQL. I used MAX where the value is less than or equal to the coordinate. That is: find the maximum coordinate value that is less than or equal to the desired coordinate. Please pardon the use of GOTO; I prefer not to use it but it was used specifically for error handling and validation. Since the

    -- The lookup table is available in: @DataValues
    -- Get the X variable position
    DECLARE  @X0 FLOAT
            ,@X1 FLOAT

    SELECT @X0 = MAX(X) FROM @DataValues WHERE X <= @XValue
    SELECT @X1 = MIN(X) FROM @DataValues WHERE X >= @XValue
    IF (@X0 IS NULL) OR (@X1 IS NULL) RETURN NULL

    -- Get the Y variable position    
    DECLARE  @Y0 FLOAT
            ,@Y1 FLOAT

    SELECT @Y0 = MAX(Y) FROM @DataValues WHERE Y <= @YValue
    SELECT @Y1 = MIN(Y) FROM @DataValues WHERE Y >= @YValue
    IF (@Y0 IS NULL) OR (@Y1 IS NULL) RETURN NULL

    -- Get the Z variable position    
    DECLARE  @Z00 FLOAT
            ,@Z01 FLOAT
            ,@Z10 FLOAT
            ,@Z11 FLOAT

    SELECT @Z00 = Z FROM @DataValues WHERE Y = @Y0 AND X = @X0
    SELECT @Z01 = Z FROM @DataValues WHERE Y = @Y0 AND X = @X1
    SELECT @Z10 = Z FROM @DataValues WHERE Y = @Y1 AND X = @X0
    SELECT @Z11 = Z FROM @DataValues WHERE Y = @Y1 AND X = @X1

    IF (@X0 = @X1) AND (@Y0 = @Y1)
    BEGIN
        -- Exact match for X and Y look-up values
        SELECT @Result = @Z00
    END
    ELSE IF (@X0 = @X1)
    BEGIN
        -- Exact match for X look-up value; find Y value
        SELECT @Result = @Z10 + (@Z11-@Z10)/(@Y1-@Y0)*(@YValue-@Y0)
    END
    ELSE IF (@Y0 = @Y1)
    BEGIN
        -- Exact match for Y look-up value; find X value
        SELECT @Result = @Z00 + (@Z01-@Z00)/(@X1-@X0)*(@XValue-@X0)

    END
    ELSE
    BEGIN
        -- Full Bilinear interpolation; determine Z position
        DECLARE  @FA FLOAT
                ,@FB FLOAT

        SELECT @FA = @Z00 + (@Z01-@Z00)/(@X1-@X0)*(@XValue-@X0)
        SELECT @FB = @Z10 + (@Z11-@Z10)/(@X1-@X0)*(@XValue-@X0)
        SELECT @Result = @FA + (@FB-@FA)/(@Y1-@Y0)*(@YValue-@Y0)
    END

    RETURN @Result

This is one area where I am keenly interested to compare the implementation and performance in a SQL based function or stored procedure compared to a CLR-based solution. Hopefully I will get some time to create a CLR-based solution and to compare the two solutions soon.

Print | posted on Tuesday, June 19, 2007 12:17 PM | Filed Under [ SQL ]