SpatialDB Advisor

Implementing a SetPoint/ST_SetPoint function in Oracle

In PostGIS there is the ST_SetPoint() function that “Replace(s) point N of linestring with given point”.

There is also the SE_VertexUpdate() extension function in IBM’s spatial products. (These are the sorts of oversights in the OGC and SQL/MM standards that I have commented on before.)

There is no such function in Oracle so I thought I would implement one along the lines of the PostGIS function in my free GEOM PL/SQL package.

Here are the relevant headers:

  /** ----------------------------------------------------------------------------------------
  * function   : SDO_SetPoint
  * precis     : Replace point (p_position) of linestring with given point (1-based index)
  * version    : 1.0
  * usage      : FUNCTION SDO_SetPoint(p_geometry   in mdsys.SDO_geometry,
  *                                     p_point      in MDSYS.Vertex_Type,
  *                                     p_position   in number )
  *                 RETURN mdsys.SDO_geometry DETERMINISTIC;
  * param      : p_geometry  : Original geometry object
  * paramtype  : p_geometry  : mdsys.SDO_geometry
  * param      : p_point     : Actual point coordinates updating existing point
  * paramtype  : p_area      : number
  * param      : p_position  : Position of point to be updated. If NULL the last point is updated otherwise, if a single SDO_point, that point is updated.
  * paramType  : p_position  : Number
  * 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_SetPoint(p_geometry   IN MDSYS.SDO_Geometry, 
                        p_point      IN MDSYS.Vertex_Type, 
                        p_position   IN Number )
    Return MDSYS.SDO_Geometry Deterministic;

  /* ST_* Wrapper */
  -- Replace point (p_position) of linestring with given point. Index is 1-based.
  Function ST_SetPoint(p_geometry   IN MDSYS.ST_Geometry, 
                       p_point      IN MDSYS.ST_Point, 
                       p_position   IN Number )
    Return MDSYS.ST_Geometry Deterministic;

The PostGIS function only handles linestrings (not multilinestrings, polygons etc). Initally I too only supported linestrings with the addition of multilinestrings with a restriction for compound linestrings (that is, linestrings composed of a combination of circular arcs and vertex-connected linestrings).

Then I thought to myself, what is wrong with supporting points or multipoints? So, even though, in many ways, supporting single points coded in the SDO_POINT field of the SDO_GEOMETRY object is fairly trivial I decided to support both of these. Then I thought more about the problem with compound linestrings (and polygons, see following) and decided that I would support the update of the points in a ciruclar arc even if that point broke the arc (it is up to you, dear reader, to know what you are doing). Then, finally, I thought that I would support polygons/multipolygons but not check whether the user updated the first/last point in a inner/outer shell (1003/2003/1005/2005) and so automatically updated the related point. (I show how to handle this in the examples below. I could put in such checks, but this is verion 1.0 of the function. If a user finds the function useful and wants such an automated fix then contact me and I will update the function.)

On to the examples of how to use the function.

1. Points

-- Test update of NULL SDO_POINT structure
select geom.SDO_SetPoint(MDSYS.SDO_Geometry(2001,null,null,null,null),
                               MDSYS.Vertex_Type(4.555,4.666,null,null,1),
                               1) as point
  from dual;

POINT
--------------------------------------------
MDSYS.SDO_GEOMETRY(2001,null,null,null,null)

