Spatial PL/SQL Packages by Example

The following is an introduction to the PL/SQL packages downloadable from this site.

The following is an example of how the GF ( G eometry F actory) package was used to implement the rounding of the vertices of an Sdo_Geometry object to the tolerances specified in the DIMINFO structure of a USER_SDO_GEOM_METADATA entry.

New users of Oracle Spatial/Locator (I will use “Oracle Spatial” to mean both Spatial and Locator from now on) get confused by the sdo_tolerance value associated with the dimensions (eg X, Y or Z) of an Sdo_Geometry table/column object as stored in the DIMINFO column of the metadata catalog (visible via USER_SDO_GEOM_METADATA).

I guess what confuses users is that they expect that when they enter a value they expect that Oracle Spatial actually rounds their data to this tolerance when it is loaded.

Perhaps they think this is like a Fuzzy tolerance used in an ArcInfo “Clean”. Then again, let’s not go there….

To reiterate, the sdo_tolerance value is nothing more than a statement to Oracle Spatial that when it needs to compare two ordinates (eg two X’s when comparing two XY coordinates) that it should stop comparing them at a particular level of precision.

So, if I set an sdo_tolerance of 0.5 what I am saying is that two ordinate values are equal if at the nearest whole unit (a meter, degree of longitude, etc). That is:

1.1 is equal to 0.9 because if we round the values to a whole unit of measure (ie 1.0) they are equal!

(Aside: To set an sdo_tolerance one should choose the unit of measure at which two values are the same eg 1cm and then set the sdo_tolerance to half that unit eg 5mm. Correctly stated this is 0.005 if the units of the projected coordinates are meters. This is similar to the way one rounds a floating point number in the C programming language. Note: For geodetic units ie longitude/latitude values the sdo_tolerance is expressed in meters and not decimal degrees.)

However, regardless as to what I set as the sdo_tolerance, if I load spatial data into Oracle Spatial (say using FME), Oracle does not, magically, use the sdo_tolerance in the appropriate USER_SDO_GEOM_METADATA entry (it might not even exist at the time of loading!), to round the ordinate values to the supplied precision.

Now some geospatial professionals get upset by this. If you are one of these then the TOLERANCE function in my GEOM package will fix this for you.

Here is how it works.

From the documentation:

Precis: Function which updates all coordinates in a shape to precision of the tolerances referenced in the diminfo structure.

Description: Nothing in the Oracle Spatial library ensures that a shape loaded into a column in a table has its ordinates set to the precision specified in the table’s SDO_GEOM_METADATA DIMARRAY. This function ensures that all the ordinates of a shape are specified to the precision documented in its dimarray.

usage:fixedShape := geom.tolerance(shape,diminfo);

This function uses my GF package (which is what this article is actually about) which allows the programmer to iterate over an Sdo_Geometry object without having to worry about the Sdo_Geomety’s actual structure.

First off. Note the header for the function is:

 Function tolerance( p_geometry IN MDSYS.SDO_GEOMETRY,
                     p_dimarray IN MDSYS.SDO_DIM_ARRAY )
   RETURN MDSYS.SDO_GEOMETRY

That is, it takes in an Sdo_Geometry (p_geometry) and a description of its dimensions and their tolerances (p_dimarray) and returns a tolerance-adjusted (new) Sdo_Geometry object.

Here is how it does it.

Firstly, it uses the p_dimarray information to generate a value that can be used by the Oracle ROUND function to create a correctly rounded value. (‘Scuse the bit of math… and, oh, by the way, let’ ignore Z for the purpose of this article)

 v_x_round_factor := round(log(10,(1/p_dimarray(1).sdo_tolerance)/2));
 v_y_round_factor := round(log(10,(1/p_dimarray(2).sdo_tolerance)/2));

Now what we do is initialse the GF package using our passed in p_geometry so that we can iterate over it correctly:

 codesys.GF.SetGeometry( p_geometry );

Once we have this initialised we can query it for partcular properties such as the dimensionality of the object (eg 2D, 3D etc) or the geometric type of the object (eg Point, Line, Polygon etc):

 v_dim := codesys.GF.GetDimension();
 v_gtype := codesys.GF.GetGType();

Now I am going to skip over the case where p_geometry is a single point encoded on the Sdo_Point element to one in which the object is described by the Sdo_Elem_Info and Sdo_Ordinate array elements to highlight the abstraction that the GF packages provides over and above the physical implementation details of any one Sdo_Geometry object.

Now, how many elements describe the p_geometry? How cares, let’s just get the first and start iterating over all possible elements:

 v_partToProcess := codesys.GF.FirstElement();
 While v_partToProcess Loop

Now, for the current element, iterate over all coordinates:

 v_coordToProcess := codesys.GF.FirstCoordinate();
 WHILE v_coordToProcess LOOP
   v_Coord4D := codesys.GF.GetCoordinate();

Now, apply our ROUNDing values to the current coordinate’s ordinates:

 v_Coord4D.x := round(v_Coord4D.x,v_x_round_factor);
 v_Coord4D.y := round(v_Coord4D.y,v_y_round_factor);
 If v_dim > 2 Then
   v_Coord4D.z := round(v_Coord4D.z,v_z_round_factor);
 End If;

Now, let’s update this coordinate to reflect the change:

 codesys.GF.SetCoordinate(v_Coord4D);

Continue to do this for all coordinates and elements until there is no more:

     v_coordToProcess := codesys.GF.NextCoordinate();
   END LOOP; -- while_coord_to_process
   v_partToProcess := codesys.GF.NextElement();
 END LOOP; -- while_part_to_process

At the end return the ROUNDed p_geometry:

 RETURN GF.GetGeometry() ;

Pretty simple and elegant.

Now, in case you say: But this is ridiculously complex for something what can be done easily by just iterating over the Sdo_Ordinate_Array.

And I would answer, and you are quite right. But before you go away thinking what I have done is overly complicated, might I suggest you download the packages and have a look at some of the functions in the geom package such as EWKT, TO_2D etc to see that, when faced with lots of interesting manipulations of Sdo_Geometry objects, a little abstraction goes a long way.

BTW I don’t profess that the code in the GF or GEOM package is perfect – far be it. It has lots of warts and limitation so, let the buyer beware. But at least I am trying to be of some use to someone, somewhere, out there…