Generate SVG from Oracle Spatial data

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 maths 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.