This article originally appeared in the Oracle e-newsletter. Click here to subscribe automatically.

Oracle Spatial is an integrated set of
functions, data structures, and tables that aid in storing and
retrieving spatial data. That data describes shapes and their
geometric relationships in two-dimensional, three-dimensional, or
four-dimensional space.

With Oracle Spatial, you would typically define
a set of shapes (such as points, lines, rectangles, polygons, or
circles); store and register the data in the Oracle Spatial data
dictionary; and then issue queries against the data to retrieve
information about area, intersections, close relations, and other
interpolated information.

Oracle Spatial data is primarily retrieved in
very hard-to-read stored objects, such as SDO_POINT and
SDO_ORDINATES. It would be useful to extract and manipulate the
data directly without needing additional software packages. SVG,
generated from the database, could handle this task quite easily as
long as an SVG viewer is installed in a browser.

In this article, I’ll show a PL/SQL package
that, when called via the modplsql interface, will generate enough
SVG to view the data used in the simple example from the Oracle Spatial User’s Guide,
chapter 2. (You can find HTML versions of the User’s Guides for each major
database version online at the Oracle Technology Network, where you
may have to sign in.) The example defines four shapes–a rectangle, two
polygons, and a circle–to represent geographical areas of
marketing interest for a soft drink manufacturer. To save space,
I’ll support only the three types of shapes that are in this
example. I won’t support unit translation (meters to pixels), but I
will support scrolling around the image and magnification. I also
assume all data is two-dimensional for now.

The biggest challenge in translating the data
to something SVG expects is the different ways of representing
shapes. For example, Oracle Spatial defines a “circle” by three
points along its circumference. This is useful to describe a
two-dimensional circle, which may be skewed in a particular
direction in three-dimensional or four-dimensional spaces. SVG
expects the coordinates of a center point and a radius. The math
required is lengthy but possible, as long as those three points
don’t lie in a relatively straight line. A minor challenge is the
fact that Oracle Spatial tends to describe data in terms of a
mathematical graph, with the Y axis in an upward direction, while
SVG maps the Y axis in a downward direction, so the entire graph
needs to be flipped vertically.

If you install Oracle Spatial, and run the
statements from chapter 2 of the Oracle Spatial User’s Guide,
you’ll be able to compile and run the following script from
modplsql. I found that “m=20” gave a nice magnification
level to fit in the diagram.

create or replace package sdosvg
is
    procedure circle_calc
    (
        x1 in number, y1
in number,
        x2 in number, y2
in number,
        x3 in number, y3
in number,
        cx out
number,
        cy out
number,
        r out number
    );
    procedure show
    (
        width
number:=640,height number:=480,   — size of
SVG
        x number:=0,y
number:=0,                —
origin
        m
number:=1                             —
magnification
    );
end sdosvg;
/
show errors;

create or replace package body sdosvg
is
    —
    — circle_calc, given three points on the
circumference of a circle,
    — calculate the center of the circle and
the radius
    —
    procedure circle_calc
    (
        x1 in number, y1
in number,
        x2 in number, y2
in number,
        x3 in number, y3
in number,
        cx out
number,
        cy out
number,
        r out number
    )
    is
    begin
        — trust me. it
works! (send me e-mail to see the math)
        cx := (((x2*x2) +
(y2*y2) – (x1*x1) – (y1*y1)) * (y3 – y1)
            –
((x3*x3) + (y3*y3) – (x1*x1) – (y1*y1)) * (y2 – y1))
            /
(2 * ((x2 – x1)*(y3 – y1) – (x3 – x1)*(y2 – y1)));
        cy := ((x3*x3) +
(y3*y3) – (x1*x1) – (y1*y1))
            /
(2 * (y3 – y1)) – cx * (x3 – x1) / (y3 – y1);
        r := sqrt((x1 –
cx)*(x1 – cx) + (y1 – cy)*(y1 – cy));
    end circle_calc;
    —
    — show “cola_market” demo spatial
