CENTROID package – Tips for Use – The Spatial Database Advisor

My CENTROID package is a very popular download from my website.

In this article I want to expose a couple of issues that catch new users of my package. These issues are documented in the header of the relevant functions but because there is no separate end user documentation (other than the blog articles on this website), these issues are often missed and so cause problems.

When using the CENTROID package be careful of the following two things.

Circular Arcs

CENTROID does not support polygons or linestrings with circular arcs. Before calling the SDO_CENTROID function (or more directly, the CENTROID_L or CENTROID_A functions), you have to convert (or stroke) them to their vertex-to-vertex equivalent beforehand either by using Oracle’s sdo_geom.sdo_arc_densify function or CENTROID.ConvertGeometry() function.

 Prompt ConvertGeometry - Compound linestring
 SELECT &&defaultSchema..CENTROID.convertGeometry(
        SDO_GEOMETRY(
            2002,
            NULL,
            NULL,
            SDO_ELEM_INFO_ARRAY(1,4,2, 1,2,1, 3,2,2), -- compound line string
            SDO_ORDINATE_ARRAY(252000,5526000,252700,5526700,252500,5526700,252500,5526500)
          ),0.1)
   FROM dual;
 .
 Prompt ConvertGeometry - Compound polygon
 SELECT &&defaultSchema..CENTROID.convertGeometry(
        SDO_GEOMETRY(
           2003,  -- two-dimensional polygon
           NULL,
           NULL,
           SDO_ELEM_INFO_ARRAY(1,1005,2, 1,2,1, 5,2,2), -- compound polygon
           SDO_ORDINATE_ARRAY(6,10, 10,1, 14,10, 10,14, 6,10)
          ),0.5)
   FROM dual;

Decimal Digits of Precision vs Tolerance

The CENTROID package exposes two methods for defining the precision of the data you are working with.

Where a function takes a p_tolerance value, the value represents the precision of an ordinate as a decimal value expressed in the same units as the data. So, for planar data in meters, a tolerance of 0.001 (or 0.0005) says the ordinate is precise to 1mm. For data expressed in decimal degrees, the same principle applies so if a longitude is accurate to 7 decimal digits one would use supply p_tolerance with a 0.0000001 decimal degree value. Oracle tolerances for long/lat data are provided in meters eg 0.05m (5 cm) – this is not supported by the CENTROID package functions.
Where a function takes p_round_x/p_round_y parameter values these are integer values that represent the number of decimal digits of precision of each ordinate. So, for planar (eg UTM) metric data a value of 3 represents 1mm (or p_tolerance == 0.001 ). For long/lat data you should use something like 6-9 digits of precision. If you use the default values, then you will most likely get an exception with a warning.

 WITH geom AS (
 SELECT
 MDSYS.SDO_GEOMETRY(2003, 8265, NULL,
 MDSYS.SDO_ELEM_INFO_ARRAY(1, 1003, 1),
 MDSYS.SDO_ORDINATE_ARRAY(
 -79.230383, 35.836761, -79.230381, 35.836795, -79.230414, 35.83683, -79.230468, 35.836857,
 -79.230502, 35.836878, -79.23055, 35.836906, -79.23059, 35.836922,  -79.230617, 35.836945,
 -79.230658, 35.836966, -79.230671, 35.837005, -79.230698, 35.837048, -79.230704, 35.837082,
 -79.230712, 35.83712, -79.230711, 35.837192, -79.230725, 35.83722, -79.230779, 35.837247,
 -79.230792, 35.837202, -79.230785, 35.837114, -79.23078, 35.837087, -79.230765, 35.837038,
 -79.230718, 35.836972, -79.230671, 35.836917, -79.230637, 35.8369, -79.23061, 35.836873,
 -79.230583, 35.83685, -79.230529, 35.836818, -79.230489, 35.83679, -79.230456, 35.836774, -79.230383, 35.836761)) AS geom
 FROM DUAL
 ) 
 SELECT mdsys.sdo_geom.sdo_centroid( a.geom, 0.005 ) AS sdo_centroid,
        &&defaultSchema..centroid.sdo_centroid(p_geometry=>a.geom,p_start=>1,p_tolerance=>0.0000001) AS centroidTol,
        &&defaultSchema..centroid.sdo_centroid(p_geometry=>a.geom,p_start=>1,p_largest=>1,p_round_x=>8,p_round_y=>8,p_round_z=>1) AS centroidRound
   FROM geom a;
 -- Results
 SDO_CENTROID
 -----------------------------------------------------------------------------------------
 CENTROIDTOL
 -----------------------------------------------------------------------------
 CENTROIDROUN
 -----------------------------------------------------------------------------
 SDO_GEOMETRY(2001,8265,SDO_POINT_TYPE(-79.2306317479159,35.8369772869256,NULL),NULL,NULL)
 SDO_GEOMETRY(2001,8265,SDO_POINT_TYPE(-79.2305865,35.8368868,NULL),NULL,NULL)
 SDO_GEOMETRY(2001,8265,SDO_POINT_TYPE(-79.2305865,35.83688679,NULL),NULL,NULL)
 --
 -- Test Long/Lat data with defaults
 -- Same WITH clause as above
 SELECT &&defaultSchema..centroid.sdo_centroid(p_geometry=>a.geom,p_start=>1,p_largest=>1) AS centroidRound
   FROM geom a;
 -- Results
 --
 Error report:
 SQL Error: ORA-20001: ORA-20105: sdo_centroid calculation failed: perhaps supplied tolerance IS NOT IN projection units eg 0.000001 DECIMAL degrees?
 ORA-06512: at "CODESYS.CENTROID", line 1215
 ORA-01403: no DATA found
 ORA-06512: at line 1

Problems, Bugs and etc

If you find a problem with the Windows installation script (though not the Linux/Unix script) or the code itself, please contact me immediately as I normally fix this stuff within a few hours of receiving the email. The CENTROID code has been stable for many years except that during February/March 2012 I homogenized all my code’s (including PL/SQL packages other than GEOM) use of a common set of TYPEs and this has led to one or two minor issues which were picked up by some users in Europe and which I fixed overnight. I expect any issues have now settled down and so you shouldn’t have any issues: but if you do contact me via email.

Leave a Reply Cancel reply