Home Straws in the Wind Blog Articles

Geomatics Degrees, Space Curves and Oracle SpatialWednesday July 05 2006 at 20:35One of the things that should be taught to GIS students at Uni but isn’t in a lot of courses is the notion of space keys or spacefilling curves. The mathematics behind these keys was done before computing by mathematicians like: The one that most GIS people are introduced to is the zorder spacefilling curve because it is well known in computer science (cf Donald Knuth’s classic work on Algorithms) as a localition preserving hash function. Therefore it has been implemented many times. It is known as Mortonordering. See here for a fuller discussion on spacefilling curves. Also, my Tesselate PL/SQL package has a good implementation of a QuadTree that also includes a PeanoKey. In the end, the goal for any localition preserving hash function (no matter whose algorithm is used), is to map a point in a multidimensional coordinate space to a scalar value. The thing to note about spacefilling curves – no matter what algorithm is used – can be seen in the figure in the Wikipedia “Spacefilling curve” page titled “Six iterations of the Hilbert curve, a spacefilling curve devised by mathematician David Hilbert”. Spot the repetition of the basic pattern as you move from top left across then down from left to right. Notice how the functions not only correlate the data in any one bucket, but the buckets themselves get correlated as well! Neat huh! I have some implementations of Hilbert, Peano and Morton keys in C but it is back at home in Oz. But what we really need is an implementation for us Oracle Spatial users… But as Aragorn said to Háleth, son of Hama at the battle for Helm’s Deep: “There’s always hope”... What is little known is that Oracle Spatial started out life as a solution to the storage of large volumes of hydrographic soundings (point data with Z and time dimensions) by Oracle Canada. What they needed was a method for storage and access for the point data that would be scalable and fast. They turned to spacefilling curves as a method for creating a single numeric key which could be the primary key for a standard relational table. They took the basic spacefulling curve implementations and generalised them to cope with multipledimensions – hence the MD in MDSYS (MultiDimension). Of course, this approach was never going to work for linear and polygonal data that is why Oracle Spatial before 8i really wasn’t much use…. but that is another story. Anyway, the implementation of their spacefilling curve (based on Peano—See HHEncode link on this site) is still available and is in the MD package. SQL> desc md Specifically, the HHENCODE function. Here is an example of how to use it to create a table of point data sorted in HHENCODE ordering… CREATE OR REPLACE FUNCTION linear_key ( p_shape in mdsys.sdo_geometry, P_diminfo in mdsys.sdo_dim_array ) RETURN RAW DETERMINISTIC IS v_ctr MDSYS.SDO_GEOMETRY; v_rval RAW; v_lvl INTEGER; BEGIN —Compute the centroid of the geometry —The geometric centroid is good enough (does not have to fall within poygon object) v_ctr := MDSYS.SDO_GEOM.SDO_CENTROID(p_shape,p_diminfo); v_lvl := 8;—Encoding level for hhcode function p_rval := MD.HHENCODE.sdo_lb, p_diminfo(1).sdo_ub, v_lvl, v_ctr.sdo_point.y, p_diminfo(2).sdo_lb, p_diminfo(2).sdo_ub, v_lvl); RETURN p_rval; END; / Now, as long as the table can be created such that the blocks it uses are sequential on physical disk, the performance of any reads of the data (display all points in this query window) will be at an absolute maximum.
