Go to content Go to navigation and search


Current PostGIS Blog Articles



RSS / Atom

Email me


Creative Commons License
All Blog Articles, Data Models and Free Source Code by Simon Greener, The SpatialDB Advisor is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License.

DMS2DD for PostGIS

Wednesday February 18 2009 at 22:27

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:

Latitude Longitude
43 050.60S 1471218.20E

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.

    RETURNS numeric
       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);
       -- 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;
                strNorm := strNorm || '.';
             End If;
             blnGotSeparator := 0;
          ElsIf strpos('neswNESW',strChr) > 0 Then -- Extract Compass Point if present
            strCompassPoint := strChr;
             -- 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 
               (dDeg + dMin / 60 + dSec / 3600));

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

latitude longitude
-43.014055556 147.205055556

Thanks Regina.

Creative Commons License

post this at del.icio.uspost this at Diggpost this at Technoratipost this at Redditpost this at Farkpost this at Yahoo! my webpost this at Windows Livepost this at Google Bookmarkspost this to Twitter

Comment [1]

I know this is rather old, but in order to save people’s time:
Be aware that in
SELECT round(dms2dd(‘43 0’‘50.60“S’),9) as latitude, round(dms2dd(‘14712’‘18.20“E’),9) as longitude;
there is a double qoute. This strictly has to be a single qoute in your data!

30 April 2014, 22:47 #