SpatialDB Advisor

Implementing SDO_VertexUpdate/ST_VertexUpdate for Oracle

I have implemented an SDO_SetPoint/ST_SetPoint (PostGIS) function for Oracle. But in so doing I discovered the SE_VertexUpdate(geom ST_LineString,old ST_Point, new ST_Point) function that IBM has implemented.

The interesting difference is that the ST_SetPoint function requires you to supply the original geometry, the new point value and the position of the point in the existing geometry that you want to update: the VertexUpdate version simply requires you to provide the before and after point values.

The problem with the PostGIS approach is apparent and explains why PostGIS has only implemented the function for LineString geometries. The reaons is that if you want to update a polygon geometry and can only supply a single point position, you can end up with a polygon that has its start and end points with different values. An example from my article shows this:

-- How to set the first and last points in a single outer shelled compound polygon polygon
select Geom.SDO_SetPoint(
          Geom.SDO_SetPoint(
                 MDSYS.SDO_Geometry(2003,null,null,
                                    SDO_ELEM_INFO_ARRAY(1,1005,2,1,2,2,5,2,1),
                                    SDO_ORDINATE_ARRAY(-0.175,9.998,-0.349,9.994,-0.523,9.986,0,0,-0.175,9.998)),
                 MDSYS.Vertex_Type(1,1,null,null,1),
                 NULL),
          MDSYS.Vertex_Type(1,1,null,null,1),
          1) as setGeom
  from dual;

SETGEOM
--------------------------------------------------------------------------------------------------------------------------------------------------
MDSYS.SDO_GEOMETRY(2003,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1005,2,1,2,2,5,2,1),MDSYS.SDO_ORDINATE_ARRAY(1,1,-0.349,9.994,-0.523,9.986,0,0,1,1))

1 rows selected

Note that you have to call the function twice AND know the position of the first and last coordinate.

The alternate approach, implemented in the IBM function, is to provide actual values of the existing point and its replacement and to replace all possible old values with new values wherever they occur.

My coding of such a function for Oracle is as follows.

First, the declarations:

  /** ----------------------------------------------------------------------------------------
  * function   : SDO_VertexUpdate
  * precis     : Replace all points of geometry with new point where they match (including Z and M)
  * version    : 1.0
  * usage      : FUNCTION SDO_VertexUpdate(p_geometry in mdsys.sdo_geometry,
  *                                         p_old_point in mdsys.vertex_type,
  *                                         p_new_point in mdsys.vertex_type )
  *                 RETURN mdsys.sdo_geometry DETERMINISTIC;
  * param      : p_geometry  : Original geometry object
  * paramtype  : p_geometry  : mdsys.sdo_geometry
  * param      : p_old_point : Actual point coordinates of an existing point
  * paramtype  : p_old_point : mdsys.vertex_type
  * param      : p_new_point : Actual point coordinates of replacement point
  * paramtype  : p_new_point : mdsys.vertex_type
  * return     : input geometry with changed point.
  * returnType : mdsys.sdo_geometry
  * history    : Simon Greener - February 2009 - 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 SDO_VertexUpdate(p_geometry  IN MDSYS.SDO_Geometry,
                            p_old_point IN MDSYS.Vertex_Type,
                            p_new_point IN MDSYS.Vertex_Type)
    Return MDSYS.SDO_Geometry Deterministic;

  /* ST_* Wrapper */
  Function ST_VertexUpdate(p_geometry  IN MDSYS.ST_Geometry,
                           p_old_point IN MDSYS.ST_Point,
                           p_new_point IN MDSYS.ST_Point)
    Return MDSYS.ST_Geometry Deterministic;

