Enterprise Software

Use matrix operations to transform Spatial geometries

Oracle Spatial is a powerful set of Oracle objects for storing, manipulating, and querying graphical shapes. Scott Stephens demonstrates a simple matrix object that supports multiplication, as well as several graphic operations that you can derive from the matrix.

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 3x3 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!

Editor's Picks