How to select an MDSYS. SDO_GEOMETRY with more than 1 polygon?

Question

I have the following MDYS object. SDO_GEOMETRY with 2 polygons:

MDSYS.SDO_GEOMETRY(
  2003,
  32723,
  NULL,
  MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1,11,1003,1),
  MDSYS.SDO_ORDINATE_ARRAY(
-47.907596018183,-15.823197195244,
-47.90770574636,-15.824928365549,
-47.906349238544,-15.82507008862,
-47.906743123853,-15.822948594956,
-47.907596018183,-15.823197195244, --- end of coordinate 1
-47.910025764977,-15.822772155044,
-47.910292291122,-15.827300809061,
-47.908579370537,-15.827056272436,
-47.908374813318,-15.823197247034,
-47.910025764977,-15.822772155044))

When I use the following query, I return only the first polygon:

SELECT
cd_geomet,
cd_empr,
cd_estud,
cd_demand,
SDO_UTIL.TO_WKTGEOMETRY(ge_geomet) AS ge_geomet,
dt_cad
FROM
    "ALG"."TS_ALG_GEOMET"
WHERE
    "CD_DEMAND" = 589;`

Here's the query return:

POLYGON ((-47.907596018183 -15.823197195244, 
        -47.90770574636 -15.824928365549, 
        -47.906349238544 -15.82507008862, 
        -47.906743123853 -15.822948594956, 
        -47.907596018183 -15.823197195244))

How do I return the 2 polygons?

I try:

SELECT
    cd_geomet,
    cd_empr,
    cd_estud,
    cd_demand,
    SDO_UTIL.TO_WKTGEOMETRY_VARCHAR(ge_geomet) AS ge_geomet,
    dt_cad
 FROM
    "ALG"."TS_ALG_GEOMET"
WHERE
    "CD_DEMAND" = 589;

However, the return is the same as the first select

When I use the query below, I get 1 element and two rings as a return, which I believe are the polygons, but I can't return them.

SELECT SDO_UTIL.GETNUMELEM(ge_geomet) AS num_elem, 
SDO_UTIL.GETNUMRINGS(ge_geomet) AS num_rings
FROM ALG.TS_ALG_GEOMET WHERE CD_GLA_GEOMET = 3;

.

Answer 1

First of all, I verified your geometry using SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT. It turns out that your geometry is invalid. For tolerances >= than 0.005, ORA-13356 is returned, which means, the geometry has duplicate vertices. For tolerances <= 0.005, ORA-13368 is returned. This means, the geometry has 2 exterior rings, which is not allowed for simple polygons (gtype=2003). You can correct the geometry using sdo_util.rectify_geometry.

select SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT(sdo_util.rectify_geometry(MDSYS.SDO_GEOMETRY(
  2003,
  32723,
  NULL,
  MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1,11,1003,1),
  MDSYS.SDO_ORDINATE_ARRAY(
-47.907596018183,-15.823197195244,
-47.90770574636,-15.824928365549,
-47.906349238544,-15.82507008862,
-47.906743123853,-15.822948594956,
-47.907596018183,-15.823197195244, --- end of coordinate 1
-47.910025764977,-15.822772155044,
-47.910292291122,-15.827300809061,
-47.908579370537,-15.827056272436,
-47.908374813318,-15.823197247034,
-47.910025764977,-15.822772155044)), 0.0005), 0.0005) from dual

Now, you can extract either one of the 2 individual rings in the polygon using the next query:

with geom_tab as (
select 
sdo_util.rectify_geometry(MDSYS.SDO_GEOMETRY(
  2003,
  32723,
  NULL,
  MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1,11,1003,1),
  MDSYS.SDO_ORDINATE_ARRAY(
-47.907596018183,-15.823197195244,
-47.90770574636,-15.824928365549,
-47.906349238544,-15.82507008862,
-47.906743123853,-15.822948594956,
-47.907596018183,-15.823197195244, --- end of coordinate 1
-47.910025764977,-15.822772155044,
-47.910292291122,-15.827300809061,
-47.908579370537,-15.827056272436,
-47.908374813318,-15.823197247034,
-47.910025764977,-15.822772155044)), 0.0005) as geom from dual
) 
select sdo_util.extract(geom, 2) from geom_tab  -- 2nd parameter is the number of the ring

Just try it out.