Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2006
    Posts
    15

    Unanswered: linear interpolation

    my data is like this:

    Code:
    header | data | key
    -------------------
    500    | 3.2  | 10
    500    | 3.4  | 20
    500    | 3.6  | 25
    500    | 3.7  | 40
    501    | 4.1  | 10
    501    | 4.2  | 15
    501    | 4.4  | 30
    501    | 4.6  | 35
    and what I want to do is find the median of "data", but keyed off of "key", so if my desired median is 30, I want to take the two records (data, key) nearest to key = 30, and get the average of "data".
    ...and do this within each "header" value.

    actually, to be precise, I want the linear interpolation, so for header = 500, I want to get the (data, key) pairs of (3.6, 25) and (3.7, 40) and return the interpolated "data" value of 3.6333 (as done here)

    so for the above example the query would produce:

    Code:
    header | interp
    -----------------
    500    | 3.633
    501    | 4.4
    possible, or am I crazy?

  2. #2
    Join Date
    Oct 2006
    Posts
    15
    looks like I may have figured it out

    I created a function to do the interpolation, turned out to be rather easy.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Got here late. Sorry no one could help you before you sussed it.

    Dude - post your solution so others can gain too.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Jun 2007
    Posts
    1
    thank you

  5. #5
    Join Date
    Oct 2006
    Posts
    15
    Quote Originally Posted by pootle flump
    Got here late. Sorry no one could help you before you sussed it.

    Dude - post your solution so others can gain too.
    whoops, was going to but totally forgot.

    first created an interpolation function:
    PHP Code:
    create function fnInterpolate(@header int, @interp float)
    returns float
    as
    begin
    declare @x0 float, @x1 float, @y0 float, @y1 float, @slope float, @b float;

    select top 1 @x0 key, @y0 data from the_table where header=@header and key < @interp order by key desc;

    select top 1 @x1 key, @y1 data from the_table where header=@header and key >= @interp order by key asc;

    set @slope = (@y1 - @y0) / (@x1 - @x0);
    set @= @y1 - (@slope * @x1);
    return (@
    slope * @interp) + @b;
    end 
    then did:

    PHP Code:
    select headerfnInterpolate(headerfrom the_table 
    sorry it's a bit messy, but the actual usage is a bit more complex so I wanted to simplify. basically I select "header" from the table, and call fnInterpolate on that header with a set of conditions I specify. then fnInterpolate uses those conditions to get the two points are around the interpolation point, computes the interpolation, and returns it.

    very well could be a more elegant way to do this though.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •