Use matrix operations to transform Spatial geometries
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 here's 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.)
create or replace package
graphic
as
function point(p_x number,p_y
number) return matrix_t;
function deg2rad(p_deg
number) return number;
function
translate(p_shape matrix_t,p_x number,p_y
number)
return
matrix_t;
function scale(p_shape matrix_t,p_sx
number,p_sy number) return matrix_t;
function
shear(p_shape matrix_t,p_dx number,p_dy number) return
matrix_t;
function rotate(p_shape
matrix_t,p_angle number) return matrix_t;
end graphic;
/
show
errors;
create or replace package body
graphic
as
g_pi
number; -- value of PI
according to
Oracle
--
function
point(p_x number,p_y number) return
matrix_t
is
begin
return
matrix_t(3,1,matrix_body_t(p_x,p_y,1));
end
point;
--
function
deg2rad(p_deg number) return
number
is
begin
return
p_deg * g_pi / 180;
end
deg2rad;
--
function
translate(p_shape matrix_t,p_x number,p_y
number)
return
matrix_t
is
l_matrix
matrix_t :=
matrix_t.identity(3);
begin
l_matrix.set_at(1,3,p_x);
l_matrix.set_at(2,3,p_y);
return
l_matrix.multiply(p_shape);
end
translate;
--
function
scale(p_shape matrix_t,p_sx number,p_sy number) return
matrix_t
is
l_matrix
matrix_t :=
matrix_t.identity(3);
begin
l_matrix.set_at(1,1,p_sx);
l_matrix.set_at(2,2,p_sy);
return
l_matrix.multiply(p_shape);
end
scale;
--
function
shear(p_shape matrix_t,p_dx number,p_dy number) return
matrix_t
is
l_matrix
matrix_t :=
matrix_t.identity(3);
begin
l_matrix.set_at(1,2,p_dx);
l_matrix.set_at(2,1,p_dy);
return
l_matrix.multiply(p_shape);
end
shear;
--
function
rotate(p_shape matrix_t,p_angle number) return
matrix_t
is
l_matrix
matrix_t :=
matrix_t.identity(3);
begin
l_matrix.set_at(1,1,cos(p_angle));
l_matrix.set_at(1,2,-sin(p_angle));
l_matrix.set_at(2,1,sin(p_angle));
l_matrix.set_at(2,2,cos(p_angle));
return
l_matrix.multiply(p_shape);
end
rotate;
begin
g_pi := acos(0) * 2;
end
graphic;
/
show errors;
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. Here are some examples:
REM -- rotate point 1,1 around axis by 45 degrees
REM --
result should be x=0,y=sqrt(2)
REM -- we end up with a number that has
a slight round-off error, but
reasonably close to
0
SQL> select
graphic.rotate(graphic.point(1,1),graphic.deg2rad(45))
result
from dual;
RESULT(M_ROWS, M_COLS,
M_BODY)
-------------------------------------------------------------------------
-------
MATRIX_T(3,
1, MATRIX_BODY_T(-3.000E-39, 1.41421356, 1))
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.
Scott Stephens worked for Oracle for more than 13 years in technical support, e-commerce, marketing, and software development.