CENTROID

 DEFINE defaultSchema = '&1'
 /* Have to be totally standalone from types used in rest of packages.
 ** Yes, this means overlap, but that's life.
 */
 CREATE OR REPLACE TYPE &&defaultSchema..T_Vertex AS OBJECT (
    x  NUMBER,
    y  NUMBER,
    z  NUMBER,
    w  NUMBER,
    id NUMBER
 );
 /
 SHOW errors
 GRANT EXECUTE ON &&defaultSchema..T_Vertex TO public WITH GRANT OPTION;
 CREATE OR REPLACE TYPE &&defaultSchema..T_Vector IS OBJECT (
     Id         INTEGER,
     StartCoord &&defaultSchema..T_Vertex,
     Endcoord   &&defaultSchema..T_Vertex
 );
 /
 SHOW errors
 GRANT EXECUTE ON &&defaultSchema..T_Vector TO public WITH GRANT OPTION;
 -- Geometry types....
 --
 CREATE OR REPLACE TYPE &&defaultSchema..t_Geometry AS OBJECT (
    geometry mdsys.sdo_geometry
 );
 /
 SHOW errors
 GRANT EXECUTE ON &&defaultSchema..t_Geometry TO public WITH GRANT OPTION;
 CREATE OR REPLACE TYPE &&defaultSchema..t_GeometrySet AS TABLE OF &&defaultSchema..t_Geometry;
 /
 SHOW errors
 GRANT EXECUTE ON &&defaultSchema..t_GeometrySet TO public WITH GRANT OPTION;

 CREATE OR REPLACE package Centroid
 AUTHID CURRENT_USER
 IS
    TYPE t_numbers     IS TABLE OF NUMBER;
    TYPE t_Vectors     IS TABLE OF &&defaultSchema..t_Vector;
   /** @NOTE : The schema owning this package, and all calling schemas (see AUTHID above) MUST have
   *           direct SELECT permissions on DBA_REGISTRY for this package to compile and run properly.
   *           SQL> connect sys/****@sid
   *           SQL> GRANT SELECT ON DBA_REGISTRY TO &&defaultSchema.;
   *   @NOTE : These packages assume a minimum of 9iR2 as the SDO_MBR function uses SDO_AGGR_MBR for
   *           Locator users. If you are on 9iR1 then modify the SDO_MBR function to uncomment out
   *           the SQL that doesn't use SDO_AGGR_MBR and comment out the SDO_AGGR_MBR SQL.
   *   @NOTE : This package uses &&defaultSchema..T_VERTEX_TYPE which Oracle changed the definition of at 10g
   *           by adding an ID field. If you want to compile the package on 9i, manually change the
   *           code in ConvertGeometry so that it does not reference the last, ID, field.
   *   @NOTE : If sdo_geometry linestrings or polygons contain circular arcs they must be "stroked" before
   *           Calling the SDO_CENTROID function. If you have SPATIAL you can use sdo_geom.sdo_arc_densify().
   *           If not use this package's ConvertGeometry() function.
   *           SDO_GEOM.SDO_ARC_DENSIFY() function before calling ado_centroid.
   *   @NOTE : SDO_Area does not work for non-2D geometries.
   **/
   /** ----------------------------------------------------------------------------------------
   * @function   : Generate_Series
   * @precis     : Function that generates a series of numbers mimicking PostGIS's function with
   *               the same name
   * @version    : 1.0
   * @usage      : Function generate_series(p_start pls_integer,
   *                                        p_end   pls_integer,
   *                                        p_step  pls_integer := 1)
   *                 Return centroid.t_integers Pipelined;
   *               eg SELECT s.* FROM TABLE(generate_series(1,1000,10)) s;
   * @param      : p_start   : Starting value
   * @paramtype  : p_start   : Integer
   * @param      : p_end     : Ending value.
   * @paramtype  : p_end     : Integer
   * @return     : p_step    : The step value of the increment between start and end
   * @rtnType    : p_step    : Integer
   * @history    : Simon Greener - June 2008 - Original coding.
   * @copyright  : Free for public use
   **/
   FUNCTION generate_series(p_start IN pls_integer,
                            p_end   IN pls_integer,
                            p_step  IN pls_integer := 1 )
        RETURN &&defaultSchema..centroid.t_numbers Pipelined;
   /** ----------------------------------------------------------------------------------------
   * @function   : SDO_MBR
   * @precis     : Returns Minimum Bounding Rectangle of a given geometry.
   * @version    : 1.0
   * @usage      : Function SDO_MBR ( p_geometry IN MDSYS.SDO_GEOMETRY )
   *                 Return MDSYS.SDO_GEOMETRY Deterministic;
   * @example    : SELECT sdo_mbr(a.geom,0.01)
   *                 FROM ProjCompound2D a,
   *                      TABLE( &&defaultSchema..geom.GetElemInfo( a.geom ) ) ei
   *                WHERE a.geom is not null;
   * @param      : p_geometry  : A shape.
   * @paramtype  : p_geomery   : MDSYS.SDO_GEOMETRY
   * @param      : p_tolerance : Dimarray sdo_tolerance value
   * @paramtype  : p_tolerance : number
   * @return     : geometry    : A 2003 vertex described polygon
   * @rtnType    : sdo_geometry : MDSYS.SDO_GEOMETRY
   * @note       : Function is pipelined
   * @history    : Simon Greener - Jul 2008 - Original coding.
   * @copyright  : Free for public use
   **/
   FUNCTION SDO_MBR( p_geometry  IN MDSYS.SDO_GEOMETRY )
            RETURN MDSYS.SDO_GEOMETRY Deterministic;
   /** ----------------------------------------------------------------------------------------
   * @function   : SDO_Length
   * @precis     : Function which computes length of linestrings or boundaries of polygons.
   * @version    : 1.0
   * @description: These are wrapper functions over SDO_3GL.LENGTH_AREA procedures that are
   *               not mentioned in Oracle's licensing as being functions limited to Spatial (EE)
   * @usage      : Function SDO_Length ( p_geometry IN MDSYS.SDO_GEOMETRY,
   *                                      p_tolerance IN Number
   *                                      p_units    IN VarChar2 )
   *                 Return Number Deterministic;
   *               eg fixedShape := &&defaultSchema..geom.length(shape,diminfo);
   * @param      : p_geometry   : A valid sdo_geometry.
   * @paramtype  : p_geomery    : MDSYS.SDO_GEOMETRY
   * @param      : p_tolerance  : The dimarray describing the shape.
   * @paramtype  : p_tolerance  : MDSYS.SDO_DIM_ARRAY
   * @param      : p_units      : The units in which the length is to be calculated eg meters, miles etc
   * @paramtype  : p_units      : varchar2
   * @return     : length       : Length of linestring or boundary of a polygon in required unit of measure
   * @rtnType    : length       : Number
   * @note       : Supplied p_units should exist in mdsys.SDO_UNITS_OF_MEASURE
   * @history    : Simon Greener - Oct 2007 - Original coding.
   * @copyright  : Free for public use
   **/
   FUNCTION SDO_Length( p_geometry  IN mdsys.sdo_geometry,
                        p_tolerance IN NUMBER,
                        p_units     IN varchar2 := 'Meter' )
     RETURN NUMBER deterministic;
   /** ----------------------------------------------------------------------------------------
   * @function   : SDO_AREA
   * @precis     : Function which computes area of a polygon.
   * @version    : 1.0
   * @description: These are wrapper functions over SDO_3GL.LENGTH_AREA procedures that are
   *               not mentioned in Oracle's licensing as being functions limited to Spatial (EE)
   * @usage      : Function SDO_Area ( p_geometry IN MDSYS.SDO_GEOMETRY,
   *                                   p_dimarray IN MDSYS.SDO_DIM_ARRAY
   *                                   p_units    IN VarChar2 )
   *                 Return Number Deterministic;
   *               eg fixedShape := sdo_area(shape,tolerance);
   * @param      : p_geometry   : A valid sdo_geometry.
   * @paramtype  : p_geomery    : MDSYS.SDO_GEOMETRY
   * @param      : p_tolerance  : The vertex tolerance .
   * @paramtype  : p_tolerance  : NUMBER
   * @return     : area         : Area of a polygon in required unit of measure
   * @rtnType    : area         : Number
   * @note       : Supplied p_units should exist in mdsys.SDO_UNITS_OF_MEASURE
   * @history    : Simon Greener - Oct 2007 - Original coding.
   * @copyright  : Free for public use
   **/
   FUNCTION SDO_Area( p_geometry   IN mdsys.sdo_geometry,
                      p_tolerance  IN NUMBER,
                      p_units      IN varchar2 := 'Square Meter' )
     RETURN NUMBER Deterministic;
   /** ----------------------------------------------------------------------------------------
   * @function   : ConvertGeometry
   * @precis     : Function which converts optimized rectangle components in a polygon to their
   *               stroked equivalent.
   * @version    : 1.0
   * @description: The centroid algorithm works with vector representations of the sides
   *               of a polygon generated by GetVector. Optimized rectangle are not directly
   *               supported in either the GetVector or sdo_centroid functions. If these exist
   *               the sdo_geometry should be put through this function before calling sdo_centroid.
   * @usage      : Function Converte_Geometry ( p_geometry IN MDSYS.SDO_GEOMETRY )
   *                 Return MdSys.Sdo_Geometry Deterministic;
   *               eg centroid := centroid.sdo_centroid(centroid.ConvertGeometry(shape),tolerance);
   * @param      : p_geometry   : A valid sdo_geometry.
   * @paramtype  : p_geomery    : MDSYS.SDO_GEOMETRY
   * @param      : p_Arc2Chord  : ArcToChord separation expressed in dataset units for converting arcs to stroked lines.
   * @paramtype  : p_Arc2Chord  : Number
   * @return     : geometry     : Converted, valid, geometry
   * @rtnType    : sdo_geometry : MDSYS.SDO_GEOMETRY
   * @note       : Oracle changed the definition of T_VERTEX_TYPE at 10g by adding and ID field. If you
   *               compiling on 9i manually change the code in ConvertGeometry so that it does not
   *               reference the ID field.
   * @history    : Simon Greener - July 2008 - Original coding.
   * @copyright  : Free for public use
   **/
   FUNCTION ConvertGeometry(p_geometry  IN mdsys.sdo_geometry,
                            p_Arc2Chord IN NUMBER := 0.1)
     RETURN mdsys.sdo_geometry Deterministic;
   FUNCTION SDO_ARC_DENSIFY(p_geometry  IN mdsys.sdo_geometry,
                            p_Arc2Chord IN NUMBER := 0.1)
     RETURN mdsys.sdo_geometry Deterministic;
   /** ----------------------------------------------------------------------------------------
   * @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      : select *
   *                 from myshapetable a,
   *                      table(centroid.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
   * @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)
     RETURN &&defaultSchema..CENTROID.t_Vectors pipelined;
   /** =================== CENTROID Functions ====================== **/
   /* ----------------------------------------------------------------------------------------
   * @function   : centroid_p
   * @precis     : Generates centroid for a point (itself) or multipoint.
   * @version    : 1.3
   * @description: This function creates centroid of multipoint via averaging of ordinates.
   * @param      : p_geometry     : MDSYS.SDO_GEOMETRY : The geometry object.
   * @param      : p_round_x      : Number : Ordinate rounding precision for X ordinates.
   * @param      : p_round_y      : Number : Ordinate rounding precision for Y ordinates.
   * @param      : p_round_z      : Number : Ordinate rounding precision for Z ordinates.
   * @return     : centroid       : MDSYS.SDO_GEOMETRY : The centroid.
   * @requires   : GetVector()
   * @history    : Simon Greener - Jul 2008 - Original coding of centroid_p as internal function
   * @history    : Simon Greener - Jan 2012 - Exposed internal function.
   * @copyright  : Free for public use
   **/
   FUNCTION centroid_p(p_geometry IN mdsys.sdo_geometry,
                        p_round_x  IN NUMBER := 3,
                        p_round_y  IN NUMBER := 3,
                        p_round_z  IN NUMBER := 2)
     RETURN MDSYS.SDO_GEOMETRY DETERMINISTIC;
   /* ----------------------------------------------------------------------------------------
   * @function   : Centroid_A
   * @precis     : Generates centroid for a polygon.
   * @version    : 1.3
   * @description: The standard MDSYS.SDO_GEOM.SDO_CENTROID function does not guarantee
   *               that the centroid it generates falls inside the polygon.
   *               This function ensures that the centroid of any arbitrary polygon falls within the polygon.
   * @param      : p_geometry     : MDSYS.SDO_GEOMETRY : The geometry object.
   * @param      : p_start        : Number : 0 = Use average of all Area's vertices for starting X centroid calculation
   *                                         1 = Use centre X of MBR
   *                                         2 = User supplied starting seed X
   * @param      : p_seed_x       : Number : Starting X ordinate for which a Y that is inside the polygon is returned.
   * @param      : p_round_x      : Number : Ordinate rounding precision for X ordinates.
   * @param      : p_round_y      : Number : Ordinate rounding precision for Y ordinates.
   * @param      : p_round_z      : Number : Ordinate rounding precision for Z ordinates.
   * @return     : centroid       : MDSYS.SDO_GEOMETRY : The centroid.
   * @requires   : GetVector()
   * @history    : Simon Greener - Jul 2008 - Original coding of centroid_a as internal function
   * @history    : Simon Greener - Jan 2012 - Exposed internal function. Added p_seed_x support.
   * @copyright  : Free for public use
   **/
   FUNCTION centroid_a(p_geometry IN mdsys.sdo_geometry,
                        p_start    IN NUMBER := 1,
                        p_seed_x   IN NUMBER := NULL,
                        p_round_x  IN NUMBER := 3,
                        p_round_y  IN NUMBER := 3,
                        p_round_z  IN NUMBER := 2)
     RETURN MDSYS.SDO_GEOMETRY DETERMINISTIC;
   /* ----------------------------------------------------------------------------------------
   * @function   : Centroid_1
   * @precis     : Generates centroid for a linestring.
   * @version    : 1.3
   * @description: The standard MDSYS.SDO_GEOM.SDO_CENTROID function does not guarantee
   *               that the centroid it generates falls inside the polygon.
   *               This function ensures that the centroid of any arbitrary polygon falls within the polygon.
   * @param      : p_geometry           : MDSYS.SDO_GEOMETRY : The geometry object.
   * @param      : p_position_as_ratio  : Number : Position along multi-line/line where "centroid" created.
   * @param      : p_round_x            : Number : Ordinate rounding precision for X ordinates.
   * @param      : p_round_y            : Number : Ordinate rounding precision for Y ordinates.
   * @param      : p_round_z            : Number : Ordinate rounding precision for Z ordinates.
   * @return     : centroid             : MDSYS.SDO_GEOMETRY : The centroid.
   * @requires   : GetVector()
   * @history    : Simon Greener - Jul 2008 - Original coding of centroid_l as internal function
   * @history    : Simon Greener - Jan 2012 - Exposed internal function.
   * @copyright  : Free for public use
   **/
   FUNCTION centroid_l(p_geometry          IN mdsys.sdo_geometry,
                        p_position_as_ratio IN NUMBER := 0.5,
                        p_round_x           IN NUMBER := 3,
                        p_round_y           IN NUMBER := 3,
                        p_round_z           IN NUMBER := 2)
     RETURN MDSYS.SDO_GEOMETRY DETERMINISTIC;
   /* ----------------------------------------------------------------------------------------
   * @function   : SDO_Centroid
   * @precis     : Generates centroid for a polygon.
   * @version    : 1.5
   * @description: The standard MDSYS.SDO_GEOM.SDO_CENTROID function does not guarantee
   *               that the centroid it generates falls inside the polygon.   Nor does it
   *               generate a centroid for a multi-part polygon shape.
   *               This function ensures that the centroid of any arbitrary polygon
   *               falls within the polygon. Also provides centroid functions for multipoints and linestrings.
   * @param      : p_geometry     : MDSYS.SDO_GEOMETRY : The geometry object.
   * @param      : p_start        : Number : 0 = Use average of all Area's vertices for starting X centroid calculation
   *                                         1 = Use centre X of MBR
   * @param      : p_largest      : Number : 0 = Use smallest of any multipart geometry.
   *                                         1 = Use largest of any multipart geometry.
   * @param      : p_round_x      : Number : Ordinate rounding precision for X ordinates.
   * @param      : p_round_y      : Number : Ordinate rounding precision for Y ordinates.
   * @param      : p_round_z      : Number : Ordinate rounding precision for Z ordinates.
   * @return     : centroid       : MDSYS.SDO_GEOMETRY : The centroid.
   * @requires   : GetVector()
   * @history    : Simon Greener - Mar 2008 - Total re-write of algorithm following on from cases the original algorithm didn't handle.
   *                                          The new algorithm does everything in a single SQL statement which can be run outside of this function if needed.
   *                                          The algorithm is based on a known method for filling a polygon which counts the type and number of crossings of a
   *                                          "ray" (in this case a vertical line) across a polygon boundary. The new algorithm also has improved handling of
   *                                          multi-part geometries and also generates a starting X ordinate for the vertical "ray" using vertex averaging
   *                                          rather than the mid point of a part's MBR. This is to try and "weight" the centroid more towards where detail exists.
   *               Simon Greener - Jul 2008 - Standalone version with no dependencies other than the need for external object types.
   * @copyright  : Free for public use
   **/
     FUNCTION sdo_Centroid(
     p_geometry     IN MDSYS.SDO_GEOMETRY,
     p_start        IN NUMBER := 1,
     p_largest      IN NUMBER := 1,
     p_round_x      IN NUMBER := 3,
     p_round_y      IN NUMBER := 3,
     p_round_z      IN NUMBER := 2)
     RETURN MDSYS.SDO_GEOMETRY DETERMINISTIC;
   /**
   * Overload of main sdo_centroid function
   * @param      : p_tolerance    : Number : Single tolerance for use with all ordinates.
   *                                         Expressed in dataset units eg decimal degrees if 8311.
   *                                         See Convert_Distance for method of converting distance in meters to dataset units.
   **/
   FUNCTION sdo_centroid (
     p_geometry     IN MDSYS.SDO_Geometry,
     p_start        IN NUMBER := 1,
     p_tolerance    IN NUMBER )
     RETURN MDSYS.SDO_Geometry deterministic;
   /**
   * Overload of main sdo_centroid function
   * @param      : p_dimarray  : Number : Supplies all tolerances when processing vertices.
   *                                      Note that the function requires the sdo_tolerances inside the diminfo to be expressed in dataset
   *                                      units eg decimal degrees if 8311. But for Oracle this is in meters for long/lat and dataset units otherwise.
   *                                      Thus use of this wrapper for geodetic data IS NOT RECOMMENDED.
   *                                      See Convert_Distance for method of converting distance in meters to dataset units.
   **/
   FUNCTION Sdo_Centroid(
     p_geometry     IN MDSYS.SDO_GEOMETRY,
     p_start        IN NUMBER := 1,
     p_dimarray     IN MDSYS.SDO_DIM_ARRAY)
     RETURN MDSYS.SDO_Geometry deterministic;
   /* ----------------------------------------------------------------------------------------
   * @function   : Sdo_Multi_Centroid
   * @precis     : Generates centroids for a all parts of a multi-polygon.
   * @version    : 1.0
   * @description: The standard MDSYS.SDO_GEOM.SDO_GEOMETRY function does not guarantee
   *               that the centroid it generates falls inside the polygon.   Nor does it
   *               generate a centroid for a multi-part polygon shape.
   *               This function generates a point for every part of a 2007 multi-part polygon..
   * @param      : p_geometry : MDSYS.SDO_GEOMETRY : The polygon shape.
   * @param      : p_start    : Number : 0 = Use average of all Area's vertices for starting X centroid calculation
   *                                     1 = Use centre X of MBR
   * @param      : p_round_x  : Number : Ordinate rounding precision for X ordinates.
   * @param      : p_round_y  : Number : Ordinate rounding precision for Y ordinates.
   * @param      : p_round_z  : Number : Ordinate rounding precision for Z ordinates.
   * @return     : centroid   : MDSYS.SDO_GEOMETRY : The centroids of the parts as a multi-part shape.
   * @history    : Simon Greener - Jun 2006 - Original coding.
   * @copyright  : Free for public use
   **/
   FUNCTION SDO_Multi_Centroid(
     p_geometry  IN MDSYS.SDO_Geometry,
     p_start     IN NUMBER := 1,
     p_round_x   IN NUMBER := 3,
     p_round_y   IN NUMBER := 3,
     p_round_z   IN NUMBER := 2)
    RETURN MDSYS.SDO_Geometry deterministic;
   /**
   * Overload of main sdo_multi_centroid function
   * @param      : p_geometry  : MdSys.Sdo_Geometry : The sdo_geometry object.
   * @param      : p_start     : Number : 0 = Use average of all Area's vertices for starting X centroid calculation
   *                                      1 = Use centre X of MBR
   * @param      : p_tolerance : Number : Tolerance used when processing vertices.
   * @return     : centroid    : MDSYS.SDO_GEOMETRY : The centroids of the parts as a multi-part shape.
   * @history    : Simon Greener - Jun 2006 - Original coding.
   * @copyright  : Free for public use
   **/
   FUNCTION sdo_multi_centroid(
     p_geometry  IN MDSYS.SDO_Geometry,
     p_start     IN NUMBER := 1,
     p_tolerance IN NUMBER)
     RETURN MDSYS.SDO_Geometry deterministic;
   /**
   * Overload of main sdo_multi_centroid function
   * @param      : p_geometry  : MdSys.Sdo_Geometry : The sdo_geometry object.
   * @param      : p_start     : Number : 0 = Use average of all Area's vertices for starting X centroid calculation
   *                                      1 = Use centre X of MBR
   * @param      : p_dimarray  : Number : Supplies all tolerances when processing vertices.
   *                                      Note that the function requires the sdo_tolerances inside the diminfo to be expressed in dataset
   *                                      units eg decimal degrees if 8311. But for Oracle this is in meters for long/lat and dataset units otherwise.
   *                                      Thus use of this wrapper for geodetic data IS NOT RECOMMENDED.
   *                                      See Convert_Distance for method of converting distance in meters to dataset units.
   * @return     : centroid    : MDSYS.SDO_GEOMETRY : The centroids of the parts as a multi-part shape.
   * @history    : Simon Greener - Jun 2006 - Original coding.
   * @copyright  : Free for public use
   **/
   FUNCTION sdo_multi_centroid(
     p_geometry  IN MDSYS.SDO_Geometry,
     p_start     IN NUMBER := 1,
     p_dimarray  IN MDSYS.SDO_Dim_Array)
     RETURN MDSYS.SDO_Geometry deterministic;
 END Centroid;