Go to content Go to navigation and search

Home

Free Software Download

Download Code, Packages and Installers

Free Software Articles and Documentation


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.

CENTROID Package Documentation

Monday August 15 2011 at 13:37

Required Types

  1. DEFINE defaultSchema = '&1'
  2. /* Have to be totally standalone from types used in rest of packages.
  3. ** Yes, this means overlap, but that's life.
  4. */
  5. CREATE OR REPLACE TYPE &&defaultSchema..T_Vertex AS OBJECT (
  6.    x  NUMBER,
  7.    y  NUMBER,
  8.    z  NUMBER,
  9.    w  NUMBER,
  10.    id NUMBER
  11. );
  12. /
  13. SHOW errors
  14. GRANT EXECUTE ON &&defaultSchema..T_Vertex TO public WITH GRANT OPTION;
  15. CREATE OR REPLACE TYPE &&defaultSchema..T_Vector IS OBJECT (
  16.     Id         INTEGER,
  17.     StartCoord &&defaultSchema..T_Vertex,
  18.     Endcoord   &&defaultSchema..T_Vertex
  19. );
  20. /
  21. SHOW errors
  22. GRANT EXECUTE ON &&defaultSchema..T_Vector TO public WITH GRANT OPTION;
  23. -- Geometry types....
  24. --
  25. CREATE OR REPLACE TYPE &&defaultSchema..t_Geometry AS OBJECT (
  26.    geometry mdsys.sdo_geometry
  27. );
  28. /
  29. SHOW errors
  30. GRANT EXECUTE ON &&defaultSchema..t_Geometry TO public WITH GRANT OPTION;
  31. CREATE OR REPLACE TYPE &&defaultSchema..t_GeometrySet AS TABLE OF &&defaultSchema..t_Geometry;
  32. /
  33. SHOW errors
  34. GRANT EXECUTE ON &&defaultSchema..t_GeometrySet TO public WITH GRANT OPTION;

