Top 5 Recent Articles

ARTICLES CATEGORIES

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.