CODEWITHSUNDEEP
phpmysqlinterpolation
Sanya
Sanya

Reputation: 1280

MySQL php Linear Interpolation of Table

I have a table titled "psytable_moist_air" shown below: enter image description here

I'm trying to develop a MySQL statement that will interpolate a value that may be between records. (EDIT: If it is easier to do the math in PHP I'm open for that solution too!]

Example: I would like to know the "hda" value where "T" = 17.8.

Notice there is no record where "T"=17.8. However, because this table is linearly related, I can get the "hda" value where "T"=17 and the "hda" value where "T"=18 and do a linear interpolation of the values.

The result math would look like this:
(Get the "hda" value where T=17 : hda = 17.102)
(Get the "hda" value where T=18 : hda = 18.108)

enter image description here

EDIT: The only way I can think of is to do two MySQL statements to grab the smaller and larger values:

SELECT MAX(`T`), MAX(`hda`) FROM `psytable_moist_air` WHERE `T`<17.8
SELECT MIN(`T`), MIN(`hda`) FROM `psytable_moist_air` WHERE `T`>17.8

Then I would use these two values to do the interpolation. This works but seems pretty inefficient. Can anyone come up with a better solution??

If anyone has any advice it would be much appreciated!

.

Upvotes: 3

Views: 1474

Answers (2)

Robert Pounder
Robert Pounder

Reputation: 1511

I recently had to do this and since there is no PHP answer:

function CalcLinearInterpolation($x1, $y1, $x2, $y2, $t){
        $n = ($x2 - $x1);
        $j = ((($t - $x1) / ($n / 100)) / 100);
        $r = $y1 + ($j*($y2 - $y1));                
        return $r;
}

CalcLinearInterpolation(2, 5, 3, 9, 2.5) would return 7

To be clear;

  • 2 (x1) = 5 (y1)
  • 3 (x2) = 9 (y2)
  • 2.5 (t) = Result

So: CalcLinearInterpolation($x1, $y1, $x2, $y2, $t) in this scenario;

CalcLinearInterpolation(2, 5, 3, 9, 2.5)

Upvotes: 2

Hozikimaru
Hozikimaru

Reputation: 1156

I believe the best way to do this would be to use the floor function.

See the following;

SET @x = 17.8;
SET @x0 = FLOOR(@x);
SET @x1 = FLOOR(@x) + 1;

SET @y0 = (SELECT `hda` FROM `psytable_moist_air` WHERE `T` = @x0);
SET @y1 = (SELECT `hda` FROM `psytable_moist_air` WHERE `T` = @x1);

SET @y = @y0 + (@y1 - @y0) * ((@x - @x0) / (@x1 - @x0));

Select @y ,@x0 ,@x1, @y0 ,@y1;

Upvotes: 1

Related Questions