Package Header.

  1. CREATE OR REPLACE package Centroid
  2. AUTHID CURRENT_USER
  3. IS
  4.    TYPE t_numbers     IS TABLE OF NUMBER;
  5.    TYPE t_Vectors     IS TABLE OF &&defaultSchema..t_Vector;
  6.   /** @NOTE : The schema owning this package, and all calling schemas (see AUTHID above) MUST have
  7.   *           direct SELECT permissions on DBA_REGISTRY for this package to compile and run properly.
  8.   *           SQL> connect sys/****@sid
  9.   *           SQL> GRANT SELECT ON DBA_REGISTRY TO &&defaultSchema.;
  10.   *   @NOTE : These packages assume a minimum of 9iR2 as the SDO_MBR function uses SDO_AGGR_MBR for
  11.   *           Locator users. If you are on 9iR1 then modify the SDO_MBR function to uncomment out
  12.   *           the SQL that doesn't use SDO_AGGR_MBR and comment out the SDO_AGGR_MBR SQL.
  13.   *   @NOTE : This package uses &&defaultSchema..T_VERTEX_TYPE which Oracle changed the definition of at 10g
  14.   *           by adding an ID field. If you want to compile the package on 9i, manually change the
  15.   *           code in ConvertGeometry so that it does not reference the last, ID, field.
  16.   *   @NOTE : If sdo_geometry linestrings or polygons contain circular arcs they must be "stroked" before
  17.   *           Calling the SDO_CENTROID function. If you have SPATIAL you can use sdo_geom.sdo_arc_densify().
  18.   *           If not use this package's ConvertGeometry() function.
  19.   *           SDO_GEOM.SDO_ARC_DENSIFY() function before calling sdo_centroid.
  20.   *   @NOTE : SDO_Area does not work for non-2D geometries.
  21.   **/
  22.   /** ----------------------------------------------------------------------------------------
  23.   * @function   : Generate_Series
  24.   * @precis     : Function that generates a series of numbers mimicking PostGIS's function with
  25.   *               the same name
  26.   * @version    : 1.0
  27.   * @usage      : Function generate_series(p_start pls_integer,
  28.   *                                        p_end   pls_integer,
  29.   *                                        p_step  pls_integer := 1)
  30.   *                 Return centroid.t_integers Pipelined;
  31.   *               eg SELECT s.* FROM TABLE(generate_series(1,1000,10)) s;
  32.   * @param      : p_start   : Starting value
  33.   * @paramtype  : p_start   : Integer
  34.   * @param      : p_end     : Ending value.
  35.   * @paramtype  : p_end     : Integer
  36.   * @return     : p_step    : The step value of the increment between start and end
  37.   * @rtnType    : p_step    : Integer
  38.   * @history    : Simon Greener - June 2008 - Original coding.
  39.   * @copyright  : Free for public use
  40.   **/
  41.   FUNCTION generate_series(p_start IN pls_integer,
  42.                            p_end   IN pls_integer,
  43.                            p_step  IN pls_integer := 1 )
  44.        RETURN &&defaultSchema..centroid.t_numbers Pipelined;
  45.   /** ----------------------------------------------------------------------------------------
  46.   * @function   : SDO_MBR
  47.   * @precis     : Returns Minimum Bounding Rectangle of a given geometry.
  48.   * @version    : 1.0
  49.   * @usage      : Function SDO_MBR ( p_geometry IN MDSYS.SDO_GEOMETRY )
  50.   *                 Return MDSYS.SDO_GEOMETRY Deterministic;
  51.   * @example    : SELECT sdo_mbr(a.geom,0.01)
  52.   *                 FROM ProjCompound2D a,
  53.   *                      TABLE( &&defaultSchema..geom.GetElemInfo( a.geom ) ) ei
  54.   *                WHERE a.geom is not null;
  55.   * @param      : p_geometry  : A shape.
  56.   * @paramtype  : p_geomery   : MDSYS.SDO_GEOMETRY
  57.   * @param      : p_tolerance : Dimarray sdo_tolerance value
  58.   * @paramtype  : p_tolerance : number
  59.   * @return     : geometry    : A 2003 vertex described polygon
  60.   * @rtnType    : sdo_geometry : MDSYS.SDO_GEOMETRY
  61.   * @note       : Function is pipelined
  62.   * @history    : Simon Greener - Jul 2008 - Original coding.
  63.   * @copyright  : Free for public use
  64.   **/
  65.   FUNCTION SDO_MBR( p_geometry  IN MDSYS.SDO_GEOMETRY )
  66.            RETURN MDSYS.SDO_GEOMETRY Deterministic;
  67.   /** ----------------------------------------------------------------------------------------
  68.   * @function   : SDO_Length
  69.   * @precis     : Function which computes length of linestrings or boundaries of polygons.
  70.   * @version    : 1.0
  71.   * @description: These are wrapper functions over SDO_3GL.LENGTH_AREA procedures that are
  72.   *               not mentioned in Oracle's licensing as being functions limited to Spatial (EE)
  73.   * @usage      : Function SDO_Length ( p_geometry IN MDSYS.SDO_GEOMETRY,
  74.   *                                     p_tolerance IN Number
  75.   *                                     p_units    IN VarChar2 )
  76.   *                 Return Number Deterministic;
  77.   *               eg fixedShape := &&defaultSchema..geom.length(shape,diminfo);
  78.   * @param      : p_geometry   : A valid sdo_geometry.
  79.   * @paramtype  : p_geomery    : MDSYS.SDO_GEOMETRY
  80.   * @param      : p_tolerance  : The dimarray describing the shape.
  81.   * @paramtype  : p_tolerance  : MDSYS.SDO_DIM_ARRAY
  82.   * @param      : p_units      : The units in which the length is to be calculated eg meters, miles etc
  83.   * @paramtype  : p_units      : varchar2
  84.   * @return     : length       : Length of linestring or boundary of a polygon in required unit of measure
  85.   * @rtnType    : length       : Number
  86.   * @note       : Supplied p_units should exist in mdsys.SDO_UNITS_OF_MEASURE
  87.   * @history    : Simon Greener - Oct 2007 - Original coding.
  88.   * @copyright  : Free for public use
  89.   **/
  90.   FUNCTION SDO_Length( p_geometry  IN mdsys.sdo_geometry,
  91.                        p_tolerance IN NUMBER,
  92.                        p_units     IN varchar2 := 'Meter' )
  93.     RETURN NUMBER deterministic;
  94.   /** ----------------------------------------------------------------------------------------
  95.   * @function   : SDO_AREA
  96.   * @precis     : Function which computes area of a polygon.
  97.   * @version    : 1.0
  98.   * @description: These are wrapper functions over SDO_3GL.LENGTH_AREA procedures that are
  99.   *               not mentioned in Oracle's licensing as being functions limited to Spatial (EE)
  100.   * @usage      : Function SDO_Area ( p_geometry IN MDSYS.SDO_GEOMETRY,
  101.   *                                   p_dimarray IN MDSYS.SDO_DIM_ARRAY
  102.   *                                   p_units    IN VarChar2 )
  103.   *                 Return Number Deterministic;
  104.   *               eg fixedShape := sdo_area(shape,tolerance);
  105.   * @param      : p_geometry   : A valid sdo_geometry.
  106.   * @paramtype  : p_geomery    : MDSYS.SDO_GEOMETRY
  107.   * @param      : p_tolerance  : The vertex tolerance .
  108.   * @paramtype  : p_tolerance  : NUMBER
  109.   * @return     : area         : Area of a polygon in required unit of measure
  110.   * @rtnType    : area         : Number
  111.   * @note       : Supplied p_units should exist in mdsys.SDO_UNITS_OF_MEASURE
  112.   * @history    : Simon Greener - Oct 2007 - Original coding.
  113.   * @copyright  : Free for public use
  114.   **/
  115.   FUNCTION SDO_Area( p_geometry   IN mdsys.sdo_geometry,
  116.                      p_tolerance  IN NUMBER,
  117.                      p_units      IN varchar2 := 'Square Meter' )
  118.     RETURN NUMBER Deterministic;
  119.   /** ----------------------------------------------------------------------------------------
  120.   * @function   : ConvertGeometry
  121.   * @precis     : Function which converts optimized rectangle components in a polygon to their
  122.   *               stroked equivalent.
  123.   * @version    : 1.0
  124.   * @description: The centroid algorithm works with vector representations of the sides
  125.   *               of a polygon generated by GetVector. Optimized rectangle are not directly
  126.   *               supported in either the GetVector or sdo_centroid functions. If these exist
  127.   *               the sdo_geometry should be put through this function before calling sdo_centroid.
  128.   * @usage      : Function Converte_Geometry ( p_geometry IN MDSYS.SDO_GEOMETRY )
  129.   *                 Return MdSys.Sdo_Geometry Deterministic;
  130.   *               eg centroid := centroid.sdo_centroid(centroid.ConvertGeometry(shape),tolerance);
  131.   * @param      : p_geometry   : A valid sdo_geometry.
  132.   * @paramtype  : p_geomery    : MDSYS.SDO_GEOMETRY
  133.   * @param      : p_Arc2Chord  : ArcToChord separation expressed in dataset units for converting arcs to stroked lines.
  134.   * @paramtype  : p_Arc2Chord  : Number
  135.   * @return     : geometry     : Converted, valid, geometry
  136.   * @rtnType    : sdo_geometry : MDSYS.SDO_GEOMETRY
  137.   * @note       : Oracle changed the definition of T_VERTEX_TYPE at 10g by adding and ID field. If you
  138.   *               compiling on 9i manually change the code in ConvertGeometry so that it does not
  139.   *               reference the ID field.
  140.   * @history    : Simon Greener - July 2008 - Original coding.
  141.   * @copyright  : Free for public use
  142.   **/
  143.   FUNCTION ConvertGeometry(p_geometry  IN mdsys.sdo_geometry,
  144.                            p_Arc2Chord IN NUMBER := 0.1)
  145.     RETURN mdsys.sdo_geometry Deterministic;
  146.   FUNCTION SDO_ARC_DENSIFY(p_geometry  IN mdsys.sdo_geometry,
  147.                            p_Arc2Chord IN NUMBER := 0.1)
  148.     RETURN mdsys.sdo_geometry Deterministic;
  149.   /** ----------------------------------------------------------------------------------------
  150.   * @function   : GetVector
  151.   * @precis     : Places a geometry's coordinates into a pipelined vector data structure.
  152.   * @version    : 3.0
  153.   * @description: Loads the coordinates of a linestring, polygon geometry into a
  154.   *               pipelined vector data structure for easy manipulation by other functions
  155.   *               such as geom.SDO_Centroid.
  156.   * @usage      : select *
  157.   *                 from myshapetable a,
  158.   *                      table(centroid.GetVector(a.shape));
  159.   * @param      : p_geometry  : MDSYS.SDO_GEOMETRY : A geographic shape.
  160.   * @return     : geomVector  : VectorSetType : The vector pipelined.
  161.   * @requires   : Global data types coordRec, vectorRec and VectorSetType
  162.   * @history    : Simon Greener - July 2006 - Original coding from GetVector
  163.   * @history    : Simon Greener - July 2008 - Re-write to be standalone of other packages eg GF
  164.   * @history    : Simon Greener - October 2008 - Removed 2D limits
  165.   * @copyright  : Free for public use
  166.   **/
  167.   FUNCTION GetVector(P_Geometry IN Mdsys.Sdo_Geometry)
  168.     RETURN &&defaultSchema..CENTROID.t_Vectors pipelined;
  169.   /** =================== CENTROID Functions ====================== **/
  170.   /* ----------------------------------------------------------------------------------------
  171.   * @function   : centroid_p
  172.   * @precis     : Generates centroid for a point (itself) or multipoint.
  173.   * @version    : 1.3
  174.   * @description: This function creates centroid of multipoint via averaging of ordinates.
  175.   * @param      : p_geometry     : MDSYS.SDO_GEOMETRY : The geometry object.
  176.   * @param      : p_round_x      : Number : Ordinate rounding precision for X ordinates.
  177.   * @param      : p_round_y      : Number : Ordinate rounding precision for Y ordinates.
  178.   * @param      : p_round_z      : Number : Ordinate rounding precision for Z ordinates.
  179.   * @return     : centroid       : MDSYS.SDO_GEOMETRY : The centroid.
  180.   * @requires   : GetVector()
  181.   * @history    : Simon Greener - Jul 2008 - Original coding of centroid_p as internal function
  182.   * @history    : Simon Greener - Jan 2012 - Exposed internal function.
  183.   * @copyright  : Free for public use
  184.   **/
  185.   FUNCTION centroid_p(p_geometry IN mdsys.sdo_geometry,
  186.                       p_round_x  IN NUMBER := 3,
  187.                       p_round_y  IN NUMBER := 3,
  188.                       p_round_z  IN NUMBER := 2)
  189.     RETURN MDSYS.SDO_GEOMETRY DETERMINISTIC;
  190.   /* ----------------------------------------------------------------------------------------
  191.   * @function   : Centroid_A
  192.   * @precis     : Generates centroid for a polygon.
  193.   * @version    : 1.3
  194.   * @description: The standard MDSYS.SDO_GEOM.SDO_CENTROID function does not guarantee
  195.   *               that the centroid it generates falls inside the polygon.
  196.   *               This function ensures that the centroid of any arbitrary polygon falls within the polygon.
  197.   * @param      : p_geometry     : MDSYS.SDO_GEOMETRY : The geometry object.
  198.   * @param      : p_start        : Number : 0 = Use average of all Area's vertices for starting X centroid calculation
  199.   *                                         1 = Use centre X of MBR
  200.   *                                         2 = User supplied starting seed X
  201.   * @param      : p_seed_x       : Number : Starting X ordinate for which a Y that is inside the polygon is returned.
  202.   * @param      : p_round_x      : Number : Ordinate rounding precision for X ordinates.
  203.   * @param      : p_round_y      : Number : Ordinate rounding precision for Y ordinates.
  204.   * @param      : p_round_z      : Number : Ordinate rounding precision for Z ordinates.
  205.   * @return     : centroid       : MDSYS.SDO_GEOMETRY : The centroid.
  206.   * @requires   : GetVector()
  207.   * @history    : Simon Greener - Jul 2008 - Original coding of centroid_a as internal function
  208.   * @history    : Simon Greener - Jan 2012 - Exposed internal function. Added p_seed_x support.
  209.   * @copyright  : Free for public use
  210.   **/
  211.   FUNCTION centroid_a(p_geometry IN mdsys.sdo_geometry,
  212.                       p_start    IN NUMBER := 1,
  213.                       p_seed_x   IN NUMBER := NULL,
  214.                       p_round_x  IN NUMBER := 3,
  215.                       p_round_y  IN NUMBER := 3,
  216.                       p_round_z  IN NUMBER := 2)
  217.     RETURN MDSYS.SDO_GEOMETRY DETERMINISTIC;
  218.   /* ----------------------------------------------------------------------------------------
  219.   * @function   : Centroid_1
  220.   * @precis     : Generates centroid for a linestring.
  221.   * @version    : 1.3
  222.   * @description: The standard MDSYS.SDO_GEOM.SDO_CENTROID function does not guarantee
  223.   *               that the centroid it generates falls inside the polygon.
  224.   *               This function ensures that the centroid of any arbitrary polygon falls within the polygon.
  225.   * @param      : p_geometry           : MDSYS.SDO_GEOMETRY : The geometry object.
  226.   * @param      : p_position_as_ratio  : Number : Position along multi-line/line where "centroid" created.
  227.   * @param      : p_round_x            : Number : Ordinate rounding precision for X ordinates.
  228.   * @param      : p_round_y            : Number : Ordinate rounding precision for Y ordinates.
  229.   * @param      : p_round_z            : Number : Ordinate rounding precision for Z ordinates.
  230.   * @return     : centroid             : MDSYS.SDO_GEOMETRY : The centroid.
  231.   * @requires   : GetVector()
  232.   * @history    : Simon Greener - Jul 2008 - Original coding of centroid_l as internal function
  233.   * @history    : Simon Greener - Jan 2012 - Exposed internal function.
  234.   * @copyright  : Free for public use
  235.   **/
  236.   FUNCTION centroid_l(p_geometry          IN mdsys.sdo_geometry,
  237.                       p_position_as_ratio IN NUMBER := 0.5,
  238.                       p_round_x           IN NUMBER := 3,
  239.                       p_round_y           IN NUMBER := 3,
  240.                       p_round_z           IN NUMBER := 2)
  241.     RETURN MDSYS.SDO_GEOMETRY DETERMINISTIC;
  242.   /* ----------------------------------------------------------------------------------------
  243.   * @function   : SDO_Centroid
  244.   * @precis     : Generates centroid for a polygon.
  245.   * @version    : 1.5
  246.   * @description: The standard MDSYS.SDO_GEOM.SDO_CENTROID function does not guarantee
  247.   *               that the centroid it generates falls inside the polygon.   Nor does it
  248.   *               generate a centroid for a multi-part polygon shape.
  249.   *               This function ensures that the centroid of any arbitrary polygon
  250.   *               falls within the polygon. Also provides centroid functions for multipoints and linestrings.
  251.   * @param      : p_geometry     : MDSYS.SDO_GEOMETRY : The geometry object.
  252.   * @param      : p_start        : Number : 0 = Use average of all Area's vertices for starting X centroid calculation
  253.   *                                         1 = Use centre X of MBR
  254.   * @param      : p_largest      : Number : 0 = Use smallest of any multipart geometry.
  255.   *                                         1 = Use largest of any multipart geometry.
  256.   * @param      : p_round_x      : Number : Ordinate rounding precision for X ordinates.
  257.   * @param      : p_round_y      : Number : Ordinate rounding precision for Y ordinates.
  258.   * @param      : p_round_z      : Number : Ordinate rounding precision for Z ordinates.
  259.   * @return     : centroid       : MDSYS.SDO_GEOMETRY : The centroid.
  260.   * @requires   : GetVector()
  261.   * @history    : Simon Greener - Mar 2008 - Total re-write of algorithm following on from cases the original algorithm didn't handle.
  262.   *                                          The new algorithm does everything in a single SQL statement which can be run outside of this function if needed.
  263.   *                                          The algorithm is based on a known method for filling a polygon which counts the type and number of crossings of a
  264.   *                                          "ray" (in this case a vertical line) across a polygon boundary. The new algorithm also has improved handling of
  265.   *                                          multi-part geometries and also generates a starting X ordinate for the vertical "ray" using vertex averaging
  266.   *                                          rather than the mid point of a part's MBR. This is to try and "weight" the centroid more towards where detail exists.
  267.   *               Simon Greener - Jul 2008 - Standalone version with no dependencies other than the need for external object types.
  268.   * @copyright  : Free for public use
  269.   **/
  270.     FUNCTION sdo_Centroid(
  271.     p_geometry     IN MDSYS.SDO_GEOMETRY,
  272.     p_start        IN NUMBER := 1,  
  273.     p_largest      IN NUMBER := 1,  
  274.     p_round_x      IN NUMBER := 3,
  275.     p_round_y      IN NUMBER := 3,
  276.     p_round_z      IN NUMBER := 2)
  277.     RETURN MDSYS.SDO_GEOMETRY DETERMINISTIC;
  278.   /**
  279.   * Overload of main sdo_centroid function
  280.   * @param      : p_tolerance    : Number : Single tolerance for use with all ordinates.
  281.   *                                         Expressed in dataset units eg decimal degrees if 8311.
  282.   *                                         See Convert_Distance for method of converting distance in meters to dataset units.
  283.   **/
  284.   FUNCTION sdo_centroid (
  285.     p_geometry     IN MDSYS.SDO_Geometry,
  286.     p_start        IN NUMBER := 1,
  287.     p_tolerance    IN NUMBER )
  288.     RETURN MDSYS.SDO_Geometry deterministic;
  289.   /**
  290.   * Overload of main sdo_centroid function
  291.   * @param      : p_dimarray  : Number : Supplies all tolerances when processing vertices.
  292.   *                                      Note that the function requires the sdo_tolerances inside the diminfo to be expressed in dataset
  293.   *                                      units eg decimal degrees if 8311. But for Oracle this is in meters for long/lat and dataset units otherwise.
  294.   *                                      Thus use of this wrapper for geodetic data IS NOT RECOMMENDED.
  295.   *                                      See Convert_Distance for method of converting distance in meters to dataset units.
  296.   **/
  297.   FUNCTION Sdo_Centroid(
  298.     p_geometry     IN MDSYS.SDO_GEOMETRY,
  299.     p_start        IN NUMBER := 1,
  300.     p_dimarray     IN MDSYS.SDO_DIM_ARRAY)
  301.     RETURN MDSYS.SDO_Geometry deterministic;
  302.   /* ----------------------------------------------------------------------------------------
  303.   * @function   : Sdo_Multi_Centroid
  304.   * @precis     : Generates centroids for a all parts of a multi-polygon.
  305.   * @version    : 1.0
  306.   * @description: The standard MDSYS.SDO_GEOM.SDO_GEOMETRY function does not guarantee
  307.   *               that the centroid it generates falls inside the polygon.   Nor does it
  308.   *               generate a centroid for a multi-part polygon shape.
  309.   *               This function generates a point for every part of a 2007 multi-part polygon..
  310.   * @param      : p_geometry : MDSYS.SDO_GEOMETRY : The polygon shape.
  311.   * @param      : p_start    : Number : 0 = Use average of all Area's vertices for starting X centroid calculation
  312.   *                                     1 = Use centre X of MBR
  313.   * @param      : p_round_x  : Number : Ordinate rounding precision for X ordinates.
  314.   * @param      : p_round_y  : Number : Ordinate rounding precision for Y ordinates.
  315.   * @param      : p_round_z  : Number : Ordinate rounding precision for Z ordinates.
  316.   * @return     : centroid   : MDSYS.SDO_GEOMETRY : The centroids of the parts as a multi-part shape.
  317.   * @history    : Simon Greener - Jun 2006 - Original coding.
  318.   * @copyright  : Free for public use
  319.   **/  
  320.   FUNCTION SDO_Multi_Centroid(
  321.     p_geometry  IN MDSYS.SDO_Geometry,
  322.     p_start     IN NUMBER := 1,
  323.     p_round_x   IN NUMBER := 3,
  324.     p_round_y   IN NUMBER := 3,
  325.     p_round_z   IN NUMBER := 2)
  326.    RETURN MDSYS.SDO_Geometry deterministic;
  327.   /**
  328.   * Overload of main sdo_multi_centroid function
  329.   * @param      : p_geometry  : MdSys.Sdo_Geometry : The sdo_geometry object.
  330.   * @param      : p_start     : Number : 0 = Use average of all Area's vertices for starting X centroid calculation
  331.   *                                      1 = Use centre X of MBR
  332.   * @param      : p_tolerance : Number : Tolerance used when processing vertices.
  333.   * @return     : centroid    : MDSYS.SDO_GEOMETRY : The centroids of the parts as a multi-part shape.
  334.   * @history    : Simon Greener - Jun 2006 - Original coding.
  335.   * @copyright  : Free for public use
  336.   **/
  337.   FUNCTION sdo_multi_centroid(
  338.     p_geometry  IN MDSYS.SDO_Geometry,
  339.     p_start     IN NUMBER := 1,
  340.     p_tolerance IN NUMBER)
  341.     RETURN MDSYS.SDO_Geometry deterministic;
  342.   /**
  343.   * Overload of main sdo_multi_centroid function
  344.   * @param      : p_geometry  : MdSys.Sdo_Geometry : The sdo_geometry object.
  345.   * @param      : p_start     : Number : 0 = Use average of all Area's vertices for starting X centroid calculation
  346.   *                                      1 = Use centre X of MBR
  347.   * @param      : p_dimarray  : Number : Supplies all tolerances when processing vertices.
  348.   *                                      Note that the function requires the sdo_tolerances inside the diminfo to be expressed in dataset
  349.   *                                      units eg decimal degrees if 8311. But for Oracle this is in meters for long/lat and dataset units otherwise.
  350.   *                                      Thus use of this wrapper for geodetic data IS NOT RECOMMENDED.
  351.   *                                      See Convert_Distance for method of converting distance in meters to dataset units.
  352.   * @return     : centroid    : MDSYS.SDO_GEOMETRY : The centroids of the parts as a multi-part shape.
  353.   * @history    : Simon Greener - Jun 2006 - Original coding.
  354.   * @copyright  : Free for public use
  355.   **/
  356.   FUNCTION sdo_multi_centroid(
  357.     p_geometry  IN MDSYS.SDO_Geometry,
  358.     p_start     IN NUMBER := 1,
  359.     p_dimarray  IN MDSYS.SDO_Dim_Array)
  360.     RETURN MDSYS.SDO_Geometry deterministic;
  361. END Centroid;

