Functions and Procedures.

per database

When defining:
delimiter //             #change client's end-of-line to // so that embedded ;'s are part of definition
...put the create... here
//
delimiter ;              #change it back to ;



Show Create Function|Procedure asdf;
Drop Function|Procedure asdf;


# Distance between 2 Points:
 create function distance (p1 point,p2 point)
     returns double
     return sqrt(pow(x(p1)-x(p2),2)+pow(y(p1)-y(p2),2));

# Extract the X coord of first point of polygon:
 create function x1 (r polygon)
     returns int
     begin
       declare polystr varchar(100);
       set polystr = astext(r);
       return substr(polystr,10,locate(' ',polystr)-10);
     end;

 create function y1 (r polygon)
     returns int
     begin
       declare polystr varchar(100);
       set polystr = astext(r);
       return substr(polystr, locate(' ',polystr)+1, locate(',',polystr)-locate(' ',polystr)-1);
     end;

# Width of a orthoganal rectangle:
 create function width (r polygon)
     returns double
     return 2 * abs((x(centroid(r)) - x1(r)));

# Height of a orthoganal rectangle:
 create function height (r polygon)
     returns double
     return 2 * abs((y(centroid(r)) - y1(r)));

# Perimeter of a orthoganal rectangle:
 create function perimeter (r polygon)
     returns double
     return 2*(width(r)+height(r));

# Correlation between X and Y of points tables pt Point:
 create function correl ()
     returns double
     return (select (count(*)*sum(x(pt)*y(pt)) - sum(x(pt))*sum(y(pt)))/
             (sqrt(count(*)*sum(pow(x(pt),2))-pow(sum(x(pt)),2)) * sqrt(count(*)*sum(pow(y(pt),2))-pow(sum(y(pt)),2)))
          from points);


# *Correlation between X and Y columns of pre-existing temporary table XYtempTable:
#                        create temporary table XYtempTable select myXcol as x, myYcol as y from myTable;
 create function correl ()
     returns double
     return (select (count(*)*sum(x*y) - sum(x)*sum(y)) /
                    (sqrt(count(*)*sum(pow(x,2))-pow(sum(x),2))*
                     sqrt(count(*)*sum(pow(y,2))-pow(sum(y),2)))
             from XYtempTable);