SpatialDB Advisor
I had cause, today, to mark the location of something in Google Earth and then provide the longitude/latitude values to a website that wanted them in decimal degrees.
Here is my location as a Google Placemark Location.

Note that the longitude/latitude values are coded as formatted degrees, minutes and seconds as follows:
| Latitude | Longitude |
|---|---|
| 43° 0’50.60“S | 147°12’18.20“E |
I wrote a DMS2DD function in my PL/SQL COGO package many years ago. I decided to use it to do the conversion.
select round(cogo.dms2dd(' 43° 0''50.60"S'),9) as latitude,
round(cogo.dms2dd('147°12''18.20"E'),9) as longitude
from dual;
Here is the source code for the function.
Function DMS2DD(strDegMinSec in varchar2)
Return Number
IS
i Number;
intDmsLen Number; --Length of original string
strCompassPoint Char(1);
strNorm varchar2(16); --Will contain normalized string
strDegMinSecB varchar2(100);
blnGotSeparator integer; -- Keeps track of separator sequences
arrDegMinSec stringarray; -- TYPE stringarray is table of varchar2(2048) ;
dDeg Number := 0;
dMin Number := 0;
dSec Number := 0;
strChr Char(1);
BEGIN
-- Remove leading and trailing spaces
strDegMinSecB := REPLACE(strDegMinSec,' ',NULL);
-- assume no leading and trailing spaces?
intDmsLen := Length(strDegMinSecB);
I hope this function is useful to someone.


















<<Implementing SDO_VertexUpdate/ST_VertexUpdate for Oracle >>Calling the Oracle Spatial shapefile loader from within the Oracle database itself
Simon. Thanks for this. I’ve been looking for this. I took the liberty of converting your function to plpgsql. You are right that pl/sql and plpgsql are pretty similar so it was a relatively painless conversion.
— Regina Feb 19, 06:39 am #