This package can be downloaded via the download page.

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 [4]

Hi Simon, I was trying your package but I get:
“ORA-06553: PLS-306: wrong number or types of arguments in call to ‘OGC_CENTROID’”
My db is a 11.2.0.2… any idea? Thanks! Stefano

— Stefano · 30 May 2012, 01:21 · #

Not unless you give me a LOT more information.

  1. What function are you calling?
  2. How are you calling the function? (Give me the SQL!!!)
  3. What line in the package is causing the problem (you appear to have truncated the error message).

S

— Simon Greener · 30 May 2012, 11:38 · #

Hi, I have the same problem.

The SQL:
SELECT codesys.centroid.sdo_multi_centroid(my_polygon, 1, 0.005) FROM my_table;

The error message:
SQL1.sql: Error (1501,1): ORA-06553: PLS-306: wrong number or types of arguments in call to ‘OGC_CENTROID’
ORA-06512: at “CODESYS.CENTROID”, line 1501
ORA-06512: at “CODESYS.CENTROID”, line 1519
ORA-06512: at “CODESYS.CENTROID”, line 1583
ORA-06512: at line 1

— Keeper · 1 February 2013, 03:41 · #

Note my previous requirements:

1. Function (DONE)
2. How (DONE – EXCEPT THAT I CAN’T REPLICATE THE ERROR BECAUSE YOU HAVE NOT PROVIDED ME WITH THE ACTUAL GEOMETRY)
3. LINE (DONE)

So, can you please supply me with the test geometry (email me it if you wish) as I cannot see how my CENTROID package even uses OGC_CENTROID….

regards
Simon

— Simon Greener · 1 February 2013, 12:07 · #
META HTTP-EQUIV=