Oracle Spatial is a powerful set of Oracle

objects for storing, manipulating, and querying graphical shapes.

There are many transformation functions to find complicated results

such as the intersection or union of two graphical shapes. There

are also functions that return the area and perimeter length of a

shape. Unfortunately, there aren’t many functions to manipulate the

shapes themselves.

Functions like “move a shape to a different

location” or “rotate a shape around a point” weren’t included in

the set of packages. It seems as if Oracle Spatial only allowed for

shape creation and deletion from database tables.

Graphic functions, like the ones I just

mentioned, aren’t that difficult to write in application code as

needed, but looking up algorithms and getting the equation right

can be tricky without thorough testing.

If you took a computer science graphics course

and remember what may have been one of the few times you were

actually required to use linear algebra and matrices, you should

remember that just about every graphic operation performed by

top-end graphic programs can be reduced to simple matrix

operations. What I’d like to demonstrate is a simple matrix object

that supports multiplication, and several graphic operations that

you can derive from the matrix.

Here is the matrix class, written as an Oracle

object. It stores the dimensions of the matrix, in standard

row/column order, followed by a VARRAY containing a linear list of

values. Besides the default constructor, I’ve added a constructor

to quickly create a matrix with all zero values and an “identity”

function to create an identity matrix. I’ve added methods to access

values by row/column pair, and to support matrix addition,

subtraction, and multiplication.

create type matrix_body_t as varray(100) of

number;

/

show errors;

create type matrix_t as object

(

m_rows integer,

m_cols integer,

m_body matrix_body_t,

constructor function matrix_t

(

p_rows

integer,

p_cols

integer

) return self as result,

static function identity(n integer) return

matrix_t,

member function equals(other matrix_t)

return boolean,

member function get_at(p_row integer,

p_col integer) return number,

member procedure set_at(p_row integer,

p_col integer, p_value number),

member function plus(other matrix_t)

return matrix_t,

member function subtract(other matrix_t)

return matrix_t,

member function multiply(other matrix_t)

return matrix_t

);

/

show errors;

create type body matrix_t

as

constructor function matrix_t

(

p_rows

integer,

p_cols

integer

) return self as result

is

l_count

pls_integer := p_rows * p_cols;

begin

m_rows :=

p_rows;

m_cols :=

p_cols;

m_body :=

matrix_body_t();

m_body.extend(l_count);

for i in

1..l_count loop

m_body(i)

:= 0;

end loop;

return;

end matrix_t;

—

static function identity(n integer) return

matrix_t

is

result

matrix_t;

begin

if n < 0 or n

> 10 then

raise_application_error(-20000,’argument out of range’);

end if;

result :=

matrix_t(n,n);

for i in 0 .. n-1

loop

result.m_body((i*n)

+ i + 1) := 1;

end loop;

return

result;

end identity;

—

member function equals(other matrix_t)

return Boolean

is

begin

if m_rows !=

other.m_rows or m_cols != other.m_cols then

return

false;

end if;

for i in 1 ..

m_body.count loop

if

m_body(i) != other.m_body(i) then

return

false;

end

if;

end loop;

return true;

end equals;

—

member function get_at(p_row integer,

p_col integer) return number

is

begin

if p_row <= 0

or p_row > m_rows then

raise_application_error(-20000,’row argument out of range’);

end if;

if p_col <= 0

or p_col > m_cols then

raise_application_error(-20000,’col argument out of range’);

end if;

return

m_body(((p_row-1) * m_cols) + p_col);

end get_at;

—

member procedure set_at(p_row integer,

p_col integer, p_value number)

is

begin

if p_row <= 0

or p_row >= m_rows then

raise_application_error(-20000,’row argument out of range’);

end if;

if p_col <= 0

or p_col > m_cols then

raise_application_error(-20000,’col argument out of range’);

end if;

m_body(((p_row-1)

* m_cols) + p_col) := p_value;

