4 Replies Latest reply: Dec 16, 2012 10:43 AM by Milan Burazor RSS

    SDO_UTIL.BEARING_TILT_FOR_POINTS in a Select statement

    Milan Burazor
      Hello all,
      I'm trying to calculate bearing (azimuth) between points in my Select statement and only similar Oracle thing that I found is procedure SDO_UTIL.BEARING_TILT_FOR_POINTS.

      How can I call this procedure in a select statement? Is there any other oracle function that I can call to do azimuth calculation?

      I'm trying to avoid to have to write my own function, if there is already one available in Oracle 11g.

      Example of my SQL statement is below. I'm calculating distance between vehicle location point and intersection point and I would like to also show if the vehicle is North, South, East or West from the intersection point and for that I need azimuth (bearing) number value.
      with VehiclesLocationsRecords as (
      select v.vehicle_number, v.message_datetime, v.vehicle_location, v.route_number
      from vehicle_location_fact v
      where v.message_datetime between '2012-12-05 14:00:00' and '2012-12-05 15:00:00' 
      and v.route_number = 25
      )
      select /*+ LEADING(v)  USE_NL(v,h)  INDEX(h intersectionpoint_spi) */ 
             v.message_datetime, v.vehicle_number, v.route_number, round(sdo_nn_distance(1)) as distance, 
             h.on_street_txt as onstreet, h.at_street_txt as atstreet 
      from intersection_point h, VehiclesLocationsRecords v
      where sdo_nn(h.intersection_point_location, v.vehicle_location, 'sdo_num_res=1 distance=100 unit=meter',1) = 'TRUE'
      order by v.message_datetime
      ;
      Thank you,
      Milan
        • 1. Re: SDO_UTIL.BEARING_TILT_FOR_POINTS in a Select statement
          Simon Greener
          Milan,

          Do we assume your data is geodetic?

          Is so, you can use the procedure as follows:
          create or replace function bearing_for_points(p_point1 in sdo_geometry,
                                                        p_point2 in sdo_geometry,
                                                        p_tolerance in number default 0.05)
          return number deterministic
          as
            bearing NUMBER;
            tilt    NUMBER;
          BEGIN
            SDO_UTIL.BEARING_TILT_FOR_POINTS(p_point1,p_point2,p_tolerance,bearing,tilt);
            return bearing;
          END;
          /
          grant execute on bearing_for_points to public;
          -- Test but convert bearing to decimal degrees
          select bearing_for_points(
              SDO_GEOMETRY(2001,8307,SDO_POINT_TYPE(-71.5, 43,  NULL),NULL,NULL), -- start_point
              SDO_GEOMETRY(2001,8307,SDO_POINT_TYPE(-71,   43.5,NULL),NULL,NULL), -- end_point
              0.05) * 180 / 3.1415926535897932384626433832795  as azimuth
          from dual;
          -- Result
          --
          AZIMUTH
          -------
          35.99483831171472622621852830950861881596
          To convert to DMS add another function...
          create or replace
          Function DD2DMS( dDecDeg in Number,
                               pDegree in NChar default '°',
                               pMinute in NChar default '''',
                               pSecond in NChar default '"' )
          Return varchar2
          IS
              sDegreeSymbol NCHAR(1) := NVL(pDegree,'°');
              sMinuteSymbol NCHAR(1) := NVL(pMinute,'''');
              sSecondSymbol NCHAR(1) := NVL(pSecond,'"');
              iDeg          Integer;
              iMin          Integer;
              dSec          Number;
          BEGIN
              iDeg := Trunc(dDecDeg);
              iMin := Trunc((Abs(dDecDeg) - Abs(iDeg)) * 60);
              dSec := Round((((Abs(dDecDeg) - Abs(iDeg)) * 60) - iMin) * 60, 3);
              Return TO_CHAR(iDeg) || sDegreeSymbol || TO_CHAR(iMin) || sMinuteSymbol || TO_CHAR(dSec) || sSecondSymbol;
          End DD2DMS;
          /
          grant execute on dd2dms to public;
          -- Test again...
          --
          select DD2DMS(bearing_for_points(
              SDO_GEOMETRY(2001,8307,SDO_POINT_TYPE(-71.5, 43,  NULL),NULL,NULL), -- start_point
              SDO_GEOMETRY(2001,8307,SDO_POINT_TYPE(-71,   43.5,NULL),NULL,NULL), -- end_point
              0.05) * 180 / 3.1415926535897932384626433832795) as azimuth
          from dual;
          -- Result
          --
          AZIMUTH
          -------------
          35°59'41.418" 
          HTH. If so, award points please.
          regards
          Simon
          • 2. Re: SDO_UTIL.BEARING_TILT_FOR_POINTS in a Select statement
            Milan Burazor
            Thank you Simon.
            So, there is no oracle function that I can directly call, I need to create my own function and add it to the database. Is this correct?

            Thank you,
            Milan
            • 3. Re: SDO_UTIL.BEARING_TILT_FOR_POINTS in a Select statement
              Simon Greener
              Milan,
              I'm trying to avoid to have to write my own function, if there is already one available in Oracle 11g.
              If you are on 11g and your data is geodetic then the solution I have given you will do what you want.

              If you mean your data is planar (not geodetic) then you can either:

              1. Project to a suitable geodetic system (8307) then use bearing_for_points or,
              2. Write your own bearing function.

              There are a number of azimuth/bearing functions available. Here is one.
              Create or Replace
              Function ST_Azimuth(p_dE1 in number,
                                  p_dN1 in number,
                                  p_dE2 in number,
                                  p_dN2 in number)
              Return Number deterministic
              IS
                  dBearing Number;
                  dEast    Number;
                  dNorth   Number;
              BEGIN
                  If (p_dE1 Is Null or p_dN1 Is Null or
                      p_dE2 Is Null or p_dE2 Is null ) THEN
                     Return Null;
                  End If;
                  If ( (p_dE1 = p_dE2) And (p_dN1 = p_dN2) ) Then
                     Return Null;
                  End If;
                  dEast  := p_dE2 - p_dE1;
                  dNorth := p_dN2 - p_dN1;
                  If ( dEast = 0 ) Then
                      dBearing := case when ( dNorth < 0 ) 
                                       Then codesys.CONSTANTS.c_PI
                                       Else 0
                                   End;
                  Else
                      dBearing := -aTan(dNorth / dEast) + codesys.CONSTANTS.c_PI / 2;
                  End If;
                  Return case when ( dEast < 0 ) 
                              Then dBearing + codesys.CONSTANTS.c_PI 
                              Else dBearing 
                          End;
              End ST_Azimuth;
              /
              
              create or replace 
              Function degrees(p_radians in number)
                return number deterministic
              Is
              Begin
                return p_radians * (180.0 / 3.14159265358979);
              End degrees;
              /
              
              select DD2DMS(Degrees(ST_Azimuth(300,100,400,200))) from dual;
              AZIMUTH
              -----------
              44°59'60"
              regards
              Simon

              Edited by: Simon Greener on Dec 16, 2012 6:11 PM
              • 4. Re: SDO_UTIL.BEARING_TILT_FOR_POINTS in a Select statement
                Milan Burazor
                Thank you Simon.
                I understand your answer and the first function (bearing_for_points) that you sent me will do the task. I work for people that prefer to use "original" oracle functions and procedures that come with the installation, so they don't need to maintain additional custom built functions. I can tell them now that there is no "original" oracle function for calculating bearing (azimuth) between two points that I can use directly in the SQL statement and that I have to wrap an original stored procedure into a custom built function and then I can call it from SQL statement directly.
                Thank you again for your help. I marked your answer as correct and marked my question as answered.

                Regards,
                Milan