SpatialDB Advisor

Filtering Rings (Oracle Spatial)

I recently wrote an article Filtering Rings in Polygon.

How can we do this for Oracle?

If you recall recently I wrote an article on How to extract elements from the result of an sdo_intersection of two polygons .

Well we can combine these two generate a solution to the filtering problem.

First, note how we can construct an Oracle SDO_GEOMETRY from a WKT input:

SQL> SELECT mdsys.sdo_geometry('POLYGON((0 0,20 0,20 20,0 20,0 0),(10 10,10 11,11 11,11 10,10 10),(5 5,5 7,7 7,7 5,5 5))',null)
  2*    FROM DUAL;

MDSYS.SDO_GEOMETRY('POLYGON((0 0,20 0,20 20,0 20,0 0),(10 10,10 11,11 11,11 10,10 10),(5 5,5 7,7 7,7 5,5 5))',NULL)
------------------------------------------------------------------------------------------------
MDSYS.SDO_GEOMETRY(2003,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1,11,2003,1,21,2003,1),MDSYS.SDO_ORDINATE_ARRAY(0,0,20,0,20,20,0,20,0,0,10,10,10,11,11,11,11,10,10,10,5,5,5,7,7,7,7,5,5,5))

1 rows selected

Now there are a few approaches we could take all on the basis of the SDO_UTIL.EXTRACT function and the GetNumRings function I wrote for the article Elem_Info_Array Processing: An alternative to SDO_UTIL.GetNumRings and querying SDO_ELEM_INFO itself

1. Generate_Series

Firstly, in the article generate_series: an Oracle implementation in light of SQL Design Patterns in which I showed a number of methods for generating an integer series of numbers. We can use this function with the GetNumRings and Extract functions as follows.

SQL> SELECT GetNumRings(mdsys.sdo_geometry('POLYGON((0 0,20 0,20 20,0 20,0 0),(10 10,10 11,11 11,11 10,10 10),(5 5,5 7,7 7,7 5,5 5))',null)) as numRings 
  2*    FROM DUAL;

NUMRINGS
--------
3

1 rows selected
SQL> SELECT b.column_value as ring_no, 
  2         Sdo_Util.Extract(mdsys.sdo_geometry('POLYGON((0 0,20 0,20 20,0 20,0 0),(10 10,10 11,11 11,11 10,10 10),(5 5,5 7,7 7,7 5,5 5))',null),1,b.column_value) as ring
  3   FROM TABLE(geom.generate_series(1,
  4                              sdo_util.GetNumRings(mdsys.sdo_geometry('POLYGON((0 0,20 0,20 20,0 20,0 0),(10 10,10 11,11 11,11 10,10 10),(5 5,5 7,7 7,7 5,5 5))',null)),
  5*                             1)) b;

RING_NO                RING
---------------------- ------------------------------------------------------------------------------------------------------------------------------
1                      MDSYS.SDO_GEOMETRY(2003,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),MDSYS.SDO_ORDINATE_ARRAY(0,0,20,0,20,20,0,20,0,0))
2                      MDSYS.SDO_GEOMETRY(2003,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),MDSYS.SDO_ORDINATE_ARRAY(10,10,11,10,11,11,10,11,10,10))
3                      MDSYS.SDO_GEOMETRY(2003,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),MDSYS.SDO_ORDINATE_ARRAY(5,5,7,5,7,7,5,7,5,5))

3 rows selected

To filter the rings and rebuild we can do the following:

SQL> SELECT SDO_AGGR_UNION(SDOAGGRTYPE(c.ring,0.05)).Get_WKT() as filtered_geom
  2    FROM (SELECT b.column_value as ring_no, 
  3                 Sdo_Util.Extract(mdsys.sdo_geometry('POLYGON((10 10,10 20,20 20,20 10,10 10),(0 0,0 1,1 1,1 0,0 0),(5 5,5 7,7 7,7 5,5 5))',null),1,b.column_value) as ring
  4            FROM TABLE(geom.generate_series(
  5                            1,
  6                            GetNumRings(mdsys.sdo_geometry('POLYGON((10 10,10 20,20 20,20 10,10 10),(0 0,0 1,1 1,1 0,0 0),(5 5,5 7,7 7,7 5,5 5))',null)),
  7                            1)) b
  8          ) c
  9    WHERE c.ring_no = 1 
 10*      OR ( c.ring_no > 1 AND SDO_GEOM.SDO_AREA(c.ring,0.05) > 2);