end set_at;

—

member function plus(other matrix_t)

return matrix_t

is

result

matrix_t; — result of addition

begin

if m_rows !=

other.m_rows or m_cols != other.m_cols then

raise_application_error(-20000,’addition not possible’);

end if;

result :=

matrix_t(m_rows,m_cols);

for i in 1 ..

m_body.count loop

result.m_body(i)

:= m_body(i) + other.m_body(i);

end loop;

return

result;

end plus;

—

member function subtract(other matrix_t)

return matrix_t

is

result

matrix_t; — result of subtraction

begin

if m_rows !=

other.m_rows or m_cols != other.m_cols then

raise_application_error(-20000,’subtraction not possible’);

end if;

result :=

matrix_t(m_rows,m_cols);

for i in 1 ..

m_body.count loop

result.m_body(i)

:= m_body(i) – other.m_body(i);

end loop;

return

result;

end subtract;

—

member function multiply(other matrix_t)

return matrix_t

is

result

matrix_t; — result of multiply

c pls_integer; —

current column

r pls_integer; —

current row offset

begin

if m_cols !=

other.m_rows then

raise_application_error(-20000,’multiplication not

possible’);

end if;

— initialize

result matrix

result :=

matrix_t(m_rows,other.m_cols);

— multiply

for i in

0..result.m_body.count-1 loop

c

:= mod(i,result.m_cols);

r

:= (i – c) / result.m_cols;

for

j in 0 .. m_cols-1 loop

result.m_body(i+1)

:= result.m_body(i+1)

+

(m_body((r*m_cols)+j+1)

*

other.m_body((j*other.m_cols)+c+1));

end

loop;

end loop;

return

result;

end multiply;

end;

/

show errors;

With this class loaded, you can perform matrix

operations from within SQL statements:

REM — return 3×3 identity matrix

SQL> select matrix_t.identity(3) from dual;

MATRIX_T.IDENTITY(3)(M_ROWS, M_COLS, M_BODY)

——————————————————–

MATRIX_T(3, 3, MATRIX_BODY_T(1, 0, 0, 0, 1, 0, 0, 0, 1))

REM — multiply two matrices

REM

REM —

[2,-1,0] [0,1,4,-1] [2,2,8,-4]

REM — [3, 1,0] x [-2,0,0,2] = [-6,0,0,6]

REM

— [0,1,4,-1]

SQL> select

matrix_t(3,2,matrix_body_t(2,-1,0,3,1,0)).multiply(

matrix_t(2,4,matrix_body_t(0,1,4,-1,-2,0,0,2)))

result from dual;

RESULT(M_ROWS, M_COLS, M_BODY)

——————————————————————–

MATRIX_T(3, 4, MATRIX_BODY_T(2, 2, 8, -4, -6, 0, 0, 6, 0, 1, 4,

-1))

Now **Listing A** contains a package that does various graphic

operations on a matrix. The operation “translate” moves a point

from one location to another. “Scale” shifts points so the shape

will expand or shrink around the origin. “Shear” stretches points

the further they are from the origin, and “rotate” will rotate a

shape around the origin by an angle in radians. (I’ve included a

function to convert degrees to radians.)

For operations that need to be made around a

point other than the origin, simply translate the shape by the

offset to the point, perform an operation, and translate it back.

**Listing B** provides some examples.

You now have plenty of reusable functions that

you can use on MDSYS.SDO_GEOMETRY objects (i.e., geometric shape

objects in Oracle Spatial) to transform your shapes without having

to delete and re-upload them.

I hope that future versions of the Oracle

Spatial package will include these kinds of operations so

applications can manipulate geometries without having to write lots

of application code to support these kinds of operations. Matrix

operations, in general, have a wide range of operations.

*TechRepublic’s Oracle newsletter covers automating Oracle utilities, generating database alerts, solving directed graph problems, and more. Automatically subscribe today!*