COGO: Converting Decimal Degrees to Degrees Minutes and Seconds – and back again (PostGIS)
It is useful to have functions for converting from individual degrees, minutes and seconds to a single decimal degree value and back again.
Here are some useful plPgSql functions.
DMS2DD
Firstly, a function to convert individual degrees, minutes and seconds values to a single decimal degree value.
CREATE FUNCTION DMS2DD( p_dDeg IN FLOAT, p_dMin IN FLOAT, p_dSec IN FLOAT) RETURNS FLOAT AS $BODY$ DECLARE v_dDD FLOAT; BEGIN v_dDD := ABS(p_dDeg) + p_dMin / 60::FLOAT + p_dSec / 3600::FLOAT; RETURN SIGN(p_dDeg) * v_dDD; END; $BODY$ LANGUAGE 'plpgsql' IMMUTABLE STRICT COST 100;
Now for some examples….
SELECT DMS2DD(-44,10,50);
Result:
| dms2dd |
|---|
| -44.1805555555556 |
SELECT DMS2DD(-44,00,00);
Result:
| dms2dd |
|---|
| -44 |
There is also a function, DMS2DD that takes a string input (cf Google Earth/Maps) and returns its decimal equivalent.
DD2DMS
Secondly, a function to convert a single decimal degree value to individual degrees, minutes and seconds values as a string.
CREATE OR REPLACE FUNCTION DD2DMS( p_dDecDeg IN FLOAT, p_sDegreeSymbol IN VARCHAR(1), p_sMinuteSymbol IN VARCHAR(1), p_sSecondSymbol IN VARCHAR(1) ) RETURNS VARCHAR(50) AS $BODY$ DECLARE v_iDeg INT; v_iMin INT; v_dSec FLOAT; BEGIN v_iDeg := Trunc(p_dDecDeg)::INT; v_iMin := Trunc( (Abs(p_dDecDeg) - Abs(v_iDeg)) * 60)::INT; v_dSec := Round(((((Abs(p_dDecDeg) - Abs(v_iDeg)) * 60) - v_iMin) * 60)::NUMERIC, 3)::FLOAT; RETURN TRIM(to_char(v_iDeg,'9999')) || p_sDegreeSymbol::text || TRIM(to_char(v_iMin,'99')) || p_sMinuteSymbol::text || CASE WHEN v_dSec = 0::FLOAT THEN '0' ELSE REPLACE(TRIM(to_char(v_dSec,'99.999')),'.000','') END || p_sSecondSymbol::text; END; $BODY$ LANGUAGE 'plpgsql' IMMUTABLE STRICT COST 100;
Some examples.
SELECT DD2DMS(-44,'d','m','s');
Result:
| dd2dms |
|---|
| -44d0m0s |
SELECT DD2DMS(-44.5,'d','m','s');
Result:
| dd2dms |
|---|
| -44d30m0s |
SELECT DD2DMS(-44.577,'d','m','s');
Result:
| dd2dms |
|---|
| -44d34m37.200s |
SELECT DD2DMS(gis.DMS2DD(-44,10,50),'d','s','"');
Result:
| dd2dms |
|---|
| -44d10s50” |
DD2DMST
But what if I only want the individual degrees, minutes and seconds values from a decimal degree value?
This can be done in a number of ways eg writing three functions to extract each individual value. But the way I will do it is via a TYPE and a single function.
CREATE TYPE DMS AS ( degree INT, MINUTE INT, SECOND FLOAT ); -- Function -- CREATE OR REPLACE FUNCTION DD2DMST( p_dDecDeg IN FLOAT ) RETURNS DMS AS $BODY$ DECLARE v_dms DMS; BEGIN v_dms.degree := Trunc(p_dDecDeg)::INT; v_dms.MINUTE := Trunc( (Abs(p_dDecDeg) - Abs(v_dms.degree)) * 60)::INT; v_dms.SECOND := Round(((((Abs(p_dDecDeg) - Abs(v_dms.degree)) * 60) - v_dms.MINUTE) * 60)::NUMERIC, 3)::FLOAT; RETURN v_dms; END; $BODY$ LANGUAGE 'plpgsql' IMMUTABLE STRICT COST 100;
Some examples.
SELECT DD2DMST(-44);
Result:
| dd2dmst |
|---|
| (-44,0,0) |
SELECT (DD2DMST(-44.5)).*;
Result:
| degree | minute | second |
|---|---|---|
| -44 | 30 | 0 |
SELECT degree,MINUTE,SECOND FROM (SELECT (DD2DMST(-44.577)).* ) AS a;
Result:
| degree | minute | second |
|---|---|---|
| -44 | 34 | 37.2 |
SELECT DD2DMST(DMS2DD(-44,10,50));
Result:
| dd2dmst |
|---|
| (-44,10,50) |
I hope these are useful to someone.