SpatialDB Advisor

Implementing SDO_RemovePoint/ST_RemovePoint for Oracle

As I indicated in my article on SDO_SetPoint PostGIS has a ST_SetPoint function that can be used to “Replace(s) point N of linestring with given point”. (And that IBM’s spatial database products have the SE_VertexUpdate() functions.)

PostGIS also has the ST_RemovePoint function that “Removes point from a linestring”. The IBM Spatial Data Blade function is SE_VertexDelete (ST_Geometry, ST_Point) which “deletes a vertex from a geometry”.

Because there is no equivalent function to PostGIS’s ST_RemovePoint in Oracle, I thought I would implement one and add it to my free GEOM PL/SQL package.

Here are the relevant headers:

Here is the main body of the functions.

  — Removes point (p_position) from a linestring. Offset is 1-based.
  Function SDO_RemovePoint(p_geometry   IN MDSYS.SDO_Geometry,
                           p_position   IN Number)
    Return MDSYS.SDO_Geometry
  Is
    v_elem_info                MDSYS.SDO_Elem_Info_Array;
    v_ordinates                MDSYS.SDO_Ordinate_Array;
    v_dims                     PLS_INTEGER;
    v_gtype                    PLS_INTEGER;
    v_coords                   PLS_INTEGER; /* Coordinate count after deletion */
    v_position                 NUMBER        := p_position;
    v_end_position             PLS_INTEGER;
    NULL_GEOMETRY              EXCEPTION;
    NOT_LINESTRING_MULTIPOINT  EXCEPTION;
    IS_COMPOUND                EXCEPTION;
    INVALID_POSITION           EXCEPTION;
  Begin
    If ( p_geometry is null ) Then
      Raise NULL_GEOMETRY;
    ElsIf ( Mod(p_geometry.sdo_gtype,10) not in (2,5,6) ) Then
       RAISE NOT_LINESTRING_MULTIPOINT;
    End If;
    If isCompound(p_geometry.sdo_elem_info) Then
       RAISE IS_COMPOUND;
    End If;
    v_dims := TRUNC;
    v_gtype := Mod(p_geometry.sdo_gtype,10);
    v_ordinates := p_geometry.sdo_ordinates;
    v_elem_info := p_geometry.sdo_elem_info;


    /* Compute correct positions */
    v_end_position := CASE WHEN v_ordinates is null
                           THEN 1
                           ELSE v_ordinates.COUNT / v_dims
                         END;
    If ( v_position is NULL or v_position <= 0 ) Then
      v_position := v_end_position;
    End If;
    — Can’t update a point that does not exist….
    If Not ( v_position BETWEEN 1 AND v_end_position ) Then
       RAISE INVALID_POSITION;
    End If;

    — First remove coordinate from ordinate array
    SELECT i.ordinate
      BULK COLLECT INTO v_ordinates
      FROM (SELECT Ceil(rownum/v_dims) as coord,
                   rownum – (Ceil(rownum/v_dims) – 1) * v_dims as ord,
                   a.column_value as ordinate
              FROM TABLE a
           ) i
     WHERE i.coord <> v_position 
    ORDER BY coord,ord /* Probably not needed, but just in case */;

    — Need coordinate count for next checks
    v_coords := v_ordinates.COUNT / v_dims;

    — If a single element of a linestring then check we have enough coords */
    If ( v_coords = 1 And v_gtype = 2) Then
      raise ZERO_DIVIDE;
    End If;

    — Now, modify sdo_elem_info if needed for multi objects
    If ( v_gtype in (5,6) ) Then
      SELECT case when ( f.elem = 1 )  /* Check ordinate value of previous element, if exists, to compute its size */
                   and ( (( ( f.new_elem_value – lag(f.new_elem_value,3) over (order by f.id)  ) / v_dims ) – 1)  = 0 )
                  then 1/0 /* Not enough ordinates to create a proper line */
                  when ( f.elem = 1 /* Compute size of last element in whole geometry */
                   And (lead(f.new_elem_value,3) over (order by f.id)) is null )
                   And ( ( ( f.max_ords – f.new_elem_value + 1 ) / v_dims ) <= 1 )
                  then 1/0 /* Last element has only 1 coord */
                  else f.new_elem_value
              end as elem_Value
        BULK COLLECT INTO v_elem_info
        FROM (SELECT rownum as id,
                     e.elem,
                     e.max_ords,
                     case when e.elem = 1
                           and ( e.elem_value > 1 And e.elem_value > e.ord_position )
                          then e.elem_value – v_dims /* Calculate new start ord position for this element */
                          when e.elem = 3
                          then /* If this is a multi-point geometry, remove one from the point count in interpretation field*/
                               case when (LAG over (order by e.rin)) = 1
                                    then e.elem_value – 1
                                    else e.elem_value
                                end
                          else e.elem_value
                      end as new_elem_value
                FROM (SELECT rownum                            as rin,
                             (( v_position – 1 ) * v_dims) + 1 as ord_position,
                             ( v_coords * v_dims)              as max_ords,
                             rownum – (Ceil(rownum/3) – 1) * 3 as elem,
                             a.column_value                    as elem_value
                        FROM TABLE a
                     ) e
             ) f;
    End If;
    Return MDSYS.SDO_Geometry(p_geometry.sdo_gtype,
                              p_geometry.sdo_srid,
                              p_geometry.sdo_point,
                              v_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 NOT_LINESTRING_MULTIPOINT THEN
        raise_application_error(CODESYS.CONSTANTS.c_i_not_line,CODESYS.CONSTANTS.c_s_not_line || 
                                                      ‘ / ‘ || CODESYS.CONSTANTS.c_s_not_point,true);
        RETURN p_geometry;
      WHEN IS_COMPOUND THEN
        raise_application_error(CODESYS.CONSTANTS.c_i_cmpnd_vector,CODESYS.CONSTANTS.c_s_cmpnd_vector,true);
        RETURN p_geometry;
      WHEN ZERO_DIVIDE THEN
        raise_application_error(-20001,‘point deletion results in an invalid one vertex element.’,true);
        return p_geometry;
      WHEN INVALID_POSITION THEN
        raise_application_error(-20001,‘invalid p_position value’,true);
        RETURN p_geometry;
  End SDO_RemovePoint;


  /* ST_* Wrapper */
  Function ST_RemovePoint(p_geometry   IN MDSYS.ST_Geometry,
                          p_position   IN Number)
    Return MDSYS.ST_Geometry
  Is
  Begin
      Return MDSYS.ST_GEOMETRY.FROM_SDO_GEOM(
            CODESYS.GEOM.SDO_RemovePoint(p_geometry.Get_Sdo_Geom(),
                                         p_position));
  End ST_RemovePoint;

Note that the IBM and PostGIS function support linestrings only. The functions above support MultiPoint, LineString and MultiLineStrings. The functions do not, as yet, support Polygons, MultiPolygons or geometries with compound elements. I could, of course, modify them to allow for the handling of non-linear geometries, but I have chosen not to in version 1.0. If a user finds the functions useful and wants support for such things then contact me and I will update them.

On to the examples of how to use the functions.

-- Test single point
select geom.SDO_RemovePoint(mdsys.SDO_Geometry(3001,null,sdo_point_type(1.1,2.4,3.5),null,null),
                            1) as point
  from dual;

Error starting at line 2 in command:
select geom.SDO_RemovePoint(mdsys.SDO_Geometry(3001,null,sdo_point_type(1.1,2.4,3.5),null,null),
                            1) as point
  from dual
Error report:
SQL Error: ORA-20116: Input geometry is not a linestring / Input geometry is not a point
ORA-06512: at "CODESYS.GEOM", line 4603
ORA-06510: PL/SQL: unhandled user-defined exception

-- Test single polygon
select Geom.SDO_RemovePoint(b.the_geom,1) as RemoveGeom
  from (select sdo_geometry('POLYGON((2 2, 2 7, 12 7, 12 2, 2 2))',NULL) as the_geom
         from dual
       ) b;

Error starting at line 2 in command:
select Geom.SDO_RemovePoint(b.the_geom,1) as RemoveGeom
  from (select sdo_geometry('POLYGON((2 2, 2 7, 12 7, 12 2, 2 2))',NULL) as the_geom
         from dual
       ) b
Error report:
SQL Error: ORA-20116: Input geometry is not a linestring / Input geometry is not a point
ORA-06512: at "CODESYS.GEOM", line 4603
ORA-06510: PL/SQL: unhandled user-defined exception

-- Remove point from end of MultiPoint
select geom.SDO_RemovePoint(mdsys.SDO_Geometry(2005,null,null,
                               sdo_elem_info_array(1,1,3),
                               sdo_ordinate_array(1.1,1.3,2.4,2.03,3.4,3.5)),
                               null/* Means remove end coordinate */) as point
from dual;

POINT
--------------------------------------------------------------------------------------------------------------
MDSYS.SDO_GEOMETRY(2005,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1,2),MDSYS.SDO_ORDINATE_ARRAY(1.1,1.3,2.4,2.03))

