1 / 39

EXCEL in Engineering Parametric modeling of aircraft geometry

EXCEL in Engineering Parametric modeling of aircraft geometry. J. Philip Barnes . 01 November 2013 . Notes / Options: Slideshow animated (F5) Also view ~ “notes page”. Presentation Contents. Getting started: EXCEL as a scientific spreadsheet

dunne
Download Presentation

EXCEL in Engineering Parametric modeling of aircraft geometry

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. EXCEL in Engineering Parametric modeling of aircraft geometry J. Philip Barnes 01 November 2013 Notes / Options: Slideshow animated (F5) Also view ~ “notes page”

  2. Presentation Contents • Getting started: EXCEL as a scientific spreadsheet • Misc., powerful Cartesian or parametric functions • Alternative iterative solution for x=f(x) • Linearize data before curve fit ~ “Pseudo-Gaussian” • Why go parametric? • High-order polynomial ~ theory & numerical method • De Casteljau-Bézier-Bernstein ~ history, review & renew • Parametric cubic spline~ theory & airfoil application • 3D visualization ~ concatenate maneuver rotations • Sneak preview: Rendering within EXCEL • Summary

  3. Getting started: EXCEL as a scientific spreadsheet • Purpose (typical): • Read input and/or data from spreadsheet • Edit & run algorithm; generate new data • Write to spreadsheet cells & plot results • Copy all data & plots as new sheet; re-run • One-time setup: • EXCEL Options ~ Formulas ~ R1C1 ... • Trust Ctr. ~ settings ~ macro ~ enable & trust • Toolbar ~ more... ~ all ... ~ Visual Basic ~ Add • Set VB editor window to float on spreadsheet • Typical operations: • Type in the column headers, i.e. t, x, y, z • VB ~ insert ~ module ~ Type: sub example • Enter or edit code ~ save file as *.xlsm • Click “run” (note: module remains part of file) • Highlight applicable columns & plot the results • New case: Copy sheet, revise inputs, repeat 4)

  4. Presentation Contents • Getting started: EXCEL as a scientific spreadsheet • Misc., powerful Cartesian or parametric functions • Alternative iterative solution for x=f(x) • Linearize data before curve fit ~ “Pseudo-Gaussian” • Why parametric? • High-order polynomial ~ theory & numerical method • De Casteljau-Bézier-Bernstein ~ history, review & renew • Parametric cubic spline~ theory & airfoil application • 3D visualization ~ concatenate maneuver rotations • Sneak preview: Rendering within EXCEL • Summary

  5. Miscellaneous Geometry & curve-fit tools ~ Cartesian or parametric 1.0 1.0 Pseudo-Cosine 0.8 0.8 0.6 0.6 e = sin(hm p) 0.4 0.4 Pseudo-Sine 0.2 0.2 h 0.0 0.0 0.0 0.2 0.4 0.6 0.8 1.0 0.0 0.2 0.4 0.6 0.8 1.0 1.0 1.0 Exponential Varabola 0.8 0.8 e =e-5hm e = hm 0.6 0.6 0.4 0.4 0.2 0.2 h 0.0 0.0 0.0 0.2 0.4 0.6 0.8 1.0 0.0 0.2 0.4 0.6 0.8 1.0 0.5 m=1.0 2.0 m = 2.0 1.0 0.5 e y / ymax = cos m (hp/2) h x / xmax 0.5 m=1.0 2.0 EXCEL Exercise: Generate & plot 0.5 m=1.0 2.0

  6. “Algebratross” math-modeled aircraft • Modeled entirely with Cartesian functions of earlier slide • Iterative solution for wing-body intersection (next topic)

  7. Presentation Contents • Getting started: EXCEL as a scientific spreadsheet • Misc., powerful Cartesian or parametric functions • Alternative iterative solution for x=f(x) • Linearize data before curve fit ~ “Pseudo-Gaussian” • Why parametric? • High-order polynomial ~ theory & numerical method • De Casteljau-Bézier-Bernstein ~ history, review & renew • Parametric cubic spline~ theory & airfoil application • 3D visualization ~ concatenate maneuver rotations • Sneak preview: Rendering within EXCEL • Summary

  8. Iterative solution for x = f(x) ~ stabilized and accelerated 0 < dy/dx < 1 dy/dx < 0 dy/dx > 1 y y y f(x) ' ALGORITHM, accel-stabilized iteration, x=f(x) ' guess = ... ' 1st guess ' i = 0 ' initialize iteration counter ' ,-> i = i + 1 ' increment iteration counter ' | if i = 1: x2 = guess ' 1st guess ' | if i = 2: x2 = 1.02 * guess ' 2nd guess ' | if i >= 3: ' | if dydx < 0: x2=(x1+y1)/2 ' half step ' | if 0<=dydx<=1: x2=2*y1 - x1 ' double step ' | if dydx > 1: x2=2*x1 - y1 ' reverse step ' | get y2 at x2 ' | test for exit criteria --> exit ' | if i >= 2 & x1#x2: dydx = (y2-y1)/(x2-x1) ' `- x1 = x2: y1 = y2 ' setup for next pass f(x) y=x • Basic: Guess x1 ; get y1 ; then set x2 = y1 • Refined: watch dy/dx ; modify "step" to (x2) • - Step mod aids convergence & stabilizes • Newton-Raphson may be faster or slower • Probability of convergence appears similar y=x y=x f(x) y1 y1 y1 x x x y1 y1 x2 x2 x1 y1 x2 x1 x1 Half step to accelerate: x2 = x1 - ½(x1-y1) = ½(x1+y1) Double step to accelerate: x2 = x1 - 2(x1-y1) = 2y1 - x1 Reverse step to stabilize: x2 = x1 +(x1-y1) =2x1-y1

  9. Iterative solution for x = f(x) ~ example with six steps

  10. Presentation Contents • Getting started: EXCEL as a scientific spreadsheet • Misc., powerful Cartesian or parametric functions • Alternative iterative solution for x=f(x) • Linearize data before curve fit ~ “Pseudo-Gaussian” • Why parametric? • High-order polynomial ~ theory & numerical method • De Casteljau-Bézier-Bernstein ~ history, review & renew • Parametric cubic spline~ theory & airfoil application • 3D visualization ~ concatenate maneuver rotations • Sneak preview: Rendering within EXCEL • Summary

  11. “Pseudo-Gaussian” ~ First try to linearize ; then fit a polynomial y ≈ exp(-axb) Take ln twice: ln (-ln y) = ln a + b ln x y = a + b c polynomial? Apply (given x, get y): (1) cln x (2) y = f (c) ~ line or polynomial (3) y = exp (-exp y) • Physics are preserved • Meaningful extrapolation “Real life” data EXCEL Exercise: Fit polynom. y(c) Add 2 new columns Re-generate y(x)

  12. Presentation Contents • Getting started: EXCEL as a scientific spreadsheet • Misc., powerful Cartesian or parametric functions • Alternative iterative solution for x=f(x) • Linearize data before curve fit ~ “Pseudo-Gaussian” • Why parametric? • High-order polynomial ~ theory & numerical method • De Casteljau-Bézier-Bernstein ~ history, review & renew • Parametric cubic spline~ theory & airfoil application • 3D visualization ~ concatenate maneuver rotations • Sneak preview: Rendering within EXCEL • Summary

  13. Why go parametric? y EXCEL Exercise: Generate data (approx. this shape) t (0→1) ; tab & polynom. fit x(t), y(t) Plot x(t), y(t), y(x) t x • “Time” marches fwd; never encounter infinite rate • Let coordinates be parametric in “time” as x(t) & y(t) • Or, parametric with (q), as a circle x=rcosq ; y=rsinq • No problem with “wrap around” or curve crossing • Extend to 3 or more dimensions when applicable • Let all coordinates (x,y,z,w...) be parametric in (t) • Simply set (x = t) if (y, z, w) depend only on (x)

  14. Presentation Contents • Getting started: EXCEL as a scientific spreadsheet • Misc., powerful Cartesian or parametric functions • Alternative iterative solution for x=f(x) • Linearize data before curve fit ~ “Pseudo-Gaussian” • Why parametric? • High-order polynomial ~ theory & numerical method • De Casteljau-Bézier-Bernstein ~ history, review & renew • Parametric cubic spline~ theory & airfoil application • 3D visualization ~ concatenate maneuver rotations • Sneak preview: Rendering within EXCEL • Summary

  15. Polynomial ~ Parametric u(t) or Cartesian y(x) • Objective: Fit nth-order polynomial through (0_to_n) points • EXCEL is limited to (n=6) ; higher order is included herein • Use “0_to_n” VB arrays; shift the origin to the “0th point” • Then for coordinate u(t) with polynomial coefficients (c): • u-uo = c1(t-to) + c2(t-to)2 + ... ci(t-to)i ...+ cn(t-to)n • Shorthand: u = c1t + c2t2 + ... cit i ... + cntn • Matrix notation with polynomial applied at points 1 to n: u u ≡ u-uo n n 2 1 2 0 1 t t ≡ t-to • Solve linear system for coefficients (c); apply for, say, n=10:

  16. Solution for 10th-order polynomial ~ Demo EXCEL Exercise: Experiment first by shifting one or more points, and second by increasing the order of the polynomial

  17. Solution for 10th-order polynomial ~ Demo, right-to-left

  18. Presentation Contents • Getting started: EXCEL as a scientific spreadsheet • Misc., powerful Cartesian or parametric functions • Alternative iterative solution for x=f(x) • Linearize data before curve fit ~ “Pseudo-Gaussian” • Why parametric? • High-order polynomial ~ theory & numerical method • De Casteljau-Bézier-Bernstein ~ history, review & renew • Parametric cubic spline~ theory & airfoil application • 3D visualization ~ concatenate maneuver rotations • Sneak preview: Rendering within EXCEL • Summary

  19. De Casteljau-Bézier Curve ~ History,* Review & Renew • P. De Casteljaufirst conceived (1963) today’s “Bézier Curve” • His notes & algorithm remained proprietary at Citroën • Set (Np) Control points for control polygon near, not on, curve • (Np-1, Np-2,...) interpolation series on polygon-sides for x(t), y(t) • De Casteljau’s Algorithm is represented by the Bernstein Basis • as discovered by A.R. Forrest ~ Geometric construct → math • S. Bernstein’s polynomial (B) derivative (dB/dt) introduced herein • P. Bézier independently conceived (1966) the control polygon • Bézier’s algorithm is not used in today’s “Bezier Curve,” but • his work at Rénault was the foundation of UNISURF & CATIA p2 1/3 y p3 1/3 x,y 1/3 p1 pNp t = 1/3 x * R.T. Farouki, The Bernstein polynomial basis: a centennial retrospective, UC Davis, Mar 2012 * G. Farin, A History of Curves and Surfaces in CAGD, Arizona State University, 2007

  20. De Casteljau Algorithm in action ~ close-up y p2 1/3 p3 t = 1/3 1/3 x,y 1/3 pNp p1 x Animated graphic (shift F5)

  21. De Casteljau-Bézier-Bernstein (CBB) System in 2D ~ Demo x = S Bi Xpi p3 p4 N=Vxk V y = S Bi Ypi t p5 p2 p6 p1 V = (dx/dt) i + (dy/dt) j dx/dt = S (dB/dt)i Xpi dy/dt = S (dB/dt)i Ypi

  22. Airfoil geometry modeling ~ “manual” C-B-B polygon pNp p1 p2 p3

  23. Inverse solution for the De Casteljau-Bezier control polygon • Thus far we have “manually” set (Np) control-polygon points (P) • The C-B curve of (Nt) points approximates the desired trajectory • Solve for (P) so the C-B curve passes through(N) points (C) • Write C-B-B system with a square Bernstein Matrix (Np = Nt = N) • Approach: invert the Bernstein and Bernstein Rate Matrices • [C]=[B][P] ; [P]=[B]-1[C] ; [dC/dt]=[dB/dt][P] ; [P]=[dB/dt]-1 [dC/dt] For matrix inversion compact method & code, see: J.T. Golden, FORTRAN iV Programming and Computing, Prentice-Hall, 1965, p.114 p2 y p3 “time” t [B] usually not square c [B] & [B]-1 made square p1 cN pN c1 x

  24. De Casteljau-Bezier-Bernstein Inverse Solution ~ First attempts 5-vertex circle 12-vertex airfoil pN-1 p2 pN p1 • Computed polygon (red) • BCC curve (black) • Specified points (white) Intent (via manual polygon) Result (via computed polygon) p3 c Polygon not shown t In each case, the computed polygon faithfully drove the curve through the specified points, but with unexpected side effects

  25. Presentation Contents • Getting started: EXCEL as a scientific spreadsheet • Misc., powerful Cartesian or parametric functions • Alternative iterative solution for x=f(x) • Linearize data before curve fit ~ “Pseudo-Gaussian” • Why parametric? • High-order polynomial ~ theory & numerical method • De Casteljau-Bézier-Bernstein ~ history, review & renew • Parametric cubic spline~ theory & airfoil application • 3D visualization ~ concatenate maneuver rotations • Sneak preview: Rendering within EXCEL • Summary

  26. Cubic spline ~ Parametric u(t) or Cartesian y(x) • Get smooth curve passing through (1_to_n) points • VB array dim. (n) elements: 0_to_n ~ ignore 0th elem. • 1st & 2nd derivative Continuity (3rd is not continuous) • Independently control L/R-end slope or 2nd derivative • Internal-node continuity yields tri-diagonal system • End constraints are applied in first and last rows • Parametric x(t) ; v  “velocity”; a  “acceleration” x n 3 2 1 t • Set ends; Solve linear EQs. for internal-knot accelerations (a)

  27. Parametric cubic spline ~ Various end constraints “Stiff” ends “Flexible” ends “Flat” ends

  28. Parametric cubic spline ~ Existing airfoil “fast & close match” L.E. radius T.E. slopes t “velocity” for tangent and normal vectors Fast, smooth & efficient design/characterization; 3 upper, 4 lower points; Add more points if req’d “velocity” for tangent and normal vectors

  29. Laminar airfoil study ~ integrated geometric/aero design f Theodorsen Angle (f) • Velocity ratio Parametric cubic spline • Pressure coefficient Discontinuous 3rd-deriv. of cubic spline does not disrupt smooth airflow

  30. Radius of curvature ~ Two coordinates parametric with “time” (t) y dy/dt v r dx/dt n x • Velocities & accelerations determine parametric curvature radius • Velocity is the tangent vector ; cross product yields normal vector • Application: Parametric-modeled airfoil leading-edge radius

  31. Presentation Contents • Getting started: EXCEL as a scientific spreadsheet • Misc., powerful Cartesian or parametric functions • Alternative iterative solution for x=f(x) • Linearize data before curve fit ~ “Pseudo-Gaussian” • Why parametric? • High-order polynomial ~ theory & numerical method • De Casteljau-Bézier-Bernstein ~ history, review & renew • Parametric cubic spline~ theory & airfoil application • 3D visualization ~ concatenate maneuver rotations • Sneak preview: Rendering within EXCEL • Summary

  32. “Trigonosoar” ~ 3D parametric model of aircraft geometry Chord, thickness, twist, etc. are parametric with spar coord. (p) from the centerline to winglet tip p ∫ds ≈S [(Dy)2 + (Dz)2 ] 0.5 z x y Aero axes

  33. z z y x y c Aero axes Rotation Matrix Concatenation ~ Review and Renew • Define geometry only once • Apply maneuver transform Optionally include 90o rotation to translate from structural axes to aero axes

  34. “Regenosoar” ~ math-modeled & visualized with tools herein

  35. Presentation Contents • Getting started: EXCEL as a scientific spreadsheet • Misc., powerful Cartesian or parametric functions • Alternative iterative solution for x=f(x) • Linearize data before curve fit ~ “Pseudo-Gaussian” • Why parametric? • High-order polynomial ~ theory & numerical method • De Casteljau-Bézier-Bernstein ~ history, review & renew • Parametric cubic spline~ theory & airfoil application • 3D visualization ~ concatenate maneuver rotations • Sneak preview: Rendering within EXCEL • Summary

  36. Rendering in EXCEL ~ using the "shapes" feature EXCEL Exercise: "Render" a circle with 5-pixel line thickness and red, green, & blue parametric with theta 1. Anchor optional background graphic top left at cell(1,1) 2. Generate object geometry (include normals & colors) 3. Render the object, pixel-by-pixel (via EXCEL "shapes")

  37. Presentation Contents • Getting started: EXCEL as a scientific spreadsheet • Misc., powerful Cartesian or parametric functions • Alternative iterative solution for x=f(x) • Linearize data before curve fit ~ “Pseudo-Gaussian” • Why parametric? • High-order polynomial ~ theory & numerical method • De Casteljau-Bézier-Bernstein ~ history, review & renew • Parametric cubic spline~ theory & airfoil application • 3D visualization ~ concatenate maneuver rotations • Sneak preview: Rendering within EXCEL • Summary

  38. Summary ~ EXCEL in Engineering / Geometry modeling y • EXCEL & VB provide capable scientific spreadsheet • Trigonometric functions ~ versatile & powerful • Suggestion: linearize data before fitting a curve • Iteration, x=f(x) ~ Alternative to Newton-Raphson • Parametric: multi-dimensions, wrap-around OK • De Casteljau-Bézier: labor-intense pass thru all pts. • Inverse Bézier runs thru all points ~ but with wild end effects • Polynomial passes thru all pts (no control at ends) • Polynomial advantages include simplicity & continuity • High-order polynomial can be quite robust • The cubic spline also passes through all points • With comprehensive control of start & end constraints • One application: efficiently design & characterize an airfoil • 3rd derivative not continuous, but geom. is “aero smooth” • EXCEL’s “shape” feature for graphics & rendering t x

  39. About the Author Phil Barnes has a Bachelor’s Degree in Mechanical Engineering from the University of Arizona and a Master’s Degree in Aerospace Engineering from Cal Poly Pomona. He has 32-years of experience in performance analysis and computer modeling of aerospace vehicles, engines, and subsystems, primarily at Northrop Grumman. He has authored SAE technical papers on aerodynamics, dynamic soaring, and regenerative soaring. This latest presentation brings together Phil’s knowledge and passions for computer graphics, and geometry math modeling.

More Related