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.

SDO_ERROR

Monday August 15 2011 at 03:53

I have written a PL/SQL package called SDO_ERROR that could be used to visualise geometry errors (specifically validate_geometry_with_context) and elements Additionally it focuses mainly on projected (not long/lat) data, so if you use with geodetic data do so at your own risk.

Required Data Types

DEFINE defaultSchema = '&1'
 
create or replace TYPE T_ElemInfo AS OBJECT (offset NUMBER, etype NUMBER, interpretation NUMBER);
/
grant execute on T_ElemInfo to public;
 
create or replace TYPE T_Geometry   AS OBJECT (geometry mdsys.sdo_geometry );
/
grant execute on T_Geometry to public;
 
create or replace TYPE T_Vertex     AS OBJECT (x number,y number,z number,w number,id number);
/
grant execute on T_Vertex to public;
 
create or replace TYPE T_Vector     AS OBJECT (id integer,startCoord codesys.T_Vertex,  endCoord codesys.T_Vertex );
/
grant execute on T_Vector to public;
 
create or replace TYPE T_Error    AS OBJECT (error   varchar2(5),
                                             element number,
                                             ring    number,
                                             id      number,
                                             geom    mdsys.sdo_geometry );
/
grant execute on T_Error to public;
 
create or replace TYPE t_VertexMark AS OBJECT (element number,
                                               ring    number,
                                               id      number,
                                               geom    mdsys.sdo_geometry,
                                               angle   number,
                                               text    varchar2(4000) );
/
grant execute on T_VertexMark to public;

Packaged Header