And the body:

  Function SDO_VertexUpdate(p_geometry  IN MDSYS.SDO_Geometry,
                            p_old_point IN MDSYS.Vertex_Type,
                            p_new_point IN MDSYS.Vertex_Type)
    Return MDSYS.SDO_Geometry
  Is
    v_ordinates       MDSYS.SDO_Ordinate_Array;
    v_dims            Number;
    v_gtype           PLS_Integer;
    v_sdo_point       Mdsys.SDO_Point_Type;
    v_measure_posn    Number;
    NULL_GEOMETRY     EXCEPTION;
    NULL_POINT        EXCEPTION;
    INVALID_POSITION  EXCEPTION;
  Begin
    If ( p_geometry is NULL ) Then
      raise NULL_GEOMETRY;
    End If;
    If ( p_old_point is null or 
        p_new_point is null ) Then
       RAISE NULL_POINT;
    End If;

    v_dims  := TRUNC(p_geometry.sdo_gtype/1000,0);
    v_gtype := Mod(p_geometry.sdo_gtype,10);
    v_measure_posn := MOD(trunc(p_geometry.sdo_gtype/100),10);
    v_sdo_point := p_geometry.sdo_point;
    v_ordinates := p_geometry.sdo_ordinates;
    -- If sdo_geometry is a single point coded in sdo_point, then update it
    If ( p_geometry.sdo_point is not null ) Then
      If ( ( p_old_point.x = v_sdo_point.x 
             or 
             ( v_sdo_point.x is null And p_old_point.x is null )
            )
           and
           ( p_old_point.y = v_sdo_point.y 
             or 
             ( v_sdo_point.y is null And p_old_point.y is null )
            )           and
           ( v_Dims = 2
             or
             ( v_Dims = 3 
               and 
               ( p_old_point.z = v_sdo_point.z 
                 or 
                 ( v_sdo_point.z is null And p_old_point.z is null )
               )
             )
           ) 
         ) Then
        v_sdo_point.X := p_new_point.X;
        v_sdo_point.Y := p_new_point.Y;
        v_sdo_point.Z := p_new_point.Z;
      End If;
    End If;
    If ( v_ordinates is not null ) Then
      -- Update the point in the ordinate array
      SELECT CASE e.rin
                  WHEN 1 THEN e.x
                  WHEN 2 THEN e.y
                  WHEN 3 THEN CASE v_measure_posn
                                   WHEN 0 THEN e.z 
                                   WHEN 3 THEN e.w
                               END
                  WHEN 4 THEN e.w
              END as ord
       BULK COLLECT INTO v_ordinates
      FROM (SELECT d.cin, a.rin, d.x, d.y, d.z, d.w
              FROM (SELECT LEVEL as rin
                      FROM DUAL
                    CONNECT BY LEVEL <= v_dims) a,
                   (SELECT cin,
                           case when xm = 1 and ym = 1 
                                 and ( zexists = 0 or (zm = 1 and zexists = 1)) 
                                 and ( wexists = 0 or (wm = 1 and wexists = 1 ))
                                then p_new_point.x else c.x end as x,
                           case when xm = 1 and ym = 1 
                                 and ( zexists = 0 or (zm = 1 and zexists = 1)) 
                                 and ( wexists = 0 or (wm = 1 and wexists = 1 ))
                                then p_new_point.y else c.y end as y,
                           case when xm = 1 and ym = 1 
                                 and ( zexists = 0 or (zm = 1 and zexists = 1)) 
                                 and ( wexists = 0 or (wm = 1 and wexists = 1 ))
                                then p_new_point.z else c.z end as z,
                           case when xm = 1 and ym = 1 
                                 and ( zexists = 0 or (zm = 1 and zexists = 1)) 
                                 and ( wexists = 0 or (wm = 1 and wexists = 1 ))
                                then p_new_point.w else c.w end as w
                      FROM (SELECT rownum as cin,
                                   b.x,b.y,b.z,b.w,
                                   DECODE(b.x,p_old_point.x,1,0) as xm,
                                   DECODE(b.y,p_old_point.y,1,0) as ym,
                                   DECODE(b.z,p_old_point.z,1,0) as zm,
                                   DECODE(b.w,p_old_point.w,1,0) as wm,
                                   CASE WHEN (( v_dims >= 3 And (v_measure_posn <> 3) )                 ) THEN 1 ELSE 0 END as zexists,
                                   CASE WHEN (( v_dims  = 3 And (v_measure_posn  = 3) ) Or (v_dims = 4) ) THEN 1 ELSE 0 END as wexists
                              FROM (SELECT v.x,
                                           v.y, 
                                           CASE WHEN v_measure_posn <> 3 /* If measured geometry and measure position is not 3 then Z is coded in this position */
                                                THEN v.z
                                                ELSE NULL
                                            END as z,
                                           CASE WHEN v_measure_posn = 3 /* If measured geometry and measure position is 3 then Z has been coded with W so move it */
                                                THEN v.z
                                                ELSE v.w
                                            END as w
                                      FROM TABLE(mdsys.sdo_util.GetVertices(p_geometry)) v
                                   ) b
                            ) c
                    ) d
             ) e
          order by e.cin, e.rin;
    End If;
    -- Return the updated geometry
    Return MDSYS.SDO_Geometry(p_geometry.sdo_gtype,
                              p_geometry.sdo_srid,
                              v_sdo_point,
                              p_geometry.sdo_elem_info,
                              v_ordinates);

    EXCEPTION
      WHEN NULL_GEOMETRY Then
        raise_application_error(CODESYS.CONSTANTS.c_i_null_geometry,
                                CODESYS.CONSTANTS.c_s_null_geometry,TRUE);
        RETURN p_geometry;
      WHEN NULL_POINT THEN
        raise_application_error(-20001,'p_point is null',true);
        RETURN p_geometry;
      WHEN INVALID_POSITION THEN
        raise_application_error(-20001,'invalid p_position value',true);
        RETURN p_geometry;
  End SDO_VertexUpdate;

  -- Replace point (p_position) of linestring with given point. Index is 1-based.
  Function ST_VertexUpdate(p_geometry   IN MDSYS.ST_Geometry,
                           p_old_point  IN MDSYS.ST_Point,
                           p_new_point  IN MDSYS.ST_Point )
    Return MDSYS.ST_Geometry
    Is
  Begin
      Return MDSYS.ST_GEOMETRY.FROM_SDO_GEOM(
            CODESYS.GEOM.SDO_VertexUpdate(p_geometry.Get_Sdo_Geom(),
                                          mdsys.vertex_type(p_old_point.ST_X(),
                                                            p_old_point.ST_Y(),
                                                            null, /*p_old_point.ST_Z(),*/
                                                            null, /*p_old_point.ST_M(),*/
                                                            null),
                                          mdsys.vertex_type(p_new_point.ST_X(),
                                                            p_new_point.ST_Y(),
                                                            null, /*p_new_point.ST_Z(),*/
                                                            null, /*p_new_point.ST_M(),*/
                                                            null)));
  End ST_VertexUpdate;

