Some Oriented Point Functions
A reader asked me for some help constructing and querying oriented points a few months back. The functions I created for him were one to create an oriented point from an normal point and a supplied whole circle bearing (0-360 degrees); the other was one that, given an oriented point, computed what they orientation angle was.
Here are those functions.
CREATE OR REPLACE FUNCTION Point2oriented(P_Point IN Mdsys.Sdo_Geometry, P_Degrees IN NUMBER, P_Dec_Digits IN INTEGER DEFAULT 5) RETURN mdsys.sdo_geometry AS V_Dx NUMBER; V_Dy NUMBER; V_Dec_Digits INTEGER := Nvl(P_Dec_Digits,5); v_vertices mdsys.vertex_set_type; BEGIN IF (p_point IS NULL OR p_point.get_gtype() != 1) THEN RETURN p_point; END IF; -- p_degrees is bearing clockwise from north. V_Dx := Round(Sin(Cogo.Radians(P_Degrees)),V_Dec_Digits); v_dy := Round(cos(cogo.radians(p_degrees)),v_dec_digits); v_vertices := mdsys.sdo_util.getVertices(p_point); RETURN mdsys.sdo_geometry(p_point.sdo_gtype, p_point.sdo_srid, NULL, mdsys.sdo_elem_info_array(1,1,1, p_point.get_dims()+1,1,0), CASE p_point.get_dims() WHEN 2 THEN Mdsys.Sdo_Ordinate_Array(V_Vertices(1).X,V_Vertices(1).Y,V_Dx,V_Dy) WHEN 3 THEN Mdsys.Sdo_Ordinate_Array(V_Vertices(1).X,V_Vertices(1).Y,V_Vertices(1).Z,V_Dx,V_Dy,V_Vertices(1).Z) WHEN 4 THEN mdsys.sdo_ordinate_array(v_vertices(1).x,v_vertices(1).Y,v_vertices(1).z,v_vertices(1).w,v_dx,v_dy,v_vertices(1).z,v_vertices(1).w) ELSE mdsys.sdo_ordinate_array(v_vertices(1).x,v_vertices(1).Y,v_dx,v_dy) END ); END Point2oriented; / SHOW errors -- -- CREATE OR REPLACE FUNCTION orientation(p_oriented_point IN mdsys.sdo_geometry) RETURN NUMBER deterministic AS -- Examines an oriented point and returns the angle of the orientation. v_vertices mdsys.vertex_set_type; v_dims NUMBER; BEGIN IF (p_oriented_point IS NULL) THEN RETURN NULL; END IF; IF ( NOT codesys.geom.isOrientedPoint(p_oriented_point.sdo_elem_info)) THEN RETURN NULL; END IF; v_vertices := mdsys.sdo_util.getVertices(p_oriented_point); IF (v_vertices IS NULL OR v_vertices.COUNT != 2) THEN RETURN NULL; END IF; v_dims := p_oriented_point.get_dims(); RETURN codesys.cogo.bearing(v_vertices(1).x, v_vertices(1).y, v_vertices(1).x + v_vertices(2).x, v_vertices(1).y + v_vertices(2).y); END orientation; / SHOW errors
Now let’s test them.
First create 10 random points and orientation angles and supply them to the Point2Oriented function.
SELECT substr(Sdo_Geom.Validate_Geometry(Oriented_point,0.005),1,6) AS valid, Orientation_Angle, Oriented_point FROM (SELECT Orientation_Angle, Point2oriented(Point,Orientation_Angle,3) AS Oriented_point FROM (SELECT Round(Dbms_Random.VALUE(0,359.9),1) AS Orientation_Angle, sdo_geometry(3001,NULL, SDO_POINT_TYPE( ROUND(dbms_random.VALUE(358880 - ( 10000 / 2 ), 358880 + ( 10000 / 2 )),2), ROUND(dbms_random.VALUE(5407473 - ( 5000 / 2 ), 5407473 + ( 5000 / 2 )),2), ROUND(dbms_random.VALUE(1,1000),1) ), NULL,NULL) AS point FROM Dual CONNECT BY Level <= 10 ) F ) g; Valid Orientation_Angle Oriented_point ----- ----------------- -------------------------------------------------------------------------------------------------------------------------------- TRUE 128.4 Sdo_Geometry(3001,NULL,NULL,Sdo_Elem_Info_Array(1,1,1,4,1,0),Sdo_Ordinate_Array(361032.79,5407813.53,347.8,0.784,-0.621,347.8)) TRUE 82.9 SDO_GEOMETRY(3001,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1,1,4,1,0),SDO_ORDINATE_ARRAY(362761.18,5405102.74,933.4,0.992,0.124,933.4)) TRUE 341.7 Sdo_Geometry(3001,NULL,NULL,Sdo_Elem_Info_Array(1,1,1,4,1,0),Sdo_Ordinate_Array(361807.1,5408334.49,872.7,-0.314,0.949,872.7)) TRUE 185.3 Sdo_Geometry(3001,NULL,NULL,Sdo_Elem_Info_Array(1,1,1,4,1,0),Sdo_Ordinate_Array(361067.44,5405260.26,872.6,-0.092,-0.996,872.6)) TRUE 203.4 Sdo_Geometry(3001,NULL,NULL,Sdo_Elem_Info_Array(1,1,1,4,1,0),Sdo_Ordinate_Array(360077.7,5406661.86,250,-0.397,-0.918,250)) TRUE 31.5 Sdo_Geometry(3001,NULL,NULL,Sdo_Elem_Info_Array(1,1,1,4,1,0),Sdo_Ordinate_Array(362073.68,5406605.41,788.5,0.522,0.853,788.5)) TRUE 96.9 Sdo_Geometry(3001,NULL,NULL,Sdo_Elem_Info_Array(1,1,1,4,1,0),Sdo_Ordinate_Array(355304.54,5408247.13,346.8,0.993,-0.12,346.8)) TRUE 249.6 Sdo_Geometry(3001,NULL,NULL,Sdo_Elem_Info_Array(1,1,1,4,1,0),Sdo_Ordinate_Array(362476.09,5409435.45,716,-0.937,-0.349,716)) TRUE 60.6 Sdo_Geometry(3001,NULL,NULL,Sdo_Elem_Info_Array(1,1,1,4,1,0),Sdo_Ordinate_Array(355431.23,5409586.01,155.8,0.871,0.491,155.8)) TRUE 291.8 SDO_GEOMETRY(3001,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1,1,4,1,0),SDO_ORDINATE_ARRAY(359221.46,5406013.39,728.3,-0.928,0.371,728.3)) 10 ROWS selected
Now create 10 random oriented points and query the result using the orientation function to discover the orientation angle and compare it to the supplied angle.
SELECT orientation_angle, round(cogo.degrees(orientation(Point2oriented(Point,Orientation_Angle,3))),1) AS Opoint FROM (SELECT ROUND(dbms_random.VALUE(0,359.9),1) AS orientation_Angle, sdo_geometry(2001,NULL, SDO_POINT_TYPE( ROUND(dbms_random.VALUE(358880 - ( 10000 / 2 ), 358880 + ( 10000 / 2 )),2), ROUND(dbms_random.VALUE(5407473 - ( 5000 / 2 ), 5407473 + ( 5000 / 2 )),2), NULL), NULL,NULL) AS point FROM Dual CONNECT BY Level <= 10 ) F; ORIENTATION_ANGLE OPOINT ----------------- ------ 80.5 80.5 147.6 147.6 16.3 16.3 309 309 176.8 176.8 69.5 69.5 41.4 41.4 308.9 308.9 312.6 312.6 22.5 22.5 10 ROWS selected
I hope these are useful to someone out there.