CREATE OR REPLACE PACKAGE SDO_ERROR 
AUTHID CURRENT_USER
IS
   TYPE T_GeometrySet   IS TABLE OF &&defaultSchema..T_Geometry;
   Type T_ElemInfoSet   Is Table Of &&defaultSchema..T_ElemInfo;
   TYPE T_VectorSet     IS TABLE OF &&defaultSchema..T_Vector;
   TYPE T_ErrorSet      IS TABLE OF &&defaultSchema..T_Error;
   TYPE T_VertexMarkSet IS TABLE OF &&defaultSchema..T_VertexMark;
   TYPE T_Strings       IS TABLE OF varchar2(4000);
 
    /* Options for marking geometries
    */
    c_ID                   CONSTANT PLS_INTEGER := 0;
    c_ID_COORD             CONSTANT PLS_INTEGER := 1;
    c_COORD                CONSTANT PLS_INTEGER := 2;
    c_ELEM                 CONSTANT PLS_INTEGER := 3;
 
    c_DEGREES              CONSTANT PLS_INTEGER := 0;
    c_RADIANS              CONSTANT PLS_INTEGER := 1;
 
   /** ----------------------------------------------------------------------------------------
    * @function   : getValidateErrors
    * @precis     : Core, or base, function which returns each individual error in a geometry.
    *               Edge errors returns as separate edges and calculated intersection point unless 
    *               p_all only set to 1.
    * @version    : 1.0
    * @usage      : SELECT b.* FROM test a, TABLE(getValidateErrors(a.geom,0.005,null,0) b;
    * @param      : p_geometry    : MDSYS.SDO_GEOMETRY  : An sdo_geometry object.
    * @param      : p_tolerance   : NUMBER      : Oracle sdo_tolerance value eg 0.005 meters for geodetic.
    * @param      : p_geog_digits : pls_integer : Number of significant digits when p_geometry contains long/lat data.
    * @param      : p_context     : varchar2    : Value returned by validate_geometry_with_context. If null, 
    *                                             the sdo_geom function will be run but this function.
    * @return     : p_all         : pls_integer : If 0 then only the error location is returned, otherwise the 
    *                                            element/ring of host geometry containing error is also returned.
    * @history    : Simon Greener - Jun 2011 - Original coding.
    * @copyright  : Licensed under a Creative Commons Attribution-Share Alike 2.5 Australia License. 
    *               http://creativecommons.org/licenses/by-sa/2.5/au/
    **/
    Function getValidateErrors(p_geometry    in mdsys.sdo_geometry,
                               p_tolerance   in number      default 0.005,
                               p_geog_digits in pls_integer default NULL, 
                               p_all         in pls_integer default 0,
                               p_context     in varchar2    default null) 
      return &&defaultSchema..SDO_ERROR.T_ErrorSet pipelined;
 
   /** ----------------------------------------------------------------------------------------
    * @function   : getErrors
    * @precis     : Function which returns each individual error in a geometry as a single geometry.
    *               Edge errors returns as separate edges and calculated intersection point unless 
    *               p_all only set to 1.
    * @version    : 1.0
    * @usage      : SELECT b.* FROM test a, TABLE(getErrors(a.geom,0.005,null,0) b;
    * @param      : p_geometry    : MDSYS.SDO_GEOMETRY  : An sdo_geometry object.
    * @param      : p_tolerance   : NUMBER      : Oracle sdo_tolerance value eg 0.005 meters for geodetic.
    * @param      : p_geog_digits : pls_integer : Number of significant digits when p_geometry contains long/lat data.
    * @return     : p_all         : pls_integer : If 0 then only the error location is returned, otherwise the 
    *                                            element/ring of host geometry containing error is also returned.
    * @history    : Simon Greener - Jun 2011 - Original coding.
    * @copyright  : Licensed under a Creative Commons Attribution-Share Alike 2.5 Australia License. 
    *               http://creativecommons.org/licenses/by-sa/2.5/au/
   **/
   Function getErrors(p_geometry    in mdsys.sdo_geometry,
                      p_tolerance   in number      default 0.005,
                      p_geog_digits in pls_integer default NULL, 
                      p_all         in pls_integer default 0)
     return &&defaultSchema..SDO_ERROR.T_ErrorSet pipelined; 
 
   /** ----------------------------------------------------------------------------------------
    * @function   : getErrorsAsMulti
    * @precis     : Function which returns all errors in a geometry as a single multipoint or compound geometry.
    *               Edge errors returns as separate edges and calculated intersection points unless 
    *               p_all only set to 1. If p_all set to 1 a multipoint is returned, otherwise 
    *               possibly a compound geometry composed of points and lines.
    * @version    : 1.0
    * @usage      : SELECT getErrorsAsMulti(a.geom,0.005,null,0) FROM test a;
    * @param      : p_geometry    : MDSYS.SDO_GEOMETRY  : An sdo_geometry object.
    * @param      : p_tolerance   : NUMBER      : Oracle sdo_tolerance value eg 0.005 meters for geodetic.
    * @param      : p_geog_digits : pls_integer : Number of significant digits when p_geometry contains long/lat data.
    * @return     : p_all         : pls_integer : If 0 then only the error location is returned, otherwise the 
    *                                            element/ring of host geometry containing error is also returned.
    * @history    : Simon Greener - Jun 2011 - Original coding.
    * @copyright  : Licensed under a Creative Commons Attribution-Share Alike 2.5 Australia License. 
    *               http://creativecommons.org/licenses/by-sa/2.5/au/
  **/
   Function getErrorsAsMulti(p_geometry    in mdsys.sdo_geometry,
                             p_tolerance   in number      default 0.005,
                             p_geog_digits in pls_integer default NULL, 
                             p_all         in pls_integer default 0)
     return mdsys.sdo_geometry deterministic;
 
   /** ----------------------------------------------------------------------------------------
    * @function   : getError
    * @precis     : Function which returns a the nominated error number as a single geometry.
    *               Edge errors returns as single edge, points as single point.
    * @version    : 1.0
    * @usage      : SELECT getError(a.geom,0.005,null) FROM test a;
    * @param      : p_geometry    : MDSYS.SDO_GEOMETRY  : An sdo_geometry object.
    * @param      : p_tolerance   : NUMBER      : Oracle sdo_tolerance value eg 0.005 meters for geodetic.
    * @param      : p_geog_digits : pls_integer : Number of significant digits when p_geometry contains long/lat data.
    * @history    : Simon Greener - Jun 2011 - Original coding.
    * @copyright  : Licensed under a Creative Commons Attribution-Share Alike 2.5 Australia License. 
    *               http://creativecommons.org/licenses/by-sa/2.5/au/
   **/
   Function getError(p_geometry     in mdsys.sdo_geometry, 
                     p_error_number in pls_integer,
                     p_tolerance    in number      default 0.005,
                     p_geog_digits  in pls_integer default NULL )
    return mdsys.sdo_geometry deterministic;
 
   /** ----------------------------------------------------------------------------------------
    * @function   : getErrorText
    * @precis     : Function which returns the text that describes each error in a geometry.
    *               All errors are returned.
    * @version    : 1.0
    * @usage      : SELECT b.* FROM test a, TABLE(getErrorText(a.geom,0.005,null,0) b;
    * @param      : p_geometry    : MDSYS.SDO_GEOMETRY  : An sdo_geometry object.
    * @param      : p_tolerance   : NUMBER      : Oracle sdo_tolerance value eg 0.005 meters for geodetic.
    * @history    : Simon Greener - Jun 2011 - Original coding.
    * @copyright  : Licensed under a Creative Commons Attribution-Share Alike 2.5 Australia License. 
    *               http://creativecommons.org/licenses/by-sa/2.5/au/
  **/
   Function getErrorText(p_geometry  in mdsys.sdo_geometry,
                         p_tolerance in number DEFAULT 0.005) 
     return &&defaultSchema..SDO_ERROR.T_Strings pipelined;
 
   /** ----------------------------------------------------------------------------------------
    * @function   : getErrorText
    * @precis     : Function which returns the text that describes a specific error in a geometry.
    *               Validate_Geometry_With_Context only returns the first error it finds so this
    *               function is useless unless the error is 13356 or 13349 as this package implements
    *               custom processing to discover all errors of this type.
    * @version    : 1.0
    * @usage      : SELECT b.* FROM test a, TABLE(getErrorText(a.geom,0.005,null,0) b;
    * @param      : p_geometry     : MDSYS.SDO_GEOMETRY  : An sdo_geometry object.
    * @param      : p_error_number : pls_integer : The position of the error returned by validate_geometry_with_context.
    * @param      : p_tolerance    : NUMBER      : Oracle sdo_tolerance value eg 0.005 meters for geodetic.
    * @history    : Simon Greener - Jun 2011 - Original coding.
    * @copyright  : Licensed under a Creative Commons Attribution-Share Alike 2.5 Australia License. 
    *               http://creativecommons.org/licenses/by-sa/2.5/au/
   **/
   Function getErrorText(p_geometry     in mdsys.sdo_geometry,
                         p_error_number in pls_integer,
                         p_tolerance    in number default 0.05)
    return varchar2 deterministic;
 
   /** ----------------------------------------------------------------------------------------
    * @function   : getMarks
    * @precis     : Returns a table that describes each vertex in the provided geometry.
    *               The desciption can be in one of a number of patterns:
    *                 - &&defaultSchema..SDO_ERROR.c_ID       <id>
    *                 - &&defaultSchema..SDO_ERROR.c_ID_COORD <id>{x,y}
    *                 - &&defaultSchema..SDO_ERROR.c_COORD    {x,y}
    *                 - &&defaultSchema..SDO_ERROR.c_ELEM     {element,ring,id}
    *               The textual marks will be rotated algorithmically depending on the vectors 
    *               in/out of a vertex. The returned angle can be either in radians or degrees
    *               depending on the value of the p_degrees parameter.
    *                 - &&defaultSchema..SDO_ERROR.c_DEGREES 
    *                 - &&defaultSchema..SDO_ERROR.c_RADIANS
    * @version    : 1.0
    * @usage      : SELECT b.* FROM test a, TABLE(getMarks(a.geom,1,0,0.005,null) b;
    * @param      : p_geometry     : MDSYS.SDO_GEOMETRY  : An sdo_geometry object.
    * @param      : p_error_number : pls_integer : The position of the error returned by validate_geometry_with_context.
    * @param      : p_geog_digits  : NUMBER      : Oracle sdo_tolerance value eg 0.005 meters for geodetic.
    * @history    : Simon Greener - Jun 2011 - Original coding.
    * @copyright  : Licensed under a Creative Commons Attribution-Share Alike 2.5 Australia License. 
    *               http://creativecommons.org/licenses/by-sa/2.5/au/
   **/
   Function getMarks(p_geometry    in mdsys.sdo_geometry,
                     p_markType    in pls_integer default 0,
                     p_degrees     in pls_integer default 0,
                     p_tolerance   in number      default 0.005,
                     p_geog_digits in pls_integer default null)
     return &&defaultSchema..SDO_ERROR.T_VertexMarkSet pipelined;
 
   /** ----------------------------------------------------------------------------------------
    * @function   : fix13348
    * @precis     : Function that corrects an ORA-13348 - polygon boundary is not closed
    * @version    : 1.0
    * @usage      : SELECT b.* FROM test a, TABLE(getMarks(a.geom,1,0,0.005,null) b;
    * @param      : p_geometry    : MDSYS.SDO_GEOMETRY  : An sdo_geometry object.
    * @param      : p_make_equal  : boolean : Boolean flag saying whether to make the last
    *                                         vertex equal to the first or whether to insert
    *                                         a nother vertex at the end that is the same as the first.
    * @param      : p_tolerance    : NUMBER : Oracle sdo_tolerance value eg 0.005 meters for geodetic.
    * @param      : p_geog_digits  : pls_integer : if p_geometry contains long/lat data then this
    *                                              parameter should be set to the number of precise 
    *                                              decimal digits of degrees for comparing two ordinates.
    * @history    : Simon Greener - Jun 2011 - Original coding.
    * @copyright  : Licensed under a Creative Commons Attribution-Share Alike 2.5 Australia License. 
    *               http://creativecommons.org/licenses/by-sa/2.5/au/
   **/
   Function fix13348(p_geometry    in mdsys.sdo_geometry,
                     p_make_equal  in boolean     default true,
                     p_tolerance   in number      default 0.005,
                     p_geog_digits in pls_integer default null)
    return mdsys.sdo_geometry deterministic;
 
  /** UTILITY FUNCTIONS THAT HAVE TO BE PUBLIC **/
 
    /*********************************************************************************
  * @function    : Tokenizer
  * @precis      : Splits any string into its tokens.
  * @description : Supplied a string and a list of separators this function
  *                returns resultant tokens as a pipelined collection.
  * @example     : SELECT t.column_value
  *                  FROM TABLE(tokenizer('The rain in spain, stays mainly on the plain.!',' ,.!') ) t;
  * @param       : p_string. The string to be Tokenized.
  * @param       : p_separators. The characters that are used to split the string.
  * @requires    : t_TokenSet type to be declared.
  * @history     : Pawel Barut, http://pbarut.blogspot.com/2007/03/yet-another-tokenizer-in-oracle.html
  * @history     : Simon Greener - July 2006 - Original coding (extended SQL sourced from a blog on the internet)
  **/
  Function Tokenizer(p_string     In VarChar2,
                     p_separators In VarChar2)
    Return &&defaultSchema..SDO_ERROR.T_Strings Pipelined;
 
    /** ----------------------------------------------------------------------------------------
    * @function   : GetVector
    * @precis     : Places a geometry''s coordinates into a pipelined vector data structure.
    * @version    : 3.0
    * @description: Loads the coordinates of a linestring, polygon geometry into a
    *               pipelined vector data structure for easy manipulation by other functions
    *               such as geom.SDO_Centroid.
    * @usage      : Function GetVector( p_geometry IN MDSYS.SDO_GEOMETRY,
    *                                   p_dimarray IN MDSYS.SDO_DIM_ARRAY )
    *                        RETURN VectorSetType PIPELINED
    *               eg select *
    *                    from myshapetable a,
    *                         table(&&defaultSchema..linear.GetVector(a.shape));
    * @param      : p_geometry : MDSYS.SDO_GEOMETRY : A geographic shape.
    * @return     : geomVector : VectorSetType      : The vector pipelined.
    * @requires   : Global data types coordRec, vectorRec and VectorSetType
    * @requires   : GF package.
    * @history    : Simon Greener - July 2006 - Original coding from GetVector
    * @history    : Simon Greener - July 2008 - Re-write to be standalone of other packages eg GF
    * @history    : Simon Greener - October 2008 - Removed 2D limits
    * @copyright  : Free for public use
  **/
  Function GetVector(P_Geometry  In Mdsys.Sdo_Geometry,
                     P_Exception In Pls_Integer Default 0)
    Return &&defaultSchema..SDO_ERROR.T_VectorSet Pipelined ;
 
end SDO_ERROR;
/
show errors

The source code, which is still under active development, is available by contacting me directly. It is not available for direct download.

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

Comment [1]

Hello,

I work as Oracle(spatial)DBA assisting Java/jts/hibernate development.

I sometimes experience problems to quickly point out the failures that pop-up from the validation of polygons(validate_geometry_with_context). These polygons consist of simples, but somtimes there are a lot inner polygons that complicate the search. Maybe your tools can help out.

Best Regards
Auke

— Auke Nammensma · 3 February 2013, 21:12 · #

META HTTP-EQUIV=