SDO_ERROR

21 Jul 2020 By Simon Greener

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

[code lang=”sql”]
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;
[/code]

Packaged Header

[code lang=”sql”]
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
[/code]

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

Leave a Reply

Your email address will not be published. Required fields are marked *