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_AddPoint/ST_AddPoint 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_AddPoint function that “Adds a point to a LineString before point “. The IBM Spatial Data Blade function is SE_VertexAppend (ST_LineString,ST_Point) which “appends a vertex to the end of an ST_LineString”. Because 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_AddPoint
* precis : Adds a point to a MultiPoint, LineString or MultiLineString geometry before point <p_position> (1-based index: Set to -1/NULL for appending.
* version : 1.0
* usage : FUNCTION SDO_AddPoint(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
* paramtype : p_geometry : mdsys.sdo_geometry
* param : p_point : Actual point coordinates to be inserted
* paramtype : p_area : number
* param : p_position : Position before which point is inserted. If NULL or -1 the point is appended to whole geometry.
* paramType : p_position : Number
* return : input geometry with new point added.
* 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_AddPoint(p_geometry IN MDSYS.SDO_Geometry,
p_point IN MDSYS.Vertex_Type,
p_position IN Number )
Return MDSYS.SDO_Geometry Deterministic;
/* ST_* Wrappers */
Function ST_AddPoint(p_geometry IN MDSYS.St_Geometry,
p_point IN MDSYS.ST_Point,
p_position IN Number )
Return MDSYS.ST_Geometry Deterministic;
Here is the main body of the functions.
— Adds a point to a MultiPoint, LineString or MultiLinestring geometry before point (1-based index: Set to -1/NULL for appending.
Function SDO_AddPoint(p_geometry IN MDSYS.SDO_Geometry,
p_point IN MDSYS.Vertex_Type,
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 Number;
NULL_GEOMETRY EXCEPTION;
NOT_A_LINESTRING EXCEPTION;
IS_COMPOUND EXCEPTION;
NULL_POINT 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_A_LINESTRING;
End If;
If ( p_point is null ) Then
RAISE NULL_POINT;
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</acronym>;
v_elem_info := p_geometry.sdo_elem_info;
If ( p_position is NULL or p_position <= 0 ) Then
v_ordinates := p_geometry.sdo_ordinates;
ADD_Coordinate( v_ordinates, v_dims, p_point );
Else
— First insert the point at the right point in the ordinate array
SELECT column_value
BULK COLLECT INTO v_ordinates
FROM (SELECT case when Ceil(rownum/v_dims) = p_position then (p_position+0.5) else Ceil(rownum/v_dims) end as coord,
rownum – (Ceil(rownum/v_dims) – 1) * v_dims as ord,
a.column_value
FROM TABLE a
UNION ALL
SELECT p_position as coord,
rownum as ord,
v.column_value
FROM TABLE) v
WHERE rownum <= v_dims
)
ORDER BY coord,ord;
— Now, modify sdo_elem_info if needed
If ( GetNumElem(p_geometry) > 1 ) Then
SELECT case when e.elem = 1
and ( e.elem_value > 1 And e.elem_value > e.new_ord_position )
then e.elem_value + v_dims
when e.elem = 3
then /* If this is a multi-point geometry, add one to the point count in interpretation field*/
case when (<acronym title="e.elem_value,1">LAG</acronym> 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
BULK COLLECT INTO v_elem_info
FROM (SELECT rownum as rin,
(( p_position – 1 ) * v_dims) + 1 as new_ord_position,
rownum – (Ceil(rownum/3) – 1) * 3 as elem,
a.column_value as elem_value
FROM TABLE a
) e;
End If;
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_A_LINESTRING THEN
raise_application_error(CODESYS.CONSTANTS.c_i_not_line,CODESYS.CONSTANTS.c_s_not_line,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 NULL_POINT THEN
raise_application_error(-20001,‘p_point is null’,true);
RETURN p_geometry;
End SDO_AddPoint;
/* ST_* Wrappers */
Function ST_AddPoint(p_geometry IN MDSYS.ST_Geometry,
p_point IN MDSYS.ST_Point,
p_position IN Number )
Return MDSYS.ST_Geometry
Is
Begin
Return MDSYS.ST_GEOMETRY.FROM_SDO_GEOM(
CODESYS.GEOM.SDO_AddPoint(p_geometry.Get_Sdo_Geom(),
mdsys.vertex_type(p_point.ST_X(),
p_point.ST_Y(),
NULL, /* p_point.ST_Z() */
NULL, /* p_point.ST_M() */
null),
p_position));
End ST_AddPoint;
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 update of SDO_POINT structure with valid ordinate values
select geom.SDO_AddPoint(mdsys.SDO_Geometry(2001,null,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;
Error starting at line 1 in command:
select geom.SDO_AddPoint(mdsys.SDO_Geometry(2001,null,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
Error report:
SQL Error: ORA-20116: Input geometry is not a linestring
ORA-06512: at "CODESYS.GEOM", line 4459
ORA-06510: PL/SQL: unhandled user-defined exception
select Geom.SDO_AddPoint(Geom.SDO_SetPoint(b.the_geom,b.the_point,1),b.the_point,NULL)
as setGeom
from (select 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;
Error starting at line 6 in command:
select Geom.SDO_AddPoint(Geom.SDO_SetPoint(b.the_geom,b.the_point,1),b.the_point,NULL)
as setGeom
from (select 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
Error report:
SQL Error: ORA-20116: Input geometry is not a linestring
ORA-06512: at "CODESYS.GEOM", line 4459
ORA-06510: PL/SQL: unhandled user-defined exception
-- Add before first point in 2D multipoint
select geom.SDO_SetPoint(mdsys.SDO_Geometry(2005,null,null,
sdo_elem_info_array(1,1,3),
sdo_ordinate_array(1.1,1.3,2.0,2.1,3.4,3.5)),
mdsys.vertex_type(-1.0,-1.1,null,null,1),
1) as point
from dual;
POINT
-------------------------------------------------------------------------------------------------------------------
MDSYS.SDO_GEOMETRY(2005,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1,3),MDSYS.SDO_ORDINATE_ARRAY(-1,-1.1,2,2.1,3.4,3.5))
1 rows selected
-- Add point at end of MultiPoint
select geom.SDO_AddPoint(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)),
mdsys.vertex_type(4.5,4.6,null,null,1),
null/* Means append to end of coordinates */) as point
from dual;
POINT
------------------------------------------------------------------------------------------------------------------------------
MDSYS.SDO_GEOMETRY(2005,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1,3),MDSYS.SDO_ORDINATE_ARRAY(1.1,1.3,2.4,2.03,3.4,3.5,4.5,4.6))
1 rows selected
-- Test a linestring with the ST_AddPoint version
SELECT GEOM.ST_ADDPoint(a.linestring,a.point,null/*Will append at the end of the linestring*/).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(4.555 4.666)') as point
FROM dual) a;
ST_GEOM
------------------------------------------------------------------------------
LINESTRING (1.12345 1.3445, 2.43534 2.03998398, 3.43513 3.451245, 4.555 4.666)
1 rows selected
-- Finally test a multilinestring
-- Insert in position 1
SELECT GEOM.ST_AddPoint(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, 1.12345 1.3445, 2.43534 2.03998398, 3.43513 3.451245), (10.0 10.0, 10.0 20.0))
1 rows selected
-- Insert in position 4 (ie first coordinate of second linestring
SELECT GEOM.ST_AddPoint(a.multilinestring,a.point,4).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(4.555 4.666)') as point
FROM dual) a;
ST_GEOM
-------------------------------------------------------------------------------------------------------------
MULTILINESTRING ((1.12345 1.3445, 2.43534 2.03998398, 3.43513 3.451245), (4.555 4.666, 10.0 10.0, 10.0 20.0))
1 rows selected
-- Insert at end of second linestring
SELECT GEOM.ST_AddPoint(a.multilinestring,a.point,null).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(30 30)') as point
FROM dual) a;
ST_GEOM
-----------------------------------------------------------------------------------------------------------
MULTILINESTRING ((1.12345 1.3445, 2.43534 2.03998398, 3.43513 3.451245), (10.0 10.0, 10.0 20.0, 30.0 30.0))
1 rows selected
I hope this article is of help to someone.