1 rows selected
-- Test update of SDO_POINT structure with NULL ordinate values
select geom.SDO_SetPoint(mdsys.SDO_Geometry(2001,null,MDSYS.SDO_point_type(null,null,null),null,null),
                               MDSYS.Vertex_Type(4.555,4.666,null,null,1),
                               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
-- Test update of SDO_POINT structure with valid ordinate values
select geom.SDO_SetPoint(mdsys.SDO_Geometry(2001,null,MDSYS.SDO_point_type(1.12345,2.43534,3.43513),null,null),
                               MDSYS.Vertex_Type(4.555,4.666,10,null,1),
                               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

-- Test to see if NULL p_position is correctly resolved to 1
select geom.SDO_SetPoint(mdsys.SDO_Geometry(3001,null,MDSYS.SDO_point_type(1.12345,2.43534,3.43513),null,null),
                               MDSYS.Vertex_Type(4.555,4.666,10,null,1),
                               null) as point
  from dual;

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

1 rows selected
-- Test if invalid p_position value is supplied
select geom.SDO_SetPoint(mdsys.SDO_Geometry(3001,null,MDSYS.SDO_point_type(1.12345,2.43534,3.43513),null,null),
                               MDSYS.Vertex_Type(4.555,4.666,10,null,1),
                               2) as point
  from dual;

Error starting at line 18 in command:
select geom.SDO_SetPoint(mdsys.SDO_Geometry(3001,null,MDSYS.SDO_point_type(1.12345,2.43534,3.43513),null,null),
                               MDSYS.Vertex_Type(4.555,4.666,10,null,1),
                               2) as point
  from dual
Error report:
SQL Error: ORA-20001: invalid p_position value
ORA-06512: at "CODESYS.GEOM", line 4645
ORA-06510: PL/SQL: unhandled user-defined exception

2. MultiPoints

-- Update last point 
select geom.SDO_SetPoint(MDSYS.SDO_Geometry(2001,null,null,MDSYS.SDO_elem_info_array(1,1,3),
                               MDSYS.SDO_ordinate_array(1.12345,1.3445,2.43534,2.03998398,3.43513,3.451245)),
                               MDSYS.Vertex_Type(4.555,4.666,10,null,1),
                               null) as point
  from dual;

POINT
-------------------------------------------------------------------------------------------------------------------------------------------
MDSYS.SDO_GEOMETRY(2001,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1,3),MDSYS.SDO_ORDINATE_ARRAY(1.12345,1.3445,2.43534,2.03998398,4.555,4.666))

1 rows selected
-- Update third point in 2D multipoint
select geom.SDO_SetPoint(mdsys.SDO_Geometry(2001,null,null,MDSYS.SDO_elem_info_array(1,1,3),
                               MDSYS.SDO_ordinate_array(1.12345,1.3445,2.43534,2.03998398,3.43513,3.451245)),
                               MDSYS.Vertex_Type(4.555,4.666,10,null,1),
                               3) as point
  from dual;

POINT
-------------------------------------------------------------------------------------------------------------------------------------------
MDSYS.SDO_GEOMETRY(2001,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1,3),MDSYS.SDO_ORDINATE_ARRAY(1.12345,1.3445,2.43534,2.03998398,4.555,4.666))

1 rows selected
-- Update third point in 3D multipoint
select geom.SDO_SetPoint(mdsys.SDO_Geometry(3001,null,null,MDSYS.SDO_elem_info_array(1,1,3),
                               MDSYS.SDO_ordinate_array(1.12345,1.3445,9,2.43534,2.03998398,9,3.43513,3.451245,9)),
                               MDSYS.Vertex_Type(4.555,4.666,10,null,1),
                               3) as point
  from dual;

POINT
--------------------------------------------------------------------------------------------------------------------------------------------------
MDSYS.SDO_GEOMETRY(3001,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1,3),MDSYS.SDO_ORDINATE_ARRAY(1.12345,1.3445,9,2.43534,2.03998398,9,4.555,4.666,10))

1 rows selected
-- Update non-existant point 
select geom.SDO_SetPoint(MDSYS.SDO_Geometry(2001,null,null,MDSYS.SDO_elem_info_array(1,1,3),
                               MDSYS.SDO_ordinate_array(1.12345,1.3445,2.43534,2.03998398,3.43513,3.451245)),
                               MDSYS.Vertex_Type(4.555,4.666,10,null,1),
                               10) as point
  from dual;

Error starting at line 19 in command:
select geom.SDO_SetPoint(MDSYS.SDO_Geometry(2001,null,null,MDSYS.SDO_elem_info_array(1,1,3),
                               MDSYS.SDO_ordinate_array(1.12345,1.3445,2.43534,2.03998398,3.43513,3.451245)),
                               MDSYS.Vertex_Type(4.555,4.666,10,null,1),
                               10) as point
  from dual
Error report:
SQL Error: ORA-20001: invalid p_position value
ORA-06512: at "CODESYS.GEOM", line 4645
ORA-06510: PL/SQL: unhandled user-defined exception

3. LineStrings

-- Change 3rd point in 3D multilinestring
select geom.SDO_SetPoint(MDSYS.SDO_Geometry(3006,null,null,MDSYS.SDO_elem_info_array(1,2,1,10,2,1),
                               MDSYS.SDO_ordinate_array(1.12345,1.3445,9,2.43534,2.03998398,9,3.43513,3.451245,9,10,10,9,10,20,9)),
                               MDSYS.Vertex_Type(4.555,4.666,10,null,1),
                               3) as mutlilinestring3
  from dual;

