Finding centre and radius of a circular geometry

Here is a method for finding the centre of a circle and its radius from a polygon (x003) sdo_geometry.

``` CREATE OR REPLACE
FUNCTION FindCircle (p_polygon IN mdsys.sdo_geometry)
RETURN mdsys.sdo_point_type deterministic
IS
v_centre  mdsys.sdo_point_type;
v_pt1     mdsys.vertex_type;
v_pt2     mdsys.vertex_type;
v_pt3     mdsys.vertex_type;
dA        NUMBER;
dB        NUMBER;
dC        NUMBER;
dD        NUMBER;
dE        NUMBER;
dF        NUMBER;
dG        NUMBER;
BEGIN
IF (p_polygon IS NULL) THEN
RETURN NULL;
END IF;
IF (p_polygon.get_gtype() NOT IN (3,7) ) THEN
RETURN NULL;
END IF;
-- Grab first three vertices for checking
--
v_pt1 := mdsys.sdo_util.getVertices(p_polygon)(1);
v_pt2 := mdsys.sdo_util.getVertices(p_polygon)(2);
v_pt3 := mdsys.sdo_util.getVertices(p_polygon)(3);
dA := v_pt2.X - v_pt1.X;
dB := v_pt2.Y - v_pt1.Y;
dC := v_pt3.X - v_pt1.X;
dD := v_pt3.Y - v_pt1.Y;
dE := dA * (v_pt1.X + v_pt2.X) + dB * (v_pt1.Y + v_pt2.Y);
dF := dC * (v_pt1.X + v_pt3.X) + dD * (v_pt1.Y + v_pt3.Y);
dG := 2.0 * (dA * (v_pt3.Y - v_pt2.Y) - dB * (v_pt3.X - v_pt2.X));
-- If dG is zero then the three points are collinear and no finite-radius
-- circle through them exists.
IF ( dG = 0 ) THEN
RETURN NULL;
ELSE
v_centre := NEW mdsys.sdo_point_type((dD * dE - dB * dF) / dG,
(dA * dF - dC * dE) / dG,
NULL);
v_centre.Z := SQRT(POWER(v_pt1.X - v_centre.X,2) + POWER(v_pt1.Y - v_centre.Y,2) );
RETURN v_centre;
END IF;
END FindCircle;
```

Some examples:

``` SELECT codesys.findCircle(mdsys.sdo_geom.sdo_buffer(mdsys.sdo_geometry(2001,NULL,sdo_point_type(10,10,NULL),NULL,NULL),10,0.02)) AS centre
FROM dual;
.
CENTRE
------------------------------
MDSYS.SDO_POINT_TYPE(10,10,10)
.
SELECT codesys.findCircle(SDO_GEOMETRY (2003, NULL, NULL, SDO_ELEM_INFO_ARRAY (1,1003,4),SDO_ORDINATE_ARRAY (15,145, 10,150, 20,150))) AS centre
FROM dual;
.
CENTRE
------------------------------
MDSYS.SDO_POINT_TYPE(15,150,5)
.
```

The SDO_UTIL.CIRCLE_POLYGON function is trickier because it generates 8307 longitude/latitude data. So the above function, based as it is on projected data, can only give us a basic approximation. The returned radius is going to be in decimal degrees so we need to convert it via SDO_GEOM.SDO_DISTANCE.

``` SELECT roundOrdinates(mdsys.sdo_util.CIRCLE_POLYGON(10,10,10,1),8) AS circle
FROM dual;
.
CIRCLE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
MDSYS.SDO_GEOMETRY(2003,8307,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),MDSYS.SDO_ORDINATE_ARRAY(10,9.99991007,10.00006457,9.99993641,10.00009132,10,10.00006457,10.00006359,10,10.00008993,9.99993543,10.00006359,9.99990868,10,9.99993543,9.99993641,10,9.99991007))
.
SELECT round(a.centre.x,8) AS cx,
round(a.centre.y,8) AS cy,
round(sdo_geom.sdo_distance(mdsys.sdo_geometry(2001,8307,mdsys.sdo_point_type(a.centre.x,           a.centre.y,NULL),NULL,NULL),
mdsys.sdo_geometry(2001,8307,mdsys.sdo_point_type(a.centre.x+a.centre.z,a.centre.y,NULL),NULL,NULL),
FROM (SELECT findCircle(roundOrdinates(mdsys.sdo_util.CIRCLE_POLYGON(10,10,10,1),8)) AS centre
FROM dual ) a;
.
----------- ----------- ------
10.00000068 10.00000071 9.938
.
-- Or.....
SELECT round(f.centre.x,8) AS cx,
round(f.centre.y,8) AS cy,
FROM (SELECT sdo_cs.transform(mdsys.sdo_geometry(2001,28355,
mdsys.sdo_point_type(b.centre.x,
b.centre.y,
b.centre.z),
NULL,NULL),8307).sdo_point AS centre
FROM (SELECT a.geom,findCircle(sdo_cs.transform(a.geom,28355)) AS centre
FROM (SELECT mdsys.sdo_util.CIRCLE_POLYGON(147,-32,10,1) AS geom
FROM dual ) a
) b
) f;
.