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!