Oracle CENTROID package update

Folks,

Today I checked the processing of the CENTROID_A method within the free CENTROID package that many download from my website.

That method failed more than I expected.

I apologise to all who downloaded, installed and tried to execute.

The package had some rushed changes while I was away consulting over the past few years and these changes appear to have been poorly written and tested.

I found the errors as I ported CENTROID.Centroid_A to the T_GEOMETRY object type that has superceded all the previous work.

Here is an example of the fix:

 -- p_tolerance parameter had wrong data type:
 FUNCTION Centroid_A(P_Geometry   IN Mdsys.Sdo_Geometry,
                     P_method     IN Pls_Integer DEFAULT 1,
                     P_Seed_Value IN NUMBER      DEFAULT NULL,
                     P_Dec_Places IN Pls_Integer DEFAULT 3,
                     P_Tolerance  IN NUMBER      DEFAULT 0.05,
                     p_loops      IN pls_integer DEFAULT 10)
   RETURN MDSYS.SDO_GEOMETRY DETERMINISTIC;
 .
 -- Example of processing all options.
 --
 WITH DATA AS (
   SELECT sdo_geometry(
            'POLYGON((2300 -700, 2800 -300, 2300 700, 2800 1100, 2300 1100,
                     1800 1100, 2300 800, 2000 600, 2300 600, 2300 500, 2400 400,
                     2300 400, 2300 300, 2300 200, 2500 150, 2100 100, 2500 100,
                     2300 -200, 1800 -300, 2300 -500, 2200 -400, 2400 -400, 2300 -700),
                    (2300 1000, 2400 900, 2200 900, 2300 1000),
                    (2400 -400, 2450 -300, 2550 -400, 2400 -400),
                    (2300 1000, 2400 1050, 2400 1000, 2300 1000))',NULL)
            AS geom
     FROM dual
 )
 SELECT t.COLUMN_VALUE AS method_id,
        CASE t.Column_Value
             WHEN  0 THEN 'Avg of Area''s X Ordinates as Centroid Seed'
             WHEN 10 THEN 'Avg of Area''s Y Ordinates as Centroid Seed'
             WHEN  1 THEN 'Centre X Ordinate of geom MBR as seed'
             WHEN 11 THEN 'Centre Y Ordinate of geom MBR as seed'
             WHEN  2 THEN 'User X ordinate'
             WHEN 12 THEN 'User Y ordinate'
             WHEN  3 THEN 'SDO_GEOM.SDO_CENTROID'
             WHEN  4 THEN 'SDO_GEOM.SDO_PointOnSurface'
         END AS Method_Text,
        CENTROID.Centroid_A(
          P_Geometry   => a.geom,
          p_method     => t.Column_Value,
          P_Seed_Value => CASE t.Column_Value WHEN 2 THEN /*X*/ 2035.4 WHEN 12 THEN /*Y*/ 284.6 ELSE NULL END,
          P_Dec_Places => 3,
          P_Tolerance  => 0.05,
          p_loops      => 5
        ) AS centroid
   FROM DATA a,
        TABLE(CENTROID.generate_series(0,12,1)) t
  WHERE t.Column_Value IN (0, 1, 2, 3, 4, 10, 11, 12)
  ORDER BY 2 ASC;
 .
  METHOD_ID METHOD_TEXT                                CENTROID
 ---------- ------------------------------------------ ----------------------------------------------------------------------------------------
          0 Avg OF Area's X Ordinates as Centroid Seed SDO_GEOMETRY(2001,NULL,SDO_POINT_TYPE(2322.857,-282.8575,NULL),NULL,NULL)
         10 Avg of Area's Y Ordinates AS Centroid Seed SDO_GEOMETRY(2001,NULL,SDO_POINT_TYPE(2396.4285,314.286,NULL),NULL,NULL)
          1 Centre X Ordinate OF geom MBR AS seed      SDO_GEOMETRY(2001,NULL,SDO_POINT_TYPE(2300,-300,NULL),NULL,NULL)
         11 Centre Y Ordinate OF geom MBR AS seed      SDO_GEOMETRY(2001,NULL,SDO_POINT_TYPE(2425,200,NULL),NULL,NULL)
          3 MDSYS.SDO_GEOM.SDO_CENTROID                SDO_GEOMETRY(2001,NULL,SDO_POINT_TYPE(2377.12121212121,234.772727272727,NULL),NULL,NULL)
          4 MDSYS.SDO_GEOM.SDO_PointOnSurface          SDO_GEOMETRY(2001,NULL,SDO_POINT_TYPE(2300,-700,NULL),NULL,NULL)
          2 USER X ordinate                            SDO_GEOMETRY(2001,NULL,SDO_POINT_TYPE(2035.4,-323.54,NULL),NULL,NULL)
         12 USER Y ordinate                            SDO_GEOMETRY(2001,NULL,SDO_POINT_TYPE(2403.85,284.6,NULL),NULL,NULL)
 .
  8 ROWS selected

By the previous work, I mean all the free packages available for download for Oracle:

  • GEOM
  • AFFINE
  • CENTROID
  • TOOLS
  • COGO
  • KML
  • TESSELATE
  • TIN and,
  • LINEAR.

These get nothing done to them anymore except for fixing serious bugs.

What I am concentrating on are:

  • New Oracle geometry types T_VERTEX T_GEOMETRY T_SEGMENT T_MBR T_VECTOR3D These are based on a port of all the original free PLSQL work above as part of the writing of the book Applying and Extending Oracle Spatial
  • EXPORTER (Java)
  • SC4O (Java/JTS)
  • GeoRaptor (but help is needed to port to SQL Server 4+)
    • SQL Server Spatial TSQL functions (This is a SQL Server version of the New Oracle geometry types in particular LRS).
    • MySQL procedures and functions (in its infancy).

For those of you who tried the CENTROID package and would like a corrected version, please re-download it from this website.