FILTERED_GEOM
--------------------------------------------------------------------------------------------------------------------------------
(CLOB) POLYGON ((20.0 20.0, 0.0 20.0, 0.0 0.0, 20.0 0.0, 20.0 20.0))

1 rows selected

While we end up with a POLYGON you will note that the inner ring has been dissolved into the polygon. This is because we used the SDO_AGGR_UNION aggregate operator. There is no other useful aggregate operator that we can use that will maintain any inner rings. So, we will have to resort to “uglier” SQL to achieve our aim.

In the Oracle “toolkit” there is the SDO_DIFFERENCE geoprocessing operator. This will do what we want but it is not an aggregate operator. The operator acts on two single SDO_GEOMETRY objects so will will have to “serve up” to this operator the outer ring separately from the inner rings (which can be aggregated).

SQL> SELECT SDO_GEOM.SDO_DIFFERENCE(a.outer_ring,d.inner_rings,0.05).Get_WKT() as filtered_geom
  2   FROM (SELECT sdo_util.Extract(mdsys.sdo_geometry('POLYGON((0 0,20 0,20 20,0 20,0 0),(10 10,10 11,11 11,11 10,10 10),(5 5,5 7,7 7,7 5,5 5))',null),1,1) as outer_ring
  3           FROM DUAL) a,
  4        (SELECT SDO_AGGR_UNION(SDOAGGRTYPE(c.ring,0.05)) as inner_rings
  5           FROM (SELECT b.column_value as ring_no, 
  6                        Sdo_Util.Extract(mdsys.sdo_geometry('POLYGON((0 0,20 0,20 20,0 20,0 0),(10 10,10 11,11 11,11 10,10 10),(5 5,5 7,7 7,7 5,5 5))',null),1,b.column_value) as ring
  7                   FROM TABLE(geom.generate_series(
  8                           1,
  9                           GetNumRings(mdsys.sdo_geometry('POLYGON((0 0,20 0,20 20,0 20,0 0),(10 10,10 11,11 11,11 10,10 10),(5 5,5 7,7 7,7 5,5 5))',null)),
 10                           1)) b
 11                ) c
 12          WHERE c.ring_no > 1 AND SDO_GEOM.SDO_AREA(c.ring,0.05) > 2
 13*       ) d;

FILTERED_GEOM
------------------------------------------------------------------------------------------------------------------
(CLOB) POLYGON ((0.0 20.0, 0.0 0.0, 20.0 0.0, 20.0 20.0, 0.0 20.0), (5.0 7.0, 7.0 7.0, 7.0 5.0, 5.0 5.0, 5.0 7.0))

1 rows selected

Which is exactly what we want.

Put in a function….

2. Use of Geom.ExtractElementsPiped

But what if you don’t want to use generate_series? I have encapsulated element extraction into the the ExplodeGeometry or ExtractElementsPiped functions in my GEOM package. We can use these to do the function extraction as follows:

SQL> SELECT b.geometry
  2*  FROM TABLE(geom.ExtractElementsPiped(mdsys.sdo_geometry('POLYGON((10 10,10 20,20 20,20 10,10 10),(0 0,0 1,1 1,1 0,0 0),(5 5,5 7,7 7,7 5,5 5))',null),0) ) b;

GEOMETRY
------------------------------------------------------------------------------------------------------------------------------
MDSYS.SDO_GEOMETRY(2003,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),MDSYS.SDO_ORDINATE_ARRAY(0,0,20,0,20,20,0,20,0,0))
MDSYS.SDO_GEOMETRY(2003,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),MDSYS.SDO_ORDINATE_ARRAY(10,10,11,10,11,11,10,11,10,10))
MDSYS.SDO_GEOMETRY(2003,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),MDSYS.SDO_ORDINATE_ARRAY(5,5,7,5,7,7,5,7,5,5))