1 rows selected

-- Remove points 0-4 in a linestring, note 0 and NULL denote is the last coord
select level-1 as point,
       geom.sdo_RemovePoint(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)),
                               LEVEL-1) as RemovedPoint
  from dual
 connect by level <= 4;

POINT                  REMOVEDPOINT
---------------------- -------------------------------------------------------------------------------------------------------------------------------------
0                      MDSYS.SDO_GEOMETRY(3002,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(1.12345,1.3445,9,2.43534,2.03998398,9))
1                      MDSYS.SDO_GEOMETRY(3002,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(2.43534,2.03998398,9,3.43513,3.451245,9))
2                      MDSYS.SDO_GEOMETRY(3002,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(1.12345,1.3445,9,3.43513,3.451245,9))
3                      MDSYS.SDO_GEOMETRY(3002,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(1.12345,1.3445,9,2.43534,2.03998398,9))

4 rows selected

-- Remove first coordinate in standard LineString
SELECT GEOM.ST_RemovePoint(a.linestring,1)/*.Get_WKT()*/ as st_geom
  FROM (SELECT mdsys.OGC_LineStringFromText('LINESTRING(1.10 1.30,2.40 2.90,3.40 3.50)') as linestring
          FROM dual) a;

ST_GEOM
----------------------------------------------------------------------------------------------------------------------------------
MDSYS.ST_LINESTRING(MDSYS.SDO_GEOMETRY(2002,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(2.4,2.9,3.4,3.5)))