database in SVG
    —
    procedure show
    (
        width
number:=640,height number:=480,   — size of
SVG
        x number:=0,y
number:=0,                —
origin
        m
number:=1                             —
magnification
    )
    is
        cnt
number;             —
number of shapes
        points
number;          —
number of points
        str
varchar2(2000);     — temp storage of
arguments
        offset
number;          —
sdo_starting_offset
        etype
number;           —
sdo_etype
        interpret
number;       —
sdo_interpretation
        px
number;              —
svg x coordinate
        py
number;              —
svg y coordinate
        ht
number;              —
object height
        wd
number;              —
object width
        cx
number;              —
center x coordinate
        cy
number;              —
center y coordinate
        r
number;               —
radius
    begin
       
owa_util.mime_header(‘image/svg+xml’);
        htp.p(‘<?xml
version=”1.0″?>’);
        htp.p(‘<svg
width=”‘||width||'” height=”‘||height||'”>’);
        htp.p(‘<rect
x=”0″ y=”0″ width=”‘||(width-1)
            ||'”
height=”‘||(height-1)
            ||'”
style=”fill:none; stroke:black;”/>’);
    for row in (select * from cola_markets c)
loop
            cnt
:= row.shape.sdo_elem_info.count / 3;
            for
i in 1..cnt loop
                offset
:= row.shape.sdo_elem_info((i*3)-2);
                etype
:= row.shape.sdo_elem_info((i*3)-1);
                interpret
:= row.shape.sdo_elem_info(i*3);
                case
etype mod 1000
                when
3 then
                    case
interpret
                    when
1 then
                        points
:= row.shape.sdo_ordinates.count / 2;
                        str
:= null;
                        for
pt in 1..points loop
                            px
:= row.shape.sdo_ordinates((pt*2)-1);
                            py
:= row.shape.sdo_ordinates(pt*2);
                            str
:= str || ‘ ‘||(px+x)*m||’,’
                               
||(height-(py+y)*m);
                        end
loop;
                        htp.p(‘<polygon
points=”‘||trim(str)
                        ||'”
style=”fill:none; stroke:black;”/>’);
                    when
3 then
                        px
:= row.shape.sdo_ordinates(1);
                        wd
:= abs(row.shape.sdo_ordinates(3) – px);
                        if
px > row.shape.sdo_ordinates(3) then
                            px
:= row.shape.sdo_ordinates(3);
                        end
if;
                        py
:= row.shape.sdo_ordinates(2);
                        ht
:= abs(row.shape.sdo_ordinates(4) – py);
                        if
py < row.shape.sdo_ordinates(4) then
                            py
:= row.shape.sdo_ordinates(4);
                        end
if;
                        htp.p(‘<rect
x=”‘||(px+x)*m
                            ||'”
y=”‘||(height-(py+y)*m)
                            ||'”
width=”‘||wd*m
                            ||'”
height=”‘||ht*m
                            ||'”
style=”fill:none; stroke:black;”/>’);
                    when
4 then
                        circle_calc

                        (

                           
row.shape.sdo_ordinates(1),
                           
row.shape.sdo_ordinates(2),
                           
row.shape.sdo_ordinates(3),
                            row.shape.sdo_ordinates(4),

                           
row.shape.sdo_ordinates(5),
                           
row.shape.sdo_ordinates(6),
                            cx,cy,r

                        );

                        htp.p(‘<circle
cx=”‘||(cx+x)*m
                        ||'”
cy=”‘||(height-(cy+y)*m)
                        ||'”
r=”‘||r*m
                        ||'”
style=”fill:none; stroke:black;”/>’);
                    else
null;  — unimplemented polygon
                    end
case;
                else
null; — unimplemented shape
                end
case;
            end
loop;
        end loop;
        htp.p(‘</svg>’);

    end show;
end sdosvg;
/
show errors;

Scott Stephens worked for Oracle for more than 13 years in technical support, e-commerce, marketing, and software development. For more of his Oracle tips, visit our Oracle Dev Tips Library.