This discussion is archived
4 Replies Latest reply: Dec 16, 2012 8:43 AM by MilanBurazor RSS

SDO_UTIL.BEARING_TILT_FOR_POINTS in a Select statement

MilanBurazor Newbie
Currently Being Moderated
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 Journeyer
    Currently Being Moderated
    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
    MilanBurazor Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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
    MilanBurazor Newbie
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points