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:
Latitude | Longitude |
---|---|
43� 0�50.60�S | 147�12�18.20�E |
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;
latitude | longitude |
---|---|
-43.014055556 | 147.205055556 |
Thanks Regina.