Top 5 Recent Articles
ARTICLES CATEGORIES
- Algorithms (22)
- All (399)
- Biography (1)
- Blog (44)
- Business Requirements (1)
- Commentary (1)
- Conversion (2)
- Customers (2)
- Data Models (1)
- Education (2)
- GeoRaptor (13)
- GPS (1)
- Image Processing (2)
- Import Export (8)
- Licensing (2)
- LiDAR (1)
- Linear Referencing (4)
- Manifold GIS (3)
- Mapping (1)
- MySQL Spatial (7)
- Networking and Routing (including Optimization) (5)
- Open Source (18)
- Oracle Spatial and Locator (194)
- Partitioning (1)
- PostGIS (36)
- Projections (1)
- Published Articles (1)
- qGIS (1)
- Recommendations (1)
- Services (1)
- Software Change Log (1)
- Source Code (37)
- Space Curves (9)
- Spatial Database Functions (109)
- Spatial DB comparison (1)
- Spatial XML Processing (11)
- SQL Server Spatial (92)
- Standards (3)
- Stored Procedure (17)
- Tessellation or Gridding (10)
- Tools (2)
- Topological Relationships (1)
- Training (2)
- Triangulation (2)
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 := <acronym title="p_geometry.sdo_gtype/1000,0">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 (<acronym title="e.elem_value,1">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.