(At 10gR2 a point does not have Z and M values.)

Note that I continue to make extensive use of SQL in my functions. I find using SQL makes it easy to develop solutions in SQLPlus/SQL Developer and transfer into PL/SQL. It is most likely more efficient (in terms of processing resources) to manipulate the sdo_ordinate array directly in PL/SQL but the speed of development (and understanding to ordinary users) is most likely less than via straight SQL. I will investigate this aspect of algorithm development in a later article.

Now, let’s run through some examples.

-- Update 2D null point
select geom.SDO_VertexUpdate(mdsys.SDO_Geometry(2001,null,sdo_point_type(null,null,null),null,null),
                               mdsys.vertex_type(null,null,null,null,1),
                               mdsys.vertex_type(4.555,4.666,null,null,1)
                               ) as point
  from dual;

POINT
------------------------------------------------------------------------------
MDSYS.SDO_GEOMETRY(2001,null,MDSYS.SDO_POINT_TYPE(4.555,4.666,null),null,null)

1 rows selected

-- Update 3D null point
select geom.SDO_VertexUpdate(mdsys.SDO_Geometry(2001,null,sdo_point_type(null,null,null),null,null),
                               mdsys.vertex_type(null,null,null,null,1),
                               mdsys.vertex_type(4.555,4.666,10,null,1)
                               ) as point
  from dual;

POINT
------------------------------------------------------------------------------
MDSYS.SDO_GEOMETRY(2001,null,MDSYS.SDO_POINT_TYPE(4.555,4.666,10),null,null)

1 rows selected

