Don, You've probably already solved this but perhaps you could use a plPgSQL function or two like this:
Create Function gis.DMS2DD( dDeg In Float, dMin In Float, dSec In Float) Returns Float AS $BODY$ Declare dDD Float; BEGIN dDD := ABS(dDeg) + dMin / 60::float + dSec / 3600::float; Return SIGN(dDeg) * dDD; End; $BODY$ LANGUAGE 'plpgsql' IMMUTABLE STRICT COST 100; SELECT gis.DMS2DD(-44,10,50); SELECT gis.DMS2DD(-44,00,00); Create or Replace Function gis.DD2DMS( dDecDeg in Float, pDegreeSymbol in VarChar(1), pMinuteSymbol in VarChar(1), pSecondSymbol in VarChar(1) ) Returns varchar(50) AS $BODY$ DECLARE iDeg Int; iMin Int; dSec Float; BEGIN iDeg := Trunc(dDecDeg)::Int; iMin := Trunc( (Abs(dDecDeg) - Abs(iDeg)) * 60)::Int; dSec := Round(((((Abs(dDecDeg) - Abs(iDeg)) * 60) - iMin) * 60)::numeric, 3)::float; Return trim(to_char(iDeg,'9999')) || pDegreeSymbol::text || trim(to_char(iMin,'99')) || pMinuteSymbol::text || case when dSec = 0::Float then '0' else replace(trim(to_char(dSec,'99.999')),'.000','') end || pSecondSymbol::text; END; $BODY$ LANGUAGE 'plpgsql' IMMUTABLE STRICT COST 100; select gis.DD2DMS(-44,'d','m','s'); select gis.DD2DMS(-44.5,'d','m','s'); select gis.DD2DMS(-44.577,'d','m','s'); select gis.DD2DMS(gis.DMS2DD(-44,10,50),'d','s','"'); regards Simon On Tue, 26 Jan 2010 23:01:44 +1100, Don <harte...@comcast.net> wrote:
I haven't been able to find a function that will convert a decimal latitude or longitude to degrees minutes seconds. Am I missing something? I am doing a query and get complicated expressions like lpad((round((((abs(long_wgs84)-floor(abs(long_wgs84)))*60::double precision - floor((abs(long_wgs84)-floor(abs(long_wgs84)))*60::double precision))*60::double precision)::numeric,1)*10::real)::text,3,'0') for seconds decimal rounded*10 padded to get the seconds for the following format ddmmsss. _______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
-- SpatialDB Advice and Design, Solutions Architecture and Programming, Oracle Database 10g Administrator Certified Associate; Oracle Database 10g SQL Certified Professional Oracle Spatial, SQL Server, PostGIS, MySQL, ArcSDE, Manifold GIS, FME, Radius Topology and Studio Specialist. 39 Cliff View Drive, Allens Rivulet, 7150, Tasmania, Australia. Website: www.spatialdbadvisor.com Email: si...@spatialdbadvisor.com Voice: +61 362 396397 Mobile: +61 418 396391 Skype: sggreener Longitude: 147.20515 (147° 12' 18" E) Latitude: -43.01530 (43° 00' 55" S) GeoHash: r22em9r98wg NAC:W80CK 7SWP3 _______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users