Friday 8 October 2010

Compute the area of...

Riddle me this
Somewhere in the world there must be an exam or test that asks the question to compute the area of a given shape using PL/SQL. I know this because occasionally I look at the Google Analytics for my blog and see some pretty crazy google searches that arrive at my page because of my chosen blog name.

In just a few hours I take off for Hong Kong to take the kids to Disneyland and visit the city for a few days. I'm happy, so (while I don't condone the use of the Internet to solve all your problems) I thought I'd make a few other people happy, and make the visit to my site worthwhile :-)

I've used formulas according to Maths is Fun, and also demonstrated a few other SQL rounding functions you can find well documented here. That's right kids, documentation is your friend.

  lc_pi constant number := 3.141592;
  -- triangle
  ln_t_base    number default 2;
  ln_t_height  number default 4;
  -- square
  ln_s_length  number default 5;
  -- circle
  ln_c_radius  number default 200;
  -- ellipse
  ln_e_width   number default 3;
  ln_e_height  number default 2;
  -- trapezoid / trapezium
  ln_z_a       number default 2;
  ln_z_b       number default 5;
  ln_z_height  number default 3;
  -- sector
  ln_r_radius  number default 4;
  ln_r_degrees number default 45;
  ln_area  number;

  -- triangle
  ln_area := 0.5 * ln_t_base * ln_t_height;
  dbms_output.put_line('Triangle: '||ln_area);

  -- square
  ln_area := POWER(ln_s_length, 2);
  dbms_output.put_line('Square: '||ln_area);

  -- circle
  ln_area := lc_pi * POWER(ln_c_radius, 2);
  dbms_output.put_line('Circle: '||ROUND(ln_area, -2));

  -- ellipse
  ln_area := lc_pi * ln_e_width * ln_e_height;
  dbms_output.put_line('Ellipse: '||FLOOR(ln_area));

  -- trapezium
  ln_area := 0.5 * (ln_z_a + ln_z_b) * ln_z_height;
  dbms_output.put_line('Trapezoid: '||CEIL(ln_area));
  -- sector
  ln_area := 1/2 * ln_r_radius**2 * ln_r_degrees / 180 / lc_pi;
  dbms_output.put_line('Sector: '||ROUND(ln_area, 5));

end simple_calcs;

Triangle: 4
Square: 25
Circle: 125700
Ellipse: 18
Trapezoid: 11
Sector: 6.28319

PL/SQL procedure successfully completed.

See you on the other side of Hong Kong!


Anonymous said...

I must say, the need for these sorts of calculations have never come up in any project I've been involved in.


I'd increase the accuracy of that PI constant. I'm pretty sure that errors would bubble up quite quickly with only six digits of precision. Just saying :)

Tony Andrews said...

Rather than the mysterious 57.2957795 I'd prefer to have seen something like (180 / lc_pi)

Scott Wesley said...

Tony - done

Jeff - I was about to go on holidays and used pi to the precision my memory recollects ;-)

I was thinking about the usability of these sorts of calculations and thought - well, I never thought I'd need to apply calculations that calculated various forms of decimal degrees & latitude/longitude, but I have. I guess you just have to be on the right project.
Luckily, I was about to find the valid calculations on a website that posted it in another language, and I was able to translate.