-- Update 4D null point
select geom.SDO_VertexUpdate(mdsys.SDO_Geometry(4001,null,null,
                                mdsys.sdo_elem_info_array(1,1,1),
                                mdsys.sdo_ordinate_array(null,null,null,null)),
                               mdsys.vertex_type(null,null,null,null,1),
                               mdsys.vertex_type(4.555,4.666,5,6,1)
                               ) as point
  from dual;

POINT
-------------------------------------------------------------------------------------------------------------
MDSYS.SDO_GEOMETRY(4001,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1,1),MDSYS.SDO_ORDINATE_ARRAY(4.555,4.666,5,6))

1 rows selected

-- Update first point 
select geom.SDO_VertexUpdate(mdsys.SDO_Geometry(2002,null,null,sdo_elem_info_array(1,2,1),
                               sdo_ordinate_array(1.12345,1.3445,2.43534,2.03998398,3.43513,3.451245)),
                               mdsys.vertex_type(1.12345,1.3445,null,null,1),
                               mdsys.vertex_type(29.8,29.9,99,null,1)) as point
  from dual;

POINT
-------------------------------------------------------------------------------------------------------------------------------------------
MDSYS.SDO_GEOMETRY(2002,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(29.8,29.9,2.43534,2.03998398,3.43513,3.451245))

1 rows selected

-- Update any point 
select geom.SDO_VertexUpdate(mdsys.SDO_Geometry(3002,null,null,sdo_elem_info_array(1,2,1),
                               sdo_ordinate_array(1.12345,1.3445,9,2.43534,2.03998398,9,3.43513,3.451245,9)),
                               mdsys.vertex_type(2.43534,2.03998398,9,null,1),
                               mdsys.vertex_type(29.8,29.9,99,null,1)) as point
  from dual;

POINT
----------------------------------------------------------------------------------------------------------------------------------------------
MDSYS.SDO_GEOMETRY(3002,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(1.12345,1.3445,9,29.8,29.9,99,3.43513,3.451245,9))

1 rows selected

-- Update last point 
select geom.SDO_VertexUpdate(mdsys.SDO_Geometry(2001,null,null,sdo_elem_info_array(1,2,1),
                               sdo_ordinate_array(1.12345,1.3445,2.43534,2.03998398,3.43513,3.451245)),
                               mdsys.vertex_type(3.43513,3.451245,10,null,1),
                               mdsys.vertex_type(29.8,29.9,99,null,1)) as point
  from dual;

POINT
-----------------------------------------------------------------------------------------------------------------------------------------
MDSYS.SDO_GEOMETRY(2001,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(1.12345,1.3445,2.43534,2.03998398,29.8,29.9))

1 rows selected

--Change first point in a OGC 2D single linestring 
SELECT GEOM.ST_VertexUpdate(a.linestring,a.old_point,a.new_point).Get_WKT() as st_geom
  FROM (SELECT mdsys.OGC_LineStringFromText('LINESTRING(1.12345 1.3445,2.43534 2.03998398,3.43513 3.451245)') as linestring,
               mdsys.OGC_PointFromText('POINT(3.43513 3.451245)') as old_point,
               mdsys.OGC_PointFromText('POINT(29.8 29.9)') as new_point
          FROM dual) a;

ST_GEOM
----------------------------------------------------------
LINESTRING (1.12345 1.3445, 2.43534 2.03998398, 29.8 29.9)

1 rows selected

-- Update the last point in a simple polygon (note result is correct, as against SDO_SetPoint)
select Geom.SDO_VertexUpdate(b.the_geom,
                         mdsys.vertex_type(2,2,null,null,1),
                         mdsys.vertex_type(29,29,null,null,1)) as setGeom
  from (select sdo_geometry('POLYGON((2 2, 2 7, 12 7, 12 2, 2 2))',NULL) as the_geom
         from dual
       ) b;

SETGEOM
--------------------------------------------------------------------------------------------------------------------------
MDSYS.SDO_GEOMETRY(2003,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),MDSYS.SDO_ORDINATE_ARRAY(29,29,2,7,12,7,12,2,29,29))

1 rows selected