1 rows selected

-- Try to remove single point from two point linestring (should get error)
SELECT GEOM.ST_RemovePoint(GEOM.ST_RemovePoint(a.linestring,1),1).Get_WKT() as st_geom
  FROM (SELECT mdsys.OGC_LineStringFromText('LINESTRING(1.10 1.30,2.40 2.90)') as linestring
          FROM dual) a;

Error starting at line 2 in command:
SELECT GEOM.ST_RemovePoint(GEOM.ST_RemovePoint(a.linestring,1),1).Get_WKT() as st_geom
  FROM (SELECT mdsys.OGC_LineStringFromText('LINESTRING(1.10 1.30,2.40 2.90)') as linestring
          FROM dual) a
Error report:
SQL Error: ORA-20001: point deletion results in an invalid one vertex element.
ORA-06512: at "CODESYS.GEOM", line 4610
ORA-01476: divisor is equal to zero
ORA-06512: at "CODESYS.GEOM", line 4624

-- Try to remove 2 points
SELECT GEOM.ST_RemovePoint(GEOM.ST_RemovePoint(a.linestring,1),1).Get_WKT() as st_geom
  FROM (SELECT mdsys.OGC_LineStringFromText('LINESTRING(1.10 1.30,2.40 2.90,3.40 3.50)') as linestring
          FROM dual) a;

Error starting at line 2 in command:
SELECT GEOM.ST_RemovePoint(GEOM.ST_RemovePoint(a.linestring,1),1).Get_WKT() as st_geom
  FROM (SELECT mdsys.OGC_LineStringFromText('LINESTRING(1.10 1.30,2.40 2.90,3.40 3.50)') as linestring
          FROM dual) a
