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.