3 rows selected

But, as we learned above, we cannot just filter these polygons and reconstruct the original polygon with SDO_AGGR_UNION. The final solution for this approach based on the last SQL of section 1 above would be:

SQL> SELECT SDO_GEOM.SDO_DIFFERENCE(a.outer_ring,d.inner_rings,0.05).Get_WKT() as filtered_geom
  2   FROM (SELECT sdo_util.Extract(mdsys.sdo_geometry('POLYGON((0 0,20 0,20 20,0 20,0 0),(10 10,10 11,11 11,11 10,10 10),(5 5,5 7,7 7,7 5,5 5))',null),1,1) as outer_ring
  3           FROM DUAL) a,
  4        (SELECT SDO_AGGR_UNION(SDOAGGRTYPE(c.ring,0.05)) as inner_rings
  5           FROM (SELECT rownum as ring_no, 
  6                        b.geometry as ring
  7                   FROM TABLE(geom.ExtractElementsPiped(mdsys.sdo_geometry('POLYGON((0 0,20 0,20 20,0 20,0 0),(10 10,10 11,11 11,11 10,10 10),(5 5,5 7,7 7,7 5,5 5))',null),0)) b
  8                ) c
  9          WHERE c.ring_no > 1 AND SDO_GEOM.SDO_AREA(c.ring,0.05) > 2
 10*       ) d;

FILTERED_GEOM
------------------------------------------------------------------------------------------------------------------
(CLOB) POLYGON ((0.0 20.0, 0.0 0.0, 20.0 0.0, 20.0 20.0, 0.0 20.0), (5.0 7.0, 7.0 7.0, 7.0 5.0, 5.0 5.0, 5.0 7.0))

1 rows selected

3. Use of Hierarchical Query

Generate_Series provides for a range of integer number generation and is, in many ways, overkill for what we are doing here. We can use a simple hierarchical query to generate our ring numbers for SDO_UTIL.EXTRACT. However, Generate_Series (or ExtractElementsPiped etc) is needed if you are running on 9i as the following hierarchical query doesn’t work in 9i.

The final SQL if you use a hierarchical query is as follows:

SQL> SELECT SDO_GEOM.SDO_DIFFERENCE(a.outer_ring,d.inner_rings,0.05).Get_WKT() as filtered_geom
  2   FROM (SELECT sdo_util.Extract(mdsys.sdo_geometry('POLYGON((0 0,20 0,20 20,0 20,0 0),(10 10,10 11,11 11,11 10,10 10),(5 5,5 7,7 7,7 5,5 5))',null),1,1) as outer_ring
  2           FROM DUAL) a,
  3        (SELECT SDO_AGGR_UNION(SDOAGGRTYPE(c.ring,0.05)) as inner_rings
  4           FROM (SELECT level as ring_no, 
  5                        Sdo_Util.Extract(mdsys.sdo_geometry('POLYGON((0 0,20 0,20 20,0 20,0 0),(10 10,10 11,11 11,11 10,10 10),(5 5,5 7,7 7,7 5,5 5))',null),1,LEVEL) as ring
  6                   FROM DUAL
  7                 CONNECT BY LEVEL <= GetNumRings(mdsys.sdo_geometry('POLYGON((0 0,20 0,20 20,0 20,0 0),(10 10,10 11,11 11,11 10,10 10),(5 5,5 7,7 7,7 5,5 5))',null) )
  8                ) c
  9          WHERE c.ring_no > 1 AND SDO_GEOM.SDO_AREA(c.ring,0.05) > 2
 10*       ) d;

FILTERED_GEOM
------------------------------------------------------------------------------------------------------------------
(CLOB) POLYGON ((0.0 20.0, 0.0 0.0, 20.0 0.0, 20.0 20.0, 0.0 20.0), (5.0 7.0, 7.0 7.0, 7.0 5.0, 5.0 5.0, 5.0 7.0))

1 rows selected

4. Encapsulate in a Function