Error report:
SQL Error: ORA-20001: point deletion results in an invalid one vertex element.
ORA-06512: at "CODESYS.GEOM", line 4610
ORA-01476: divisor is equal to zero
ORA-06512: at "CODESYS.GEOM", line 4624

-- Demonstrate removing each point in the following multipoint geometry
select level as removedPoint,
       codesys.geom.SDO_RemovePoint(
            SDO_Geometry(3006,null,null,
                  sdo_elem_info_array(1,2,1,10,2,1),
                  sdo_ordinate_array(/*Element 1*/0.2,0.3,9,1.1,1.3,9,2.4,2.5,9, 
                                     /*Element 2*/3.4,3.5,9,4.4,4.5,9,5.6,5.4,9)),
            level) as new_geom
  from dual
  connect by level <= 6;

REMOVEDPOINT           NEW_GEOM
---------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------
1                      MDSYS.SDO_GEOMETRY(3006,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1,7,2,1),MDSYS.SDO_ORDINATE_ARRAY(1.1,1.3,9,2.4,2.5,9,3.4,3.5,9,4.4,4.5,9,5.6,5.4,9))
2                      MDSYS.SDO_GEOMETRY(3006,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1,7,2,1),MDSYS.SDO_ORDINATE_ARRAY(0.2,0.3,9,2.4,2.5,9,3.4,3.5,9,4.4,4.5,9,5.6,5.4,9))
3                      MDSYS.SDO_GEOMETRY(3006,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1,7,2,1),MDSYS.SDO_ORDINATE_ARRAY(0.2,0.3,9,1.1,1.3,9,3.4,3.5,9,4.4,4.5,9,5.6,5.4,9))
4                      MDSYS.SDO_GEOMETRY(3006,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1,10,2,1),MDSYS.SDO_ORDINATE_ARRAY(0.2,0.3,9,1.1,1.3,9,2.4,2.5,9,4.4,4.5,9,5.6,5.4,9))
5                      MDSYS.SDO_GEOMETRY(3006,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1,10,2,1),MDSYS.SDO_ORDINATE_ARRAY(0.2,0.3,9,1.1,1.3,9,2.4,2.5,9,3.4,3.5,9,5.6,5.4,9))
6                      MDSYS.SDO_GEOMETRY(3006,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1,10,2,1),MDSYS.SDO_ORDINATE_ARRAY(0.2,0.3,9,1.1,1.3,9,2.4,2.5,9,3.4,3.5,9,4.4,4.5,9))

6 rows selected

-- Create two point element in multilinestring and then try and delete a single point
select level as removedPoint,
       codesys.geom.SDO_RemovePoint(
            SDO_Geometry(3006,null,null,
                  sdo_elem_info_array(1,2,1,10,2,1,19,2,1),
                  sdo_ordinate_array(/*Element 1*/0.2,0.3,9,1.1,1.3,9,2.4,2.5,9, 
                                     /*Element 2*/3.4,3.5,9,4.4,4.5,9,5.6,5.4,9,
                                     /*Element 3*/19,20,21,22)),
            level) as new_geom
  from dual
  connect by level <= 8;

Error starting at line 2 in command:
select level as removedPoint,
       codesys.geom.SDO_RemovePoint(
            SDO_Geometry(3006,null,null,
                  sdo_elem_info_array(1,2,1,10,2,1,19,2,1),
                  sdo_ordinate_array(/*Element 1*/0.2,0.3,9,1.1,1.3,9,2.4,2.5,9, 
                                     /*Element 2*/3.4,3.5,9,4.4,4.5,9,5.6,5.4,9,
                                     /*Element 3*/19,20,21,22)),
            level) as new_geom
  from dual
  connect by level <= 8
Error report:
SQL Error: ORA-20001: point deletion results in an invalid one vertex element.
ORA-06512: at "CODESYS.GEOM", line 4610
ORA-01476: divisor is equal to zero

I hope this article is of help to someone.