Go to content Go to navigation and search

Home

Free Software Download

Download Code, Packages and Installers

Object Documentation

Package Documentation

CENTROID
COGO
EXPORTER
GEOM
KML
NETWORK
SDO_ERROR
Spatial Companion For Oracle (SC4O)
TOOLS

Articles

    New versions of LINEAR, CENTROID and GEOM etc packages
    TESSELATE and CENTROID package updates
    Applying and Extending Oracle Spatial: Source Code Problem
    CENTROID package use with ESRI's sde.st_geometry
    Changes to CENTROID and TESSELATE packages
    CENTROID Package now returns centroid of multi-linestring
    New versions of PL/SQL packages uploaded
    New Release of Free PL/SQL Packages
    New version of PL/SQL packages released
    New Version PL/SQL Spatial packages released
    New PL/SQL Package Upload
    PL/SQL Spatial Types and Packages
    Update to PL/SQL Packages
    New PL/SQL Packages
    VB6 Oracle Spatial or Locator OO4O code
    Spatial PL/SQL Packages by Example
    Catalog Registry Tool
    Image Catalog Tool
    Oracle Spatial PL/SQL and VB6 Source Code


Search

Browse

RSS / Atom

Email me

textpattern

Creative Commons License
All Blog Articles, Data Models and Free Source Code by Simon Greener, The SpatialDB Advisor is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License.

Spatial PL/SQL Packages by Example

Tuesday August 29 2006 at 19:44

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…

Creative Commons License

post this at del.icio.uspost this at Diggpost this at Technoratipost this at Redditpost this at Farkpost this at Yahoo! my webpost this at Windows Livepost this at Google Bookmarkspost this to Twitter

Comments

META HTTP-EQUIV=