This content has been marked as final. Show 4 replies
Do we assume your data is geodetic?
Is so, you can use the procedure as follows:
To convert to DMS add another function...
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
HTH. If so, award points please.
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"
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"
Edited by: Simon Greener on Dec 16, 2012 6:11 PM
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.