I had cause, today, to mark the location of something in Google Earth and then provide the longitude/latitude values to a website that wanted them in decimal degrees.

Here is my location as a Google Placemark Location.

Google Placemark Properties Dialog

Note that the longitude/latitude values are coded as formatted degrees, minutes and seconds as follows:

Latitude	Longitude
43° 0’50.60“S	147°12’18.20“E

I wrote a DMS2DD function in my PL/SQL COGO package many years ago. I decided to use it to do the conversion.

select round(cogo.dms2dd(' 43° 0''50.60"S'),9) as latitude,
       round(cogo.dms2dd('147°12''18.20"E'),9) as longitude
  from dual;

LATITUDE               LONGITUDE
---------------------- ----------------------
-43.014055556          147.205055556

1 rows selected

Here is the source code for the function.

    Function DMS2DD(strDegMinSec in varchar2)
    Return Number
    IS
       i               Number;
       intDmsLen       Number; --Length of original string
       strCompassPoint Char(1);
       strNorm         varchar2(16); --Will contain normalized string
       strDegMinSecB   varchar2(100);
       blnGotSeparator integer;      -- Keeps track of separator sequences
       arrDegMinSec    stringarray;  --  TYPE stringarray is table of varchar2(2048) ;
       dDeg            Number := 0;
       dMin            Number := 0;
       dSec            Number := 0;
       strChr          Char(1);
    BEGIN
       -- Remove leading and trailing spaces
       strDegMinSecB := REPLACE(strDegMinSec,' ',NULL);
       -- assume no leading and trailing spaces?
       intDmsLen := Length(strDegMinSecB);

       blnGotSeparator := 0; -- Not in separator sequence right now

       -- Loop over string, replacing anything that is not a digit or a
       -- decimal separator with
       -- a single blank
       FOR i in 1..intDmsLen LOOP
          -- Get current character
          strChr := SubStr(strDegMinSecB, i, 1);
          -- either add character to normalized string or replace
          -- separator sequence with single blank
          If InStr('0123456789,.', strChr) > 0 Then
             -- add character but replace comma with point
             If (strChr <> ',') Then
                strNorm := strNorm || strChr;
             Else
                strNorm := strNorm || '.';
             End If;
             blnGotSeparator := 0;
          ElsIf InStr('neswNESW',strChr) > 0 Then -- Extract Compass Point if present
            strCompassPoint := strChr;
          Else
             -- ensure only one separator is replaced with a blank -
             -- suppress the rest
             If blnGotSeparator = 0 Then
                strNorm := strNorm || ' ';
                blnGotSeparator := 0;
             End If;
          End If;
       End Loop;

       -- Split normalized string into array of max 3 components
       arrDegMinSec := strtok(strNorm, ' ');

       --convert specified components to double
       i := arrDegMinSec.Count;
       If i >= 1 Then
          dDeg := TO_NUMBER(arrDegMinSec(1));
       End If;
       If i >= 2 Then
          dMin := TO_NUMBER(arrDegMinSec(2));
       End If;
       If i >= 3 Then
          dSec := TO_NUMBER(arrDegMinSec(3));
       End If;

       -- convert components to value
       return (CASE WHEN UPPER(strCompassPoint) IN ('S','W') 
                    THEN -1 
                    ELSE 1 
                END 
               *
               (dDeg + dMin / 60 + dSec / 3600));
    End DMS2DD;

I hope this function is useful to someone.

Simon. Thanks for this. I’ve been looking for this. I took the liberty of converting your function to plpgsql. You are right that pl/sql and plpgsql are pretty similar so it was a relatively painless conversion.

— Regina Feb 19, 06:39 am #

<<Implementing SDO_VertexUpdate/ST_VertexUpdate for Oracle >>Calling the Oracle Spatial shapefile loader from within the Oracle database itself

Name		Remember
E-mail
http://
Message
	Textile Help