-- Update first point of complex polygon
select Geom.SDO_VertexUpdate(
                 SDO_GEOMETRY(2003,null,null,
                             SDO_ELEM_INFO_ARRAY(1,1005,2,1,2,2,5,2,1),
                             SDO_ORDINATE_ARRAY(-0.175,9.998,-0.349,9.994,-0.523,9.986,0,0,-0.175,9.998)),
                 mdsys.vertex_type(-0.175,9.998,null,null,1),
                 mdsys.vertex_type(1,1,null,null,1)) as UpdateGeom
  from dual;

UPDATEGEOM
--------------------------------------------------------------------------------------------------------------------------------------------------
MDSYS.SDO_GEOMETRY(2003,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1005,2,1,2,2,5,2,1),MDSYS.SDO_ORDINATE_ARRAY(1,1,-0.349,9.994,-0.523,9.986,0,0,1,1))

1 rows selected

-- MultiPolygon: Update first/last point in second geometry
select Geom.SDO_VertexUpdate(b.the_geom,
                         mdsys.vertex_type(20,20,null,null,1),
                         mdsys.vertex_type(21,21,null,null,1)).Get_WKT() as UpdateGeom
  from (select sdo_geometry('MULTIPOLYGON(((2 2, 2 7, 12 7, 12 2, 2 2)), ((20 20, 20 70, 120 70, 120 20, 20 20)) )',NULL) as the_geom
         from dual
       ) b;

UPDATEGEOM
-----------------------------------------------------------------------------------------------------------------------------
MULTIPOLYGON (((2.0 2.0, 2.0 7.0, 12.0 7.0, 12.0 2.0, 2.0 2.0)), ((21.0 21.0, 20.0 70.0, 120.0 70.0, 120.0 20.0, 21.0 21.0)))

1 rows selected

Conclusion

I have to admin that, having implemented the PostGIS *_SetPoint functions and the IBM *_VertexUpdate functions, I think that the IBM versions are far better than the PostGIS ones because one does not have to worry about the complexity of start/end points for polygon geometry elements (inner and outer rings).

I hope this article is of use to someone.

select mdsys.st_geometry(sdo_geometry('POLYGON((2 2, 2 7, 12 7, 12 2, 2 2))',NULL)).st_numinteriorring() geom
  from dual;

Error starting at line 1 in command:
select mdsys.st_geometry(sdo_geometry('POLYGON((2 2, 2 7, 12 7, 12 2, 2 2))',NULL)).st_numinteriorring() geom
  from dual
Error at Command Line:1 Column:102
Error report:
SQL Error: ORA-00904: "MDSYS"."ST_GEOMETRY"."ST_NUMINTERIORRING": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:

select a.geom.st_numinteriorring() as geom 
  from (select mdsys.st_geometry(sdo_geometry('POLYGON((2 2, 2 7, 12 7, 12 2, 2 2))',NULL)) geom
          from dual ) a;

Error starting at line 4 in command:
select a.geom.st_numinteriorring() as geom 
  from (select mdsys.st_geometry(sdo_geometry('POLYGON((2 2, 2 7, 12 7, 12 2, 2 2))',NULL)) geom
          from dual ) a
Error at Command Line:4 Column:7
Error report:
SQL Error: ORA-00904: "A"."GEOM"."ST_NUMINTERIORRING": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:

select a.geom.st_numinteriorring() as geom 
  from (select mdsys.st_polygon(sdo_geometry('POLYGON((2 2, 2 7, 12 7, 12 2, 2 2))',NULL)) geom
          from dual ) a;

GEOM                   
---------------------- 
1                      

1 rows selected

select a.geom.st_numinteriorring() as geom 
  from (select mdsys.OGC_PolygonFromText('POLYGON((2 2, 2 7, 12 7, 12 2, 2 2))',NULL) geom
          from dual ) a;

GEOM                   
---------------------- 
1                      

1 rows selected

select mdsys.OGC_PolygonFromText('POLYGON((2 2, 2 7, 12 7, 12 2, 2 2))',NULL).st_numinteriorring() geom
  from dual;

GEOM                   
---------------------- 
1                      

1 rows selected