MUTLILINESTRING3
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
MDSYS.SDO_GEOMETRY(3006,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1,10,2,1),MDSYS.SDO_ORDINATE_ARRAY(1.12345,1.3445,9,2.43534,2.03998398,9,4.555,4.666,10,10,10,9,10,20,9))

1 rows selected

--Change first point in a 2D single linestring from -1 3 to -1 1
SELECT GEOM.ST_SetPoint(a.linestring,a.point,1).Get_WKT() as st_geom
  FROM (SELECT mdsys.OGC_LineStringFromText('LINESTRING(-1 2,-1 3)') as linestring,
               mdsys.OGC_PointFromText('POINT(-1 -1)') as point
          FROM dual) a;

ST_GEOM
--------------------------------
LINESTRING (-1.0 -1.0, -1.0 3.0)

1 rows selected

/* Note: PostGIS Equivalent 
SELECT ST_AsText(ST_SetPoint('LINESTRING(-1 2,-1 3)', 0, 'POINT(-1 1)'));
	   st_astext
-----------------------
 LINESTRING(-1 1,-1 3)
*/

-- Change 3rd point in 3D multilinestring
select geom.SDO_SetPoint(MDSYS.SDO_Geometry(3006,null,null,MDSYS.SDO_elem_info_array(1,2,1,10,2,1),
                               MDSYS.SDO_ordinate_array(1.12345,1.3445,9,2.43534,2.03998398,9,3.43513,3.451245,9,10,10,9,10,20,9)),
                               MDSYS.Vertex_Type(4.555,4.666,10,null,1),
                               3) as mutlilinestring3
  from dual;

MUTLILINESTRING3
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
MDSYS.SDO_GEOMETRY(3006,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1,10,2,1),MDSYS.SDO_ORDINATE_ARRAY(1.12345,1.3445,9,2.43534,2.03998398,9,4.555,4.666,10,10,10,9,10,20,9))

1 rows selected

--Change first point in a 2D single linestring from -1 3 to -1 1
SELECT GEOM.ST_SetPoint(a.multilinestring,a.point,1).Get_WKT() as st_geom
  FROM (SELECT mdsys.OGC_MultiLineStringFromText('MULTILINESTRING ((1.12345 1.3445, 2.43534 2.03998398, 3.43513 3.451245), (10.0 10.0, 10.0 20.0))') as multilinestring,
               mdsys.OGC_PointFromText('POINT(-1 -1)') as point
          FROM dual) a;

ST_GEOM
-------------------------------------------------------------------------------------------
MULTILINESTRING ((-1.0 -1.0, 2.43534 2.03998398, 3.43513 3.451245), (10.0 10.0, 10.0 20.0))

1 rows selected

4. Polygons

Some polygon tests showing the first/last coordinate in a shell issue.

-- Update the last point in a simple polygon (note result is incorrect)
select Geom.SDO_SetPoint(b.the_geom,
                         MDSYS.Vertex_Type(1,1,null,null,1),
                         NULL) as setGeom
  from (select MDSYS.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(2,2,2,7,12,7,12,2,1,1))

1 rows selected

-- Now do it properly...
select Geom.SDO_SetPoint(Geom.SDO_SetPoint(b.the_geom,b.the_point,1),b.the_point,NULL) 
         as setGeom
  from (select MDSYS.SDO_Geometry('POLYGON((2 2, 2 7, 12 7, 12 2, 2 2))',NULL) as the_geom,
               MDSYS.Vertex_Type(1,1,null,null,1) as the_point
         from dual
       ) b;

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

1 rows selected

-- 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

5. MultiPolygons

-- Update the second point in the first outer shell 
select Geom.SDO_SetPoint(b.the_geom,
                         MDSYS.Vertex_Type(2,7.5,null,null,1),
                         2).Get_WKT() as setGeom
  from (select MDSYS.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;

SETGEOM
-----------------------------------------------------------------------------------------------------------------------------
MULTIPOLYGON (((2.0 2.0, 2.0 7.5, 12.0 7.0, 12.0 2.0, 2.0 2.0)), ((20.0 20.0, 20.0 70.0, 120.0 70.0, 120.0 20.0, 20.0 20.0)))

1 rows selected

I hope this is useful to somone.