Search code examples
oracle-databasegeometryoracle10goracle-spatial

create polygon with a hole as Oracle SDO_GEOMETRY from two circle geometries


In the example given in Oracle Docs, there is a way to create a polygon with a hole with the following syntax:

SDO_GEOMETRY(
    2003,  -- two-dimensional polygon
    NULL,
    NULL,
    SDO_ELEM_INFO_ARRAY(1,1003,1, 19,2003,1), -- polygon with hole
    SDO_ORDINATE_ARRAY(2,4, 4,3, 10,3, 13,5, 13,9, 11,13, 5,13, 2,11, 2,4,
        7,5, 7,10, 10,10, 10,5, 7,5)
  )

In my case, I have two SDO_GEOMETRY created as follows:

SELECT sdo_util.circle_polygon (longitude_1,
                                latitude_1,
                                r_1,                                         
                                tol)
           INTO inner_circle_geom
           FROM DUAL;


SELECT sdo_util.circle_polygon (longitude_2,
                                latitude_2,
                                r_2,                                         
                                tol)
           INTO outer_circle_geom
           FROM DUAL;

How can I create the polygon with a hole using the two geometries above?

I've tried using

...
SDO_ORDINATE_ARRAY(outer_circle_geom.sdo_ordinates, inner_circle_geom.sdo_ordinates)

But I receive the error

PLS-00306: wrong number or types of arguments in call to 'SDO_ORDINATE_ARRAY'

EDIT: the Oracle version is 10g


Solution

  • MT0's thought is right. You can use select sdo_geom.sdo_difference(
    sdo_util.circle_polygon (longitude_2, latitude_2,r_2, tol),
    sdo_util.circle_polygon (longitude_1, latitude_1,r_1, tol), tol)
    from dual;