Coefficients 2nd Order Polynomial Curve

I have a recordset of x an y values that I want to use to return the coefficients that would be used to equate 'y' for a 2nd order polynomial curve.  Is there a SQL function that will return those?
LVL 1
vbNewbie2009Asked:
Who is Participating?
 
Mike EghtebasDatabase and Application DeveloperCommented:
Basically the general form of polynomial is ax^2+bx+c=y

after entering three points like A(1,1), B(0,2), and C(2,2)

you will get 3 equation with 3 unknowns a, b, c.

try this:  
   declare  @p1x decimal= 1.0;
 declare @p1y decimal= 1.0;
 declare @p2x decimal= 0;
 declare @p2y decimal= 2,0;
 declare @p3x decimal = 2.0;
 declare @p3y decimal = 2.0;
 declare   @a decimal;
 declare @b decimal;
 declare @c decimal;
 SELECT @a=(@p3y * @p2x  - @p1y * @p2x - @p3y * @p1x  + @p1y * @p1x - @p2y * @p3x + @p1y * @p3x + @p2y * @p1x  - @p1y * @p1x)/(- @p2x*@p2x * @p3x + @p1x*@p1x * @p3x + @p2x*@p2x * @p1x - @p1x*@p1x * @p1x + @p3x*@p3x * @p2x - @p1x*@p1x * @p2x - @p3x*@p3x * @p1x + @p1x*@p1x * @p1x);


  SELECT @b = ((@p2y - @a * @p2x*@p2x - @p1y + @a * @p1x*@p1x)/(@p2x - @p1x));

  SELECT @c = @p1y - @a * @p1x*@p1x - @b * @p1x

  Select @a a, @b b, @c c


/* this is how I derived the equations starting with ax^2+bx+c=y
  a * @p1x^2 + b * @p1x + c = @p1y    
  a * @p2x^2 + b * @p2x + c = @p2y
  a * @p3x^2 + b * @p3x + c = @p3y

  c = @p1y - a * @p1x^2 - b * @p1x    
  c = @p2y - a * @p2x^2 - b * @p2x
  c = @p3y - a * @p3x^2 - b * @p3x

  @p1y - a * @p1x^2 - b * @p1x = @p2y - a * @p2x^2 - b * @p2x     
  @p1y - a * @p1x^2 - b * @p1x = @p3y - a * @p3x^2 - b * @p3x    

  b * @p2x - b * @p1x = @p2y - a * @p2x^2 - @p1y + a * @p1x^2   
  b * @p3x - b * @p1x = @p3y - a * @p3x^2 - @p1y + a * @p1x^2

  b * (@p2x - @p1x) = @p2y - a * @p2x^2 - @p1y + a * @p1x^2
  b * (@p3x - @p1x) = @p3y - a * @p3x^2 - @p1y + a * @p1x^2

  b  = ((@p2y - a * @p2x^2 - @p1y + a * @p1x^2)/(@p2x - @p1x))
  b  = ((@p3y - a * @p3x^2 - @p1y + a * @p1x^2)/(@p3x - @p1x))

  ((@p2y - a * @p2x^2 - @p1y + a * @p1x^2)/(@p2x - @p1x)) = ((@p3y - a * @p3x^2 - @p1y + a * @p1x^2)/(@p3x - @p1x))

  (@p2y - a * @p2x^2 - @p1y + a * @p1x^2) * (@p3x - @p1x) = (@p3y - a * @p3x^2 - @p1y + a * @p1x^2) * (@p2x - @p1x)

  (@p2y * @p3x - a * @p2x^2 * @p3x - @p1y * @p3x + a * @p1x^2 * @p3x)  - @p2y * @p1x + a * @p2x^2 * @p1x + @p1y * @p1x - a * @p1x^2 * @p1x = 
  (@p3y * @p2x - a * @p3x^2 * @p2x - @p1y * @p2x + a * @p1x^2 * @p2x)  - @p3y * @p1x + a * @p3x^2 * @p1x + @p1y * @p1x - a * @p1x^2 * @p1x

  - a * @p2x^2 * @p3x + a * @p1x^2 * @p3x + a * @p2x^2 * @p1x - a * @p1x^2 * @p1x + a * @p3x^2 * @p2x - a * @p1x^2 * @p2x - a * @p3x^2 * @p1x + a * @p1x^2 * @p1x  = 
  @p3y * @p2x  - @p1y * @p2x - @p3y * @p1x  + @p1y * @p1x - @p2y * @p3x + @p1y * @p3x + @p2y * @p1x  - @p1y * @p1x

  a(- @p2x^2 * @p3x + @p1x^2 * @p3x + @p2x^2 * @p1x - @p1x^2 * @p1 + @p3x^2 * @p2x - @p1x^2 * @p2x - @p3x^2 * @p1x + @p1x^2 * @p1x)  = 
  @p3y * @p2x  - @p1y * @p2x - @p3y * @p1x  + @p1y * @p1x - @p2y * @p3x + @p1y * @p3x + @p2y * @p1x  - @p1y * @p1x

  SELECT @a=(@p3y * @p2x  - @p1y * @p2x - @p3y * @p1x  + @p1y * @p1x - @p2y * @p3x + @p1y * @p3x + @p2y * @p1x  - @p1y * @p1x)/(- @p2x*@p2x * @p3x + @p1x*@p1x * @p3x + @p2x*@p2x * @p1x - @p1x*@p1x * @p1 + @p3x*@p3x * @p2x - @p1x*@p1x * @p2x - @p3x*@p3x * @p1x + @p1x*@p1x * @p1x)

*/

Open in new window


There was a typo and it is fixed now. I have tested the solution, it works fine.

You can use the same equations in any other languages like c#, vba, etc.
0
 
ozoCommented:
No, you would have to define your own function
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
vbNewbie2009,

I want make sure you can see how easy this solution is to apply. Here is vba version of it if you are not comfortable with TSQL

Test: we have three points of polynomial
X^2 – 2x + 2 = y
P1(1, 1), P2(0,2), P3(2, 2)
Now, plugging these six values into the solution:

 
Sub PolyTest()

Dim p1x As Single: p1x = 1
 Dim p1y As Single: p1y = 1
 Dim p2x As Single: p2x = 0
 Dim p2y As Single: p2y = 2
 Dim p3x As Single: p3x = 2
 Dim p3y As Single: p3y = 2
 Dim a As Single
 Dim b As Single
 Dim c As Single
 
 a = (p3y * p2x - p1y * p2x - p3y * p1x + p1y * p1x - p2y * p3x + p1y * p3x + p2y * p1x - p1y * p1x) / (-p2x * p2x * p3x + p1x * p1x * p3x + p2x * p2x * p1x - p1x * p1x * p1x + p3x * p3x * p2x - p1x * p1x * p2x - p3x * p3x * p1x + p1x * p1x * p1x)
b = ((p2y - a * p2x * p2x - p1y + a * p1x * p1x) / (p2x - p1x))
c = p1y - a * p1x * p1x - b * p1x
MsgBox "a: " & a & ", b: " & b & ", c: " & c


End Sub

Open in new window


You get a: 1, b: -2, and c: 2

Also, depending how it is going to be used, you can put the tsql from my first post in a stored procedure.

Mike
0
 
vbNewbie2009Author Commented:
This base formula worked great; thank you!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.