Regardless as to the approach taken to extract the elements, it will be cleaner to encapsulate our algorithm inside a function as follows.

  /** ----------------------------------------------------------------------------------------
  * function   : Filter_Rings
  * precis     : Function that allows a user to remove inner rings from a polygon/multipolygon based on an area value.
  * version    : 1.0
  * usage      : FUNCTION Filter_Rings(p_geometry   in mdsys.sdo_geometry,
  *                                     p_tolerance in number,
  *                                     p_area      in number,
  *                                     p_ring      in number := 0)
  *                 RETURN mdsys.sdo_geometry DETERMINISTIC;
  *               eg select Filter_Rings(mdsys.sdo_geometry('POLYGON((0 0,20 0,20 20,0 20,0 0),(10 10,10 11,11 11,11 10,10 10),(5 5,5 7,7 7,7 5,5 5))',null),
  *                                      10,
  *                                      0.05)
  *                    from dual;
  * param      : p_geometry  : Original polygon/multipolygon
  * paramtype  : p_geometry  : mdsys.sdo_geometry
  * param      : p_area      : Area in square srid units below which an inner ring is removed.
  * paramtype  : p_area      : number
  * param      : p_tolerance : Standard Oracle diminfo tolerance.
  * paramType  : p_tolerance : Number
  * Param      : p_ring      : The number of the internal ring to be removed
  * ParamType  : p_ring      : Number
  * return     : input geometry with any qualifying inner rings removed
  * returnType : mdsys.sdo_geometry
  * history    : Simon Greener - December 2008 - Original coding.
  * copyright  : Licensed under a Creative Commons Attribution-Share Alike 2.5 Australia License. (http://creativecommons.org/licenses/by-sa/2.5/au/)
  **/
  FUNCTION Filter_Rings(p_geometry  in mdsys.sdo_geometry,
                        p_tolerance in number,
                        p_area      in number,
                        p_ring      in number := 0)
    RETURN MDSYS.SDO_GEOMETRY
  IS
    v_num_rings  number;
    v_geom       mdsys.sdo_geometry;
  BEGIN
    If ( p_geometry is null
         or
         Mod(p_geometry.sdo_gtype,10) not in (3,7) ) Then
       raise_application_error(-20001,'p_geometry is null or is not a polygon',true);
    End If;
    v_num_rings := CODESYS.GEOM.GetNumRings(p_geometry);
    SELECT SDO_GEOM.SDO_DIFFERENCE(a.outer_ring,d.inner_rings,p_tolerance) as filtered_geom
      INTO v_geom
      FROM (SELECT MDSYS.SDO_UTIL.Extract(p_geometry,1,1) as outer_ring
              FROM DUAL
            ) a,
           (SELECT SDO_AGGR_UNION(SDOAGGRTYPE(c.ring,0.05)) as inner_rings
              FROM (SELECT level as ring_no,
                           MDSYS.SDO_UTIL.Extract(p_geometry,1,LEVEL) as ring
                      FROM dual
                    CONNECT BY level <= v_num_rings
                   ) c
             WHERE c.ring_no > 1
               AND ( MDSYS.SDO_GEOM.SDO_AREA(c.ring,0.05) > p_area
                     AND
                     c.ring_no <> p_ring )
           ) d;
    RETURN v_geom;
  END Filter_Rings;

I have not extended this function to allow it to handle multipolygons in order to to make this blog too complicated.

Testing it we get:

SQL> SELECT filter_rings(mdsys.sdo_geometry('POLYGON((0 0,20 0,20 20,0 20,0 0),(10 10,10 11,11 11,11 10,10 10),(5 5,5 7,7 7,7 5,5 5))',null),
  2                                        2,
  3                                        0.05).Get_WKT() as filtered_geom
  4*  FROM DUAL;

FILTERED_GEOM
------------------------------------------------------------------------------------------------------------------
(CLOB) POLYGON ((0.0 20.0, 0.0 0.0, 20.0 0.0, 20.0 20.0, 0.0 20.0), (5.0 7.0, 7.0 7.0, 7.0 5.0, 5.0 5.0, 5.0 7.0))

1 rows selected

Which is the answer we are after.

I hope this helpful to someone.