DMS2DD for PostGIS

Yesterday, I posted a short article on Converting Google Earth Formatted Longitude/Latitude points to decimal degrees. The Google Earth longitude/latitude values are expressed as a string containing degree, minute and seconds values with text separators as follows:

The function described was written in PL/SQL for Oracle.

Regina Obe emailed me today as she had read the article and had converted it to PostGIS (supplying me a copy).

You will notice, in the code that follows, that PL/SQL and PL/PgSQL are very similar, making conversion much easier than you might expect.

 CREATE OR REPLACE FUNCTION DMS2DD(strDegMinSec varchar)
     RETURNS numeric
     AS
     $$
     DECLARE
        i               numeric;
        intDmsLen       numeric;          -- Length of original string
        strCompassPoint Char(1);
        strNorm         varchar(16) = ''; -- Will contain normalized string
        strDegMinSecB   varchar(100);
        blnGotSeparator integer;          -- Keeps track of separator sequences
        arrDegMinSec    varchar[];        -- TYPE stringarray is table of varchar(2048) ;
        dDeg            numeric := 0;
        dMin            numeric := 0;
        dSec            numeric := 0;
        strChr          Char(1);
     BEGIN
        -- Remove leading and trailing spaces
        strDegMinSecB := REPLACE(strDegMinSec,' ','');
        -- 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 strpos('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 strpos('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 := string_to_array(strNorm, ' ');

        --convert specified components to double
        i := array_upper(arrDegMinSec,1);
        If i >= 1 Then
           dDeg := CAST(arrDegMinSec[1] AS numeric);
        End If;
        If i >= 2 Then
           dMin := CAST(arrDegMinSec[2] AS numeric);
        End If;
        If i >= 3 Then
           dSec := CAST(arrDegMinSec[3] AS numeric);
        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 
 $$
     LANGUAGE 'plpgsql' IMMUTABLE;

 SELECT round(dms2dd('43� 0''50.60"S'),9) as latitude,
         round(dms2dd('147�12''18.20"E'),9) as longitude;

Thanks Regina.