Data Management

Oracle Tip: Generate SVG from Oracle Spatial data

This Oracle tip shows 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.

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.

Editor's Picks