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