|
Converting Google Earth Formatted Longitude/Latitude points to decimal degrees
Wednesday February 18 2009 at 20:38
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.
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.
        
|
SpatialDB Advisor
Home
Comment [1]
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 · 19 February 2009, 06:39 · #